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.
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.
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.
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).
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.
This uses the label in column B to name the corresponding cell in column C, which will make formulas easier to understand.
In cell C4 (to the right of Loan), type =Price*(1-Down) and press Enter.
The value $4,000 appears in the cell.
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.
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.)
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.
Enter 12000 in cell C2, and press Enter.
The loan amount should change to $9,600, and the payment should change to $301.
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.
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.)
Click the Undo button on the Quick Access Toolbar to change the price back to $12,000.
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.