Cách sử dụng chức năng PMT trong Excel 2016 - núm vú

Excel 2016 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ỳ. (Nếu bạn bỏ qua tham số 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 khoản thanh toán cho 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). Bảng sử dụng hiệu trưởng ban đầu mà bạn nhập vào ô B2, sao chép nó vào ô A7, và sau đó tăng nó lên $ 1,000 trong dải A8: A16. Bảng sử dụng lãi suất ban đầu mà bạn nhập vào ô B3, sao chép vào ô B6, và sau đó tăng tỷ lệ ban đầu này bằng 1/4 của một phần trăm trong dải ô C6: G6. Thuật ngữ trong các năm trong tế bào B4 là một yếu tố liên tục được sử dụng trong toàn bộ bảng thanh toán khoản vay.

Bảng thanh toán khoản vay sử dụng chức năng PMT để tính toán các khoản thanh toán khoản vay.

Để có được một ý tưởng dễ dàng như thế nào để xây dựng loại bảng thanh toán khoản vay này với chức năng PMT, hãy làm theo các bước sau để tạo nó trong một bảng tính mới:

Nhập tiêu đề Loan Payments trong ô A1, Principal in ô A2, Lãi suất trong ô A3, và Kỳ hạn (năm) trong ô A4.
Nhập 150.000 đô la 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ị trong B6 với công thức = B6 + 0. 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ó. Bởi 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 phải chuyển đổi tỷ lệ hàng năm đến một tỷ lệ hàng tháng bằng cách chia giá trị trong ô B6 xuống 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 chiếu tương đối B4 sang tham khảo 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 là hàng năm , nhưng bạn muốn biết thanh toán khoản vay hàng tháng , bạn cần phải chuyển đổi các khoảng thời gian hàng năm sang các tháng bằng cách nhân giá trị trong ô B4 với 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.