- Learn Excel, Templates
- Last updated on February 20, 2024
Chandoo
Share
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
Download the Mortgage Calculator
Click here to download the Excel Mortgage Payments Calculator (updated with money saved calculation)
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.
- 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 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)
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:
- 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 theinput columnwhere 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.
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:
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.
Looking for an Amortization Schedule instead?
If you want to make a regular amortization schedule with Excel, check out Excel Amortization Schedule template.
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:
Share this tip with your colleagues
Get FREE Excel + Power BI Tips
Simple, fun and useful emails, once per week.
Learn & be awesome.
- 15 Comments
- Ask a question or say something...
- Tagged underdownloads, dynamic array functions, Financial Modeling, Learn Excel, Microsoft Excel Formulas, nper, PMT(), screencasts, sequence, videos
- Category:Learn Excel, Templates
PrevPreviousLearn SQL for Data Analysis in one hour
NextWhich Excel Formulas should you learn first?Next
Welcome to Chandoo.org
Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.Read my story • FREE Excel tips book
Want an AWESOME Excel Class?
Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
FREE Goodies for you...
100 Excel Formulas List
From simple to complex, there is a formula for every occasion. Check out the list now.
20 Excel Templates
Calendars, invoices, trackers and much more. All free, fun and fantastic.
13 Advanced Pivot Table Skills
Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.
Get started with Power BI
Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.
Automatically Format Numbers in Thousands, Millions, Billions in Excel [2 Techniques]
Ever wanted to automatically format values in thousands, millions or billions in Excel? In this article, let me show you two powerful techniques to do just that.
How to compare two Excel sheets using VLOOKUP? [FREE Template]
Sales Analysis Dashboards with Power BI – 30+ Alternatives
Announcing Power BI Dashboard Contest (win $500 prizes!)
Compound Interest Formula in Excel
- Excel for beginners
- Advanced Excel Skills
- Excel Dashboards
- Complete guide to Pivot Tables
- Top 10 Excel Formulas
- Excel Shortcuts
- #Awesome Budget vs. Actual Chart
- 40+ VBA Examples
Related Tips
Charts and Graphs
Automatically Format Numbers in Thousands, Millions, Billions in Excel [2 Techniques]
Learn Excel
Compound Interest Formula in Excel
Learn Excel
How to calculate the Gender Pay Gap using Excel Formulas? (Free Calculator Template)
Learn Excel
Weighted Average in Excel [Formulas]
Excel Howtos
How to fix SPILL Error in Excel Tables (3 easy solutions)
Financial Modeling
Loan Amortization Schedule in Excel – FREE Template
15 Responses to “Mortgage Calculator with Extra Payments – Excel Download”
Rayd says:February 16, 2023 at 10:08 pm
Kia ora Chandoo, thanks for this wonderful template brother. How can I modify this/ or do you have a similar template for a fortnightly
payment? Thank youReply
- Chandoo says:
February 20, 2023 at 5:23 am
Morena Rayd. You can adjust the multiplier / divider to 26 (I think it was set to 12) to see fortnightly details. Watch the companion video to understand the steps / logic.
Reply
- Alicia says:
April 11, 2023 at 7:06 am
It is very simple and easy to use. Is user interface is attractive. It is very helpful and beneficial for calculations.
Reply
- Vince says:
September 8, 2023 at 11:25 am
This is great, thanks a lot!
I think there is an error in the "You save X $ in interest". If i dont put any extra payment, it still says that I will save 19k in interest rate.Reply
- Chandoo says:
September 10, 2023 at 8:35 pm
I too realized this problem. I will post an update once I fix the error.
Reply
- Chandoo says:
September 10, 2023 at 8:50 pm
Updated on 11-Sep: I fixed the problem with "interest saved" calculation. Turns out there was an internal bug in cumipmt function in Excel that reports incorrect values. Something new!
Please use the link above to re-download the correct file.Reply
- Lor says:
November 7, 2023 at 6:28 am
Hi Chandoo, do you have a template that will make changes to figures going forward if the interest rate changes part way through the loan? Thank you for your help.
Reply
- Aniket says:
February 13, 2024 at 9:49 pm
I wanted calculator which can consider floating interest rate, can you please advise how to get that?
Reply
- Christine says:
April 30, 2024 at 5:34 pm
I love your tutorial. If I am making weekly acelerated mortgage payments, how to I account for that in the Eff term?
Thank yo so much,
Christine
Reply
- VAGEESAN says:
June 30, 2024 at 9:26 am
Hi Chandoo. I want an excel template where I can have my favourite stocks listed for which the prices changes on a dynamic basis every day as per the market. I mean a sheet which is linked to NSE for price changes and other changes like daily low, Daily high, Yesterday closing, last 10 day range, 52 week high, 52 week low, one month high, one month low, one week high and one week low etc
Reply
- Ritwik says:
July 21, 2024 at 4:57 pm
The excel is awesome but I am getting #NAME? error with the field 'Old Interest Paid' and subsequently Original Interest and Money saved. Can you tell me how to fix that. I am trying to check if I should complete my loan by the extra payments or instead invest the same amount elsewhere
Reply
- Chandoo says:
July 22, 2024 at 2:19 am
#NAME error could mean your Excel doesn't have one of the functions I am using. Can you tell me what version you are using? If I am not mistaken, this file requires Excel 365.
Reply
- Abhishek says:
July 26, 2024 at 7:03 am
How do you change the dollar to rupees for whole sheet to match indian currency
Reply
- Chandoo says:
July 31, 2024 at 2:54 pm
You can select the values and apply rupee formatting from cell formatting options.
Reply
- John Bohannon says:
August 3, 2024 at 5:26 pm
Hey Chandoo,
Thank you for making the excel spreadsheet! It is great, but there another, little known money saving action that you can take to DRAMATICALLY reduce your total interest owed on your mortgage allowing you to pay it off 40% to 60% faster. I am going to share with you for free. It's called recasting your mortgage, which is a form of re-amortizing the interest that you owe on your loan by paying the the bank an early payment of $10,000 or more. The bank will modify your loan to cancel the original interest that you owe, and recalculate the total interest based on the new lower loan loan amount for the remaining term of the loan, instead of the original loan amount. This pays off the most expensive section of interest on your loan which is the next owed interest, instead of cancelling the interest at the end of your loan. Your charts show the interest being canceled on the last payments, however the interest can be canceled on your next upcoming portion of the loan, but keeps the ending date the same. However, the result of recasting the loan is you, pay the loan off 2 or 3 times faster, by paying off the portion of the loan with the highest interest cost. I am wondering if you can build a excel spreadsheet that shows this for me? I will pay you for making it. I am a mortgage loan officer and would like to use it to help my clients see the impact of paying off their loans this way. Thank you for considering this!Reply
Rayd 
Vince 
Lor 
Aniket 
Christine 
VAGEESAN 
Ritwik 
Abhishek 
