Hướng dẫn cách sử dụng hàm PMT tính số tiền thanh toán hàng kỳ cho khoản vay và cách phân tích trả nợ gốc và lãi cùng chuỗi hàm PMT trong Excel

Bạn cần vay một khoản tiền là 100 triệu tại thời điểm hiện tại, trả đều đặn hàng tháng trong vòng 3 năm với lãi suất 6% mỗi tháng. Vậy mỗi tháng phải trả bao nhiêu tiền? Tổng cộng sau 3 năm phải trả cả gốc và lãi là bao nhiêu tiền? Phân tích số tiền gốc, tiền lãi, còn nợ của từng kỳ?

Cách làm:

Để phân tích trả nợ, chúng ta lập 1 bảng xác định rõ từng kỳ thanh toán như sau:

• Xác định rõ từng kỳ thanh toán
• Tại mỗi kỳ sẽ xác định các chỉ tiêu: Số phải trả, Số trả gốc, Số trả lãi, số còn nợ

Mỗi chỉ tiêu sẽ được tính cụ thể như sau:

Số phải trả = hàm PMT: F2 =PMT($C$4,$C$3,$C$2)

Các vị trí của tham chiếu trong hàm PMT sẽ cần được cố định để chúng ta có thể tính tiếp cho các kỳ sau.

Trả gốc = hàm PPMT (Principal Part of the Payment)

Cấu trúc hàm PPMT(Rate, Per, Nper, PV, [FV], [Type])

Ở đây xuất hiện thêm 1 tham chiếu là Per = Tại kỳ nào => Xác định cụ thể trả gốc vào kỳ nào. Khoản tiền này sẽ được quy đổi về giá trị tương lai của khoản tiền tại thời điểm kết thúc kỳ hạn trả nợ.

G2 =PPMT($C$4,$E2,$C$3,$C$2)

Trả lãi = hàm IPMT (Interest Part of the Payment)

Cấu trúc hàm IPMT(Rate, Per, Nper, PV, [FV], [Type])

Cấu trúc này tương tự với cấu trúc hàm PPMT, chỉ khác là kết quả của hàm cho chúng ta số tiền là số lãi phải trả, quy đổi về giá trị tương lai của khoản tiền tại thời điểm kết thúc kỳ hạn trả nợ.

H2 =IPMT($C$4,E2,$C$3,$C$2)

Số còn nợ = Khoản vay + Trả gốc (vì trả gốc là số âm)

Số còn nợ = Khoản vay còn lại sau mỗi kỳ – Trả gốc mỗi kỳ

I2 = C2+G2

I3 = I2+G3

I4=I3+G4

…

=> Sau đó Filldown toàn bộ công thức cho các kỳ còn lại để xem kết quả:

Như vậy chúng ta có thể phân biệt được rõ số gốc và số lãi trong mỗi kỳ thanh toán.

Càng trả nhiều kỳ thì số lãi phải trả càng tăng

Chúng ta thử xét ví dụ trên với số kỳ trả nợ là 12 tháng xem sao:

Như vậy chỉ cần giảm số kỳ trả lãi từ 36 xuống 12 thì đã giảm được hơn 100 triệu tiền lãi rồi.

Qua bài học này, chúng ta học thêm được kỹ năng phân tích khoản vay và việc thanh toán khoản vay nhờ vào các hàm PMT trong Excel. Đây là kỹ năng cơ bản nhưng rất quan trọng mà khi làm việc ở mảng tài chính chúng ta đều cần phải nắm được.