How to Use IPMT Function in Excel (Find Interest Payment)

Hello friends! Today I show you IPMT() function in Excel. It is the special brother of PMT!

What is IPMT? It stand for "Interest Payment". When you pay $200 for your car loan every month, some money goes to pay the car, and some money goes to the bank as interest. IPMT helps you see exactly how much interest money you give the bank each month!

🛠️ How to write IPMT Function

To use IPMT, you need to tell Excel: the interest rate, WHICH month you want to check, total months, and the total loan amount.

Formula Syntax:

=IPMT(rate, per, nper, pv)

• rate: Interest rate from bank (Must divide by 12 for monthly rate!).
• per: WHICH month you want to check? (Example: 1 for first month, 5 for fifth month).
• nper: Total number of months you will pay. (Example: 5 years = 60 months).
• pv: Present Value. Total loan money you borrow right now.

📊 Short & Clear Example

Imagine you buy a car for $10,000 (This is PV). Bank charge 6% interest per year (This is Rate). You will pay for 5 years (This is NPER).

You want to know: How much interest do I pay in the very FIRST month?

Formula to find Interest for Month 1:

=IPMT(B1/12, B2, B3*12, B4)

• Result: -$50.00
• What it means? It means in your very first month, $50 goes straight to the bank's pocket as interest!
• Why divide and multiply by 12? Because we check monthly! Rate 6% divide by 12, and 5 years multiply by 12 (60 total months).

Check out my Advance Loan Calculator Tool to calculate loan payment easily!

💡 Easy Pro Tips

1. Interest goes DOWN over time: If you change per to month 60 (the very last month): =IPMT(B1/12, 60, B3*12, B4). The result is only -$0.96! Why? Because as you pay your loan, the interest gets smaller and smaller!
2. Make it Positive: The result shows minus (-) because it is money you pay. If you want a positive number, just put a minus sign before the PV: =IPMT(B1/12, B2, B3*12, -B4).
3. PPMT is the other brother: While IPMT finds the interest, PPMT finds the Principal (the money that actually pays off your car). Together, IPMT + PPMT = PMT (Total Monthly Payment)!

📚 Useful Resources

• How to Use PMT Function in Excel (Calculate Loan Easy)
• How to Use RATE Function in Excel (Find Hidden Interest)
• How to Use PV Function in Excel (Find Present Value)