Cách để Lập lịch biểu chi trả dần trên Excel

Lịch biểu chi trả dần (hay lịch trả nợ) thể hiện lãi suất phải trả cho khoản vay cố định và quá trình giảm nợ gốc thông qua các khoản thanh toán. Lịch biểu cung cấp một cái nhìn tổng quan chi tiết về tất cả các khoản thanh toán, qua đó bạn sẽ biết được số tiền sẽ hoàn vào nợ gốc và bao nhiêu sẽ đi vào lãi. Hơn nữa, lịch trả nợ có thể được lập dễ dàng trên Microsoft Excel. Cùng bắt đầu bước 1 bên dưới để tạo lịch biểu chi trả gần tại nhà mà không phải tốn tiền thuê dịch vụ!

Các bước

1
Khởi chạy Microsoft Excel và mở bảng tính mới.
2
Đặt tên cho các ô A1 xuống A4 như sau: Số tiền đã vay, Tiền lãi, Tháng và Thanh toán.
3
Nhập dữ liệu tương ứng vào các ô từ B1 đến B3.
4
Nhập lãi suất vay dưới dạng phần trăm.
5
Tính khoản thanh toán ở ô B4 bằng cách nhập " =ROUND(PMT($B$2/12,$B$3,-$B$1,0), 2)" vào thanh công thức (không có dấu ngoặc kép) và nhấn Enter.
- Ký hiệu đô la trong công thức này là tham chiếu tuyệt đối để đảm bảo rằng công thức sẽ luôn lấy dữ liệu từ những ô cụ thể ngay cả khi chúng được sao chép đến bất cứ đâu trong bảng tính.
- Tiền lãi phải được chia cho 12 vì đây là lãi suất hàng năm và tính theo tháng.
- Ví dụ: nếu bạn vay 3.450.000.000 đồng với lãi suất 6% trả trong 30 năm (360 tháng) thì khoản thanh toán lần đầu của bạn sẽ là 20.684.590 đồng.
6
Đặ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ỳ.
7
Điền 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. Có thể bạn cần định dạng để cột hiển thị tháng năm chính xác.
- Chọn ô, nhấp và kéo xuống để điền vào ô A367. Bạn cần chắc chắn rằng tùy chọn Auto Fill (Tự động điền) đã được đặt thành "Fill Months" (Điền tháng).
8
Điền tiếp những thông tin còn lại vào ô B8 đến H8.
- Nhập số dư đầu kỳ của khoản vay vào ô B8.
- Nhập "=$B$4" vào ô C8 rồi nhấn "Enter".
- Tạo công thức tính lãi của số dư đầu kỳ trong kỳ hạn đó vào ô E8. Công thức sẽ tương tự như sau: "=ROUND($B8*($B$2/12), 2)". Ký hiệu đô la cũng chỉ ra một tham chiếu tương đối ở đây. Công thức sẽ tìm ô thích hợp trong cột B.
- Ở ô D8, bạn lấy tổng thanh toán trong ô C8 trừ cho tiền lãi từ ô E8. Sử dụng tham chiếu tương đối để ô này được sao chép chính xác. Công thức sẽ tương tự như sau: " =$C8-$E8".
- Trong ô H8, hãy tạo công thức lấy số dư đầu kỳ của kỳ hạn đó trừ cho tiền gốc. Công thức như sau: " =$B8-$D8".
9
Tiếp tục lịch biểu bằng cách điền vào các ô từ B9 đến H9.
- Ô B9 sẽ bao gồm tham chiếu tương đối của số dư cuối kỳ trước. Hãy nhập "=$H8" vào ô này và nhấn Enter. Sao chép ô C8, D8, E8 rồi dán vào C9, D9 và E9. Sao chép ô H8 và dán vào ô H9. Đây là nơi mà tham chiếu tương đối bắt đầu phát huy tác dụng.
- Ở ô F9, bạn cần tạo công thức tính tiền gốc tích lũy đã trả. Công thức sẽ là: " =$D9+$F8". Thực hiện tương tự cho ô lãi suất tích lũy ở G9 với công thức tương tự như sau: " =$E9+$G8".
10
Hoàn thành lịch trả nợ.
- Tô sáng từ ô B9 đến H9, di chuột xuống góc dưới bên phải vùng chọn để con trỏ chuyển sang hình chữ thập, sau đó nhấp và kéo vùng chọn xuống hàng 367. Thả chuột ra.
- Bạn cần chắc chắn rằng tùy chọn Auto Fill đã được đặt thành "Copy Cells" (Sao chép các ô) và số dư sau cùng là 0 đồng.
Quảng cáo

Lời khuyên

Nếu số dư sau cùng không phải là 0 đồng, hãy kiểm tra lại để chắc chắn rằng bạn đã sử dụng những tham chiếu tuyệt đối và tương đối như hướng dẫn, đồng thời các ô phải được sao chép chính xác.
Bây giờ bạn có thể cuộn xuống bất kỳ giai đoạn nào trong quá trình thanh toán khoản vay để xem trong khoản thanh toán thì bao nhiêu đã đi vào nợ gốc, bao nhiêu là lãi, cũng như số tiền gốc và lãi mà bạn đã trả cho đến thời điểm đó.

Cảnh báo

Phương pháp này chỉ áp dụng được với khoản vay mua nhà được tính theo tháng. Đối với khoản vay mua xe hoặc khoản vay mà bạn phải trả lãi mỗi ngày thì lịch biểu chỉ có thể cung cấp ước tính sơ bộ về lãi suất.