Need help with spreadsheet to calculate how to pay mortgage off early

freestyle_3

Dear All,

After the BBC programme which I can see has been mentioned already. I am interested in producing a model spreadsheet that would enable myself (and others) to see how many years they could get their mortgage down to.

Now, I'm not the best person in world at matchs or spreadsheets so I am looking for help.

Of course before I set to try and create one, I am making the assumption that no one here has got one already. And of course if we were to share it, then it would need to be generic.

Quite simply my idea goes along the lines of.

1. Enter mortage amount left, both time and money
2. Enter interest rate(allow room in the spreadsheet for introductory offers etc)

Then as far as I can make out that's the guts of it.

Scenario, I overpay an extra £100/month. that's a small amount but would be interesting to see a model of what happens

Bermuda_John

http://www.oneaccount.com/onev3/calculator/calculator-detailed.shtml


fill in the relevant details and it will do it all for you.

You finish at page 2 or you can carry on and see if your savings would make a large difference but then its a whole new issue. (see offset threads)

tomstickland

You can make simple month by month model.
Take the annual gross rate and divide by 12 to give monthly rate.
eg: 5% becomes 0.417% monthly. Hence you pay 1+0.417/100 of the total amount owed in interest each month.

Make first row is current amount owed
Make the next cell down equal the previous month x the monthly multiplier - the amount paid that month
Repeat this for 25x12 cells or whatever is left. See how many months it takes to get below 0.

Eg:
col1 / col2
Month number / amount owed
0 / =start_amount
=cell_above + 1 / = (cell_above)*(1.00417)-(monthly payment)

Bermuda_John

on a 55,000 with 25 years to run from the oneaccount calculator a $100 overpayment reduces it by 9 years and save 19k in interest

freestyle_3

Wow replies already !

tomstickland

Personally I always prefer to understand how the model works, so I don't use online calculators except to check my own maths.

freestyle_3

Ok.

I have done a fill down and calculated how many months it will take to pay off the morgage.

However I am having to manually look down the list and cross match the month to where it hits a negative value.

How can I make excel do this work for me ?

tomstickland

You can use one of the lookup functions to search for the column where it crosses over. Read the help files on vlookup, hlookup and lookup. You want one that interpolates for 0. I'll have a look later.

freestyle_3

yep.

I can honestly say I've got a lot of work to do.

Rather than simply using a fill down, I need it to give an answer

tomstickland

The other way is to encode the proper equation based on amount owed, annual rate and payment values. Something I'm going to do soon.

innovate

There's a good calculator here. It's on a US site, so the currency sign is $ but the numbers still apply all the same.