mortgage amortization formula

  • Thread starter Thread starter scott
  • Start date Start date
S

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
 
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
 
-----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:
Click to expand...

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.
 
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
 
Back
Top