Skip to content

Excel function · Financial

Excel IPMT function

IPMT tells you how much of a chosen payment on an equal-payment loan is interest: the interest share is large in the first payments and shrinks over the term. It builds the interest column of a repayment schedule.

In Turkish Excel: FAİZTUTARI

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

English Excel=IPMT(rate, per, nper, pv, [fv], [type])
Turkish Excel=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

ArgumentRequiredDescription
rateRequiredThe interest rate per period, e.g. 0.06/12 for monthly payments.
perRequiredThe number of the payment whose interest you want; it must be between 1 and nper.
nperRequiredThe total number of payments.
pvRequiredThe loan amount, positive as money you receive.
fvOptionalThe amount left after the last payment; leave it out for a loan.
typeOptional0 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.

E2fx=PMT($B$3/12,$B$4,$B$2)
The interest share of the first and last payments: example table
ABCDEFG
1ItemValuePayment NoPaymentInterestPrincipal
2Loan Amount300,000.001-1,932.90-1,500.00-432.90
3Annual Rate6.00%2-1,932.90-1,497.84-435.07
4Term (Months)300150-1,932.90-1,022.71-910.20
5300-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.

B2fx=IPMT($E$3/12,A2,$E$4,$E$2)
An interest column and total interest on a short loan: example table
ABCDE
1Payment NoInterestItemValue
21-112.50Loan Amount18,000.00
32-94.04Annual Rate7.50%
43-75.47Term (Months)6
54-56.78Total Interest-395.79
65-37.97
76-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

Type this in Nexell

Add the interest part of each payment to my repayment schedule, with the total interest underneath

Convert to table

Bring 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.

Related functions

All financial functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free