Jump to content
House Price Crash Forum
Sign in to follow this  
Mr_Sminty

How To Calculate Mortgage Payments Over 25 Years

Recommended Posts

Trying to create a mortgage calculator in excel but dont think I am correctly calculating repayements, and never having had a mortgage I cant sense check things

Ive got

M=Mortgage balance remaing remaining

r=interest rate i.e. 0.05

y= years remaining

To work out annual payment im using= M /(1/r)-(1/(r*(1+r(powerY))

This figure I take from M, to give me a new mortage balance remaining figure which I then carry across and do the above calculation on it again with years remaining reduced by 1.

The figures I get are below which dont look correct, any ideas or help appreciated. Not necessarily from an Excel point of view but just the way mortgages work in the real world etc.

Start Annual payment

190916 14255

176660 13428

163232 12648

150584 11912

138672 11218

127455 10562

116892 9944

106948 9361

97587 8811

88776 8291

80485 7801

72684 7338

65346 6900

58446 6487

51959 6096

45863 5726

40137 5376

34761 5044

29717 4729

24988 4429

20559 4142

16418 3866

12552 3598

8954 3331

5623 3054

2569 2715

Share this post


Link to post
Share on other sites

your annual payments should be the same throught the lifetime of the mortgage.

at the start you pay mainly interest, then you pay the capital off later on.

Edited by Milkshock

Share this post


Link to post
Share on other sites
Trying to create a mortgage calculator in excel but dont think I am correctly calculating repayements, and never having had a mortgage I cant sense check things 

Ive got

M=Mortgage balance remaing remaining

r=interest rate i.e. 0.05

y= years remaining

To work out annual payment im using= M /(1/r)-(1/(r*(1+r(powerY))

This figure I take from M, to give me a new mortage balance remaining figure which I then carry across and do the above calculation on it again with years remaining reduced by 1.

The figures I get are below which dont look correct, any ideas or help appreciated.  Not necessarily from an Excel point of view but just the way mortgages work in the real world etc.

Start        Annual payment

190916 14255

176660 13428

163232 12648

150584 11912

138672 11218

127455 10562

116892 9944

106948 9361

97587 8811

88776 8291

80485 7801

72684 7338

65346 6900

58446 6487

51959 6096

45863 5726

40137 5376

34761 5044

29717 4729

24988 4429

20559 4142

16418 3866

12552 3598

8954 3331

5623 3054

2569 2715

Excel has got a PMT function (stop laughing at the back) which lets you input the capital value, number of repayments and interest rate. This will give you the repayments based on a repayment mortgage. Just make sure you use consistent time periods for repayments and the interest rate (ie make them all monthly or all annual).

If you need an explanation hit F1 and take a look at PMT in the help menu. The examples are pretty easy to follw.

Share this post


Link to post
Share on other sites

Pod, that mortgage calculator is genius (tapping forehead vigorously)

If I only pay 2500 extra off my motgage a year in extra payments, it saves me 47k and shortens mortgage by 8 1/2 years, for only the equivalent of 3 extra mortgage payments/year.

I hear you referring to the general sheeple as financially illiterate.

Well I might have learned my first word.

Share this post


Link to post
Share on other sites

A simple way is just multiply the loan amount by 2 if IR are 6% and by 3 if IR are 12%, simple.

Could use Excel PMT funtion I believe, it does the same thing, must remember to put the IR in the formula as %/1200 though otherwise you get some crazy answers.

Edited by Dicky

Share this post


Link to post
Share on other sites
I wrote a simple one here ussing VB.net. Works pretty good, certainly got my mortgage right to the penny.

I have been wanting this formula for ages. I just can't work it out myself. I can get pretty damn close using a progessive running total but it aint a formula which would be really clever! (they vary from bank calculator to bank calculator, my running total system synced with Natwest but not the rest) Unfortunately yours failed to initialise . Could you provide the formula?

Share this post


Link to post
Share on other sites
I have been wanting this formula for ages.  I just can't work it out myself.  I can get pretty damn close using a progessive running total but it aint a formula which would be really clever!  (they vary from bank calculator to bank calculator, my running total system synced with Natwest but not the rest) Unfortunately yours failed to initialise .  Could you provide the formula?

Unfortunately, I do not have the formula, it is a preset function within a microsoft dll, (same one called up for excel) and similar to trig functions.

The calculator will only initialise if you have xp with .net framework 1.1 (came with service pack 2 I think).

Yet another example of microsofts foresight and planning. A bit like buying an expensive tv without a plug.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • 302 Brexit, House prices and Summer 2020

    1. 1. Including the effects Brexit, where do you think average UK house prices will be relative to now in June 2020?


      • down 5% +
      • down 2.5%
      • Even
      • up 2.5%
      • up 5%



×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.