Hướng dẫn cách xác định các khoản vay phải thanh toán trong Excel bằng hàm PMT và IPMT

Với chức năng PMT, bạn có thể nhập một vài giá trị và tính toán các khoản thanh toán hàng tháng cho số tiền vay. Nhưng khi nào thì mỗi khoản thanh toán đến hạn và tiền lãi và tiền gốc là bao nhiêu? Ví dụ về hàm PMT và IPMT này sẽ cho thấy cách tính.

Ví Dụ Về PMT Và IPMT

Ví dụ này cho thấy cách tính các khoản thanh toán hàng tháng và số tiền lãi với các hàm PMT và IPMT của Excel.

Công ty cho bạn vay có thể sử dụng các tính toán khác nhau, vì vậy hãy kiểm tra với họ để biết con số chính xác .

Chi Tiết Khoản Vay

Trong ví dụ này, chi tiết khoản vay được nhập ở đầu bảng tính và hàm PMT sẽ tính toán số tiền thanh toán hàng tháng.

Có 3 số liệu bắt buộc phải có cho hàm PMT:

• Rate  :  lãi suất hàng năm được nhập vào ô D2 (được đặt tên là LoanRate )
• Periods  : Số tháng cho thời hạn cho vay được nhập vào ô C2 (được đặt tên là LoanMths )
• Value : Số tiền cho vay được nhập vào ô B2 (được đặt tên là LoanAmt )

Các ô màu xanh lá cây khác (A2, được đặt tên là LoanStart ) là ngày thanh toán đầu tiên. Không yêu cầu chức năng PMT, nhưng sẽ được sử dụng trong bảng thanh toán.

Tính Số Tiền Thanh Toán

Hàm PMT được sử dụng trong ô E2 (có tên LoanPmt), để tính toán số tiền thanh toán hàng tháng.

• =-PMT(LoanRate/12,LoanMths,LoanAmt)

• Có một dấu trừ ở đầu công thức, để hiển thị kết quả là một số dương.
• Tỷ lệ hàng năm được chia cho 12 , để tính tỷ lệ hàng tháng

Hàm PMT tính rằng nếu chúng ta vay 5000 đô la, trong 36 tháng, với lãi suất hàng năm là 5%, khoản thanh toán hàng tháng sẽ là 149,85 đô la

Để xem nhiều chức năng khác cùa PMT Bấm vào đây.

Bảng Chi Tiết Thanh Toán

Bên dưới phép tính PMT, có một bảng Excel có tên- tblPay – chứa các công thức. Nó sẽ tính toán các chi tiết cho mỗi khoản thanh toán.

Bảng có 48 hàng cho dữ liệu hàng tháng. Nếu bạn đang thanh toán trong một khoảng thời gian dài hơn, hãy thêm nhiều hàng vào bảng. Các công thức sẽ tự động điền vào các hàng mới

Cách xác định kết quả cột Bảng thanh toán

Có 7 cột trong bảng chi tiết thanh toán. Dưới đây là tên cột và mục đích:

• Pay Date : Ngày đến hạn thanh toán
• Outstanding : Số tiền cho vay chưa trả, trước khi thanh toán được thực hiện còn gọi là dư nợ
• Mth Pmt : Số tiền thanh toán
• Interest  : Số tiền lãi trong thanh toán
• Tiền gốc : Số tiền gốc trong thanh toán
• Total Princ Paid : tổng số tiền gốc đã trả
• Pmt Num : Số tiền phải thanh toán

Cách tính số tiền phải trả

Trong cột G, công thức này tính số tiền phải thanh toán, sử dụng hàm MAX và hàm SUM :

= IF (MAX (G $ 6: G6) <LoanMths, SUM (G6,1), “”)

Trong ví dụ này, thời hạn cho vay là 36 tháng, vì vậy số thanh toán dừng ở 36, ở hàng 42.

• Trong hàng 43, MAX trong G6: G42 là 36.
• Nó nhiều hơn số trong ô LoanMths, do đó, kết quả là một chuỗi rỗng

Các Công Thức Khác

Tất cả các công thức khác kiểm tra cột Pmt Num (G), để xem nó có trống không. Nếu có, các công thức khác cũng hiển thị một chuỗi rỗng.

