Lập lịch trả nợ vay gốc đều, lãi theo ngày thực tế bằng Excel

ExcelNâng caoMiễn phí
1HÀM, CHỨC NĂNG

EDATE · IF · ROUND · Ctrl+Enter

2TƯ DUY, PHƯƠNG PHÁP

Dựng từng kỳ từ dư nợ đầu kỳ: gốc chia đều, kỳ cuối nhận phần còn lại; lãi tính theo số ngày thực tế giữa hai lần trả.

3TÌNH HUỐNG

Tài chính: lịch trả nợ vay ngân hàng để lập kế hoạch tiền

Bài toán

Vay 600 triệu, trả gốc đều 12 tháng, lãi tính trên dư nợ theo số ngày thực tế. Cần biết mỗi kỳ trả bao nhiêu để chuẩn bị tiền.

Làm xong được gì: Bảng 12 kỳ: ngày trả, số ngày, dư nợ đầu kỳ, gốc, lãi, tổng trả, dư nợ cuối kỳ; kỳ cuối dư nợ về đúng 0. Đổi số tiền vay hay lãi suất là cả bảng tính lại.

File thực hành

Sheet LichTraNo: tham số ở C3:C6; bảng kỳ A8:H20 mới có cột Kỳ.

Bộ file gồm file đề để làm theo video và file đáp án để so kết quả.

👉 Vào nhóm Zalo để tải file

Các bước làm

1Ngày trả và số ngày

Bước một, ngày trả bằng hàm EDATE: ngày giải ngân cộng thêm số tháng bằng số kỳ. Số ngày bằng ngày trả kỳ này trừ ngày trả kỳ trước. Riêng kỳ một trừ ngày giải ngân.

=EDATE($C$6,A9)
=B9-IF(A9=1,$C$6,B8)

2Dư nợ và gốc

Bước hai, dư nợ đầu kỳ: kỳ một là số tiền vay, các kỳ sau lấy dư nợ cuối kỳ trước. Gốc chia đều mười hai kỳ. Kỳ cuối trả hết phần dư nợ còn lại, để không treo vài đồng do làm tròn.

=IF(A9=1,$C$3,H8)
=IF(A9=$C$5,D9,ROUND($C$3/$C$5,0))

3Lãi theo ngày

Bước ba, lãi bằng dư nợ đầu kỳ nhân lãi suất năm, nhân số ngày, chia ba trăm sáu mươi lăm. Tổng trả bằng gốc cộng lãi. Dư nợ cuối kỳ bằng dư nợ đầu kỳ trừ gốc. Kỳ cuối dư nợ về đúng không. Tháng hai ít ngày nên tiền lãi kỳ đó thấp hơn hẳn.

=ROUND(D9*$C$4*C9/365,0)
=D9-E9

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

  • Ngân hàng có thể tính lãi theo 360 hoặc 365 ngày; sửa số 365 trong công thức lãi cho đúng hợp đồng.
  • Kỳ cuối lấy toàn bộ dư nợ còn lại để không treo vài đồng do làm tròn.
Muốn học Excel bài bản cùng AI?

Khóa AI EXCEL PRO học trực tiếp qua Zoom với Thầy Đạt: làm quen công cụ AI, tự động hóa báo cáo, xử lý dữ liệu nhanh gấp nhiều lần.

Xem khóa họcNhắn Zalo Thầy 0396.523.865

Bài học khác

Dữ liệu trong bài là dữ liệu minh họa. Xem tất cả bài học.

Lên đầu trang