Practice free

ISPMT function

ISPMT returns the interest for a period of a loan repaid in equal chunks of principal, so interest shrinks in a straight line. It differs from IPMT, which assumes level payments.

Example

Read it as a sentence: find the interest part of the payment for period 1 on a 4000 loan over 4 periods at 10% per period, which gives -300.

Practice this in the app

Syntax

ISPMT(rate, per, nper, pv)

rate (required)
Interest rate per period.
per (required)
Period to check, where the first period is 0.
nper (required)
Total number of periods.
pv (required)
Loan amount.

Returns: Interest for a period of a loan with equal principal payments.

What Excel actually returns

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

FormulaExcel showsCase
=ISPMT(0.1,1,3,8000000) -533333.3333 Period 1
=ISPMT(0.1,0,3,8000000) -800000 Period 0
=ISPMT(0.1,5,3,1000) 66.66666667 Per > nper (no check)
=ISPMT(-0.1,1,3,1000) 66.66666667 Negative rate
=ISPMT("0.1","1","3","1000") -66.66666667 Text number
=ISPMT(0.1,{0,1,2},3,1000) -100 Spill
=ISPMT(0.1,1,0,1000) #DIV/0! Nper 0
=ISPMT("a",1,3,1000) #VALUE! Text

Watch out for

  • Periods count from 0, unlike IPMT where they start at 1.
  • The result is negative for a positive loan.

Where it works

Excel
ISPMT 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 .