fbpx
Search
Close this search box.

Interactive Mortgage Calculator to know how much you can borrow (with Excel)

Share

Facebook
Twitter
LinkedIn

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:
Mortgage Payment Calculator

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

Adding a scroll-bar control in Excel using Developer Ribbon

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:

Set Properties for this Scroll-bar - Excel

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.

Mortgage Payment Calculator

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:

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

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 storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

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.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

24 Responses to “Interactive Mortgage Calculator to know how much you can borrow (with Excel)”

  1. Hank says:

    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?

  2. Andy says:

    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.

  3. Andy says:

    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.....)

  4. Andy says:

    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'.

  5. NSN says:

    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 🙂 ?

  6. Hank says:

    Andy-
    That's been driving me nuts for some time. It was a simple solution i would have never found! Thanks.

  7. [...] How to make an interactive mortage calculator in Excel January 25th, 2010 jedwan Leave a comment Go to comments Article [...]

  8. Great calculator. It has plenty of features for getting your calculations just right. The interface is a little tricky but it's worth it.

  9. Zach says:

    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?

    • Chandoo says:

      @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.

  10. Andy says:

    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.

  11. Alok Doshi says:

    how to do with fractions and decimals 0.001 or 0.01 or 0.1

    Alok Doshi

  12. Chandoo says:

    @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.

  13. [...] And scrollbar form controls to adjust the % completion values. [learn how to] [...]

  14. Marcelo Rivadeneira says:

    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

  15. Rene says:

    Did I forget something or do you really have to press 'F9' each time you've updated the calculator?

  16. Rene says:

    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.

  17. Hui... says:

    @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

  18. Rene says:

    @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.

  19. Sam says:

    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

  20. […] 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 […]

  21. Mariya says:

    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

  22. Luke says:

    thanks for sharing doing this shit buddy

Leave a Reply