What's this?
You can use this spreadsheet to estimate your mortgage payments for a given purchase price and deposit, and test different structures.
The mortgage dashboard will show you an overview of the loan balances over time and how much interest you’ll pay, letting you test different payment terms and rates to see how they impact total borrowing costs, including use of revolving or floating facilities.
How the Mortgage Calculator Works
Interest Rates
First, check to make sure that the interest rates on the Rates tab of the spreadsheet are up to date. The rates table at interest.co.nz is updated frequently; you can find it here.
Make sure that the rates for your lender at least are current and up to date.
Mortgage Structure Input
On the Structure tab, input the key details of your property and loan. The input cells are highlighted in green. In the first section, input the purchase price of the property, the deposit amount, and your loan term:
Interest rates in the table below will update based on the lender you select fro the “Lender” dropdown menu. If your lender isn’t listed, you can choose another bank and just add your lenders interest rates in the appropriate place on the Rates tab.
Once the loan details are input, you can enter your structure. You can add up to seven different loans:
You don’t have to use all seven. In the Facility column you can select the loan term (including floating or revolving) from the dropdown in each row. Then enter the amount of each loan in the second column. The loan total will turn green when it matches the total lending calculated in the top section.
The Percentage of total, Rate, Term, and Payment columns will all populate automatically. You can manually overwrite the loan term for a specific loan although depending on your version of Excel it may break the table formatting.
The Payments columns will list the monthly, fortnightly, or weekly payments with a total at the bottom of each column.
Loan Dashboard
The Loan Dashboard tab will show you four charts and several totals. These are outputs only. You’ll see:
- The total interest and principal payments over the life of the loan – note that this excludes any revolving sums and assumes a constant rate of interest over the term of the loan.
- A chart showing the loan balance over time.
- Three charts showing how much of your payments are contributing towards principal and interest payments over time.
Download
You can download the mortgage calculator spreadsheet here:
Terms of Use
By downloading this calculator you agree to the following terms of use.
This workbook is provided for informational purposes only and does not consist of personal financial advice. No warranty, explicit or implied, is offered to users of this workbook and use is at the users own risk. It is intended as a guide only and you should seek professional advice before relying on the outputs of this workbook. You can find the most recent version of this worksheet and instructions here.
This worksheet is made available under the CC BY-ND 3.0 license which allows you to redistribute freely but not modify. You can see the full license here.
Need professional mortgage advice?
Get in touch now for a free no-obligation chat about how we can support you and your property and mortgage needs.