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 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.
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:
Doing Cost Benefit Analysis in Excel – a case study
Using IRR with Data Tables – Modeling Cash-flow Scenarios in Excel
Sara’s Copy Shop – Break even analysis and what-if modeling in Excel [Videos]
Interactive Mortgage Calculator to know how much you can borrow (with Excel)
Nest Egg Calculator using Power BI
6 Responses to “Make VBA String Comparisons Case In-sensitive [Quick Tip]”
Another way to test if Target.Value equal a string constant without regard to letter casing is to use the StrCmp function...
If StrComp("yes", Target.Value, vbTextCompare) = 0 Then
' Do something
End If
That's a cool way to compare. i just converted my values to strings and used the above code to compare. worked nicely
Thanks!
In case that option just needs to be used for a single comparison, you could use
If InStr(1, "yes", Target.Value, vbTextCompare) Then
'do something
End If
as well.
Nice tip, thanks! I never even thought to think there might be an easier way.
Regarding Chronology of VB in general, the Option Compare pragma appears at the very beginning of VB, way before classes and objects arrive (with VB6 - around 2000).
Today StrComp() and InStr() function offers a more local way to compare, fully object, thus more consistent with object programming (even if VB is still interpreted).
My only question here is : "what if you want to binary compare locally with re-entering functions or concurrency (with events) ?". This will lead to a real nightmare and probably a big nasty mess to debug.
By the way, congrats for you Millions/month visits 🙂
This is nice article.
I used these examples to help my understanding. Even Instr is similar to Find but it can be case sensitive and also case insensitive.
Hope the examples below help.
Public Sub CaseSensitive2()
If InStr(1, "Look in this string", "look", vbBinaryCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub CaseSensitive()
If InStr("Look in this string", "look") = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub NotCaseSensitive()
'doing alot of case insensitive searching and whatnot, you can put Option Compare Text
If InStr(1, "Look in this string", "look", vbTextCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub