Documentation/How Tos/Calc: ISPMT function
From Apache OpenOffice Wiki
< Documentation | How Tos
CALC FUNCTIONS
Depreciation
Payment Streams, Annuities, Loans
Securities
Coupons
Miscellaneous
Contents
ISPMT
Returns the interest paid in a period for a fixed rate loan.
Syntax:
ISPMT(rate; period; numperiods; principal)
- rate: the interest rate per period.
- period: the period for which interest is to be calculated.
- numperiods: the total number of payment periods in the term.
- principal: the initial sum borrowed.
- ISPMT considers a fixed rate loan, where you repay the same amount of capital at the start of each period, and pay interest on the outstanding balance at the end of each period. The interest you pay will depend of the remaining outstanding balance each period, and will decline with time. ISPMT returns that interest.
- The capital you repay each period is given by principal/numperiods.
- This is not the same as IPMT, where the total sum you pay each period (interest plus capital) is constant.
Example:
ISPMT(5%; 1; 4; 12000)
- returns -450. You take out a 4 year loan of 12000 currency units at a yearly interest rate of 5%, making yearly payments. On the day you take out the loan, you repay capital of 12000/4 = 3000, which leaves an outstanding capital balance of 12000 - 3000 = 9000. At the end of the 1st period interest will be due, that is 9000 * 5% = 450. The sign is negative, because you have to pay the interest.
ISPMT(5%; 2; 4; 12000)
- returns -300. You take out a 4 year loan of 12000 currency units at a yearly interest rate of 5%, making yearly payments. At the start of the second period, you pay the 450 interest due as above, and you also repay a second 3000 of capital, leaving an outstanding capital balance of 9000 - 3000 = 6000. At the end of the 2nd period interest will be due, that is 6000 * 5% = 300. The sign is negative, because you have to pay the interest.
ISPMT(5%; 3; 4; 12000)
- returns -150. You take out a 4 year loan of 12000 currency units at a yearly interest rate of 5%, making yearly payments. At the start of the third period, you pay the 300 interest due as above, and you also repay a third 3000 of capital, leaving an outstanding capital balance of 6000 - 3000 = 3000. At the end of the 3rd period interest will be due, that is 3000 * 5% = 150. The sign is negative, because you have to pay the interest.
ISPMT(5%; 4; 4; 12000)
- returns 0. You take out a 4 year loan of 12000 currency units at a yearly interest rate of 5%, making yearly payments. At the start of the fourth period, you pay the 150 interest due as above, and you also repay a fourth 3000 of capital, leaving an outstanding capital balance of 3000 - 3000 = 0. There is thus no interest to pay during the fourth period.
Issues:
- ISPMT is implemented by Excel, apparently because the function was available in Lotus 1-2-3. It is far from clear if any real-life loan operates in this manner.
See Also