Explainry Tutorials to Expert in IT

ISPMT function in Excel


ISPMT function calculates the amount of interest paid on a loan/ investment for a specific period.


ISPMT(rate, per, nper, pv)


rate means the rate of interest per period,

per means the period for which you need to calculate interest,

nper means the total number of period over which loan investments will be paid, and

pv means the present value.


For a loan of $800,000, to be repaid in 36 months (3 years) with an annual interest rate of 12%, the ISPMT function calculates the amount of interest paid as under:



Common Error

The most common error made by users that they forget to convert the interest rate (rate) to months or quarters. For this remember that the rate and nper arguments should be in same units like:

months = 12 * years
quarters = 4 * years
monthly rate = annual rate / 12
quarterly rate = annual rate / 4


Copyright © 2016 - 2020 Explainry.com | All Rights Reserved