Repayment Mortgage Calculator

nostobbi

After a long and tedious battle with both R&SA and the Finincial Services Ombudsman, R&SA have finally conceded that I was miss sold my endowment and have offered compensation.

The mortgage the endowment covers was for £28,450, taken out in Dec 1985 over 25 years. R&SA tell me that if I had taken out a repayment mortgage in 1985, I would have now paid off £16,000. I know that in the early years the majority of your payments go on interest, but £16,000 after 19 years would leave £12,450 to be paid off in the remaining 6 years - which seems a bit high to me.

Does anyone know a site with a calculator which will give you an independent view of how much you would have paid off on a repayment mortgage taken out "n" years ago?

Thanks

david78

I have pm'd the author but for others. You can work this out using the following formulae.

(Sorry its a bit mathematical, its what I do for a living).

You need to assume an average interest rate (in reality it may go up and down).

Let,

r = average interest rate as a fraction (so 6% means r = 0.06)
m = term of mortgage IN MONTHS (so m = 300 for 25 year loan)
n = time into mortgage IN MONTHS (so n = 84 for seven years)
B0 = initial mortgage amount
Bn = amount of principal owing after n months

(be sure to put m and n in months!)

Then calculate:

rm = r/12
a = (1+rm)^m/((1+rm)^m-1)
b = (1+rm)^(m-n)/((1+rm)^(m-n)-1)

Bn = B0 * (a/b)

The last equation gives the balance after n months.

Its easy to set this up in a spreadsheet. Check your spreadsheet by putting n = 0, you should find balance owed is B0. Also put n = m and balanced owed should be a big fat 0.

Proof

The monthly payment can be calculated at the start of the loan.

P = B0*rm/((1+rm)^m-1) + B0*rm

But as payments are "level" you can also calculate the payment using the balance after n months and remembering there's m-n months to go.

P = Bn * rm/(1+rm)^(m-n)^1) + Bn*rm

Equating RHS's gives result above. (qed).

bunking_off

Phew, well my brain's exploded....

