I'm trying to determine how much difference overpayments on my mortgage would make but I cant find a calculator online. So I figure I'd create a spreadsheet to work it out for myself and can then also add in changes in interest rates on it.

Can someone tell me how they work out your mortgage payment? i.e. is it capital divided by term of years divided by 12?

The above seems to make sense but then what I cant figure out is what amount do they charge the interest on? if adding to the total capital outstanding it seems to give a VERY large interest amount which doesnt make sense.


Can anyone explain this to me?

HSBC one is nice and simple.

https://mortgages.hsbc.co.uk/mortgage-calculators/Mortgage-Overpayments-Calculator

Depends on whether interest is added annually or monthly.

If its monthly, then the they do it like this:

capital x days in month x int rate / 365

eg 50000 outstanding in August is:

31 * 50000 = 1550000

Times that by interest rate say 5%

1550000 * 5% = 77500

divided by 365

77500 / 365 = £212.33 in interest

So if in your spreadsheet you start with balance and then put in that formula for interest and then take off a payment and do that over 12 months it shows you what happens with payment.

I have one I did, bit rough and ready but it'll give an idea if you want a copy PM me and I'll email it.

quote:

---

Originally posted by McLannahan
HSBC one is nice and simple.

https://mortgages.hsbc.co.uk/mortgage-calculators/Mortgage-Overpayments-Calculator

---

PDM - thanks for that. So if it is a repayment mortgage then the repayment of the capital is the same each month for the length of the mortgage?

i.e. capital/number of years/12 ?

hi

yes i suppose it would be.

if you divide capital by term then 12, you then would have to add on that months interest.

the way i did it was do 12 cells for the year

in A1 put you capital
in A2 put "=((((A1*31)*5)/100)/365)"
in a3 put your payment
in a4 put "=a1+a2-a3"

then in b1 out "=a4"
b2 is "=((((b1*31)*5)/100)/365)"
b3 is payment
b4 is "b1+b2-b3"

etc etc etc

HTH that helps

Check your small print!
Lots of Mortgages make a charge for paying it off early.

quote:

---

Originally posted by nitram38
Check your small print!
Lots of Mortgages make a charge for paying it off early.

---

Calculator with adjustable interest, amount and term:

LINK

[Edited on 19/9/10 by Stott]

not that i am any expert but we did a similar thing.

from I gather if you are on a discount, you cant normally repay it before discount ends without a penalty but you may be ok after that.

if you overpay for a bit (as opposed until its repaid), they tend to reduce your repayments so that you end up still paying it over original term

most you can repay up to 10% a year over.


Hmm - this is starting to sound like financial advice for which I am most definitely NOT qualified.

mads - the above is just from my experience please please make sure you read up your small print etc as nitram38 says


I'm ducking out now before I bankrupt anyone but if you do want a copy of spreadsheet I can send it....

thanks pdm

dont worry i wont be suing you for misinformation and i have read the small print. what you have said is correct re: reducing the payments so length of term is still the same but smaller payments. also, i am allowed to pay 10% over without any penalties.


i have sent a u2u for your calculator but yes please, i would like to see what you've done.

IIRC you have to have the ability to repay an amount of capital by law - hence most allow up to 10% of the remaining equity to be over-paid every year.

As far as the amount of interest goes, most mortgages are geared so that in the first year you pay 95% interest and 5% capital, and by the end it moves to 95% capital and 5% interest.

That's why when you get your first fw mortgage statements it looks like you;ve paid off about 30p of the capital for the £12k paid in over the last year (not that I received my statement recently...)

Its not until you go to the building society to payoff your morgage and they get all the paper work out in front of you that you realise how much you have payed for your house! and it is about 3 times what you payed for it!.

If you can pay it off early do so and then try and bank what you were paying monthly into your savings.

http://www.mortgagesexposed.com/

http://www.guardian.co.uk/money/mortgage-calculator

Overpayments are always a good idea if you can afford them. They are directly off the capital and depending on how the mortgage company calculates interest, should mean as you are reducing the capital owed, more of your standard monthly payments goes towards clearing the capital element.

As said though - read the small print or check with your mortgage provider as each may do it slightly differently.

Its been a while since i've done any mortgages but many recalculate overpayments annually and some monthly to produce your new monthly payments. Obviously when you look to remortgage to get a new deal then you should owe less so meaning a lower repayment then the last time and so allowing you to make larger overpayments. In the later years, you may want to specify a mortgage allowing overpayments above the standard 10% off the outstanding balance annually if you can afford it.

This should help with a guide at least

cheers for all the info folks. BMSeven - the first link is certainly an interesting read. going to have to spend some time reading it mind.

I've just gone through my previous two years statements and it looks like they have been taking my overpayments off the remaining capital each month which is good. I've been very fortunate in that I have benefited from the low interest rates and in fact for all of last year was only paying about 5% of my mortgage payment as interest!

If you are using MS-Excel have a look at the PMT function. Calculates the payment for a given balance and interest rate. I guess that or something similar in that section should do what you want.

Tim.