Microsoft Loan Amortization templates - help please

[simonmc]

Hi,

I'm using Microsofts Loan Amortization templates which I downloade
from here:

http://tinyurl.com/3czsn

Now the problem is a lots of lenders in the UK have special mortgag
deals. e.g For the first 5 years they give you discounts off thei
normal rate - 2% discount in year 1, 1.5% year 2, 1% year 3, 0.5% fo
years 4&5.

Unfortunately microsofts template will only allow amortization table t
be calculated at constant rate. I've been trying to reprogram it can'
get it right. Could anybody help me with this or point me in the righ
dirrection?

Many thanks
Simo

 

[Norman Harker]

Hi Simon!

E-mail privately and I'll send you a workbook that allows changes to
the rates.

Looking at the Microsoft template I see that it would need quite a
comprehensive re-structuring to achieve a variable rate capability.

I achieve it by inserting a separate column for rates. I then use that
rate in each row to calculate the interest paid on the previous
balance. Repayment less the interest paid gives the principal repaid.
Previous balance less the principal repaid gives the balance for that
period. Once you have that sorted for the first row of the schedule,
it's copy down.

Watch out for the rates used in UK Building Society mortgages. I
believe that some at least are still using a declared rate system
whereby that rate is used to calculate the *annual* in arrears
repayment which is then divided by 12 to get the monthly repayment.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.