Syntax. ISPMT(Rate,Per,Nper,Pv)
Definition. This function calculates the sum of interest that accrues for a certain part of the year with a given annual interest rate and simple yield in arrears. Other time periods are possible, but not common.
For reasons of compatibility, this function was included in Lotus 1-2-3.
Arguments
Rate (required) The annual interest rate in arrears that is to be distributed equally among all the periods in the year
Per (required) Specifies the number of the period (starting with zero) from the end of which the interest is to be calculated until the end of the year
Nper (required) The total amount of periods with the year; determines the factor for the annual interest rate
Pv (required) The present value (cash value) whose interest in arrears is to be calculated
Background. Dividing the annual interest rate into a period interest rate of intra-annual time periods is a process that happens frequently (overdrawing checking accounts, current accounts, or mortgage accounts). However, usually the intra-annual interest rate is used to pay compound interest within the year. This is not taken into consideration by this function.
Example. What the function actually does is best explained with the example of a savings account. If you deposit $100.00 on April 30 to your savings account that has an interest rate of 6 percent, you receive it for 8/12 of the year (assuming that the amount stays in your account until the end of the year). By using
=ISPMT(6%,4,12,-100)
you get the result of $4.00.
If you deposit the same amount on May 5, already 135 interest days of the year have passed and you still get interest for 225 days. With
=ISPMT(6%,135,360,-100)
you get the amount of $3.75.