Kỹ thuật lập bảng kế hoạch trả nợ dần
Để lập được bảng kế hoạch trả nợ dần (Amortization Schedule - AS) bạn
phải xác định được 3 yếu tố sau: tổng số vốn vay (số dư nợ vào đầu kỳ trả
nợ đầu tiên), thời gian trả nợ và lãi suất vay vốn. Sau đây tôi xin chia sẻ cách
lập bảng kế hoạch trả nợ dần trong Excel.
Gọi
i là kỳ khoản trả nợ thứ i (i = 1,2,…n)
n là tổng số kỳ khoản trả nợ
Ci là số dư nợ vào đầu kỳ khoản thứ i (i = 1,2,…n)
r là lãi suất vay vốn
Iilà số tiền lãi phải thanh toán trong kỳ khoản thứ i (i = 1,2,…n)
Mi là số vốn gốc trả trong kỳ khoản thứ i (i = 1,2,…n)
a (annuity - niên kim) là tổng số tiền trả nợ trong kỳ (theo cách trả nợ
này thì tổng số tiền trả nợ hằng kỳ đều như nhau).
Cấu trúc bảng AS gợi ý như sau:
Kỳ
trả nợ
Dự nợ
đầu kỳ (Ci)
Thanh toán
Lãi (Ii)
Thanh toán
Vốn (Mi)
Tổng thanh
toán (ai)
(i)
1 C1 I1 = C1.r M1 = a – I1 a
2 C2 = C1 – M1 I2 = C2.r M2 = a – I2 a
… … …
n Cn = Cn-1 – Mn-1 In = Cn.r Mn = a - In a
Tổng a.n
Để lập được bản trả nợ trên mấu chốt là phải tính được số tiền trả nợ hằng
kỳ a. Bạn có thể tính a từ số vốn gốc theo công thức rất cơ bản trong toán tài
chính sau.
Để dễ hiểu sau đây xin lấy ví dụ minh hoạ đơn giản:
Vay ngân hàng số vốn 100.000 đồng, thời gian trả nợ 5 năm, lãi suất 10%
một năm.
Áp dụng công thức trên, ta tính được số tiền trả nợ hằng năm:
Bảng trả nợ được lập bằng Excel như sau:
Kết quả được tính là:
i
Ci Ii Mi ai
1
100000,00
10000,00
16379,75 26379,75
2
83620,25 8362,03 18017,73 26379,75
3
65602,53 6560,25 19819,50 26379,75
4
45783,03 4578,30 21801,45 26379,75
5
23981,58 2398,16 23981,59 26379,75
31898,74
100000,01
131898,75
Do đã làm tròn ở giá trị a nên bảng kế hoạch trả nợ có sai số.
Tuy nhiên trong Excel, bạn không nên tính thủ công như vậy bởi vì bạn có
thể dung các hàm tài chính (financial functions) để lập bảng AS.
Lập hàm cho ví dụ trên như sau:
Kết quả chính xác hơn:
i
Ci Ii Mi ai
1
100000,00
-
10000,00
-16379,75
-26379,75
2
83620,25 -8362,03
-18017,72
-26379,75
3
65602,53 -6560,25
-19819,50
-26379,75
4
45783,03 -4578,30
-21801,44
-26379,75
5
23981,59 -2398,16
-23981,59
-26379,75
-
31898,74
-
100000,00
-
131898,74
Lưu ý: dấu (-) thể hiện dòng tiền chi ra (outflow) để trả nợ.
Qua vài nét phác thảo này, hy vọng chia sẻ thêm thông tin với các bạn.