Lịch trả nợ vay ngân hàng trả gốc đều, lãi theo ngày thực tế bằng Excel: khớp giấy báo nợ

ExcelNâng cao Video 2:51

Bài toán

Vay ngân hàng trả gốc đều, lãi tính trên dư nợ theo số ngày thực tế; tháng nào bạn cũng bấm máy tính lãi rồi so với giấy báo nợ, lệch vài trăm đồng là ngồi dò.

Làm xong được gì

Lịch trả nợ cả khoản vay tự ra từng kỳ: ngày trả, số ngày, gốc, lãi, dư nợ; khớp giấy báo nợ, đổi lãi suất giữa chừng chỉ sửa một ô.

Dùng trong bài

EDATEIFROUNDCtrl+Enter

File thực hành gồm

LichTraNo_Vay.xlsx
Sheet LichTraNo, lịch trả nợ một khoản vay 1,85 tỷ, giải ngân 31/10/2025, trả gốc đều 24 kỳ hằng tháng (dòng 10 tới 33, dòng 34 là dòng cộng, ô I35 là ô kiểm). Đây là bảng cũ: cột ngày trả và số ngày còn trống, gốc gõ tay 77.083.333 cho cả 24 kỳ, lãi tính kiểu chia 12 với lãi suất gõ cứng 8,5%. Cột J đã có tiền lãi theo giấy báo nợ của kỳ 1 tới 11, cột K là chênh giữa hai bên.
LichTraNo_Vay_dap_an.xlsx
Bản đã gắn công thức, mở ra để so kết quả.

Các bước làm

1
Xem ba chỗ lệch của bảng cũ
Bấm ô G13 (kỳ 4, trả tháng 2): công thức là =ROUND(D13*8.5%/12,0). Tháng 2 có 28 ngày mà vẫn tính lãi như mọi tháng, ô K13 lệch 911.009. Bấm ô K16 (kỳ 7): ngân hàng đã tăng lãi suất lên 9,2% nhưng bảng vẫn 8,5%, lãi hụt 1.013.382. Bấm ô I35: trả đủ 24 kỳ mà dư nợ còn treo 8 đồng, vì gốc gõ tay cùng một số.
2
Ngày trả từng kỳ bằng EDATE
Bôi B10:B33, gõ công thức dưới đây rồi bấm Ctrl+Enter. EDATE lấy ngày giải ngân ở ô D5 cộng thêm số tháng bằng số kỳ ở cột A. Giải ngân ngày 31 nên kỳ tháng 1 là 31/01/2026 (B12), kỳ tháng 2 tự về 28/02/2026 (B13).
=EDATE($D$5,A10)
3
Số ngày của từng kỳ
Bôi C10:C33, gõ công thức dưới đây rồi Ctrl+Enter. Lấy ngày trả trừ ngày trả kỳ trước, tức EDATE của số kỳ trừ 1. Ở kỳ đầu, số kỳ trừ 1 bằng 0 nên EDATE trả về đúng ngày giải ngân. Ô C13 ra 28 ngày, dòng cộng C34 ra 730 ngày, tròn hai năm.
=B10-EDATE($D$5,A10-1)
4
Gốc trả đều, kỳ cuối trả nốt phần lẻ
Bôi F10:F33, gõ công thức dưới đây rồi Ctrl+Enter. Kỳ nào có số kỳ bằng tổng số kỳ ở ô D6 thì trả hết dư nợ đầu kỳ. Các kỳ còn lại lấy số tiền vay ở D4 chia số kỳ, ROUND về tròn đồng. Ô F33 ra 77.083.341, gánh nốt 8 đồng lẻ. Ô kiểm I35 về 0.
=IF(A10=$D$6,D10,ROUND($D$4/$D$6,0))
5
Gõ lãi suất mới vào kỳ 7
Bấm ô E16, gõ 9,2% rồi Enter (máy dùng dấu chấm thập phân thì gõ 9.2%). Các ô lãi suất bên dưới lấy theo ô phía trên nên E16:E33 cùng thành 9,20%.
6
Lãi theo dư nợ và số ngày thực tế
Bôi G10:G33, gõ công thức dưới đây rồi Ctrl+Enter: dư nợ đầu kỳ nhân lãi suất năm nhân số ngày, chia 365, làm tròn tới đồng. Cả 24 kỳ có tiền lãi theo đúng số ngày. Cột Chênh từ K10 tới K20 về 0 hết (số 0 hiện thành dấu gạch ngang).
=ROUND(D10*E10*C10/365,0)
7
Soát kỳ tháng 2 với giấy báo nợ
Dòng 13: 1.618.750.001 × 8,5% × 28 / 365 = 10.555.137, bằng đúng số trên giấy báo nợ ở J13. Tổng lãi cả khoản vay ở G34 là 171.245.167. Tháng sau giấy báo nợ về thì gõ tiền lãi vào cột J rồi xem cột Chênh.
8
Thử đổi lãi suất một kỳ sau
Bấm ô E22 (kỳ 13), gõ 8,8% rồi Enter. E22:E33 thành 8,80%, tiền lãi G22:G33 và tổng lãi G34 tính lại. Bấm Ctrl+Z để trả về như cũ.

Lưu ý khi làm với file thật

Cách ngân hàng tính lãi: năm 365 ngày, lãi tính trên dư nợ thực tế và số ngày duy trì dư nợ, tính ngày đầu bỏ ngày cuối (Thông tư 14/2017/TT-NHNN, Thông tư 39/2016/TT-NHNN).
Hợp đồng tín dụng của từng ngân hàng có thể quy định thêm, ví dụ ngày trả trùng ngày nghỉ hay cách làm tròn. Đọc hợp đồng của khoản vay đang theo dõi trước khi áp công thức.
Ngân hàng đổi lãi suất thì gõ vào đúng ô lãi suất của kỳ bắt đầu áp dụng, các kỳ sau tự lấy theo.

Bài dùng EDATE, IF, ROUND, Excel 2019, 2021, 365 đều có các hàm này. Không dùng hàm mảng, không dùng PMT.

File thực hành của bài này

EzTech sẽ cập nhật link tải sau.

Dữ liệu trong bài là dữ liệu minh hoạ.

Bài học khác

Tất cả bài học