mortgage amortization formula
 

I have a mortgage amortized annually. Right now I have
the term at 15 years. But since I may use a 30 year term
in some cases I have entered 30 rows to accomodate 30
years. So when I use 15 year term, balance becomes zero
at end of 15th year, and then 16th year and beyond the
balance shows as negative number, etc. everything is
inverted. Is there a way to enter this so that if I have
entry fields as follows:
Borrowed amount: 150000
Term in years: 15
Rate: 5%

that if I enter 15 years in the term field, the
amortization schedule will stop at the 15th year?

Thanks

mortgage amortization formula
 

It is unclear how you are set up, but in your table, just check for a
negative value and show zero

in f15 for example (assume payment to be subtracted is in F14
=max(F14-E15,0)

Or use a formula that zeros the payment

Regards,
Tom Ogilvy


"scott" wrote in message
...
I have a mortgage amortized annually. Right now I have
the term at 15 years. But since I may use a 30 year term
in some cases I have entered 30 rows to accomodate 30
years. So when I use 15 year term, balance becomes zero
at end of 15th year, and then 16th year and beyond the
balance shows as negative number, etc. everything is
inverted. Is there a way to enter this so that if I have
entry fields as follows:
Borrowed amount: 150000
Term in years: 15
Rate: 5%

that if I enter 15 years in the term field, the
amortization schedule will stop at the 15th year?

Thanks