Microsoft Office Tutorials and References

In Depth Information

**CUMIPMT and CUMPRINC**

Example:
Your accountant might ask you for the amount of interest that was

paid on the loan from January 1, 2010, through December 31, 2010. You can use the

CUMIPMT function for the 11th through the 22nd months/periods. The answer is

$35,163.32.

Two notable differences from the previous functions:

You
do
need to enter a 0 or a 1 for the
Type
. Previously it was 0 by default.

n

You cannot enter a

(minus) sign in front of the PV as we did before. The

n

sign has to be in front of the function:

[

¼

CUMIPMT(Rate/12, Years*12, Loan, 11, 22, 0)]

n

Figure 32.12 shows the CUMIPMT Function Arguments menu. Use the scroll

bar to make the Type field accessible for filling.

The CUMPRINC, cumulative principal, has the same structure:

CUMPRINC

ð

rate

;

nper

;

pv

;

start
period
;

end
period
;

type

Þ

For example, say you wanted to calculate the principal portion paid over the first

10 years of the loan, in order to figure out the balance left to pay.

Use Excel

’

s CUMPRINC function and the result will be:

CUMPRINC(Rate/12, Years*12, Loan, 1, 120, 0)
resulting in $141,888.12.

The balance of the loan after 120 payments (or 10 years) is still

$458,111.88. Amazing; you paid 120 installments of $3,865.81 totaling

$463,897.01 on a $600,000.00 loan and your balance is still over 75 per-

cent of the originally borrowed amount.

=−

FIGURE 32.12
Cumulative Interest Payment Function