I'd agree that the figures you've been quoted are probably broadly correct (I qualify this as I don't have any data for the average interest rate in the period).

An alternative way to look at this, for folks who possibly aren't so mathematically minded, is to set up a spreadsheet (excel notation used):

Column A will be the amount outstanding at each month of the mortgage, so in cell A1 type the original loan amount.

In cell B1, enter the formula =(1+nn)^(1/12)
...where nn is the annual interest rate

In cell C1, enter the amount payable each month

Then, in cell A2, enter the formula =A1*$B$1-$C$1
...then fill this down right until row 300 (assuming a 25 yr mortgage)
...the calculation works on the basis that the amount outstanding each month is that outstanding the previous month, plus interest, minus whatever you've paid
...so each row will give the amount outstanding at each month during the mortage.

Before anyone flames me, I know this is simplistic
- it assumes the interest rate is a proper APR
- it assumes interest is debitted monthly
- it assumes payments are creditted monthly

...all of which are dangerous assumptions. However, it does give a reasonably accurate indication of the amount outstanding at any point in time.

What this does highlight is the real benefit of making overpayments - an overpayment of £10/month for this example would result in the mortgage being paid off about 2 years early.

DavidLaGuardia

Let,

r = average interest rate as a fraction (so 6% means r = 0.06)
m = term of mortgage IN MONTHS (so m = 300 for 25 year loan)
n = time into mortgage IN MONTHS (so n = 84 for seven years)
B0 = initial mortgage amount
Bn = amount of principal owing after n months

(be sure to put m and n in months!)

Then calculate:

rm = r/12
a = (1+rm)^m/((1+rm)^m-1)
b = (1+rm)^(m-n)/((1+rm)^(m-n)-1)

Bn = B0 * (a/b)

The last equation gives the balance after n months.

There is a mistake here!

If the annual rate is 6.00% then the monthly rate should not be 0.5% or this would compound up to greater than 6%.

Hence
rm = r/12
is not correct.

If the interest rate is 6% the annual growth factor is 1.06
To work out the monthly growth factor (which likewise will be the monthly interest plus 1) we need to work out the twelfth root of 1.06. I'm not sure if most spreadsheets have a root function, but they do understand logarithms by which we can get to the same thing

rm + 1 = 10^(log(r+1)/12)

where r is 6% this makes rm = 0.487% not 0.5%

The difference could be significant over time.

DavidLaGuardia

Hence on the spreadsheet to get rm type:
=10^(log(r+1)/12)-1

bunking_off

I don't know about specifically having square root functions, but certainly excel can achieve the same by

e.g. 4^(1/2) means the square root of 4, ie 2.

Hence the 12th root, which is what you want for monthly rate is (1+rate)^(1/12) (minus 1 * 100 if you want it in %age terms) - hence the equation in my spreadsheet solution.

The problem is the banks quite often can't do compounding themselves, or try to oversimplify things. My mortgage supposedly has interest applied daily. I couldn't work out why my figures didn't marry with the bank's. It turned out that what they were actually applying was

rate/365

per day.

Of course, that makes the real interest rate

= (((1+(rate/365))^365)-1)*100

For e.g. 5.5%/yr quoted, that means the real interest rate is 5.654% ...call me picky but it adds up.

The other thing, of course, is that this could be all academic because it used to be common practise (not sure if it still is with some providers) to only apply the credits and debits once a year....

david78

The formulae I have posted are correct for all mortgages I know of which calculate "interest daily". Mortgage interest is not calculated the same way as savings interest. With mortgages which don't operate on an "annual rests" basis the interest is calculated daily, but is only compounded monthly.

(I've now edited this to be clearer)

Normally, the "interest rate" r is a nominal interest rate (not a true rate) for mortgages.

The true rate rtrue is related to the nominal rate by:

rtrue = (1+r/12)^12-1

The monthly rate is:

rm = (1+rtrue)^(1/12)-1 (as you say above)

which is the same as

rm = r/12 (as I have said above)

This is probably why your values don't stack up. The lender is using a nominal rate not a true one. Of course the rates "look" a bit cheaper if a nominal rate is quoted (its another way they are trying to con us.)

You can learn more at http://www.mortgagesexposed.com/

My formulae are approximate only in the sense that they assume all months are of equal length.

If you put the equations for the payment amount into a spreadsheet and compare the values with those you get from the Egg mortgage calculator http://new.egg.com/visitor/0,2388,3_54988--View_1028,00.html you will find they agree.

bunking_off

David78

Thanks for an excellent reference. Do you know of any site that provides comparisons of when interest is applied & compounded for different lenders? I have a One Account, and had sort of assumed that both the application & the compounding was daily...I'm now having doubts on the latter.

It's not that it adds up to a huge amount of money (my figures agree with their's to within a few pence per month, and invariably I calculate slightly higher interest than them), but the mathematician in me always likes to understand exactly what's going on.....

DavidLaGuardia

The formulae I have posted are correct for all mortgages I know of which calculate "interest daily". Mortgage interest is not calculated the same way as savings interest. With mortgages which don't operate on an "annual rests" basis the interest is calculated daily, but is only compounded monthly.

Assuming you are correct thank you for the correction. It is similar to the priinciple of comparing the published rate of a monthly interest account whose AER (annual equivalent rate) is higher when one twelfth of the interest rate is ocmpounded

It seems bit cheeky of Mortgage lenders to do this and perhaps they SHOULD show a true rate. I know that APR is shown but that is only ever relevant to an assumed term which may not be finished if the mortgage is moved at a later date. It also includes costs that in my view are separate to a mortgage such as the survey. Unless you are daft would you not have a survey anyway when purchasing a property without a mortgage? Surely the survey cost should only apply in remortgages?

I appreciate there may only be small amounts of discrepancy but it seems inaccurate to me.

david78

bunking_off,

I don't know any comparison sites which give what you want. I think your One account would be similar to my Direct Line flexible mortgage. You can calculate the interest accrued over a number of days n using:

interest = B*(1+r*n/365)

Whenever a payment is made (or a withdrawl) B should be changed and n reset to 0. Here r is a nominal rate. You need to adjust the constant 365 to 366 every leap year. I have spreadsheets which check my annual mortgage statements very carefully and my formulae usually agree within 1 penny.

There's a spreadsheet on the mortgagesexposed site which will do the comparison for you. There are lotus and excel versions. You can download the excel version with this link:

http://www.mortgagesexposed.com/Spreadsheets/Loan%20Comparator.xls

If you have a go at using this spreadsheet please post what you find out here.

David,
I agree with your statements on APR. Its a bit of a waste of time. The best thing would be lenders to provide the Internal Rate of Return for the loan (see the mortgages exposed book -- a pdf file) but I don't think they will do this as 99% of their customers won't really understand it. (most don't understand APR either).

Both,
If interested in overpaying your mortgage can I urge you to look at the section in the book which describes various schemes available -- a real eye opener.

david78

I just found this java calculator:

http://www.yourmortgage.co.uk/08calculators/08_calculators2.htm#