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
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 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.
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.
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
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.
source https://chandoo.org/wp/mortgage-calculator-with-extra-payments-excel-download/
0 Comments