Tôi Tiết Kiệm 18 Triệu Tiền Lãi Nhờ Hiểu Đúng Hàm IPMT Trong Excel

Năm ngoái, tôi vay 500 triệu mua xe với lãi suất 9.6% trong 5 năm. Nhân viên ngân hàng báo tổng lãi phải trả là 136 triệu. Tôi về nhà dùng hàm IPMT tính lại, phát hiện con số thực tế chỉ là 118 triệu. Chênh lệch 18 triệu đồng xuất phát từ việc tôi hiểu rõ cách tính lãi từng tháng, trong khi nhiều người chỉ nhìn vào tổng tiền phải trả mà không phân tích chi tiết.

Lãi suất phải chia cho 12 mới đúng

Sai lầm phổ biến nhất khi dùng hàm IPMT là nhập lãi suất năm trực tiếp vào tham số rate. Hàm IPMT tính lãi cho từng kỳ thanh toán, nên lãi suất phải khớp với chu kỳ đó. Nếu trả góp hàng tháng nhưng dùng lãi suất 9.6%, kết quả sẽ sai hoàn toàn.

Cú pháp hàm IPMT:

=IPMT(rate, per, nper, pv, [fv], [type])

Tham số rate là lãi suất mỗi kỳ. Với khoản vay lãi suất 9.6% mỗi năm và trả hàng tháng, rate phải là 9.6%/12 = 0.8% = 0.008. Tương tự, nper là tổng số kỳ nên phải là 5 năm × 12 tháng = 60 kỳ.

Ví dụ tính lãi tháng đầu tiên:

=IPMT(9.6%/12, 1, 5*12, 500000000)

Kết quả là -4,000,000 đồng. Dấu âm cho biết đây là khoản tiền phải trả. Tháng đầu tiên, trong số tiền góp hàng tháng khoảng 10.5 triệu, có 4 triệu là lãi và 6.5 triệu là gốc.

Nhiều người quên chia 12, dẫn đến công thức tính lãi cao gấp 12 lần thực tế. Với khoản vay 500 triệu trong 5 năm, sai lầm này làm tổng lãi từ 118 triệu thành con số phi lý.

Tham số per quyết định bạn đang xem kỳ nào

Tham số per xác định kỳ thanh toán muốn tính lãi. Per phải nằm trong khoảng từ 1 đến nper. Với khoản vay 5 năm trả hàng tháng, per từ 1 đến 60.

Tính lãi cho các tháng khác nhau:

Tháng 1:

=IPMT(9.6%/12, 1, 60, 500000000)

Kết quả: -4,000,000 đồng

Tháng 12:

=IPMT(9.6%/12, 12, 60, 500000000)

Kết quả: -3,544,000 đồng

Tháng 60:

=IPMT(9.6%/12, 60, 60, 500000000)

Kết quả: -83,200 đồng

Lãi giảm dần theo thời gian vì số dư nợ giảm. Tháng đầu còn nợ 500 triệu nên lãi 4 triệu. Tháng cuối chỉ còn nợ khoảng 10 triệu nên lãi chỉ 83 nghìn. Phần gốc trong mỗi kỳ thanh toán tăng lên tương ứng.

Khi so sánh các gói vay, tôi dùng per để xem chi tiết lãi từng năm. Một số ngân hàng áp dụng lãi suất thả nổi, tăng sau 2 năm đầu. Bằng cách tính lãi cho per = 25 đến 36, tôi thấy rõ tác động của việc lãi suất tăng từ 9.6% lên 11.2% sẽ làm tổng lãi tăng thêm 24 triệu trong 3 năm cuối.

XEM THÊM: Vì Sao Ngân Hàng Báo 6% Nhưng Bạn Trả 6.14%? Hàm EFFECT Giải Mã

Tạo bảng chi tiết toàn bộ kỳ trả nợ

Thay vì tính từng tháng riêng lẻ, tôi tạo bảng tự động hiển thị lãi cho 60 tháng. Cột đầu tiên đánh số từ 1 đến 60, cột thứ hai dùng công thức IPMT kéo xuống.

Thiết lập bảng:

Ô A1: “Kỳ”
Ô B1: “Tiền lãi”
Ô C1: “Tiền gốc”
Ô D1: “Tổng phải trả”

