# How to make a mortgage calculator in excel

## Dr. Scroll-bar Mortgage Calculator or: Why you should not be borning and use form controls

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

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 2007. In Excel 2003 use View > Toolbars > Forms and select Scroll-bar control. (related: enable developer toolbar in excel 2007 )

Source: m.chandoo.org

Category: Credit

## Similar articles:

Q&A: How the student finance calculator works

How Do I Calculate My Mortgage Payment Without Using A Mortgage Calculator?

How Many Cars Must Tesla Sell: This Interactive Calculator Has The Scary Answer