Calculating mortgage repayments

Busa

I'm lucky enough to have a tiddler of a mortgage - but as my latest 5 year fixed rate deal comes to an end, I'm still struggling to work out what my repayments will be each month for any new deal.

Does anyone have a quick and relatively easy way to calculate what my monthly payments will be, because however hard I try, the closest I can work it out (based on my last deal) is about a tenner away?

My last £21,831 mortgage was at 3.59% and actual monthly repayments were £157.70 - but the closest I could work out using O level mafs was £166.28.

Thanks for your help

getmore4less

what calculation are you using?

https://www.whatsthecost.com/mortgage.aspx

will get you close enough.

£21,831 @ 3.59% over 15 years is £157.03
might have been 14y 11m that would make the payment just under £158

You should have after 5 years on that just under £15,800 left

new deal over 10y(120months) say between 1% and 2% £138pm-£145pm

carry on paying £157 on say 1.5% paid off in 107 month

Busa

Ah wonderful - thanks for that - calcs look just about spot on.

Any ideas on the underlying calculations to get there? Quite fancied being able to add it into my tracking spreadsheet...

FormulaDriven

OK - so here's how the maths works.


Remember, mortgage rates are per annum rates "payable monthly", so take your 3.59% and divide by 12 to get the effective monthly rate, 0.2992% = 0.002992


For 15 years, that's 15x12 = 180 payments.


Then if payments are to made in arrears (ie the first repayment is made exactly one month after the start of the loan) then the monthly payments are 21831 x 0.002992 / (1 - 1.002992^(-180)) = 157.03, as quoted by previous poster.

FormulaDriven

Should add, that if you want to know what the outstanding balance is after 5 years, you can invert the formula. 60 months have passed, so the present value of the remaining payments will be:
157.03 x (1 - 1.002992^(-120)) / 0.002992 = 15812, in line again with what previous poster quoted.

TrickyDicky101

Busa wrote: »

Ah wonderful - thanks for that - calcs look just about spot on.

Any ideas on the underlying calculations to get there? Quite fancied being able to add it into my tracking spreadsheet...

Look up Annuity Formula in Google.

PMT is the relevant function in Excel to determine monthly payment and CUMPRINC will calculate the cumulative capital paid off between two periods.

getmore4less

Busa wrote: »

Ah wonderful - thanks for that - calcs look just about spot on.

Any ideas on the underlying calculations to get there? Quite fancied being able to add it into my tracking spreadsheet...

you need the Financial Functions/amortization functions.

start with PMT and CUMIPMT

for the x year amount owing FV function does the job.

once you grasp the basics with a bit of use of the help google and wiki you can try harder stuff.

I have a calculation that given 2 interest rates and 2 payments works out the amount borrowed.

Busa

Fantastic - thanks guys - all mega helpful. Now to get stuck into excel