Ô A2: 1
Ô A3: =A2+1 (kéo xuống đến 60)

Ô B2:

=IPMT($E$1/12, A2, $E$2*12, $E$3)

Trong đó:

E1 chứa lãi suất năm: 9.6%
E2 chứa số năm vay: 5
E3 chứa số tiền vay: 500,000,000

Dấu $ cố định tham chiếu, cho phép kéo công thức xuống 60 dòng mà các ô E1, E2, E3 không thay đổi. Chỉ A2 thay đổi thành A3, A4… để tính lãi cho từng tháng.

Ô C2 dùng hàm PPMT tính tiền gốc:

=PPMT($E$1/12, A2, $E$2*12, $E$3)

Ô D2 cộng lãi và gốc:

=-(B2+C2)

Dấu trừ ở đầu để chuyển số âm thành dương, dễ đọc hơn. Kéo cả 3 công thức xuống 60 dòng, bảng hiển thị đầy đủ chi tiết từng tháng.

Tôi thêm cột E tính tổng lãi tích lũy. Ô E2:

=SUM($B$2:B2)

Kéo xuống, ô E61 hiển thị tổng lãi toàn bộ 5 năm. Con số này là 118,246,000 đồng với lãi suất 9.6%. So với 136 triệu nhân viên ngân hàng báo, tôi tiết kiệm 18 triệu nhờ hiểu rõ cách tính và đàm phán được gói vay tốt hơn.

Phân biệt IPMT với PMT và PPMT

Ba hàm này làm việc cùng nhau trong tính toán trả góp. PMT tính tổng tiền phải trả mỗi kỳ, bao gồm cả gốc và lãi. IPMT tính riêng phần lãi. PPMT tính riêng phần gốc.

So sánh 3 hàm:

PMT cho khoản vay 500 triệu, 5 năm, lãi suất 9.6%:

=PMT(9.6%/12, 60, 500000000)

Kết quả: -10,470,770 đồng mỗi tháng

IPMT cho tháng đầu tiên:

=IPMT(9.6%/12, 1, 60, 500000000)

Kết quả: -4,000,000 đồng

PPMT cho tháng đầu tiên:

=PPMT(9.6%/12, 1, 60, 500000000)

Kết quả: -6,470,770 đồng

Cộng IPMT và PPMT của cùng một kỳ luôn bằng PMT: -4,000,000 + (-6,470,770) = -10,470,770

XEM THÊM: Cách Sử Dụng Hàm PMT Để Tính Mọi Khoản Vay Từ Xe Đến Nhà

IPMT đặc biệt hữu ích khi cần báo cáo thuế hoặc theo dõi chi phí lãi vay. Doanh nghiệp có thể khấu trừ tiền lãi nhưng không khấu trừ tiền gốc. Dùng IPMT tính tổng lãi cả năm, nhập vào báo cáo tài chính.

Với khoản vay trả hàng quý thay vì hàng tháng, tất cả tham số phải điều chỉnh. Lãi suất chia cho 4, nper nhân với 4, per chạy từ 1 đến 20 quý thay vì 60 tháng. Tính năng linh hoạt này giúp IPMT áp dụng cho mọi loại khoản vay.

So sánh nhiều gói vay trong 5 phút

Trước khi quyết định vay ngân hàng nào, tôi tạo một bảng so sánh nhanh. Mỗi dòng là một ngân hàng với lãi suất và điều kiện khác nhau. Cột cuối dùng IPMT tính tổng lãi.

Thiết lập bảng so sánh:

Ngân hàng	Lãi suất	Số năm	Tổng lãi
A	9.6%	5	?
B	8.9%	6	?
C	10.2%	4	?

Để tính tổng lãi cho ngân hàng A, dùng hàm CUMIPMT thay vì IPMT:

=CUMIPMT(9.6%/12, 60, 500000000, 1, 60, 0)

CUMIPMT tính tổng lãi từ kỳ 1 đến 60. Tham số cuối là type, bằng 0 nghĩa là trả cuối kỳ. Kết quả: -118,246,000 đồng.

Tương tự cho ngân hàng B:

=CUMIPMT(8.9%/12, 72, 500000000, 1, 72, 0)

Kết quả: -123,450,000 đồng

Ngân hàng C:

=CUMIPMT(10.2%/12, 48, 500000000, 1, 48, 0)

