The IPMT function calculates the interest payment portion from a given periodic payment against an investment, based on periodic constant payments. For example, suppose you made an investment of $1000 for one year with monthly periodic payments, by using the IPMT, you can segregate the interest received from any specified monthly payment. Similarly, you can also calculate the principal portion only by using the PPMT function.
IPMT(rate, per, nper, pv, [fv], [type])
The IPMT function has the followings arguments:
rate – Required. The rate of interest on the investment.
per – Required. The period for which interest amount needs to be calculated. It starts from 1 to nper.
nper – Required. The total number of payments period in an annuity.
pv – Required. The present value of the investment.
[fv] – Optional. The cash balance you want to attain after the last payment is made. The future value of the investment. If omitted, Excel assumed 0.
[type] – Optional. The time for periodic payments when they due. Use 0 if payments are due at the end of the period and 1 if at the beginning of the period. If type is omitted, it is assumed to be 0.
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
PPMT function calculates the principal value of an investment for a given period.
PMT function is used to calculate the periodic payment for a loan.
CUMIPMT function calculates the cumulative interest paid between the start date and the end date.
CUMPRINC function calculates the cumulative principal paid on a loan between the specified period.