Video: Học Excel 2: 1.2.1. Nhóm hàm Tài chính và hàm PMT 2025
Excel 2013 PMT chức năng trên thanh công cụ thả xuống của nút Tài chính trên tab Công thức của Ribbon tính thanh toán định kỳ cho một năm, giả sử một dòng thanh toán bằng nhau và tỷ lệ lãi suất liên tục. Chức năng PMT sử dụng cú pháp sau:
= PMT (rate, nper, pv, [fv], [type])
Cũng như các chức năng tài chính chung khác, rate là lãi suất mỗi kỳ, nper là số kỳ, pv là giá trị hiện tại hoặc số tiền mà các khoản thanh toán trong tương lai là đáng giá, fv là giá trị tương lai hoặc tiền mặt số dư mà bạn muốn sau khi thanh toán cuối cùng được thực hiện (Excel sẽ giả định một giá trị tương lai là số không khi bạn bỏ qua đối số tùy chọn này như khi bạn tính các khoản thanh toán khoản vay) và type là giá trị 0 cho các khoản thanh toán được thực hiện tại cuối kỳ hoặc giá trị 1 cho thanh toán được thực hiện vào đầu kỳ.
type tùy chọn, Excel sẽ giả định thanh toán được thực hiện vào cuối kỳ) Chức năng PMT thường được sử dụng để tính toán thanh toán các khoản vay thế chấp có lãi suất cố định. Hình này cho bạn một bảng tính mẫu có chứa một bảng sử dụng chức năng PMT để tính toán khoản vay cho một loạt các mức lãi suất (từ 2.75% đến 4.00 phần trăm) và các hiệu trưởng (150.000 đến 159.000 USD).
Nhập tiêu đề Loan Payments trong ô A1, Hiệu trưởng trong ô A2, Lãi suất trong ô A3, và Kỳ hạn (năm) trong ô A4.
-
Nhập $ 150,000 vào ô B2, nhập 2. 75% vào ô B3, và nhập 30 vào ô B4.
-
Đây là những giá trị khởi đầu mà bạn tạo bảng Loan Payments.
Vị trí con trỏ ô trong B6 và sau đó xây dựng công thức = B3.
-
Bằng cách tạo ra một công thức liên kết đưa ra giá trị lãi suất ban đầu ở B3 bằng công thức, bạn đảm bảo rằng giá trị lãi suất trong B6 sẽ phản ánh ngay bất kỳ thay đổi nào bạn thực hiện trong ô B3.
Định vị con trỏ ô trong tế bào C6 và sau đó xây dựng công thức = B6 +. 25%.
-
Bằng cách thêm 1/4 phần trăm của lãi suất vào giá trị B6 với công thức = B6 +. 25% trong C6 chứ không phải tạo một chuỗi với tay lái Tự động điền, bạn đảm bảo rằng giá trị lãi suất trong ô C6 sẽ luôn luôn là 1/4 của một phần trăm lớn hơn bất kỳ giá trị lãi suất nào được nhập vào ô B6.
Kéo Fill handle trong ô C6 để mở rộng vùng lựa chọn sang phải tới ô G6 rồi thả nút chuột.
-
Định vị con trỏ ô trong tế bào A7 và sau đó xây dựng công thức = B2.
-
Một lần nữa, bằng cách sử dụng công thức = B2 để đưa hiệu trưởng ban đầu chuyển tiếp tới ô A7, bạn đảm bảo rằng ô A7 luôn có cùng giá trị với ô B2.
Vị trí con trỏ ô trong A8 hoạt động và sau đó xây dựng công thức = A7 + 1000.
-
Ở đây, bạn sử dụng công thức = A7 + 1000 chứ không phải tạo ra một loạt với tính năng Tự động điền để giá trị chính trong A8 luôn luôn là $ 1 000 lớn hơn bất kỳ giá trị nào được đặt trong ô A7.
Kéo Fill handle trong ô A8 xuống cho đến khi bạn kéo vùng lựa chọn lên ô A16 rồi thả nút chuột.
-
Trong ô B7, nhấp vào nút Chèn Chức năng trên thanh công thức, chọn Tài chính từ danh sách thả xuống Hoặc Chọn một danh mục và nhấp đúp vào chức năng PMT trong hộp Danh sách Chức năng Chọn.
-
Hộp thoại Đối số Chức năng mở ra cho phép bạn chỉ định các đối số, nper, và pv . Đảm bảo di chuyển hộp thoại Đối số hàm sang phải để không có phần nào che dấu dữ liệu trong các cột A và B của bảng tính của bạn trước khi tiếp tục các bước sau để điền các đối số. Nhấp vào ô B6 để chèn B6 vào hộp văn bản Rate và sau đó nhấn F4 hai lần để chuyển tham chiếu tương đối B6 sang tham chiếu hỗn hợp B $ 6 (cột tương đối, hàng tuyệt đối) trước khi bạn gõ / 12.
-
Bạn chuyển đổi tham chiếu tế bào tương đối B6 sang tài liệu hỗn hợp B $ 6 để Excel không
không điều chỉnh số hàng khi bạn sao chép công thức PMT xuống mỗi hàng của bảng, nhưng điều chỉnh ký tự cột khi bạn sao chép công thức qua các cột của nó. Vì lãi suất ban đầu nhập vào B3 (và sau đó chuyển sang tế bào B6) là lãi suất
hàng năm , nhưng bạn muốn biết thanh toán khoản nợ hàng tháng , bạn cần để chuyển đổi tỷ lệ hàng năm thành tỷ lệ hàng tháng bằng cách chia giá trị trong ô B6 cho đến 12. Nhấp vào hộp văn bản Nper, nhấp vào ô B4 để chèn tham chiếu ô này vào hộp văn bản này và sau đó nhấn F4 một lần để chuyển đổi tham khảo B4 với tham chiếu tuyệt đối $ B $ 4 trước khi bạn nhập * 12.
-
Bạn cần phải chuyển đổi tham chiếu tế bào tương đối B4 sang tham chiếu tuyệt đối $ B $ 4 để Excel không điều chỉnh cả số thứ tự dòng và ký tự cột khi bạn sao chép công thức PMT xuống các hàng và trên các cột của bảng.
Vì kỳ hạn ở B3 (sau đó chuyển sang tế bào B6) là khoảng
hàng năm , nhưng bạn muốn biết thanh toán khoản nợ hàng tháng , bạn cần phải chuyển đổi hàng năm vào các khoảng thời gian hàng tháng bằng cách nhân giá trị trong ô B4 lên 12. Nhấp vào hộp văn bản Pv, nhấp vào A7 để chèn tham chiếu ô này vào hộp văn bản này và sau đó nhấn F4 ba lần để chuyển tham chiếu tương đối A7 sang tham chiếu hỗn hợp $ A7 (cột tuyệt đối, hàng tương đối).
-
Bạn cần phải chuyển đổi tham chiếu tế bào tương đối A7 sang tham chiếu hỗn hợp $ A7 để Excel không điều chỉnh ký tự cột khi bạn sao chép công thức PMT qua mỗi cột của bảng, nhưng sẽ điều chỉnh số hàng khi bạn sao chép công thức trên các hàng của nó.
Nhấp OK để chèn công thức = PMT (B $ 6/12, $ B $ 4 * 12, $ A7) trong ô B7.
-
Bây giờ bạn đã sẵn sàng để sao chép công thức PMT ban đầu xuống và sau đó để điền vào toàn bộ bảng Khoản vay.
Kéo Fill handle trên ô B7 xuống cho đến khi bạn mở rộng phạm vi điền vào ô B16 rồi thả nút chuột.
-
Sau khi bạn đã sao chép công thức PMT ban đầu xuống đến ô B16, bạn đã sẵn sàng sao chép nó sang phải G16.
Kéo Fill handle ở bên phải cho đến khi bạn mở rộng phạm vi điền B7: B16 đến ô G16 và sau đó nhả nút chuột.
-
Sau khi sao chép công thức ban đầu bằng tay điền, hãy nhớ mở rộng các cột từ B đến G để hiển thị kết quả của chúng. (Bạn có thể làm điều này bằng một bước bằng cách kéo các tiêu đề của các cột này và sau đó nhấp đúp vào biên phải của cột G)
Sau khi tạo một bảng cho vay như thế này, bạn có thể thay đổi nguyên tắc bắt đầu hoặc lãi suất, cũng như thời hạn để xem những gì các khoản thanh toán sẽ được theo kịch bản khác nhau. Bạn cũng có thể bật tính toán lại Thủ công để bạn có thể kiểm soát khi bảng tính Khoản Khoản vay được tính lại.
