Hướng dẫn chi tiết cách lập lịch biểu chi trả dần trên Excel

25/02/2025

Nội dung bài viết

Lịch biểu chi trả dần (hay lịch trả nợ) giúp bạn theo dõi lãi suất và quá trình giảm nợ gốc thông qua các khoản thanh toán định kỳ. Với lịch biểu này, bạn có thể nắm rõ số tiền dùng để trả nợ gốc và phần dành cho lãi suất. Đặc biệt, bạn có thể tự tạo lịch biểu này trên Microsoft Excel một cách dễ dàng, tiết kiệm chi phí thuê dịch vụ bên ngoài. Hãy bắt đầu từ bước 1 dưới đây để tự lập lịch biểu chi trả ngay tại nhà!

Các bước thực hiện

Mở Microsoft Excel và tạo một bảng tính mới.

Đặt tên cho các ô từ A1 đến A4 như sau: Số tiền vay, Lãi suất, Thời hạn và Khoản thanh toán.

Nhập dữ liệu tương ứng vào các ô từ B1 đến B3.

Nhập lãi suất vay dưới dạng phần trăm.

Tính toán khoản thanh toán tại ô B4 bằng cách nhập công thức "=ROUND(PMT($B$2/12,$B$3,-$B$1,0), 2)" vào thanh công thức (không bao gồm dấu ngoặc kép) và nhấn Enter.

Ký hiệu đô la trong công thức đảm bảo tham chiếu tuyệt đối, giúp công thức luôn tham chiếu đến các ô cụ thể dù được sao chép đến bất kỳ đâu trong bảng tính.
Lãi suất được chia cho 12 để chuyển đổi từ lãi suất hàng năm sang lãi suất hàng tháng.
Ví dụ: Khoản vay 3.450.000.000 đồng với lãi suất 6% trong 30 năm (360 tháng) sẽ có khoản thanh toán đầu tiên là 20.684.590 đồng.

Đặt tên cho các cột từ A6 đến H7 như sau: Thời hạn, Số dư đầu kỳ, Thanh toán, Tiền gốc, Tiền lãi, Tiền gốc tích lũy, Tiền lãi tích lũy và Số dư cuối kỳ.

Điền thông tin vào cột Thời hạn.

Nhập tháng và năm của khoản thanh toán đầu tiên vào ô A8. Định dạng cột để hiển thị chính xác tháng và năm.
Chọn ô, nhấp và kéo xuống để điền dữ liệu đến ô A367. Đảm bảo tùy chọn Auto Fill (Tự động điền) được đặt thành "Fill Months" (Điền tháng).

Điền thông tin còn lại vào các ô từ B8 đến H8.

Nhập số dư đầu kỳ của khoản vay vào ô B8.
Nhập "=$B$4" vào ô C8 và nhấn Enter.
Tạo công thức tính lãi suất dựa trên số dư đầu kỳ tại ô E8: "=ROUND($B8*($B$2/12), 2)". Ký hiệu đô la giúp tham chiếu chính xác đến các ô trong cột B.
Tại ô D8, tính tiền gốc bằng cách lấy tổng thanh toán (C8) trừ đi tiền lãi (E8): "=$C8-$E8".
Trong ô H8, tính số dư cuối kỳ bằng cách lấy số dư đầu kỳ (B8) trừ đi tiền gốc (D8): "=$B8-$D8".

Tiếp tục điền thông tin vào các ô từ B9 đến H9.

Ô B9 tham chiếu số dư cuối kỳ trước: "=$H8". Sao chép ô C8, D8, E8 vào C9, D9, E9. Sao chép ô H8 vào H9.
Tại ô F9, tính tiền gốc tích lũy: "=$D9+$F8". Tương tự, tại ô G9, tính tiền lãi tích lũy: "=$E9+$G8".

Hoàn thiện lịch trả nợ.

Chọn từ ô B9 đến H9, kéo xuống đến hàng 367 để điền tự động.
Đảm bảo tùy chọn Auto Fill được đặt thành " Cells" và số dư cuối cùng phải là 0 đồng.

Lời khuyên hữu ích

Nếu số dư cuối cùng khác 0, hãy kiểm tra lại việc sử dụng tham chiếu tuyệt đối và tương đối, đồng thời đảm bảo các ô được sao chép chính xác.
Bạn có thể cuộn xuống bất kỳ thời điểm nào để xem chi tiết số tiền đã trả vào gốc, lãi, cũng như tổng số tiền đã trả đến thời điểm đó.

Lưu ý quan trọng

Phương pháp này chỉ phù hợp với các khoản vay mua nhà có lãi suất tính theo tháng. Đối với khoản vay mua xe hoặc khoản vay tính lãi hàng ngày, lịch biểu chỉ mang tính chất ước lượng và không chính xác tuyệt đối.