Using the Cumulative Interest and Principal Add-in Functions

Excel supplies two functions specifically for calculating cumulative interest and principal payments on a loan: CUMIPMT, which calculates the cumulative interest payments on a bond or note, and CUMPRINC, which calculates the cumulate principal payments on a bond or note.

Some Background Info on the Cumulative Interest and Principal Add-in Functions

Both functions use the same set of arguments, including the interest rate, the loan term (or number of periods), the loan balance (or present value), the starting date and the ending date of the period for which you want to calculate the cumulative interest or principal payments, and a type-of-annuity switch to indicate whether the stream of payments occurs as an ordinary annuity or an annuity due.

The interest rate, loan term, loan balance, and type-of-annuity switch arguments work the same way for the CUMIPMT and CUMPRINC functions as they work for the standard financial functions.

NOTE If you have questions about how the interest rate, loan term, loan balance, or type-ofannuity switch arguments work, refer to the earlier section “Using the Payment Functions.”

The starting date and ending date arguments, as noted earlier, simply provide the starting and ending points for the period of time for which you want to calculate the cumulative interest or principal paid.

Common Errors Made When Using the Cumulative Interest and Principal Functions

Both the CUMIPMT and CUMPRINC functions return an error value in several predictable situations as well as one surprising situation:

1. If the interest rate or the loan term rate is less than or equal to zero.

2. If the starting date or ending date is nonsensical or if the starting date follows the ending date.

3. If the loan present value is less than or equal to zero. (Note that this means you don’t use the convention of specifying the loan present value as a negative number to show that it’s a cash outflow.)

Using the CUMIPMT Function

The CUMIPMT function calculates the cumulative interest paid on a loan between two dates you specify given the interest rate, loan term, loan present value, the starting date and ending date, and the type-of-annuity switch. It uses the following syntax:

CUMIPMT (rate, nper, pv, start period, end period, type)

Suppose, for example, that you want to calculate the cumulative interest paid on a $1,000,000, ten-year equipment loan that charges 9% interest and requires monthly payments arranged as an annuity due. Further suppose that you want to calculate the cumulative interest payments made over the first five years, or sixty months. To make this calculation, you use the following formula:

=CUMIPMT (.09/12,10*12,1000000,1,60,1)

The function returns the value –360094.

Using the CUMPRINC Function

The CUMPRINC function calculates the cumulative principal paid on a loan between two dates you specify given the interest rate, loan term, loan present value, the starting date and ending date, and the type-of-annuity switch. It uses the following syntax:

CUMPRINC (rate, nper, pv, start period, end period, type)

Suppose, for example, that you want to calculate the cumulative principal paid on a $1,000,000, ten-year equipment loan that charges 9% interest and requires monthly payments arranged as an annuity due. Further suppose that you want to calculate the cumulative principal payments made over the first five years, or sixty months. To make this calculation, you use the following formula:

=CUMPRINC (.09/12,10*12,1000000,1,60,1)

The function returns the value –394303.