From: Ronald Raygun 
Subject: Re: Calculation possible?
Newsgroups: uk.finance
Date: Tue, 06 Jun 2006 17:20:25 GMT

nospam@invalid.invalid wrote:

>> But after year 2 the factor is (1-1.0499^-1)/(1-1.0499^-3) which is

The above is wrong, by the way, Tim must be slacking not to have picked
up on this.  The factor should be (1-f^-12)/(1-f^-36) where f=1+0.0499/12,
and I had short-handed f^-12 and f^-36 to F^-1 and F^-3 but written F as
1.0499 instead of the f^12=1.051057 it ought to have been.  Head duly hung
in shame for three whole seconds.

>> 0.34969, making the balance £15736, and the overpayment limit £3147
>> or £262 a month, so you're scuppered.  Your objective of clearing the
>> loan in 3 years using a 36-month-fixed SO is unachievable.
> 
> Out of interest - is it possible then to finish paying the mortgage off in
> 3 years by overpaying the full 20% on a flexible basis? ie, £750pm 1st
> year, £512pm, 2nd year and £262pm in the last year - does that work out to
> a 0 balance after 3 years? I just don't know where to begin to work these
> things out!

You need to be careful.  The £512 figure for year 2 was based on overpaying
£500pm in year 1, so if you actually overpay £750 in year 1, the year 2(3)
limit will be a bit lower than £512(£262).

But the answer is yes, almost.

The monthly payments are  P = An * r / (1 - f^-n)    [1]
Where An means "amount owing when there are still n months to go),
and r is the monthly interest rate (in this case 0.0499/12),
and f=1+r.

To work out the balance after 12 months, you solve [1] for An
and plug in k instead of n, using k=n-12 (i.e. you work out the amount
owing when there are 12 fewer months to go than there were originally.

Ak = P*(1-f^-k)/r   [2]

and if you substitute the expression above for P, the 'r's cancel out
and you get

Ak = An * (1-f^-k)/(1-f^-n)  [3]

One more thing.  To answer the question "if I start with A, and pay P per
month, what will the balance be in 12 months' time?" you first need to
work out how long it would take to pay off.

To do this, solve equation [1] for n:

(1-f^-n) = A * r/P
f^-n = 1 - A*r/P
log (f^-n) = log(1-A*r/P)
-n = log(1-Ar/P)/log(f)   [4]

Then, and only then, can you use this n (and k=n-12) to work out a
new balance using [2] or [3].

This is what I did earlier:

First use [1] to work out P for n=60, which is £849.
Then overpay the max £750, and this gives a new P of £1599,
and if starting from £45k, [4] gives a new n of 30.  Then
use [3] to work out the end-of-year-1 balance, using n=30 and k=18.
That's £27669 which leads to a max monthly overpayment for year 2
of £461.

So, next step:  P = £849+£461 = £1310.  Use this and £27669 in [4]
to get a new n of 22.15, so use a k of 10.15 to work out the year 2
end balance using [3]:  I get a new balance of £12995 leading to max
overpayments of £216 per month.

New P for year 3:  £216+£849 = £1065.
New n: 12.55

So there you go.  Target *almost* reached, and the loan will be paid
off about halfway through month 37.

You need to redo everything each time the interest rate changes,
but if you're on a fixed rate, you'll be laughing.