My Excel mortgage spreadsheet

Albee · 28 October 2008 at 12:07PM

Hi,

Fantastic spreadsheet! It'll make my search for a decent remortgae in the next couple of months a lot easier! Cheers!

In an effort to get a proper handle on my financial circumstances following an accident in July that's left me unable to work I used your spreadsheet to calculate how much I owe NR as I took out a £85,5000 mortgage and £9,500 as an unsecured loan in late Oct 2005 at a fixed 5.99% for 3 years the onto SVR (7.48%). This year is the first time I have been able to make over payments and have paid £770 so far (not alot of money to some - but alot to me!). Anyway, using the spread sheet my present debt to NR should be £91,252 but according to NR I owe them £93,209

Is there a problem with the spread sheet or have NR got their figures wrong and I need to question it? Any advice would be much appreciated.

gazareth · 28 October 2008 at 12:20PM

Great spreadsheet! Amazing to see just how effective even small overpayments are.

Locoblade · 31 October 2008 at 10:56PM

Wayno wrote: »

Locoblade I've been searching the web for a spreadsheet like yours for a while now and yours is by far the best i have personally come across.
While navigating around the sheet, learning how it works. I tried using the addtional borrowing column, each time i place a figure in th cell it doesn't alter the cumulative overpayment or end balance. (this was after having paid in more over payments than wishing to draw on addtional borrowing). Was just wondering if you can let me know how this part works?

Great spreadsheet!

Hi

Unfortunately, as I found out a week or so ago, that bit doesn't seem to work at the moment, and having been studying for an exam recently I haven't had time to look into why and fix it.

I'll make sure I do that before I release the next version though, for now what you could do is put in a one-off additional payment as a negative number (e.g. -£20,000) for the month you want that additional borrowing, as I think that should do prety much the same thing in terms of repayment calculations etc.

Locoblade · 31 October 2008 at 11:13PM

PayAttention wrote: »

Locoblade,

Great spreadsheet, many thanks. If I compare the effect of adding a monthly overpayment vs the effect of monthly offset savings, as expected the reduction in the time to pay off the loan is greater using overpayment. However, the total interest is marginally higher (about 7%). Interest rate was fixed throughout and I excluded all fees, to keep the comparison simple. Intuitively I would not not expected this. Any thoughts on this?

Hi

Could you maybe PM me the exact figures you added in so I can have a look please, as when I use the default settings as downloaded (£100k loan over 25 years etc), and put in a £300 monthly overpayment, or a £300 monthly offset, the amount of interest is virtually the same with either. There will be a slight error/variation in there compared to overpayments because of the limitations on how it can all be calculated. When offsetting for example, the spreadsheet looks at the offset total and the remaining capital, and if the former is larger than the latter, it sets the interest rate to zero and starts drawing the monthly payment out of the offset. Obviously if the offset is £1 under at the end of a month, it will go through another full monthly cycle of putting money into the offset before it starts the above process. If it was £1 over, then it will obviously kick in straight away so will pay things off a month earlier which will affect the figures you see. I can't think why it would be as much as 7% out though.

Locoblade · 31 October 2008 at 11:31PM

Albee wrote: »

Hi,

Fantastic spreadsheet! It'll make my search for a decent remortgae in the next couple of months a lot easier! Cheers!

In an effort to get a proper handle on my financial circumstances following an accident in July that's left me unable to work I used your spreadsheet to calculate how much I owe NR as I took out a £85,5000 mortgage and £9,500 as an unsecured loan in late Oct 2005 at a fixed 5.99% for 3 years the onto SVR (7.48%). This year is the first time I have been able to make over payments and have paid £770 so far (not alot of money to some - but alot to me!). Anyway, using the spread sheet my present debt to NR should be £91,252 but according to NR I owe them £93,209

Is there a problem with the spread sheet or have NR got their figures wrong and I need to question it? Any advice would be much appreciated.

Hi

Are you sure they aren't giving you a figure including any early redemption fees, or fees that were added at the start of the mortgage? Also are you sure the unsecured loan is also at the same rate as the loan?

I can't guarantee the spreadsheet is correct obviously, but I've compared it as much as possible with other mortgage calculators online etc, so I believe it should be correct, at least for the fairly simple calculations like yours.
(edited to add) - The spreadsheet does only do monthly compound interest calculations though, so if your lender uses daily interest etc it will be slightly different, although only by a few pounds per month max.

Can you send me the remainder of your details that you put in, how long the loan is for, when you made overpayments and how much etc?

Jett · 1 November 2008 at 12:00AM

wow, very impressive

Billy_Bob_3 · 8 November 2008 at 9:19PM

Been thinking about attempting to write a spreadsheet like this for ages. Glad I found this and saved the time.

benjdr · 12 November 2008 at 3:14PM

Just a bump and a thank you. This thing rocks.

Locoblade · 12 November 2008 at 3:24PM

No probs Ben, the only bug I know of which Ive yet to get around to fixing is mentioned above, namely adding something into the additional borrowing column doesnt work, but the workaround if you wish to look at additional borrowing is to either add the additional borrowing into the overpayments column as a negative number, or use the second loan spreadsheet to calculate it seperately.

Stu78 · 12 November 2008 at 4:44PM

I am getting a "the cell you are trying to change is protected" error when i try to put in manual over payments or extra borrowing.

Is it possible that you inadvertantly protected all the cells, and not just the ones you need to protect?

Cheers

Stu.

My Excel mortgage spreadsheet

Comments

Categories

Get our FREE Weekly email full of deals & guides - and it’s spam-free