by :

**Stephen L. Nelson, CPA**

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.