Using a Loan Payment Calculator

When you interact with Excel, you do so through Excel’s graphical user interface. A graphical user interface includes buttons, task panes, dialog boxes, list boxes, scroll bars, and other graphical images. A graphical user interface makes a program easier to learn and also helps reduce errors by restricting choices to valid options.

Historically, creating a graphical user interface was the domain of professional computer scientists. More recently, users of advanced applications have been able to add graphical controls to custom dialog boxes. Now, with Excel, you can take advantage of dialog box–style controls directly on the worksheet, without doing any programming at all.

In this chapter, you’ll create a worksheet model to calculate a car loan payment. You’ll add ActiveX controls to the worksheet to make it easy to use, even for a friend who is unfamiliar with spreadsheets. In the process, you’ll become familiar with how ActiveX controls work, which will be useful when you create custom forms.

Create a Loan Payment Model

Cells B2 through B7 of the Loan sheet of the Chapter10 workbook contain the labels Price, Down, Loan, Interest, Years, and Payment. These labels will help you create a model that uses an Excel worksheet function to calculate the monthly payments for a car loan.

image from book

The following steps create a fully functional loan payment calculator.


If you are in a location that does not use the dollar sign as a currency symbol, your workbook will show your local currency symbol.

  1. Type 5000 in cell C2 (to the right of Price), type 20 in cell C3 (to the right of Down), type 8 in cell C5 (to the right of Interest), and type 3 in cell C6 (to the right of Years).

    image from book

  2. Select the range B2:C7, and then on the Formulas tab of the Ribbon, in the Defined Names section, click the Create from Selection button, make sure that only Left column is selected, and then click OK.

    image from book

    This uses the label in column B to name the corresponding cell in column C, which will make formulas easier to understand.

    image from book

  3. In cell C4 (to the right of Loan), type =Price*(1-Down) and press Enter.

    The value $4,000 appears in the cell.

    image from book

  4. In cell C7 (to the right of Payment), type =PMT(Interest/12,Years*12,Loan) and press Enter.

    The payment amount, $125, appears in the cell.

    image from book

    The result is the monthly payment amount for the hypothetical car. The parentheses around the number in the worksheet indicate a negative number. Unfortunately, you don’t receive this amount; you pay it. (If you want to change the monthly payment to a positive number, put a minus sign in front of Loan in the formula.)

Use the Loan Payment Model

Now that you have all the formulas set up for the loan payment model, you can replace values in the input cells and see the effect on the price. For example, you can see what the monthly payment would be for a more expensive car.

  1. Enter 12000 in cell C2, and press Enter.

    The loan amount should change to $9,600, and the payment should change to $301.

    image from book

    This simple model calculates monthly loan payments for a given set of input variables. You change the input variables to anything you like, and the payment changes accordingly. You can even enter outlandish values.

  2. Enter $1,500,000 as the price of the car.

    This is a very expensive car. The payment formula bravely calculates the monthly payment, but you can’t read it because it’s too big. (In case you’re interested, the monthly payment for this expensive car is $37,604.)

    image from book

  3. Click the Undo button on the Quick Access Toolbar to change the price back to $12,000.

    image from book

One problem with this model is that it’s too flexible. You can enter a ridiculously large price or a ridiculously high interest rate, such as 500%. You can even enter something totally useless as the number of years, such as Dog. The wide spectrum of choices available-only a few of which are meaningful-might be confusing to your friend. You can add controls to the worksheet that will avoid the potential for this kind of confusion.

Microsoft Office Excel 2007 Visual Basic for Applications Step by Step
Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))
ISBN: 0735613591
EAN: 2147483647
Year: 2004
Pages: 99
Authors: Reed Jacobsen

Similar book on Amazon
Excel 2003 Power Programming with VBA (Book & CD-ROM)
Excel 2003 Power Programming with VBA (Book & CD-ROM)
Microsoftu00ae Office Excelu00ae 2007 Visual Basicu00ae for Applications Step by Step (BPG-step by Step)
Microsoftu00ae Office Excelu00ae 2007 Visual Basicu00ae for Applications Step by Step (BPG-step by Step)
Excel 2003 Formulas
Excel 2003 Formulas
Writing Excel Macros with VBA, 2nd Edition
Writing Excel Macros with VBA, 2nd Edition © 2008-2017.
If you may any questions please contact us: