What it does
On an equal-payment loan every payment is the same, but the split between interest and principal changes. Interest is charged on the remaining balance, so early payments are mostly interest and little principal; as the balance falls, so does the interest share. IPMT returns the interest part of the payment number you give it.
The arguments are the same as for PMT, with the payment number (1, 2, … nper) added. The sign rule is the same too: if the loan amount is entered as money in (positive), the interest comes back negative because it is money you pay.
For the principal part of the same payment, use PPMT; the two add up to PMT. To total the interest over a range of payments in one formula, there is CUMIPMT.
Syntax
=IPMT(rate, per, nper, pv, [fv], [type])=FAİZTUTARI(oran; dönem; dönem_sayısı; bd; [gd]; [tür])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
rate | Required | The interest rate per period, e.g. 0.06/12 for monthly payments. |
per | Required | The number of the payment whose interest you want; it must be between 1 and nper. |
nper | Required | The total number of payments. |
pv | Required | The loan amount, positive as money you receive. |
fv | Optional | The amount left after the last payment; leave it out for a loan. |
type | Optional | 0 or omitted: payments at the end of each period. 1: at the start, in which case the first payment carries no interest. |
Examples
1The interest share of the first and last payments
The loan details are in column B. Column E gives the payment, column F the interest part with IPMT and column G the principal part with PPMT. The loan amount is positive, so all three are negative.
=PMT($B$3/12,$B$4,$B$2)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Value | Payment No | Payment | Interest | Principal | |
| 2 | Loan Amount | 300,000.00 | 1 | -1,932.90 | -1,500.00 | -432.90 | |
| 3 | Annual Rate | 6.00% | 2 | -1,932.90 | -1,497.84 | -435.07 | |
| 4 | Term (Months) | 300 | 150 | -1,932.90 | -1,022.71 | -910.20 | |
| 5 | 300 | -1,932.90 | -9.62 | -1,923.29 |
- E2 (filled down to E5)
=PMT($B$3/12,$B$4,$B$2) - F2 (filled down to F5)
=IPMT($B$3/12,D2,$B$4,$B$2) - G2 (filled down to G5)
=PPMT($B$3/12,D2,$B$4,$B$2)
The payment is the same every month (-1,932.90), but -1,500.00 of the first one is interest. By payment 300 the interest share is only -9.62; the rest is principal: -1,923.29.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2An interest column and total interest on a short loan
The payment numbers are in column A and the loan details in column E. The formula in B2 is filled down; E5 adds up the interest of the six payments.
=IPMT($E$3/12,A2,$E$4,$E$2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Payment No | Interest | Item | Value | |
| 2 | 1 | -112.50 | Loan Amount | 18,000.00 | |
| 3 | 2 | -94.04 | Annual Rate | 7.50% | |
| 4 | 3 | -75.47 | Term (Months) | 6 | |
| 5 | 4 | -56.78 | Total Interest | -395.79 | |
| 6 | 5 | -37.97 | |||
| 7 | 6 | -19.04 |
- B2 (filled down to B7)
=IPMT($E$3/12,A2,$E$4,$E$2) - E5
=SUM(B2:B7)
The interest share falls each month: -112.50 on the first payment, -19.04 on the last. The total interest over six payments is -395.79; the sign is negative because the money leaves you.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #NUM!
Cause: per is less than 1 or greater than nper; for example payment 301 of a 300-month loan.
Fix: Make sure the payment number is between 1 and the number of periods.
- Interest far too large
Cause: The annual rate was not divided by 12.
Fix: For monthly payments, give the rate as
annual_rate/12.- Negative result
Cause: The loan amount was positive, so the interest comes back negative to show it is money paid.
Fix: Put a minus sign in front of the formula to see it as positive.
Good to know
- In a schedule, take the payment number from a column:
=IPMT($E$3/12,A2,$E$4,$E$2)filled down gives each row the interest of its own payment. - For the total interest on the loan:
=CUMIPMT(0.06/12,300,300000,1,300,0); the result is negative.
In Nexell
Get Nexell to do it from a sentence
Add the interest part of each payment to my repayment schedule, with the total interest underneath
Convert to tableBring your schedule into the tool and type this. Nexell adds the interest column and the total with real Excel formulas and shows them in the preview; in the downloaded file, every row recalculates when you change the rate.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How much interest do I pay in the first year of a loan?
=CUMIPMT(rate,nper,pv,1,12,0) adds up the interest of the first 12 payments (CUMIPMT). You get the same result by adding IPMT for each payment from 1 to 12.