My Excel mortgage spreadsheet

Locoblade

acron^ wrote: »

Locoblade,

I really wanted to make some additions to the spreadsheet - such as adding a 'with overpayments' and 'without overpayments' line to the repayment chart, except it's passworded as I'm sure you're aware. Any chance of giving it out so I can make these changes?

Thanks

If you want to create extra charts off the back of the data in the spreadsheet then you can always create your own spreadsheet with charts and tables etc linked to the data in the main spreadsheet.

Marine_life

Have just run my numbers through the calculators and the numbers are quite scary.

Initial mortage was 490,000 taken out in December 2008 on a 30 year term :eek:.

Maximum overpayment allowed is 5% of the initial capital which equates to 24,500 per annum - which we have so far achieved.

The scary part is that the mortgage term will reduce by around 18 years (we would be paid off in 12) and the total interest bill will be reduced from around 400,000 to around 150,000 - a saving of 250,000 :eek::eek:

That'll be my retirement fund then!

strawberries1

thanks for this. I'm a first time buyer and trying to educate myself on mortgages as I intend to have bought by the 3rd quater of the year.
PS. Advice appreicated.
Thanks.

Rose_Crow

Thanks so much for this Locoblade. I got the link from the MFW board, I'm 6 months into my first mortgage. The spreadsheet is so handy and informative, though I havea question about it. On the key figures sheet input 11 'manual input initial month payment' has a n/a next to it. I can't change it, so when I go to the monthly table I can't alter monthly overpayments, they're just stuck at one figure.

Can you help me out?

Thanks again!

cococoladebt

There's something wrong with the excel sheet.

I have inputted my mortgage details as 150,000 for 25 years at 6.19% fixed for 5 years. I added 1,995 to the loan as a fee... but the mortgage calculator only calculates to 22 years and makes my monthly payment of £997.03 when my monthly payment is £991.92 ... something doesn't add up... why isn't it calculating to 25 years???

SalsaDanca

cococoladebt wrote: »

There's something wrong with the excel sheet.

I have inputted my mortgage details as 150,000 for 25 years at 6.19% fixed for 5 years. I added 1,995 to the loan as a fee... but the mortgage calculator only calculates to 22 years and makes my monthly payment of £997.03 when my monthly payment is £991.92 ... something doesn't add up... why isn't it calculating to 25 years???

I've just copied the details from your post into the spreadsheet, and I've also got the repayment figure of £997.03. However, the spreadsheet is calculating repayments for 25 years.

Do you think it's calculating repayments for 22 years because the last repayment is in October 2033? If so, you need to change the start year on the Inputs table - it defaults to 2008.

cococoladebt

No, the start year I have is 2011. I have the last payment as Nov 2032. I have below added image links to the spreadsheets. Perhaps you could take a look at the images?

cococoladebt

Not to worry. Input number 5 was the problem... I inputted that after the intial 5 year period the loan will revert to the providers current rate above base which is 4.79. I have now changed this to 6.19% and I now have the 25 years.

Locoblade

Option 18 is actually what's causing the "issue", by setting it to "Keep Same" you tell the spreadsheet to keep your payments the same unless they need to be increased (ie dont drop your payments when the minimum payment drops), therefore your £997 monthly payment needed to cover the mortgage payment at 6.19% is retained throughout the loan period, so when you drop the interest rate to 4.79% you're making an overpayment and therefore the mortgage finishes early. If you set that option to "Reduce Monthly" it will cut the payments to £888 after 5 years (because of the reduced interest rate) and the mortgage calculates to the full 25 year period.

Perhaps I didnt label that option as clearly as I could, will try to remember to amend the label and description popup on the next version to include this scenario.

Locoblade

As to the £5 difference between your real payments and what gets calculated, as mentioned in the notes the spreadsheet calculates interest montly and is unlikely to use the same formula that your bank use. Mortgages with daily interest, annual interest and various parts calculated a month in arrears etc will all give you a slightly different monthly payment and there's no way to mirror this exactly within the spreadsheet due to how complicated it would have to be, plus every lender seem to use very slightly different methods and its not always obvious exactly how they do it anyway.