The Excel ISPMT function calculates the interest paid, during a specific period of a loan or investment.
The syntax of the function is :
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|
The following spreadsheet shows the Excel Ispmt function used to calculate 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.
The spreadsheet on the left shows the format of the functions, and the spreadsheet on the right shows the results.
Note that in this example :
Further examples of the Excel Ispmt function are provided on the Microsoft Office website.
If you get an error from the Excel Ispmt function, this is likely to be the #VALUE! error:
|#VALUE!||-||Occurs if any of the supplied arguments are not recognised as numeric values|
Also, the following problem is encountered by some users:
The result from the Excel Ispmt function is much higher or much lower than expected.
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;||monthly rate||=||annual rate / 12|
|quarters||=||4 * years;||quarterly rate||=||annual rate / 4|