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

payments.

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

of 6%.

=CUMIPMT(6%/12,30*12,220000,16,27,0)

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

period ($2,911.50):

=CUMPRINC(6%/12,30*12,220000,16,27,0)

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.