Practice free

IPMT function

IPMT returns the interest portion of a single loan payment, as a negative number for a positive loan. Pair it with PPMT to build a loan amortization table.

Example

Read it as a sentence: for an 8000 loan at 10% a year paid monthly over 36 months, find the interest part of payment number 1.

Practice this in the app

Syntax

IPMT(rate, per, nper, pv, [fv], [type])

Arguments in [square brackets] are optional.

rate (required)
Interest rate per period.
per (required)
Period to analyse (1 to nper).
nper (required)
Total number of periods.
pv (required)
Loan amount today.
fv (optional)
Balance wanted at the end; 0 if omitted.
type (optional)
0 = payments at period end (default), 1 = at start.

Returns: Interest portion of one loan payment, as a negative number.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=IPMT(0.05/12,1,60,20000) ($83.33) Period 1
=IPMT(0.05/12,60,60,20000) ($1.57) Last period
=IPMT(0.05/12,1,60,20000,0,1) $0.00 Type 1 period 1 (0)
=IPMT(0.05,1.5,10,1000) ($48.04) Per 1.5
=IPMT(0,1,10,1000) $0.00 Rate 0
=IPMT("0.05","1","10","1000") ($50.00) Text number
=IPMT(0.05,0,10,1000) #NUM! Per 0
=IPMT(0.05,11,10,1000) #NUM! Per > nper

Watch out for

  • Use a monthly rate for monthly payments.
  • Result is negative when pv is positive.

Where it works

Excel
IPMT is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .