Loan Amortization

[Guest]

I used the Loan Amortization.xlt template to set up the details of my
mortgage a couple of years ago and it works well keeping track of extra
payments etc.

Now the bank has increased its interest rate and I am wondering is there a
way to reflect this interest rate hike in the sheet.

If I just enter the new rate it calculates the entire loan from beginning to
end at the new rate and not the loan from next payment to end at the new rate.

The only way that i can think of doing it is to create a new amortization
sheet for an odd term (e.g. the 23 years and 7 months left) at the new rate
starting from next month with the beginning balance as the ending balance of
the old rate, but ideally I would like one sheet that shows the amortization
for the whole loan.

Any ideas?

unlikeKansas

 

[Kevin H. Stecyk]

unlikeKansas...

I used the Loan Amortization.xlt template to set up the details of my
mortgage a couple of years ago and it works well keeping track of extra
payments etc.

Now the bank has increased its interest rate and I am wondering is there a
way to reflect this interest rate hike in the sheet.

If I just enter the new rate it calculates the entire loan from beginning
to
end at the new rate and not the loan from next payment to end at the new
rate.

The only way that i can think of doing it is to create a new amortization
sheet for an odd term (e.g. the 23 years and 7 months left) at the new
rate
starting from next month with the beginning balance as the ending balance
of
the old rate, but ideally I would like one sheet that shows the
amortization
for the whole loan.

Hi,

There are probably several solutions. I have two ideas for you.

A) Freeze the cells that are historical.

1) Copy your historical cells
2) Edit | Paste Special Values
Now your historical values are frozen.
3) Change your interest rate

B) Create multiple interest rates

1) Create InterestRate1, InterestRate2 cells
2) Edit your spreadsheet so that the historical cells use InterestRate1and
the future cells use InterestRate2

If it were me, I would freeze the historical cells. That way they can't
change. And I would put a note on the spreadsheet providing the interest
rate used from Date1 to Date2.

I hope this helps.

Regards,
Kevin