Mortgage Extra Payment Calculator Excel

Recently we moved houses. And with the house move, came a brand new mortgage. But I find it a little difficult to accept the steady nature of mortgage payments as a self-employed person with variable income. I therefore wanted to know what effect making arbitrary additional payments would have on my mortgage. Unfortunately, I couldn’t find such a calculator. So I used Excel to create a mortgage calculator with additional payments. Here is a quick demo of the calculator. If you want to learn more, continue reading or download this.

Mortgage Calculator – DEMO

To comprehend the procedure, look at the illustration below and click on the hot spots. When using the template, there are basically 5 things you need to specify or consider.

Enter your loan amount

Enter the loan term (in years)

Enter the interest rate (APR)

Specify extra payments in the green column at relevant month rows.

Look at the chart to understand the impact of your extra payments on the mortgage term.

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 section of the article describes how to build such a calculator from scratch. If you’re interested, continue reading (or watch the instructional video).

The key idea is…

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

We can use the PMT() function to determine the periodic payment if you have the Loan amount, term, and APR in cells E5, E6, and E7.

Let’s assume that my loan is for $500,000, the term is 20 years, and the APR is 5%. 35% per annum.

Then the Monthly payment would be

Step 2: Set up amortization schedule

We must create an amortization table in order to clearly see the impact of additional payments because they will shorten the remaining loan term.

In order to account for longer mortgages, set up a range of 360 months. You can use =SEQUENCE(360) to automatically generate all the months.

Your table should look like this:

Opening Balance is same as loan amount for month=1. For subsequent months, this will same as previous month’s closing balance.

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.

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)

Extra Payment is the input column where we can type any extra payments.

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.

Fill in the “Extra payment” column with values to experiment with the table. You can see the effective term reducing.

With some additional work, we can create a nice chart that will help us see the effect:

Please refer to the downloaded workbook for information on the chart’s 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

Check out the tutorials & examples below for more information on setting up calculators, models, or business systems with Excel:

Share this tip with your colleagues

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week. Learn & be awesome.

Thank you so much for visiting. Making you awesome in Excel and Power BI is my goal. On this website, I share videos, advice, samples, and downloads to help. More than 1,000 pages covering Excel, Power BI, Dashboards, and VBA are available here. Go ahead and spend few minutes to be AWESOME. Read my story • FREE Excel tips book.

For every situation, there is a formula, from straightforward to complex. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Filters, Slicers, Conditional Formats, Power Query, Data Model, DAX, and gorgeous charts Its all here.

Learn what Power BI is, how to get it, and how to create your first report from scratch in this getting started guide if you’re still on the fence about it.

When you open an Excel document, some cells display #### rather than the value. Here is a quick fix.

Related Tips Learn Excel

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.

© All rights reserved.

FAQ

How do I calculate extra mortgage payment in Excel?

1. InterestRate – C2 (annual interest rate)
2. LoanTerm – C3 (loan term in years)
3. PaymentsPerYear – C4 (number of payments per year)
4. LoanAmount – C5 (total loan amount)
5. ExtraPayment – C6 (extra payment per period)

How many years can you take off your mortgage by paying extra?

26 half-payments are made as a result, for a total of 13 full monthly payments per year. Using the aforementioned example, an additional payment can shorten a 30-year mortgage by four years and save you over $25,000 in interest.

What happens if I pay 5 extra mortgage payments a year?

By increasing your principal payments, you can reduce the term of your mortgage and accelerate the process of equity growth. You’ll make fewer total payments because your balance is being paid off more quickly, which will result in more savings.

What happens if I pay an extra $200 a month on my 20 year mortgage?

You can shorten the term of your loan by more than 8 years and lower the amount of interest paid by more than $44,000 if you pay $200 more in principal each month. Making half-monthly payments every two weeks rather than a single full-monthly payment is another way to pay off your loan faster.