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):
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
Figure 11-14: Calculating the principal and interest components of selected payments.