Mortgage comparison websites and free online mortgage calculators are good tools to use when deciding what mortgage to choose. But in my opinion they aren’t good enough! A few years ago when my partner and I were buying our first house in the UK, I was perusing the mortgage options on various comparison websites. The online calculator that I liked the most was the Money Saving Expert calculator. Since I last used the various calculators they have added an overpayment calculator. While this is a significant improvement over when I originally created mine, they don’t provide the flexibility that I required in a mortgage calculator.
I wanted to determine whether a mortgage with a higher introductory rate, but lower fees, was more expensive than a mortgage with a lower introductory rate and higher fees. I also wanted to include any potential bonus cashback from the different deals as a mortgage overpayment. That way I could do the most accurate comparison between mortgages identifying the total amount paid for a mortgage over the introductory period including interest and fees. I should point out though, that while my mortgage calculator is fairly accurate, it is still an estimate. If you choose to use this calculator (and I hope it proves worthwhile for someone), then please note that these calculations are not going to match the mortgage costs exactly.
The calculator proved to be a useful guide for us when we were choosing our first mortgage. Recently, I used the calculator again in order to determine whether it was worth our while re-mortgaging as our introductory rate is due to expire and we have since immigrated to Canada, further complicating matters. Using my calculator I was able to determine if a re-mortgage to another mortgage with a better rate + the fees involved in switching was cheaper in the long run compared to staying on the same mortgage with a new higher rate, and making an overpayment matching the fees associated with the other mortgages. To say it another way I wanted to compare the interest paid after a period of time for:
monthly repayment at better rate of new mortgage + fees for moving to new mortgage
monthly repayment at increased rate of old mortgage + one overpayment of amount of fees for moving to a new mortgage
The one major drawback of producing a calculator with the flexibility that I required, was that I couldn’t think of a nice and simple user interface (UI). I also didn’t want to spend too much time developing a UI as I wanted to concentrate on the logic of the calculator. Therefore I decided to use Microsoft Excel. This also gave me to opportunity to learn VBA.
Paddy from Informed Decisions provided me with the motivation to share my calculator. Hopefully it will prove useful to someone. Keep up the great work Paddy, your podcasts helped me whittle away the time on my indoor bike trainer over the cold Canadian Winter!
Where to get it
I store the Excel Workbook in a Git repository that is accessible through BitBucket. Click on the AdvancedMortgageCalculator.xlsm file to download it.
How it works
I used Microsoft Excel 2016 to create the mortgage calculator, which is comprised of 4 worksheets. As the mortgage calculator is a file that you download from the internet and it executes VBA code in each Excel Worksheet, you will probably have to enable editing and/or enable the content by clicking the buttons below. I can guarantee that the code isn’t malicious, but I can’t confirm that it is bug free!
Mortgage Comparison Worksheet
This sheet enables you to compare multiple mortgages including their associated fees as well as allowing you to specify the length of time for the introductory rate period. The main goal of this sheet is to be able to easily compare the total cost and remaining mortgage for the various products after the initial introductory rate period.
The image below shows the first few columns. The cells in green are the ones that require input to produce the results displayed in the grey cells. From left to right you can specify the principal amount for the mortgage along with the total mortgage term, the initial introductory rate, the amount, if any, of regular monthly overpayments, as well as the term for the introductory rate. Scrolling further to the right, you will see where you can enter the various fees associated with the product as well as specifying whether it is a repayment or an interest only mortgage. You can also specify the compound period, identifying how compound interest is calculated. Nowadays this is usually calculated daily.
Savings Comparison Worksheet
The main purpose of this sheet was to determine whether it was worth using the money to make an overpayment on the mortgage or to save the extra money. This sheet calculates the total amount of interest that could be earned on an amount of money if it was lodged in a savings account for a particular term. Once I had calculated the compound interest from a savings account I compared it against the total interest for a particular mortgage in the previous ‘Mortgage Comparison Worksheet’. For the same length of time I was comparing:
Compound interest from – Initial deposit + Monthly deposits
Mortgage interest paid for – Principal + Monthly overpayments
The monthly deposits and monthly overpayments were the same amount. The initial deposit for the savings account was going to be our “rainy day” fund. The difference was massive and proved that it was much better to make the monthly overpayments on the mortgage.
Mortgage Payments Monthly Worksheet
Most of the work in producing this calculator centres on the features in this worksheet. The cells along the top provide the initial values for the calculations. You can hover the cursor over the heading cells with a red triangle in the top right hand corner to get a tool tip explaining the cell function.
The reason for having two cells defining a term is to allow you to calculate the three totals after a initial period that is different to the mortgage term, for example when you have an introductory rate for a specific period. If you intend on switching mortgages after that introductory rate expires then there is no need to do any calculations after that rate.
You can however specify the same amount of time for both terms. One is specified in years and the other in months. Initially each row of the ‘Total Monthly Payment’ column is populated by combining the values in the ‘Monthly Repayments’ and ‘Monthly Overpayments’ cells for the duration of the introductory period/mortgage term. Changing a value in the ‘Total Monthly Payment’ column doesn’t affect any of the rows below it. However, changing the interest rate for one cell in the ‘Monthly Interest Rate’ column automatically updates all of the subsequent interest rate cells, leaving all the previous ones intact. To begin with this column is populated with the value from the ‘Initial Interest Rate’ column. The sheet is only fully populated when each of the pale green cells at the top have received valid input. Updating any one of these pale green cells will result in the sheet being recalculated and any previous modifications will be lost apart from the date.
While writing the VBA code I wanted a way to automatically validate my algorithms to ensure that it would produce an expected output from a known input. That way it should be more flexible to deal with any input values based on your own personal circumstances. In order to do so I wrote various unit tests that are run each time the sheet is updated, but can also be invoked upon clicking the ‘Run Tests’ button. The ‘VBA Code Validation’ cell in the top row gives a summary of the number of tests run, whereas the ‘Test Results’ cell displays any output from the tests. If any of the tests fail they will print the failure reason in this cell changing the background colour to red. This mechanism for running test will be useful going forward if any regressions are discovered and I need to fix the code.
This sheet doesn’t have any calculations on it. We tried to figure out a way of scoring the two houses that we were interested in purchasing, based on the scale in the sheet, to help us decide which one to go for. I was surprised at how close the scores were but we went for the one with the lowest score in the end.
This mortgage calculator has proved useful to us on two occasions now: when we were originally deciding on a mortgage and recently as our introductory rate is expiring. We make regular overpayments of differing values and this enables us to get an idea of the totals that we will end up paying at the conclusion of the mortgage. It also allows us to decide if it is worthwhile making certain overpayments at certain times. If you want to use this feature, then familiarise yourself with the overpayment limitations on your mortgage and any relevant fees. If you are not bothered about overpayments when deciding what mortgage to opt for then either use the ‘Mortgage Comparison Worksheet’ or any of the free online mortgage calculators.
I have added the MIT license to the VBA code which essentially means you can do anything you like with it as long as the copyright notice is maintained.
If you have any questions about anything in the sheet then please reach out in the comments below. Thanks for reading.