Mortgage Calculator with Extra Payments – Excel Download

Recently we moved houses. And with the house move, came a brand new mortgage. But as a self-employed person with variable income, I find the steady nature of mortgage payments little hard to digest. So I wanted to know what impact it would have on my mortgage if I make arbitrary extra payments. Unfortunately, I couldn’t find such a calculator. So I made a mortgage calculator with extra payments using Excel. Here is a quick demo of the calculator. Read on if you want to know more or download this.

Mortgage Calculator – DEMO

Mortgage calculator Excel with extra payments - demo

Download the Mortgage Calculator

Click here to download the Excel Mortgage Payments Calculator

How to use this Calculator?

Refer to below illustration and click on the hot spots to understand the process. Essentially, there are 5 things you need to specify or look at when using the template.

  1. Enter your loan amount
  2. Enter the loan term (in years)
  3. Enter the interest rate (APR)
  4. Specify extra payments in the green column at relevant month rows. 
  5. Look at the chart to understand the impact of your extra payments on the mortgage term.
How to use the Calculator

Enter your mortgage details here.

Enter extra payment amount in relevant month rows.

The chart will show the impact!

How to create such a calculator yourself…

This part of the article discusses the process for constructing such a calculator yourself. Read on (or watch the video tutorial) if you are interested.

The key idea is…

Any extra payments you make bring down the outstanding principal of your loan, thus bringing down the “loan term”.

Step 1: Calculate the monthly (or weekly / fortnightly) payment:

Assuming you have the Loan amount, term & APR in three cells E5, E6 & E7, we can use the PMT() function to calculate the periodic payment.

In my case, let’s say loan is $500,000, term is 20 years and APR (Interest rate) is 5.35% per annum.

Then the Monthly payment would be

=PMT(E7/12,E6*12,E5)

Using PMT formula in Excel to calculate mortgage payments

Step 2: Set up amortization schedule

As extra payment will bring down the outstanding loan term, we need to set up an amortization table to see the impact clearly.

So, set up a range of 360 months (or longer if you want to cater for longer mortgages). You can use =SEQUENCE(360) to automatically generate all the months.

Related: Read about SEQUENCE and other Dynamic Array functions in Excel.

Your table should look like this:

Setting up an amortization table in Excel

  1. Opening Balance is same as loan amount for month=1. For subsequent months, this will same as previous month’s closing balance.
  2. Effective term is how long it would take you to pay off the mortgage based on the opening balance, and agreed upon monthly payment (calculated in Step 1) and interest rate (Cell E7). We can use NPER function to get the answer here. 
    =ROUND(NPER($E$7/12,$E$10,$D13),0) will tell us how many months it is rounded.
  3. Principal Paid is the amount of principal paid in each month. We can get this with the PPMT() function. =PPMT($E$7/12,1,E13,D13)
  4. Extra Payment is the input column where we can type any extra payments.
  5. Closing Balance is opening balance minus principal paid minus extra payment.

Complete this table with necessary formulas and fill everything down.

Step 3: Your mortgage will end when the “Eff. Term” is 0.

Go ahead and play with the table by typing some values in the “Extra payment” column. You can see the effective term reducing. 

We can visualize the impact with a nice chart (requires some extra work) like this:

Impact of Extra payments on mortgage

Do check the download workbook for details on how the chart is setup.

Video Tutorial – I made a mortgage calculator in Excel

Watch below video to understand how I made the mortgage calculator with flexible payments. If you prefer to see it on YouTube, click here.

More Financial Models with Excel

If you want to learn more about setting up calculators, models or business systems with Excel, check out below tutorials & examples:

Dr. Scroll-bar Mortgage Calculator or: Why you should not be borning and use form controls

Nest Egg Calculator using Power BI

Doing Cost Benefit Analysis in Excel – a case study

Sara’s Copy Shop – Break even analysis and what-if modeling in Excel [Videos]

How to embed Excel files, calculators on your website? – Step by step instructions

IRR and data tables in Excel

Using IRR with Data Tables – Modeling Cash-flow Scenarios in Excel

The post Mortgage Calculator with Extra Payments – Excel Download appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Original source: https://chandoo.org/wp/mortgage-calculator-with-extra-payments-excel-download/

Close Menu