Calculating the Interest and Principal Components

This section discusses four Excel functions that enable you to

• Calculate the interest or principal components of a particular payment.

• Calculate cumulative interest or principal components between any two time periods.

On the CD 

The examples in this section are available on the companion CD-ROM in a file named  payment components.xlsx.

Using the IPMT and PPMT Functions

You may need to know (or simply be curious about) 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 in the beginning of the loan because the interest portion is higher (because of the higher balance).

Note 

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(.055/12,1,30*12,350000) =PPMT(.055/12,1,30*12,350000) 

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

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.

Note 

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(.06/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(.06/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.

Figure 11-15: Using the CUMIPMT and CUMPRINC functions.

Tip 

The worksheet has an amortization schedule so you can verify the calculations.