File Excel tính lãi vay ngân hàng: dư nợ giảm dần, trả góp đều, lãi phẳng và lãi suất thả nổi
Hướng dẫn lập file Excel tính lãi vay ngân hàng theo ba cách phổ biến: gốc trả đều với lãi trên dư nợ giảm dần, trả góp đều hằng tháng bằng hàm PMT, lãi phẳng tính trên số tiền vay ban đầu, cách quy đổi lãi phẳng sang lãi suất thực và mô phỏng giai đoạn lãi suất thả nổi, kèm ví dụ khoản vay 500 triệu.
Trả lời nhanh: để tính lãi vay ngân hàng bằng Excel, bạn cần biết khoản vay đang áp dụng cách tính nào, vì cùng một mức lãi suất nhưng số tiền lãi có thể chênh nhau rất nhiều:
- Gốc trả đều, lãi trên dư nợ giảm dần: mỗi tháng trả một phần gốc cố định; lãi = dư nợ còn lại × lãi suất tháng, nên tiền trả giảm dần theo thời gian.
- Trả góp đều (niên kim): tổng tiền trả mỗi tháng bằng nhau, tính bằng
=PMT(LaiNam/12,SoThang,-SoTienVay). - Lãi phẳng: lãi tính trên số tiền vay ban đầu suốt thời gian vay, thường gặp ở một số khoản vay tiêu dùng, trả góp; lãi suất thực cao hơn con số công bố.
Ví dụ vay 500 triệu, 9%/năm, 60 tháng: tổng lãi khoảng 114,4 triệu nếu gốc trả đều, 122,8 triệu nếu trả góp đều và 225 triệu nếu lãi phẳng. Số liệu chỉ để minh họa; điều khoản thực tế xem trong hợp đồng tín dụng của bạn.
Ô thông số đầu vào
| Ô | Tên | Ví dụ |
|---|---|---|
| B2 | SoTienVay | 500.000.000 |
| B3 | LaiNam | 9% |
| B4 | SoThang | 60 |
| B5 | NgayGiaiNgan | Ngày nhận tiền |
| B6 | LaiThang | =LaiNam/12 |
Excel tiếng Việt vẫn dùng tên hàm tiếng Anh (PMT, IPMT, RATE…). Nếu máy dùng định dạng vùng Việt Nam, dấu ngăn cách tham số là dấu chấm phẩy thay vì dấu phẩy.
Cách 1: Gốc trả đều, lãi trên dư nợ giảm dần
Bảng lịch trả nợ gồm các cột: Kỳ, Ngày trả, Dư nợ đầu kỳ, Gốc, Lãi, Tổng trả, Dư nợ cuối kỳ.
- Gốc mỗi kỳ:
=ROUND(SoTienVay/SoThang,0) - Lãi:
=ROUND(C12*LaiThang,0)(C12 là dư nợ đầu kỳ) - Tổng trả:
=D12+E12 - Dư nợ cuối kỳ:
=C12-D12; dư nợ đầu kỳ sau:=G12 - Ngày trả kỳ sau:
=EDATE(B12,1)
Với ví dụ 500 triệu, 9%/năm, 60 tháng:
| Kỳ | Dư nợ đầu kỳ | Gốc | Lãi | Tổng trả |
|---|---|---|---|---|
| 1 | 500.000.000 | 8.333.333 | 3.750.000 | 12.083.333 |
| 2 | 491.666.667 | 8.333.333 | 3.687.500 | 12.020.833 |
| … | … | … | … | … |
| 60 | 8.333.333 | 8.333.333 | 62.500 | 8.395.833 |
Tổng lãi cả khoản vay: 114.375.000đ. Kiểm tra nhanh bằng công thức: SoTienVay × LaiThang × (SoThang + 1) ÷ 2 = 500.000.000 × 0,75% × 61 ÷ 2. Đặc điểm của cách này: tháng đầu trả nhiều nhất, nên cần đảm bảo thu nhập đủ cho giai đoạn đầu.
Cách 2: Trả góp đều hằng tháng
Số tiền trả mỗi kỳ cố định: =PMT(LaiThang,SoThang,-SoTienVay) = 10.379.178đ. Trong đó:
- Lãi kỳ thứ k:
=IPMT(LaiThang,k,SoThang,-SoTienVay) - Gốc kỳ thứ k:
=PPMT(LaiThang,k,SoThang,-SoTienVay)
Tổng lãi: 10.379.178 × 60 − 500.000.000 ≈ 122,75 triệu, cao hơn cách 1 vì gốc được trả chậm hơn ở giai đoạn đầu. Đổi lại, số tiền trả tháng đầu thấp hơn khoảng 1,7 triệu, dễ cân đối ngân sách hơn.
Cách 3: Lãi phẳng và lãi suất thực
Với lãi phẳng 9%/năm, lãi mỗi tháng luôn là 500 triệu × 0,75% = 3.750.000đ, dù dư nợ đã giảm. Tổng lãi 60 tháng = 225 triệu, gần gấp đôi cách 1, và mỗi tháng trả 12.083.333đ.
Để biết lãi suất thực tương đương (tính trên dư nợ giảm dần), dùng hàm RATE:
=RATE(SoThang,-TienTraMoiThang,SoTienVay)*12
Kết quả khoảng 15,7%/năm. Vì vậy, khi so sánh các khoản vay, luôn hỏi rõ lãi tính trên dư nợ ban đầu hay dư nợ giảm dần, và đọc tổng chi phí khoản vay thay vì chỉ nhìn con số lãi suất quảng cáo.
So sánh ba cách với cùng khoản vay
| Cách tính | Trả tháng đầu | Trả tháng cuối | Tổng lãi |
|---|---|---|---|
| Gốc đều, dư nợ giảm dần | 12.083.333 | 8.395.833 | 114.375.000 |
| Trả góp đều (PMT) | 10.379.178 | 10.379.178 | ≈ 122.750.000 |
| Lãi phẳng | 12.083.333 | 12.083.333 | 225.000.000 |
Mô phỏng lãi suất ưu đãi rồi thả nổi
Nhiều khoản vay mua nhà, mua xe có giai đoạn lãi suất ưu đãi cố định, sau đó điều chỉnh định kỳ theo công thức trong hợp đồng (thường là lãi suất tham chiếu cộng biên độ). Để mô phỏng, thêm cột “Lãi suất năm” cho từng kỳ:
=IF(A12<=SoThangUuDai,LaiUuDai,LaiSauUuDai) và đổi công thức lãi thành =ROUND(C12*F12/12,0).
Ví dụ giả định với cách gốc trả đều: 12 tháng đầu 7%/năm, từ tháng 13 là 10,5%/năm. Tháng 1 trả 11.250.000đ, nhưng tháng 13 tăng lên 11.833.333đ dù dư nợ đã giảm; tổng lãi khoảng 117,5 triệu, cao hơn kịch bản 9% cố định. Hãy thử vài mức lãi sau ưu đãi (ví dụ 10%, 11%, 12%) để biết khả năng chi trả của mình trong trường hợp xấu.
Trả nợ trước hạn
Thêm cột “Trả thêm gốc” vào bảng (cách 1): dư nợ cuối kỳ =C12-D12-H12. Bạn sẽ thấy ngay tiền lãi các kỳ sau giảm bao nhiêu. Tuy nhiên, phần lớn hợp đồng tín dụng có phí trả nợ trước hạn theo tỷ lệ trên số tiền trả trước, và mức phí thường thay đổi theo năm vay; hãy trừ khoản phí này khi tính lợi ích thực. Nếu bạn có nhiều khoản nợ, xem thêm file Excel kế hoạch trả nợ để quyết định ưu tiên khoản nào.
Vì sao số liệu của ngân hàng có thể lệch đôi chút?
- Nhiều ngân hàng tính lãi theo số ngày thực tế của kỳ trên cơ sở 365 ngày, nên tháng 31 ngày có lãi cao hơn tháng 30 ngày.
- Ngày trả nợ có thể rơi vào ngày nghỉ và được dời.
- Cách làm tròn khác nhau.
Muốn sát hơn, dùng công thức lãi theo ngày: =ROUND(C12*LaiNam*(B13-B12)/365,0), với B12, B13 là ngày trả nợ của hai kỳ liên tiếp.
Đưa khoản trả nợ vào ngân sách
Tiền trả nợ hằng tháng nên là một dòng cố định trong file Excel quản lý chi tiêu cá nhân. Một nguyên tắc an toàn thường được nhắc tới là không để tổng tiền trả nợ chiếm quá lớn trong thu nhập hằng tháng, và luôn giữ quỹ dự phòng cho những tháng lãi suất tăng.
Câu hỏi thường gặp
Nên chọn gốc trả đều hay trả góp đều?
Gốc trả đều tốn ít lãi hơn nhưng tháng đầu trả nhiều; trả góp đều giúp số tiền hằng tháng ổn định. Tùy dòng tiền của bạn và lựa chọn ngân hàng cho phép.
Lãi suất 0,75%/tháng có phải là 9%/năm không?
Về danh nghĩa là 9%/năm. Nếu tính lãi kép theo tháng, lãi suất hiệu dụng cả năm cao hơn một chút: =(1+0,75%)^12-1 ≈ 9,38%.
Dùng Google Sheets được không?
Được. PMT, IPMT, PPMT, RATE và EDATE có đầy đủ trên Google Sheets.
Xem thêm trong nhóm Trả nợ và tiết kiệm.