44.

Using a Loan Payment Calculator

When you interact with Excel, you do so through Excel's graphical user interface. A graphical user interface includes menus, dialog boxes, list boxes, scroll bars, buttons, 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. These controls are called ActiveX controls.

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 for a friend who's unfamiliar with worksheets. 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

The Loan sheet of the practice workbook contains labels that will help you create a model that uses an Excel worksheet function to calculate the monthly payments for a car loan.

Cells B2 through B7 contain the labels Price, Down, Loan, Interest, Years, and Payment. Go through the following steps to create a fully functional loan payment calculator.

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

   

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

   The value $4,000 appears in the cell. Excel interprets which cells contain the price and down payment by looking at the labels next to the cells.

   

   Tip 

   If the word #NAME? appears in the cell, Excel is not set to accept labels in formulas. Point at the Tools menu, click Options, and select the Calculations tab. Select the Labels In Formulas check box, and click OK. Then re-enter the formula.

3. In cell C7 (to the right of Payment), type =PMT(Interest/12,Years*12,Loan) and press the Enter key. The payment amount, $125.35, appears in the cell. Once again, Excel uses the labels next to the cells to determine which cells you meant to use.

   

   The result is the monthly payment amount for the hypothetical car. The red text and 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

1. Enter $12000 in cell C2.

   The loan amount should change to $9,600, and the payment should change to $300.83. 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.

   

3. Press Ctrl+Z to change the price back to $12,000. (The monthly payment for the expensive car, in case you're interested, would be $37,603.64.)

One problem with this model is that it's too flexible. You can enter ridiculously large prices, even ridiculously high interest rates. (Try 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 when your friend is using the model. You can add controls to the worksheet that will avoid possible confusion.