The Ultimate Mortgage Calculator discussion

Bob_Bobson wrote: »

How does the mortgage calculator actually work? I have created a spreadsheet that ought to do the exact same thing, but all of my monthly payments come out to be nearly double what they should be!

The formula I am using is:

((Annual interest rate^Years left on mortgage)*Initial loan)/(Years left on mortgage*12)

So for a mortgage of 100,000 over 20 years at a rate of 5%:

(1.05^20)*100,000/(20*12)

The monthly repayment though comes out at £1106, which is clearly not right. I'm guessing I'm not accounting for the interest paid off during the mortgage period, but I have no idea what the formula would be to factor that in. Can anyone help?

RedMonty wrote: »

FAULT REPORT

I'm trying to work out if I should go for an offset remortgage, & your calculator isn't helping much.

There seems to be a fault in the maths of the Offset Mortgage calculator, as well as several unclear aspects in the headings.

Data used: Mortgage £100,000, term 25yrs, interest 5% for both normal and offset, fee £0 for both, savings £20,000, savings interest 3%, Basic taxpayer, saving £200/month.

Fault: Monthly repayments in both 'normal' and 'offset' is shown as £585/month. The Offset mortgage repayment would actually be based on £100k-(£20k of offsetting savings) = 80k mortgage balance. So the monthly repayments should be a fair bit lower for the Offset mortgage.

some(most) mortgages are set up like this otherwise the term stays the same if you use the adjusted lower payment

Should be around £467 not the £585 shown.

Carrying on from this, the Offset mortgage payer should be able to put away more than £300/month into the savings account (used for offsetting) as they have lower monthly repayments than the 'normal' repayer.

Also for the offset mortgage, these monthly repayments decline through the term - as opposed to the fixed payments of the 'normal' mortgage, so every month, the offset repayer is able to put even more into the offsetting savings account.

Is this included in the maths at all?

Also re presentation of the figures - some numbers are shown in red boxes, and some are shown in blue boxes. This seems to have no link to whether they are savings or losses or anything at all.

Finally, 'Amount left in savings' - I don't understand what this means.

Hope this feedback helps to improve this tool.

RedMonty