Tính toán khoản trả góp hàng tháng là một trong những việc quan trọng nhất khi quyết định vay tiền. Tôi từng ước tính sai con số này khi vay mua xe máy năm đầu đi làm, nghĩ rằng chỉ cần lấy tổng tiền chia cho số tháng. Kết quả là khoản trả thực tế cao hơn 30% so với tôi tính, khiến ngân sách hàng tháng mất cân đối.
Hàm PMT tính gì
Hàm PMT trong Excel tính số tiền phải trả cố định mỗi kỳ cho một khoản vay hoặc đầu tư, bao gồm cả gốc và lãi. Số tiền này không đổi trong suốt thời hạn vay, với điều kiện lãi suất cố định.
Cú pháp:
=PMT(rate, nper, pv, [fv], [type])Các tham số:
- rate: Lãi suất theo kỳ thanh toán (nếu trả hàng tháng thì lãi suất năm chia 12)
- nper: Tổng số kỳ thanh toán (nếu vay 5 năm trả hàng tháng thì 5 × 12 = 60)
- pv: Giá trị hiện tại của khoản vay (số tiền vay ban đầu, nhập số âm)
- fv: Giá trị tương lai sau khi trả xong (thường để trống hoặc 0)
- type: 0 nếu trả cuối kỳ, 1 nếu trả đầu kỳ (thường bỏ qua)
Quy tắc quan trọng nhất: rate và nper phải cùng đơn vị thời gian. Nếu rate tính theo năm mà nper tính theo tháng, kết quả sẽ sai hoàn toàn.
Tính khoản vay mua xe ô tô
Giả sử bạn định mua xe ô tô giá 500 triệu đồng, trả trước 100 triệu, vay ngân hàng 400 triệu với lãi suất 7.5% mỗi năm trong 5 năm.
Dữ liệu:
- Số tiền vay: 400,000,000 đồng
- Lãi suất năm: 7.5%
- Thời hạn: 5 năm (60 tháng)
Công thức trong Excel:
=PMT(7.5%/12, 60, -400000000)Phân tích công thức:
7.5%/12: Lãi suất hàng tháng là 0.625% (lãi năm chia 12)60: Tổng số tháng phải trả-400000000: Số tiền vay (âm vì là tiền bạn nhận từ ngân hàng)
Kết quả: 8,004,828 đồng mỗi tháng
Trong 5 năm, tổng số tiền trả là 480,289,680 đồng. Phần lãi là 80,289,680 đồng (tương đương 20% tổng tiền vay). Nếu tính bằng tay như tôi từng làm, chỉ nhân 7.5% với 400 triệu rồi chia cho 60 tháng, sẽ được khoản trả thấp hơn thực tế.
Tính khoản vay mua nhà
Khoản vay nhà có số tiền lớn hơn và thời hạn dài hơn rất nhiều so với vay xe. Giả sử bạn vay 2 tỷ đồng mua căn hộ, lãi suất 6% mỗi năm, trả trong 20 năm.
Dữ liệu:
- Số tiền vay: 2,000,000,000 đồng
- Lãi suất năm: 6%
- Thời hạn: 20 năm (240 tháng)
Công thức trong Excel:
=PMT(6%/12, 240, -2000000000)Kết quả: 14,327,760 đồng mỗi tháng
Lưu ý về quy đổi đơn vị: Nếu bạn muốn tính khoản trả hàng quý thay vì hàng tháng, công thức sẽ là:
=PMT(6%/4, 80, -2000000000)Trong đó 6%/4 là lãi suất hàng quý (1.5%), và 80 là số quý trong 20 năm.
So sánh với tính thủ công: Nhiều người nghĩ chỉ cần lấy 2 tỷ nhân 6% rồi cộng vào gốc và chia đều. Theo cách này, mỗi tháng trả khoảng 13,333,333 đồng. Nhưng đây là sai vì cách tính này áp dụng lãi đơn, trong khi ngân hàng tính lãi kép trên dư nợ giảm dần. Hàm PMT tính chính xác theo phương thức này.
Tổng tiền trả sau 20 năm: 3,438,662,400 đồng. Phần lãi chiếm 1,438,662,400 đồng (tương đương 72% tổng tiền vay ban đầu).
Tính ngược: Cần gửi bao nhiêu mỗi tháng để có X tiền
Hàm PMT cũng tính được số tiền cần gửi tiết kiệm định kỳ để đạt mục tiêu tài chính. Giả sử bạn muốn có 500 triệu đồng sau 10 năm để mua nhà, với lãi suất tiết kiệm 5% mỗi năm.
Dữ liệu:
- Mục tiêu: 500,000,000 đồng
- Lãi suất năm: 5%
- Thời hạn: 10 năm (120 tháng)
- Không có số tiền ban đầu
Công thức trong Excel:
=PMT(5%/12, 120, 0, -500000000)Phân tích công thức:
5%/12: Lãi suất hàng tháng120: Số tháng gửi tiền0: Không có số tiền gốc ban đầu-500000000: Giá trị tương lai (âm vì là số tiền bạn muốn nhận)
Kết quả: 3,227,240 đồng mỗi tháng
Nếu gửi 3.2 triệu đồng mỗi tháng trong 10 năm, bạn sẽ có đủ 500 triệu. Tổng số tiền gửi vào là 387,268,800 đồng, phần lãi là 112,731,200 đồng.
Những sai lầm thường gặp
Lỗi 1: Không chuyển đổi đơn vị thời gian đúng
Nhiều người nhập =PMT(6%, 240, -2000000000) khi tính trả hàng tháng. Điều này sai vì 6% là lãi năm nhưng nper là 240 tháng. Kết quả sẽ ra con số không có ý nghĩa.
Lỗi 2: Quên dấu âm ở pv
Nếu nhập =PMT(6%/12, 240, 2000000000) không có dấu âm, Excel vẫn tính được nhưng kết quả hiển thị là số âm, gây nhầm lẫn. Thêm dấu âm trước pv để kết quả hiển thị dương.
Lỗi 3: Tính lãi suất thực tế sai
Một số ngân hàng quảng cáo lãi suất 6%/năm nhưng tính lãi hàng tháng theo cách khác. Luôn kiểm tra công thức tính lãi thực tế với ngân hàng trước khi dùng PMT.
Mẹo nâng cao
Để xem chi tiết từng kỳ thanh toán (bao nhiêu là gốc, bao nhiêu là lãi), dùng thêm hai hàm:
PPMT: Tính phần gốc trong một kỳ cụ thểIPMT: Tính phần lãi trong một kỳ cụ thể
Ví dụ tính phần gốc và lãi của tháng thứ 12 trong khoản vay xe 400 triệu:
=PPMT(7.5%/12, 12, 60, -400000000)
=IPMT(7.5%/12, 12, 60, -400000000)Kết hợp ba hàm này giúp tạo bảng trả nợ chi tiết theo từng tháng, rất hữu ích khi lập kế hoạch tài chính dài hạn.
Phiên bản hỗ trợ
Hàm PMT có mặt trong Excel 2007 trở lên, bao gồm Excel 365, Excel 2021, Excel 2019, và Excel 2016. Hàm này cũng hoạt động trên Excel for Mac và Excel Online. Nếu làm việc với Google Sheets, cú pháp hoàn toàn giống nhau.
Khi vay tiền từ ngân hàng, luôn dùng PMT để tính chính xác khoản trả hàng tháng trước khi ký hợp đồng. So sánh con số Excel tính với con số ngân hàng báo để đảm bảo không có sai sót. Nếu muốn tính chi tiết hơn về phần gốc và lãi từng tháng, học thêm về PPMT và IPMT trong bài viết tiếp theo về các hàm tài chính Excel.
