Hướng dẫn tính lịch trả nợ theo dư nợ giảm dần Excel

Trong bài này chúng ta sẽ cùng tìm hiểu cách tạo một lịch biểu chi trả dần (Loan Amortization Schedule) trên Excel.

1. Chúng ta sẽ sử dụng hàm PMT để tính khoản phải trả hàng tháng cho khoản vay với lãi suất hàng năm là 5%. Trong thời hạn 2 năm và giá trị hiện tại (số tiền đã vay) là 20 nghìn đô la. Ta đặt tên cho dải ô để xác định các ô dữ liệu đầu vào.

• Ô B1 được đặt tên là Annual Interest Rate (Lãi suất hàng năm).
• Ô B2 được đặt tên là Years (Thời hạn).
• Ô B3 được đặt tên là Payments Per Year (Phải trả mỗi năm).
• Ô B4 được đặt tên là Amount (Số tiền).

Và ta có công thức tính khoản tiền phải trả hàng tháng như sau:

=PMT(AnnualInterestRate/PaymentsPerYear,Years*PaymentsPerYear,Amount)

2. Sử dụng hàm PPMT để tính số tiền gốc của khoản phải trả. Đối số thứ hai xác định số phải trả.

3. Sử dụng hàm IPMT để tính số tiền lãi của khoản phải trả. Đối số thứ 2 xác định số phải trả.

4. Cập nhật số dư.

5. Chọn phạm vi dữ liệu A7:E7 (khoản phải trả đầu tiên) và kéo nó xuống một hàng. Thay đổi công thức số dư.

6. Chọn phạm vi dữ liệu A8:E8 (khoản phải trả thứ hai) và kéo nó xuống đến hàng thứ 30.

Như vậy chúng ta đã có số thống kê khoản phải trả trong 24 tháng của khoản vay này. Ta cũng thấy được tiền gốc tăng lên và tiền lãi giảm đi theo mỗi lần thanh toán.