Today we will build a mortgage payment calculator using excel. But we will not build a boring excel sheet, we will build a mortgage calculator that is easy to play with.
A mortgage payment is a monthly installment that you pay towards a loan. Any mortgage loan will typically have,
- loan amount
- duration of the loan (also called as tenure of mortgage) in years
- interest rate (APR) per year
Given these 3 parameters, we can easily determine the monthly installment amount (this will be the same amount for all months during loan tenure)
We are going to use Excel’s form controls (more on this below) to build a mortgage payment calculator like this:
Why you should not be boring and use the form controls
A form control is a button or check box or scrollbar or some other click-able thing you see in Windows. Do you know that you can add the very same controls to Excel spreadsheet to make the it interactive?
For example,
- instead of asking a user to enter “yes” or “no” in a cell, you can ask them to click a check box.
- instead of taking “age” in a cell, you can use a scroll bar and set the values from 0 to 100.
This way of gathering inputs is more fun, engaging and interactive.
Now that you find form controls hot and attractive, lets proceed and make a house loan payment calculator.
How is mortgage payment calculated?
As I said above, any mortgage (or housing loan) will have 3 parts – loan amount (p), loan tenure (n) and annual interest rate (r).
Given the values of P, N and R, we can find the monthly payments using Excel’s PMT formula like this:
=PMT(R/12,N*12,P)
[Related: PMT formula syntax & examples]
[Related: Amortization Schedule in Excel]
We are dividing interest rate (R) by 12 since R is annual interest rate and we make monthly payments.
We are multiplying loan duration (N) with 12 since we are going to make monthly payments.
Making the mortgage calculator in Excel
We will use scroll-bar controls to take numeric inputs required (P,N and R) for the payment calculation. And we feed these values to PMT formula to find the monthly installment amount.
Step 1: Add a Scroll-bar Control
We will use this scroll bar to take “loan amount” input. To keep it simple, we will ask users to enter input in ‘000s. So, if the loan is $120,000, the input should be 120.
First add a scroll-bar form control to your excel sheet. To do this go to Developer Ribbon > Insert > Scroll-bar Form Control in Excel (related: enable developer toolbar in Excel)
Add a Scroll-bar Control
Once selected, just add the control to spreadsheet by clicking anywhere.
Step 2: Set Properties for this Scroll-bar
To set the properties for the scrollbar control, right click on it and go to “format control” option. Now go to “Control” tab.
Here set minimum and maximum values for the scroll bar. To keep our model simple, just set minimum as 35 and maximum has 500.
Also, select a cell to link the scrollbar. When you do this, excel links the scroll bar to the selected cell. So whenever scroll bar is updated the cell gets updated too (and vice-a-versa). See this illustration:
Step 3: Add Remaining Scroll bars
Repeat the same steps for 2 other scroll bars. One for interest rate and one for loan tenure.
Make sure you set the minimum and maximum values in a reasonable range.
Step 4: Plug the values in to PMT formula
Now that the scroll bars are ready, just write the PMT formula. Assuming you have linked scroll bars like this:
- Loan amount in cell A1
- Interest rate in cell A2
- Loan tenure (years) in cell A3
The formula will be,
=PMT((A2/12)%,A3*12,A1)
Remember, PMT returns value in negative numbers (as it is the amount we need to pay, not get). But you can make it positive (for display purposes) by multiplying it with -1 like this = -PMT((A2/12)%,A3*12,A1)
Step 5: Play with your Model
Now your mortgage payment calculator is ready. You can play with it by testing various combinations and finding monthly payments. You can easily see what happens when you increase loan tenure or decrease interest rate.
Download Excel Mortgage Payment Calculator
Here is the excel mortgage payment calculator file. Download and play with it.
Bonus – Making an Amortization Schedule
You can easily extend this model to add an amortization schedule to see how much of each monthly payment is towards principal and how much is for interest.
- You can calculate principal portion for any month using PPMT formula like this
=PPMT(R/12,M,N*12,P). Here “M” is the month for which you want principal amount. - You can calculate interest portion for any month using IPMT formula like this
=IPMT(R/12,M,N*12,P).
Full tutorial: Loan Amortization Schedule with Excel.
Do you love form controls?
Do you use form controls in your spreadsheets? I find them pretty intuitive and use them wherever I can. I have made many complex spreadsheet models easy to understand and work with by just adding form controls. The beauty is that, they require no programming or anything. You just add them and link them to a cell.
What about you? Do you love form controls? Where do you use them most?
Learn More about Excel Form Controls:
24 Responses to “Interactive Mortgage Calculator to know how much you can borrow (with Excel)”
I'm using 2003 and have added a scrollbar control. When i right click to format it, i dont get the same control tab to edit the parameters that i get when i right click over your scrollbar. The first four are the same though. Any suggestions?
Form controls are a brilliant way to produce an interactive spreadsheet. Scroll bars and check boxes / radio buttons tend to be what I use the most. The main drawback with them is that they cannot by modified by VBA code, which is why I frequently have to use the ActiveX versions of the controls.
A nice application of these controls is to create a chart based on the cells that update when (for example) the scroll bar control is used. This gives you a simple and effective visual way to show how a change in one or more variables can affect the results you are interested in.
Hank: did you add the ActiveX version of the scrollbar? The ActiveX controls work slightly differently, as they are designed to interface with VBA code. If you aren't seeing the 'Control' tab, then you probably used ActiveX rather than the basic form control. I can't remember the exact layout of the controls in 2003, but when you are selecting the control to add there should be two sections, labelled 'Form Controls' and 'ActiveX Controls'. Make sure you add the scrollbar from the 'Form Controls' section (which should be the upper of the two sections, if memory serves me correctly.....)
Hank: Just been checking up on the 2003 menus, and Jon Peltier mentions it in this post:
http://peltiertech.com/WordPress/forms-controls-and-activex-controls-in-excel/
When you opened the toolbar, you need the 'Forms' toolbar, rather than the 'Control Toolbox'.
I have been looking around for some excel help on how principal repayments impact the schedule - interest savings (particularly on visual representation)
To clarify: I am trying to get a graphical illustration between
(i) Outstanding principal if I were to make no repayments vis-a-vis
(ii) Outstanding principal factoring repayments made
Can some one help me with this, probably we can add this to the existing work and make it complete 🙂 ?
Andy-
That's been driving me nuts for some time. It was a simple solution i would have never found! Thanks.
[...] How to make an interactive mortage calculator in Excel January 25th, 2010 jedwan Leave a comment Go to comments Article [...]
Great calculator. It has plenty of features for getting your calculations just right. The interface is a little tricky but it's worth it.
I'm using the scroll bar for another use and I want to be able to adjust the incremental change by .1 instead of whole numbers. Is there a way to do this?
@Zach.. this is a limitation of scrollbar controls. One way to over come this is by using an intermediate helper cell. I have done that for interest rate in the above example. Download and see it for yourself.
Zach: as far as I can tell, scrollbars can only take integer values as their increment. However, if it's an increment of 0.1 that you want, I would use the standard increment of 1 in the scrollbar settings, and then in the next cell divide that output by 10.
how to do with fractions and decimals 0.001 or 0.01 or 0.1
Alok Doshi
@Alok: You cannot use scrollbar control to capture fractions. You can however use helper cells to convert the scrollbar value to a fraction. you can see that in the downloadable example.
[...] And scrollbar form controls to adjust the % completion values. [learn how to] [...]
Hi Chandoo:
I am glad you changed your site name to Chandoo, I never really appreciated the dillbert reference, Chandoo sounds more original and easier to remember, also the time and effort you invest in it, will be on your own brand.
By the way I subscribed to your e mail updates, which are straight forward and practical, but never was able to download 25 tips e book.
Regards,
Marcelo
[...] Scroll-bar form control is used to select dates [...]
Did I forget something or do you really have to press 'F9' each time you've updated the calculator?
Can someone let me know if I forgot something? The calculator works perfectly it's only that each time the toolbars are slid 'F9' has to be pressed to get the new value. Thanks in advance for your feedback.
@Rene
It sounds as if you have Calculations turned to Manual
Goto the Formulas Tab and find the Calculation Options Button
Change Calculation to Automatic
@Hui,
Calculations were set as manual. However, I pressed the 'Calculate Sheet' button and now it's working properly. Thanks for pointing me in the right direction.
There's a pretty cool mortgage calculator spreadsheet (VBA-based) at http://investexcel.net/mortgage-payment-calculator/
The tool generates a complete payment schedule (given a mortgage start date, and a payment frequency) up to amortization. Predicts the date at which the bank takes money out of my account spot on
[…] Mortgage Payment Calculator – Chandoo.org – Use this excel based mortgage payment calculator to calculate your estimated monthly mortgage payments for various loan amounts and interest rates and loan […]
Thank you! Wonderful tutorial!
I just have one queston. Which one did you do first on the Interest rate cell? First you formatted it with the developer, or first you made the formula to receieve as result not the whole numbers. I tried to do it both ways but the numbers are whole, 3,4,5% not as your model. I would happy to understand it fully.Thanks
Mariya
thanks for sharing doing this shit buddy