# How To Build A Mortgage Calculator In Excel

Our current mortgage is up for a refinance in a few months. With the interest rates going crazy(!) now, I wanted to see how our repayments would be impacted if we started making overpayments.

Unfortunately, online bank calculators don’t help you calculate this, so I made my own mortgage calculator with overpayments using Excel!

I’ll walk you through how to build a calculator yourself, alternatively, scroll down to the bottom of the article for a free download.

## How To Build A Mortgage Calculator With Overpayments

### Set Up The Inputs Section

As I advocate in best practice modeling, we will set up a clear inputs section and lay out the spreadsheet with a clear logical calculation flow.

The first three inputs we need are:

• Loan amount – the outstanding balance of your mortgage today
• Loan Term – how many years are remaining on your loan
• Interest rate – the interest rate on your loan

With these three inputs, you can calculate your monthly repayment (assuming no additional extra payments). We will use the PMT function for this “=PMT(D9/12,D8*12,D7)”. Remember to use the same monthly periodicity with the interest rate and the number of periods.

Interesting read – Top 5 Excel Formulas For Finance Analysts

### Create Mortgage Amortization Schedule

Next, we create our calculation table for the mortgage. To show the loan payments and balance in a clear manner, we can create an amortization schedule. This will also allow you to input overpayments at key dates.

• Month – The default template is 360 periods, but feel free to extend/delete as needed.
• Loan Balance – This is the opening loan balance. The first period is the total loan amount and all the following periods are equal to the previous period’s closing balance.
• Periods Remaining – We use the NPER function to determine how many periods are remaining on the loan. If you make an extra payment, this will re-calculate the remaining periods based on the original monthly payment.
• Principal Payment – PPMT function (=PPMT(\$D\$9/12,1,D14,C14)) to calculate the principal payment paid off every month.
• Interest Payment – IPMT function (=IPMT(\$D\$9/12,1,D14,C14)) to calculate the interest paid every month. This can help you compare how much savings you will make on interest if you pay your mortgage off faster
• Overpayments – this is highlighted differently and set up as an input column. Input any extra payments you make against the mortgage here.
• Closing balance – Loan balance less principal payment less overpayments.

In the worked example, there is a Balance without overpayments calculation in column J. This has been charted against the amortization schedule above and will help you visualize how much faster you can pay down your debt.

Related article – How To Format Every Other Row In Excel