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
Search JabSto ::

Custom Search