Bắt đầu tất cả các công thức khác bằng

=IF(G7=””,””,

Công thức thanh toán theo ngày

Trong cột A, công thức này tính theo từng ngày thanh toán, dựa trên ngày bắt đầu được nhập trong ô A1 (LoanStart):

=IF(G7=””,””,
DATE(YEAR(LoanStart),
MONTH(LoanStart)+G7-1,
DAY(LoanStart)))

Hàm DATE lấy năm, tháng và ngày từ ô LoanStart.

Trong tháng, nó cũng thêm số Thanh toán và sau đó trừ đi 1.

Vì vậy, nếu khoản thanh toán đầu tiên là ngày 1 tháng 12 năm 2019, khoản thanh toán thứ hai là ngày 1 tháng 1 năm 2020.

DATE(2019, 12+2-1,1)

Excel sẽ hiển thị rằng đó là một, thay vì tạo ra một lỗi vì chúng ta đã bảo nó hiển thị tháng 13 (12 + 2-1)

Công Thức Pmt Mth

Trong cột C, có một công thức đơn giản để hiển thị số tiền thanh toán hàng tháng, dựa trên kết quả chức năng PMT ở đầu trang tính:

=IF(G7=””,””,LoanPmt)

Công Thức Lãi Suất

Trong cột D, hàm IPMT tính toán số tiền lãi trong mỗi khoản thanh toán:

= IF (G7 = “”, “”, – IPMT (LoanRate / 12, G7, LoanMths, LoanAmt))

Hàm IPMT tương tự như hàm PMT, nhưng sử dụng số thanh toán làm đối số thứ hai của nó (G7)

Đặt một dấu trừ trước hàm để nó là một số dương.

Công thức tính cộng dồn số tiền thanh toán

Sau khi tính lãi, bạn dễ dàng tìm thấy số tiền gốc chỉ cần trừ số tiền lãi từ số tiền thanh toán.

=IF(G7=””,””,C7-D7)

Từ số tiền gốc, chúng tôi có thể tính toán số tiền chưa thanh toán. Trong mỗi hàng, đó là số tiền cho vay ban đầu, trừ tổng số tiền gốc trong các hàng ở trên .

= IF (G7 = “”, “”, LoanAmt-SUM (E$6: E6))

Và đối với cột Total Princ Paid, công thức là tổng của tất cả các khoản tiền gốc, bao gồm hàng hiện tại .

=IF(G7=””,””,SUM(E$6:E7))

Ảnh chụp màn hình này hiển thị công thức ở hàng 9, với tổng bắt đầu trong ô tiêu đề (E$6) và kết thúc bằng ô E9

Cách làm nổi bật khoản thanh toán gần nhất

Có một quy tắc định dạng có điều kiện trên bảng, do đó nó làm nổi bật khoản thanh toán mới nhất, dựa trên ngày hiện tại.

Để tạo quy tắc định dạng có điều kiện, ta làm theo các bước sau:

Chọn tất cả các ô dữ liệu trong bảng, bắt đầu từ ô A7.

Trên tab Home của Excel, bấm Conditional Formatting , sau đó bấm New Rule

Đối với Loại quy tắc( Rule Type ), chọn “Sử dụng công thức để xác định các ô cần định dạng”

Trong hộp công thức, nhập công thức INDEX / MATCH này , tham chiếu đến ô A7 (ô hiện hoạt):

• =$A7=INDEX($A$7:$A$54, MATCH(TODAY(), $A$7:$A$54,1))

Quy Tắc Hoạt Động Như Thế Nào

Trong quy tắc định dạng có điều kiện:

• MATCH tìm ngày hiện tại (chức năng TODAY ) trong cột ngày thanh toán
• Nếu không tìm thấy ngày hiện tại, MATCH trả về vị trí của ngày gần nhất trước ngày hiện tại, vì đối số thứ 3 là 1 (Ít hơn)
• Sau đó, hàm INDEX trả về ngày từ vị trí đó trong danh sách ngày thanh toán
• Nếu ngày trong hàng hiện tại khớp với ngày đó, hàng của bảng được tô sáng bằng màu cam nhạt