calculate-loan

01 Loan payments

Excel Interest Payment Function: IPMT Explained

Calculator No. 01-24295 · Updated · Formula and assumptions shown below

Interest payment (IPMT) $133.33
Full payment
$-405.53
Level-payment formula, rounded to the cent
Balance after period
$-20,538.86
From the formula below, to the cent
Loan amount
$20,000
Input
You enter
Loan amount ($), Interest rate (APR) (%), Loan term (years), Payment period
You get
Interest payment (IPMT) ($), Full payment ($), Balance after period ($)
Rounding
2 dp

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

Excel’s interest payment function, IPMT, calculates the interest portion of a loan payment for a given period, assuming a fixed interest rate and constant payments. You enter the rate, number of periods, total number of payments, loan amount, and optionally the future value and payment timing, and it returns the interest amount for that specific period. This article explains how IPMT works, how to use it correctly, and how it differs from other Excel loan functions.

Formula

Excel’s IPMT function uses this formula:

IPMT = – (P × r × (1 + r)^(n – 1) – PMT × ((1 + r)^(n – 1) – 1))

Where:

  • P = principal (loan amount) in dollars
  • r = periodic interest rate (APR divided by 12 for monthly payments)
  • n = the specific period number (1 for first month, 2 for second, etc.)
  • PMT = the full payment amount, calculated as P × r / (1 – (1 + r)^(-N)), where N is the total number of periods

The negative sign in Excel’s output reflects cash flow direction: money you pay out is negative. If you prefer positive numbers, you can wrap the function in ABS() or simply ignore the sign.

Worked Example

Using the default values: $20,000 loan, 8% APR, 5-year term, and we want the interest for period 1.

  1. Convert APR to monthly rate: 8% / 12 = 0.0066667
  2. Total number of payments: 5 × 12 = 60
  3. Calculate the full payment: PMT = 20000 × 0.0066667 / (1 – (1.0066667)^-60) = $405.53
  4. Calculate IPMT for period 1: IPMT = 20000 × 0.0066667 × (1 – (1.0066667)^(1-1)) – 405.53 × ((1.0066667)^(1-1) – 1) = 133.33 – 0 = $133.33

The calculator above shows the same result: $133.33 for the first period’s interest. The full payment is $405.53, and the balance after period 1 is $19,727.80 (because you paid $405.53, of which $133.33 was interest, so principal reduced by $272.20).

Assumptions

  • Fixed interest rate for the entire loan term.
  • Payments are made at the end of each period (unless you set the ‘type’ argument to 1 for beginning of period).
  • Interest compounds monthly at APR/12, which is standard for most amortizing loans.
  • No fees, taxes, insurance, or other charges are included in the calculation.

How IPMT Differs from PPMT and PMT

Excel has three related functions:

  • PMT returns the total payment (interest + principal) for each period.
  • IPMT returns only the interest portion of that payment.
  • PPMT returns only the principal portion.

For any period, PMT = IPMT + PPMT. As the loan ages, IPMT decreases and PPMT increases, but the total payment stays constant.

How to Use IPMT in Excel

The syntax is: =IPMT(rate, per, nper, pv, [fv], [type])

  • rate: periodic interest rate (e.g., 8%/12 for monthly)
  • per: the period you want interest for (must be between 1 and nper)
  • nper: total number of payment periods
  • pv: present value, or loan amount (enter as a positive number)
  • fv: future value, usually 0 (optional)
  • type: 0 for end of period (default), 1 for beginning (optional)

Example: =IPMT(8%/12, 1, 60, 20000) returns -133.33 (or 133.33 if you omit the sign).

Common Mistakes and Pitfalls

  • Using APR directly: Always divide the annual rate by 12 for monthly payments. Using 8% instead of 8%/12 will give a wildly wrong result.
  • Mismatched periods: If you have a 5-year loan with monthly payments, nper must be 60, not 5.
  • Wrong period number: ‘per’ must be between 1 and nper. Using 0 or a number greater than nper returns an error.
  • Sign confusion: Excel returns negative values for payments. If you want positive numbers, use =ABS(IPMT(…)) or just ignore the sign.
  • Forgetting the future value: For most loans, fv is 0. If you omit it, Excel assumes 0, which is correct. But if you’re calculating a savings goal, you’d set fv to the target amount.

When to Use IPMT vs. Other Methods

Use IPMT when you need the interest for a specific month, such as for tax deduction calculations or to see how much of a payment goes to interest. If you need the total interest over the life of the loan, multiply the monthly payment by the number of periods and subtract the principal, or use CUMIPMT. If you need the remaining balance after a certain period, use the balance formula shown in the calculator above, or use the CUMPRINC function.

Frequently Asked Questions

Why does IPMT return a negative number?

Excel treats money you pay out as negative cash flow. So a payment of $133.33 is shown as -133.33. To see a positive number, wrap the function in ABS(), like =ABS(IPMT(…)). The sign doesn’t affect the math.

What is the difference between IPMT and ISPMT?

ISPMT is an older function that calculates interest for a period assuming equal principal payments, not equal total payments. It’s rarely used. IPMT is the standard function for amortizing loans with constant payments.

Can I use IPMT for a loan with variable rates?

No, IPMT assumes a fixed rate. For variable rates, you’d need to build a custom amortization schedule that recalculates the rate each period.

How do I calculate total interest paid over the life of the loan?

Use the formula: total interest = (PMT × nper) – principal. Or use the CUMIPMT function: =CUMIPMT(rate, nper, pv, 1, nper, 0). This gives the cumulative interest from period 1 to the last period.

What if I make extra payments?

IPMT doesn’t handle extra payments. You’d need to build a schedule that reduces the principal balance when an extra payment is made, then recalculate interest on the new balance.

The result from any Excel function or this calculator is an estimate based on the inputs you provide; your lender’s amortization schedule will show the exact figures.

Related

Formula used
in p | Loan amount | $ | 20000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 5 | 0.0833 | 50
in per | Payment period | - | 1 | 1 | 600
out ipmt | Interest payment (IPMT) | $ | -round(p * (apr / 1200) * (1 - (1 + apr / 1200) ^ -(per - 1) - (1 - (1 + apr / 1200) ^ -yrs) / (1 - (1 + apr / 1200) ^ -yrs) * (1 + apr / 1200) ^ -(per - 1)), 2) | 2
out pmt | Full payment | $ | -round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out bal | Balance after period | $ | -round((p * (1 + apr / 1200) ^ per - (-round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2)) * ((1 + apr / 1200) ^ per - 1) / (apr / 1200)), 2) | 2

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.

More in this section

The whole Loan payments section →