Microsoft Office Tutorials and References
In Depth Information
Calculating the Interest and Principal Components
The formulas for the other payments are the same except that the per argument reflects the
payment being computed. Summing the IMPT and the PPMT amounts returns the same result as
using the PMT function.
It’s interesting (and a little disheartening) to see how little of that first payment goes
toward paying off the debt.
Using the CUMIPMT and CUMPRINC functions
The IPMT and PPMT functions show the interest and principal components for a single payment.
The CUMIPMT and CUMPRINC functions show the same components but for a specified series of
The syntax for these functions is shown here (all arguments are required):
CUMIPMT(rate, nper, pv, start_period, end_period, type)
CUMPRINC(rate, nper, pv, start_period, end_period, type)
The following example computes the amount of interest paid on a home mortgage in 2006. It
assumes a $220,000 mortgage that originated in October of 2004 and carries an interest rate
January 2006 represents the 16th payment and December 2006 is the 27th payment. The
interest paid between those two payments, inclusive, comes to $12,916.64.
The following formula calculates how much the principal has decreased over that same time
Figure 11-15 shows a workbook that’s set up to calculate the cumulative interest and principal for
any series of payment periods. Enter the starting payment in cell B4 and the ending payment in
cell B5. Cell E4 uses the CUMIPMT function to calculate the cumulate interest, and cell D5 uses
the CUMPRINC to calculate the cumulative principal.
The worksheet has an amortization schedule so you can verify the calculations.