ID EN
…
#Financial

ISPMT

Excel Functions

Get interest paid for specific period

Syntax

EXCEL 
=ISPMT(rate, per, nper, pv)

Arguments

Parameter Description
rate Interest rate.
per Period (starts with zero, not 1).
nper Number of periods.
pv Present value.

Return Value

Interest amount in given period

Details

The ISPMT function calculates the amount of interest in a given period of an investment where principal payments are equal. The given period is specified as a zero-based number instead of a 1-based number. For example, to calculate the interest amount in payments for a loan where the amount is $10,000, the interest rate is 10%, and there are 5 periods in which the principal payment is constant (even), you can use: In the example shown, the formula in H11, copied down, is: Note ISPMT assumes principal amounts are equal, but the payment is variable. For a loan where the payment is a fixed amount, see the IPMT function.

Examples

Example 1

The ISPMT function calculates the amount of interest in a given period of an investment where principal payments are equal. The given period is specif

EXCEL 
=ISPMT(10%,0,5,-10000) // interest in period 1
=ISPMT(10%,1,5,-10000) // interest in period 2
=ISPMT(10%,2,5,-10000) // interest in period 3
=ISPMT(10%,3,5,-10000) // interest in period 4
=ISPMT(10%,4,5,-10000) // interest in period 5
Example 2

In the example shown, the formula in H11, copied down, is:

EXCEL 
=ISPMT($C$6,B11-1,$C$7,-$C$5)

See Also

IPMT