Excel Mortgage Tracker for Start to Finish

akira181

I was wondering does anyone have a mortgage tracker that can track your mortgage from start to finish?  As in, if I remortgage, I can put in the terms of my second mortgage without clearing the payment history of the first?

I've seen the sheet by Locoblade from way back when that seems to still work well but I can only track one mortgage at a time. I put in the details of my remortgage and the first couple years of payments are wiped.

Anyone know of a solution?

grumbler

I have no idea about "the sheet by Locoblade", but if its one tab in Excel, just duplicate it every time you remortgage. You'll get a separate tab for every mortgage.

akira181

The sheet by Locoblade is the one on here, https://forums.moneysavingexpert.com/discussion/1157173/my-excel-mortgage-spreadsheet/p1

However, it's password protected so you cannot edit anything.

Exodi

I'm not sure how proficient in Excel you are, but it's relatively easy to create your own using the FV function. I won't go into depth about the criteria for FV function as this can be found easily online and would make this post excessively long.

For example if you had a £250k mortgage on a 25 year term at 5% interest. Monthly payment of £1461.48 (can be calculated using the PMT function). 

Year 0: £250,000
Year 1 =FV(0.05/12,12,1461.48,-250000,0) - returns £244,700.40
Year 2 =FV(0.05/12,12,1461.48,-[YEAR 1 BALANCE],0) - returns £239,199.66
etc

You can wrap each formula in the ROUND function (to 2 decimal places) to tidy things up.

While I have input static numbers in here as an example, the beauty of excel is you can refer to cells instead of fixed numbers. You can also easily change the interest rate or monthly payment (for example in year 6 onwards if you were on a 5 year fix). You could also artificially remove change the PV value on a certain year to marry up to your statements, or to reflect over-payments. If you wanted to go super in depth, you could change the nper to 1 and have the sheet run Y1M1, Y1M2, etc. You can also go a step further and forecast the future monthly payment using a fixed interest rate and the PMT function.

On my spreadsheet I have calculated my mortgage assuming a 5% rate for the next 3 years (I remortgage in July next year) and then a 4% rate for the duration after. It can be quite confusing at first but it offers the ultimate flexibility. Happy to help if needed. It has also enabled me to estimate how much I'd need to save up if I wanted to try move down an LTV bracket before remortgage.

akira181

I was quite good with excel but it's been a long time, I've forgotten most of the functions beyond the basic ones and VBA is back to being an entirely foreign language.

Now that you've saved me the difficulty of finding out which are the proper functions to use, I should be able to draft something to do what I want pretty easily.

Thanks!

ElwoodBlues

I did my own in excel. It's pretty straightforward, if a bit crude. Only compromise is that it calculates interest monthly rather than daily, but it still very close - less than £100 out after 10 years. (I also keep another worksheet alongside where I log my actual mortgage balance each month)

It's really useful because I can plug in different future scenarios (over payments, interest rates, fixed term lengths etc) and see the effects month by month.

Exodi

akira181 said:

I was quite good with excel but it's been a long time, I've forgotten most of the functions beyond the basic ones and VBA is back to being an entirely foreign language.

Now that you've saved me the difficulty of finding out which are the proper functions to use, I should be able to draft something to do what I want pretty easily.

Thanks!

If you were using VBA then the FV & PMT functions should be simple for you, even if you have not used them for a while.

As you say, it should be relatively easy to create a full mortgage forecast, but if you need any help, let me know.

Two things to note that seems to catch people out, the PV part of the function should be negative because it is a debt. A 'period' being defined as a month is for simplicity sake since payment is usually made monthly and the sheet becomes unnecessarily complicated trying to calculate it any other way. While you wouldn't usually expect the figures to match up 100% to your bank statement, they're usually extremely close (you also have the issue of shorter months accruing less interest than longer months, which the formula would not consider but the bank would).