CUMPRINC Function

[Richard]

I am trying to develop an amortization table in Excel
2000. This table includes a column for cumulative
interest and cumulative principal.
These functions were not available on my function palete
so I loaded the analisys tool kit from my disk. The
functions were now visible but when I went to use them
they did not seem to funtion.
The text book from which I have been working (Mastering
Excel 2000) suggested that the formula should be:
=CUMPRINC (rate/12,payments,amt,1,A6,0) where A6 was the
cell containing the first period (ie 1)
using my formula palete my formula looks like this:
=CUMPRINC (rate/12,payments,amt,A6,0)
There is no field in which to enter the numeral 1 and if
I enter it manually this has not effect.The cell that
should contain the result shows #N/A.
What am i doing wrong. Thanks for your help.

 

[Paul Corrado]

Richard,

If you're building an amortization table you should not need either of those
formulae.

Set up your table with these five columns

Loan Balance Payment Interest Principal Ending Balance


The formulas you need are

Loan Balance = Original loan to start and prior period ending balance
thereafter

Payment = Your monthly loan payment (PMT function)

Interest = Loan Balance * Interest Rate/12

Principal = Payment - Interest

Ending Balance = Beginning Balance - Principal

If you need to show cumulative interest or principal add two additional
formula to sum the payments to date

Cumulative Interest (with the first period interest in C5) = SUM($C$5:C5)
(this can be copied down)

Cumulative Principal (with the first period principal in D5) = SUM($D$55)
(this can be copied down)

PC

 

[Paul Corrado]

Harlan,

I see what you are saying and agree that those calculations would produce
more exact results. However, they will not eliminate the rounding errors.
Or if they do it is only in one's XL application.

The problem that remains unresolved is that currency limitations force
precision to two decimal places. (In most Western countries at least)
Subsequently, the level of precision afforded by the formula you suggest is
lost in the accounting and payment process that accompanies any loan.

IMO, Since both calculations have the capability of precision that is far
greater than can be implemented, the simpler formulae I recommended lead to
an application that is more user friendly/easily understood.

PC

 

[Harlan Grove]

...
...

IMO, Since both calculations have the capability of precision that is far
greater than can be implemented, the simpler formulae I recommended lead to
an application that is more user friendly/easily understood.

...

First, =ROUND(IPMT(...),2) and ROUND(PPMT(...),2) would take care of rounding to
real world precision.

As for your final point, more user friendly/easily understood up to the point at
which the table shows a nonzero principal balance after the final loan payment,
at which point (as a casual reading of this ng attests) all hell breaks loose.

 

[Paul Corrado]

Richard,

I don't believe you need to activate the function. (I just installed XL on
my computer last week and included the analysis toolpack functions in the
install and no further action was necessary.)

From XL Help.

CUMPRINC(rate,nper,pv,start_period,end_period,type)

Rate is the interest rate.

Nper is the total number of payment periods.

Pv is the present value.

Start_period is the first period in the calculation. Payment periods are
numbered beginning with 1.

End_period is the last period in the calculation.

Type is the timing of the payment.


For Start & End period make sure you are using the number of the payment, ie
1,2,3...

PC

 

[Harlan Grove]

. . . I want to be able to calculate cumulative interest. . . .

If you have interest paid in each period, which would in algebraic terms always
be the peiodic interest rate times the principal balance for that loan period,
then the sum of these interest payments from the first loan payment through the
Nth loan payment would be the cumulative interest paid through the Nth payment.
Using a part absolute/part relative range address is the only trick needed. For
example, if period interest payments appeared in column E starting in row 5, so
cell E5, then =SUM(E$5:E5) would be the cumulative interest paid through the 1st
payment, copied down one cell the formula would be =SUM(E$5:E6) and would be the
cumulative interest paid through the 2nd payment, and so on.