IPMT
Calculate the interest portion of a loan payment for a given period with IPMT.
Financial
| Excel All versions
| Google Sheets Supported
Spreadsheet editor
Spreadsheet editor
Syntax
=IPMT(Rate, Period, Num_periods, PV, [FV], [Type])
Returns: Number Arguments
| Argument | Required | Description |
|---|---|---|
| Rate | Yes | The interest rate per period. For monthly payments on an annual rate, divide by 12. |
| Period | Yes | The specific period for which you want the interest amount. Must be between 1 and Num_periods. |
| Num_periods | Yes | The total number of payment periods for the loan or investment. |
| PV | Yes | The present value (loan amount or current balance). |
| FV | No | The future value, or the balance you want after the last payment. Defaults to 0 if omitted. |
| Type | No | When payments are due. 0 (default) for end of period, 1 for beginning of period. |
About
IPMT returns the interest portion of a specific payment on a loan or investment with constant periodic payments and a fixed interest rate. For example, if you have a 5-year car loan, IPMT can tell you exactly how much of your 12th monthly payment goes toward interest versus principal.
This is especially useful when building amortization schedules or comparing financing options. Early loan payments typically have a larger interest component, and IPMT lets you see that shift over time. Use PPMT to get the principal portion of the same payment, or PMT to calculate the full payment amount.
Keep in mind that IPMT returns a negative number when you owe money (positive present value), since interest paid is cash going out. To get a positive result, put a minus sign before the formula or use ABS. For total interest paid across all periods, consider CUMIPMT.
This is especially useful when building amortization schedules or comparing financing options. Early loan payments typically have a larger interest component, and IPMT lets you see that shift over time. Use PPMT to get the principal portion of the same payment, or PMT to calculate the full payment amount.
Keep in mind that IPMT returns a negative number when you owe money (positive present value), since interest paid is cash going out. To get a positive result, put a minus sign before the formula or use ABS. For total interest paid across all periods, consider CUMIPMT.
Exercises using IPMT
Examples
How interest shrinks over a 30-year mortgage
Compare the interest portion at different points in a $300,000 mortgage. Notice how month 1 is almost all interest, while month 360 is nearly all principal. Change the rate or loan amount to see how the shift changes.
Spreadsheet editor
Annual rate vs. monthly rate mistake
See what happens when you forget to divide the annual rate by 12 for a $300,000 mortgage. The wrong result is dramatically larger. Edit the rates in column B to experiment with different values.
Spreadsheet editor
How payment timing changes interest
See how payment timing changes the interest portion across the full loan. Type 1 pays at the start of each period, so the first interest charge drops to zero and later periods stay lower than Type 0.
Spreadsheet editor
Watch out for
⚠
Using the annual rate instead of the periodic rate
You pass the annual interest rate directly (e.g., 6%) but your payments are monthly, giving you a result that is far too large.
→ Divide the annual rate by the number of periods per year. For monthly payments, use Rate/12. For quarterly, use Rate/4.
⚠
Period out of range
Setting the Period argument to 0, a negative number, or a value greater than Num_periods returns a #NUM! error.
→ Make sure Period is between 1 and Num_periods (inclusive).
⚠
Confused by negative results
IPMT returns a negative number and you expected a positive one.
→ When PV is positive (you owe money), interest paid is an outflow, so Excel returns it as negative. Negate the formula (=-IPMT(...)) or wrap it with ABS if you want a positive number.
Tips & notes
Make sure your Rate and Num_periods use the same time unit. A 6% annual rate with monthly payments means Rate = 0.06/12 and Num_periods = years * 12. Mismatched units are the most common source of wrong results.
Common questions
Why does IPMT return a negative number?
When your present value is positive (you borrowed money), interest payments are cash outflows, so Excel shows them as negative. Use =-IPMT(...) or =ABS(IPMT(...)) to get a positive result.
How do I calculate total interest paid over the life of a loan?
Use CUMIPMT when you want the total interest across a range of payments. For example, =CUMIPMT(6%/12,360,300000,1,360,0) returns the total interest paid over a 30-year loan with monthly payments. If you prefer to work with IPMT, calculate each period's interest in a helper column and sum those results.
Does IPMT work for investments as well as loans?
Yes. For an investment, enter PV as a negative number (money you deposit). The interest earned will then be returned as a positive value.