Hướng dẫn cách sử dụng hàm ISPMT tính tiền lãi của khoản vay trên Excel

Sau khi đọc bài viết “Hướng dẫn cách lập bảng tính lãi suất vay tiêu dùng theo dư nợ giảm dần trên Excel” chắc hẳn chúng ta đều thấy việc phân tích 1 dự án tài chính (hay 1 khoản đầu tư, 1 khoản vay) trên Excel khá thú vị phải không nào. Vậy bạn có biết Excel còn cung cấp cho chúng ta những hàm tài chính riêng để áp dụng cho những việc này không? Hãy cùng Học Excel Online tìm hiểu về hàm ISPMT để tính tiền lãi của khoản vay trên Excel nhé.

Xem nhanh

Ý nghĩa và cấu trúc của hàm ISPMT

Nhắc đến hàm ISPMT, chúng ta có thể thấy ngay phần PMT trong tên hàm, điều này có ý nghĩa rằng hàm này thuộc nhóm hàm PMT, dùng cho việc phân tích trả nợ gốc và lãi.

Tham khảo: Hướng dẫn cách phân tích trả nợ gốc và lãi cùng chuỗi hàm PMT trong Excel

Cụ thể hàm ISPMT có tác dụng: Tính tiền lãi của 1 khoản vay (hay khoản đầu tư) theo nguyên tắc dư nợ giảm dần.

Cấu trúc của hàm như sau:

ISPMT(rate, per, nper, pv)

Trong đó:

Rate là lãi suất của khoản vay (hay khoản đầu tư). Lãi suất phải quy đổi thống nhất đơn vị tính theo kỳ
Per: Kỳ tính lãi. Kỳ này bắt đầu tính từ 0 chứ không phải từ 1
Nper: Tổng số kỳ của khoản vay (hay khoản đầu tư)
PV: Giá trị hiện tại của khoản đầu tư (hay số tiền vay được giải ngân)

Những lưu ý khi sử dụng hàm ISPMT:

Kết quả của hàm là số âm
Phải thống nhất về đơn vị tính kỳ trong Rate, Per, Nper
Per luôn phải tính từ 0

Áp dụng hàm ISPMT vào tính tiền lãi của khoản vay trên Excel

Trở lại bài viết “Hướng dẫn cách lập bảng tính lãi suất vay tiêu dùng theo dư nợ giảm dần trên Excel“, đây là 1 bài toán mà chúng ta có thể sử dụng hàm ISPMT để tính cho cột Trả lãi mà không cần phụ thuộc vào cột Nợ đầu kỳ.

Tải file mẫu kèm theo bài viết trên tại địa chỉ: http://bit.ly/2N4p19Q

Tại cột H, chúng ta sẽ tính phần Trả lãi theo hàm ISPMT như sau:

Ô H14 đặt công thức ISPMT
Rate = giá trị ô G5 (lãi suất quy ra tháng để tương ứng theo hình thức trả lãi là theo Tháng = ô B4)
Per là kỳ tương ứng với dòng 14. Tuy nhiên kỳ xét từ 0, trong khi cột A thì kỳ bắt đầu từ 1, do đó chúng ta sẽ trừ đi 1 đơn vị
Nper là tổng số kỳ đã quy đổi về kỳ theo tháng = ô G3
PV là số tiền vay tại ô B2

Ta có =ISPMT($G$5,A14-1,$G$3,$B$2)

Vì hàm cho ra kết quả là số âm, nên chúng ta có thể chuyển về số dương bằng cách nhân với -1

H14=ISPMT($G$5,A14-1,$G$3,$B$2)*(-1)

Tiếp theo Filldown công thức từ H14 xuống các dòng còn lại, ta có kết quả giống với cách tính ở cột D, như vậy là chúng ta đã tính đúng.