ISPMT function in Excel

Use

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

Syntax

ISPMT(rate, per, nper, pv)

Where,

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.

Example

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:

Formula:

Result:

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 - 2018 Explainry.com | All Rights Reserved