Mortgage Calculator

I have a lot of friends and friends of friends that are in real estate.  More importantly, I have a lot of friends who are home owners or soon-to-be home owners.  And I, too, am trying to navigate this grown-up stuff that includes providing a home for my family. So, it dawned on me the other day and I remembered one of Excel’s cool functions, Pmt().  Given the loan details— amount, interest rate, and term— the function returns the monthly payment.

So I decided to put together a simple Excel Sheet (form, really) that applies this function. It also highlights some of Excel’s other cool features, such as:

1. Scroll Bar (Form Control)
   To add a scroll bar to your sheet, go to the Developer Tab in Ribbon and Select Insert/Scroll Bar (Form Control)
   Note: If you can’t see the Developer Ribbon, go to Excel Options/Show Developer Tab in Ribbon
2. Custom Number Formatting
   Rather than show the full number for loan amount, I show the user the numbers in $1,000s; rather than show the interest rate in a decimal number, I show the user the interest rate as a whole number, then convert by dividing by 100.
3. Clip Art
   You can get tons of resources, including templates and clip art from Microsoft’s Office. I got the picture of the house from there.

The only real tricky step in this case is the Scroll Bar (Form Control). The simple steps are:

1. Once you’ve selected the option to add a scroll bar to your sheet, simply draw a rectangle anywhere on your sheet. For my mortgage example, I used a horizontal bar, but you can also choose to have it vertically oriented. Once you’ve drawn the scroll bar, a window for formatting the control will appear.
   
2. The first step I normally do is link it to a cell. This item is listed at the very bottom. This is where the value of the scroll bar will be placed. Any change in this scale will also slide the bar left/down or right/up.
   
3. Minimum and Maximum values place upper and lower bounds on the values the user can use.
   
4. Incremental change is how many units the scroll bar moves as you slide it. You can override whatever is in here with a manually entered value into the link cell.
   

And that’s about it. Creating sheets like this are not only more fun because they are interactive, but they are also more intuitive and allow users to visually manipulate different variables to see the result. Download the files and play with them a little. I guarantee you’ll like them, too.

Mortgage Calculator – 2007 version
Mortgage Calculator – Earlier Versions

The layout idea was adopted from http://www.chandoo.org/wp