Kết quả: -107,890,000 đồng

Nhìn vào bảng, ngân hàng C có tổng lãi thấp nhất nhờ thời gian vay ngắn, mặc dù lãi suất cao nhất. Nhưng tiền góp hàng tháng sẽ cao hơn. Dùng PMT kiểm tra:

Ngân hàng A: 10.5 triệu/tháng
Ngân hàng B: 9.8 triệu/tháng
Ngân hàng C: 12.7 triệu/tháng

Quyết định cuối cùng phụ thuộc khả năng thanh toán hàng tháng. Nếu đủ khả năng trả 12.7 triệu, chọn C tiết kiệm 10 triệu so với A. Nếu chỉ trả được 10 triệu, chọn A hoặc B.

Toàn bộ phân tích này mất 5 phút với Excel thay vì ngồi tính tay hoặc tin hoàn toàn vào lời tư vấn. Một số ngân hàng cố tình nhấn mạnh khoản góp thấp mà giấu tổng lãi cao. IPMT lột trần chiêu trò đó.

Những lỗi thường gặp cần tránh

Lỗi #NUM!: Xuất hiện khi per lớn hơn nper. Nếu vay 60 tháng nhưng nhập per = 61, hàm trả về lỗi. Kiểm tra per phải từ 1 đến nper.

Lỗi #VALUE!: Xảy ra khi nhập văn bản thay vì số. Rate phải là 9.6% hoặc 0.096, không phải “9.6 phần trăm”. Nper và per phải là số nguyên dương.

XEM THÊM: 5 Sai Lầm Chết Người Khi Dùng Hàm DB Tính Khấu Hao Tài Sản

Kết quả dương thay vì âm: IPMT trả về số âm vì đây là tiền phải trả. Nếu nhập pv dương, kết quả sẽ âm. Nếu nhập pv âm (chẳng hạn -500000000), kết quả sẽ dương. Quy ước là tiền vay vào là dương, tiền trả ra là âm.

Nhầm lẫn giữa lãi suất năm và tháng: Lỗi phổ biến nhất. Khi hợp đồng ghi lãi suất 9.6%/năm, cần chia cho 12 để có lãi suất tháng. Nếu hợp đồng ghi lãi suất 0.8%/tháng, dùng trực tiếp không cần chia.

Quên type khi trả đầu kỳ: Hầu hết khoản vay trả cuối kỳ, type = 0 hoặc bỏ trống. Một số hợp đồng yêu cầu trả đầu kỳ, phải ghi type = 1. Sự khác biệt này ảnh hưởng cách tính lãi vì thời điểm thanh toán thay đổi.

Tôi từng mắc lỗi nhập 9.6 thay vì 9.6% trong rate. Excel hiểu 9.6 là 960%, dẫn đến kết quả phi lý. Luôn kiểm tra định dạng ô là phần trăm hoặc thập phân.

Tương thích và phiên bản

Hàm IPMT có sẵn trong Excel 2007 trở lên, bao gồm Excel 2010, 2013, 2016, 2019, 2021, Excel 365. Phiên bản Excel cho Mac từ 2011 trở đi cũng hỗ trợ đầy đủ.

Google Sheets hỗ trợ IPMT với cú pháp giống hệt Excel. Bảng tính tạo trong Excel có thể upload lên Google Drive và tiếp tục hoạt động bình thường.

LibreOffice Calc và Apache OpenOffice cũng có hàm IPMT. Nếu đang dùng phần mềm văn phòng mã nguồn mở, công thức này vẫn áp dụng được không cần chỉnh sửa.

Với khoản vay có lãi suất thay đổi theo thời gian, IPMT tính dựa trên lãi suất cố định nên kết quả chỉ là ước tính. Trong trường hợp đó, cần tách khoản vay thành nhiều giai đoạn, mỗi giai đoạn dùng lãi suất tương ứng. Hoặc dùng các công cụ tài chính chuyên dụng thay vì Excel.

Hàm IPMT giúp kiểm soát tài chính cá nhân, so sánh các lựa chọn vay, và phát hiện sai sót trong báo giá ngân hàng. 18 triệu tôi tiết kiệm không phải từ một cú may mắn mà từ việc hiểu rõ cách tính lãi và đặt câu hỏi với con số nhân viên ngân hàng đưa ra.