The Excel ISPMT Function

Related Function:

IPMT

Function Description

The Excel ISPMT function calculates the interest paid during a specific period of a loan or investment.

The syntax of the function is:

ISPMT( rate, per, nper, pv )

Where the arguments are as follows:

rate	-	The interest rate, per period.
per	-	The period for which the interest is to be calculated (must be an integer between 1 and nper).
nper	-	The number of periods over which the loan or investment is to be paid.
pv	-	The present value of the loan/investment.

Cash Flow Sign Convention:

Note that, in line with the general cash flow sign convention, cash outflows are represented by negative numbers and cash inflows are represented by positive numbers. This is seen in the example below.

Excel Ispmt Function Example

In the following spreadsheet, the Excel Ispmt function is used to calculate the interest paid, during months 1 and 2, of a loan of $50,000 which is to be paid off over 5 years. Interest is charged at a rate of 5% per year.

Formula:

	A	B
1	Interest payments during months 1 and 2 on a loan of $50,000 that is to be paid off over 5 years, with an interest rate of 5% per year:
2	Mth 1:	=ISPMT( 5%/12, 1, 60, 50000 )
3	Mth 2:	=ISPMT( 5%/12, 2, 60, 50000 )

Result:

	A	B
1	Interest payments during months 1 and 2 on a loan of $50,000 that is to be paid off over 5 years, with an interest rate of 5% per year:
2	Mth 1:	-$204.86
3	Mth 2:	-$201.39

Note that in this example:

The payments are made monthly, so we have to converted the annual interest rate of 5% into the monthly rate (=5%/12), and the number of periods from years to months (=5*12).
The returned interest values are negative, as these represent outgoing payments (for the individual taking out the loan).

For further details and examples of the Excel Ispmt function, see the Microsoft Office website.

Ispmt Function Error

If you get an error from the Excel Ispmt function, this is likely to be the #VALUE! error:

Common Error

#VALUE!

Occurs if any of the supplied arguments are non-numeric.

Also, the following problem is encountered by some users:

Common Problem

The result from the Excel Ispmt function is much higher or much lower than expected.

Possible Reason

Many users, when calculating monthly or quarterly payments, forget to convert the interest rate or the number of periods to months or quarters.

Solve this problem by ensuring that the rate and the nper arguments are expressed in the correct units. I.e.:

months = 12 * years
quarters = 4 * years

monthly rate = annual rate / 12
quarterly rate = annual rate / 4

Return to the Excel Financial Functions Page
Return to the List of All Built-In Excel Functions