My Excel mortgage spreadsheet

Locoblade

Hi

Is it 10% of the outstanding balance you're allowed to overpay each year, or 10% over and above your regular annual repayment total?

If the latter you can work that out yourself simply (minimum monthly payment/10 then add that as a monthly overpayment) but I think overpaying in that way will shave very little off your term so wouldnt get close to the 5 year target unless it's not much more than 5 years to start with.

If the former Im not really sure how I could encorporate that in a reliable way to be honest because firstly the spreadsheet never tallies exactly with real mortgages because of the various ways mortgage providers do their calculations, and I imagine this 10% type figure would also be slightly different for each provider in terms of exactly how and when the 10% figure is calculated, and obviously if its not accurate its not much good because you cant rely on it to know how much to overpay!

The best I can suggest is to mimic it as closely as possible by going into the Mortgage 1 monthly tab, selecting the monthly overpayment column, scroll down to the first anniversary month (it will be marked by a 1 in the first column labelled End of Year), then add in a figure that's 10% of the "Start Balance" for that month, so for example on the spreadsheet as downloaded 12 cells down you find October 2009 as the first anniversary and the start balance for that month is £90,817.15, so an overpayment of £9081 could be put into the overpayment column to indicate that year's overpayments. Its not ideal but would give you a rough idea of how much you'd need to overpay per year and how long it will take to be mortgage free.

Suarez

I am putting 35 years in the loan term box (6) and in the box on the right and the table below it's only calculating the mortgage over 23 years.

Locoblade

Hi Suarez, it sounds like you've left the overpayments setting (18) as "Keep Same" which will effectively overpay at whatever your initial repayment is, so if you subsequently lower the APR or overpay, it won't reduce the following payments to keep the term at 35 years, it will continue to pay at your original amount until its all paid off.

cheers

mjl123

Hi, I'd really like to try this spreadsheet, but the download link doesn't seem to be working? (403 error). Does anyone have a copy online somewhere please?

Thanks,

Matt

Locoblade

I'll get it sorted today, I switched web hosts this week, everything else migrated across fine so not sure why this didn't.

Locoblade

Working again now

csq

WOW, this sheet is amazing, thank you !!

Edit: it's working fine

mjl123

Thanks for the quick reply, I had a quick look last night and it looks fantastic, thank you! :T

Locoblade

I've just uploaded v1.11, its mainly just to fix a bug with the Mortgage 2 sheet where it retained some settings from Mortgage 1 unintentionally, for example if you set the start month on Mortgage 2 the spreadsheet ignored it and used the start month for Mortgage 1. Not sure how nobody spotted that for such a long time
I've also unprotected the charts to allow customisation if you need, but other than that there's no change so those using v1.10 only need to update if the above is important.

You can download from here or via the link on Page 1, if you find any new bugs then please let me know as I havent had time to do too much testing of these changes.

cheers

sammyjammy

Locoblade wrote: »

I've just uploaded v1.11, its mainly just to fix a bug with the Mortgage 2 sheet where it retained some settings from Mortgage 1 unintentionally, for example if you set the start month on Mortgage 2 the spreadsheet ignored it and used the start month for Mortgage 1. Not sure how nobody spotted that for such a long time
I've also unprotected the charts to allow customisation if you need, but other than that there's no change so those using v1.10 only need to update if the above is important.

You can download from here or via the link on Page 1, if you find any new bugs then please let me know as I havent had time to do too much testing of these changes.

cheers

Chris

I've just downloaded the new version but I can't get beyond your top sheet with the coffee picture on it, I'm sure its me being stupid! There are no tabs or anything.

I gave you a teeny tiny donation too