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.
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 mortgage details here.
Enter extra payment amount in relevant month rows.
The chart will show the impact!
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”.
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)
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.
Your table should look like this:
Complete this table with necessary formulas and fill everything down.
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.
If you want to learn more about setting up calculators, models or business systems with Excel, check out below tutorials & examples:
The post Mortgage Calculator with Extra Payments – Excel Download appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.
source
