Microsoft Office Tutorials and References

In Depth Information

**Calculating the Interest and Principal Components**

Using the IPMT and PPMT functions

You may need to know how much of a particular payment constitutes interest, and how much of

the payment goes toward paying off the debt (the principal). The portion of the payment that

pays down the debt is smaller at the beginning of the loan because the interest portion is higher

(because of the higher balance).

If youâ€™ve created an amortization schedule, these functions are not particularly useful

because you can simply refer to the schedule. The IPMT (interest payment) and PPMT

(principal payment) functions are most useful when you need to determine the

interest/principal breakdown of a particular payment.

The syntax for these two functions is as follows (bold arguments are required):

IPMT(rate,per,nper,pv,fv,type)

PPMT(rate,per,nper,pv,fv,type)

As with all amortization functions, the rate, per, and nper arguments must match in terms of the

time period. If the loan term is measured in months, the rate argument must be the effective rate

per month, and the per argument (that is, the period of interest) must be a particular month.

The example in Figure 11-14 shows calculations for three payments toward a 30-year mortgage:

the first payment, a payment at month 180, and the last payment (month 360). The formulas for

computing the amounts for payment number 1 are

=IPMT(5.5%/12,1,30*12,350000)

=PPMT(5.5%/12,1,30*12,350000)

Figure 11-14:
Calculating the principal and interest components of selected payments.