mortgage amortization formula

[scott]

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

 

[Tom Ogilvy]

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]

-----Original Message-----
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





.
Hi Tom. I have it set up as follows:

a b c d
1 PV 150000
2 Rate 5.5%
3 Term/years 15
4 Payment =-pmt(b2,b3,b1)
5 Year interest exp. Pmt. Balance
6 1 =b1*b2 =$b$4 =b2-c6+b6
7 2 =d6*$b$2 =$b$4 =d6-c7+b7
8 3 =d7*$b$2 =$b$4 =d7-c8+d8
9 4 etc.

This works great to show year on year amort. However,
this is a 15 year term. So I have tried to make it so I
can accomodate a 30 year term by having 30 rows. This is
fine except there is no need for the extra rows if I use a
15 year term. Thanks for your help.

 

[Tom Ogilvy]

Change C7 to

=MIN(D6+B7,$B$4)

and copy down. This will take care of the final payment so the balance is
zero. With zero balance due (column D), interest will be zero (column B)
and you won't go negative.

Regards,
Tom Ogilvy