Amortization table problem

[pbc444]

I am trying to set up a dynamic amortization schedule that allows me t
change the loan variables (interest rate, length of loan, amount, etc.
I am using the PPMT and IPMT functions to calculated the principal an
interest payments for each period. My model covers a 30 year tim
horizon.

The problem occurs when I set the loan time less than 30 years. Fo
example, for a 20 year loan I have a line that counts 1, 2, 3, ..., 20
0, 0, to tell the PPMT and IPMT equations which period to calculate.
But when it reaches year 21, a zero feeds into the PPMT equation fo
time period, resulting in a #NUM! problem. It works fine if the loa
lengths is 30 years. But less than 30 and I get the error.

Is there any way to set this up so that a zero doesn't feed into th
PPMT and IPMT equations, while still retaining the dynamic function?
don't want to hard code the number of years evertime I change the loa
length.

Thanks for the help. This is driving me crazy

 

[Guest]

Try using an error trap.

If(iserror(your formula),"-",(your formula)

between the quotations you can place anything you want to
see. If you want a numeric value, omit the quotes
hth

 

[pbc444]

Thanks, the error trap worked perfectly