Amortization table problem

P

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top