Excel là công cụ mạnh mẽ hỗ trợ tính toán tài chính, đặc biệt hữu ích khi phân tích khoản vay. Bài viết này hướng dẫn bạn cách sử dụng hàm PMT, PPMT và IPMT trong Excel để phân tích chi tiết khoản vay, bao gồm số tiền phải trả hàng kỳ, số trả gốc, số trả lãi và số còn nợ.
Chúng ta sẽ cùng phân tích ví dụ sau: Bạn vay 100 triệu đồng, trả góp hàng tháng trong 3 năm với lãi suất 6%/năm (tương đương 0.5%/tháng). Mỗi tháng bạn phải trả bao nhiêu? Tổng số tiền phải trả sau 3 năm là bao nhiêu? Phân tích số tiền gốc, lãi và nợ còn lại của từng kỳ như thế nào?
Cách Phân Tích Khoản Vay
Để phân tích khoản vay, chúng ta cần lập bảng theo dõi các kỳ thanh toán với các thông số: Kỳ thanh toán, Số phải trả, Số trả gốc, Số trả lãi và Số còn nợ.
Bảng phân tích khoản vay
1. Tính Số Tiền Phải Trả Hàng Kỳ (Hàm PMT)
Hàm PMT (Payment) tính toán số tiền phải trả hàng kỳ cho khoản vay có lãi suất cố định.
Cú pháp: PMT(rate, nper, pv, [fv], [type])
rate: Lãi suất mỗi kỳ (0.5%/tháng trong ví dụ này).nper: Tổng số kỳ thanh toán (36 tháng).pv: Giá trị hiện tại của khoản vay (100 triệu đồng).
Trong ô F2, nhập công thức: =PMT($C$4,$C$3,$C$2)
Tính số tiền phải trả hàng kỳ bằng hàm PMT
Lưu ý: Cố định các tham chiếu ($C$4, $C$3, $C$2) để có thể sao chép công thức cho các kỳ tiếp theo.
2. Tính Số Trả Gốc (Hàm PPMT)
Hàm PPMT (Principal Payment) tính toán số tiền trả gốc trong mỗi kỳ.
Cú pháp: PPMT(rate, per, nper, pv, [fv], [type])
per: Kỳ thanh toán hiện tại (từ 1 đến 36).
Trong ô G2, nhập công thức: =PPMT($C$4,$E2,$C$3,$C$2)
Tính số tiền trả gốc bằng hàm PPMT
3. Tính Số Trả Lãi (Hàm IPMT)
Hàm IPMT (Interest Payment) tính toán số tiền trả lãi trong mỗi kỳ.
Cú pháp: IPMT(rate, per, nper, pv, [fv], [type])
Trong ô H2, nhập công thức: =IPMT($C$4,E2,$C$3,$C$2)
Tính số tiền trả lãi bằng hàm IPMT
4. Tính Số Còn Nợ
Số còn nợ sau mỗi kỳ được tính bằng số nợ kỳ trước cộng với số trả gốc (số trả gốc là số âm).
- Trong ô I2, nhập công thức:
=C2+G2 - Trong ô I3, nhập công thức:
=I2+G3
Sau đó, sao chép công thức xuống các ô tiếp theo.
Bảng phân tích khoản vay hoàn chỉnh
So Sánh Với Khoản Vay 12 Tháng
Nếu rút ngắn thời gian vay xuống còn 12 tháng, số tiền lãi sẽ giảm đáng kể.
Bảng phân tích khoản vay 12 tháng
Kết Luận
Việc sử dụng các hàm PMT, PPMT và IPMT trong Excel giúp phân tích khoản vay một cách chi tiết và hiệu quả. Từ đó, bạn có thể đưa ra quyết định tài chính sáng suốt hơn, ví dụ như lựa chọn thời hạn vay phù hợp để tối ưu chi phí lãi vay.
Discussion about this post