Creating an Error-Resistant Loan Payment Calculator

Excel has tools that enable you to make a loan payment calculator error-resistant. By restricting options to valid items, you can make your model less likely to produce erroneous results, as well as much easier to use. The Insert list on the Developer tab of the Ribbon contains various ActiveX controls that you can put on a worksheet.

See Also 

For more information about ActiveX controls or for instructions for enabling the Developer tab of the Ribbon, see the section titled “Creating Custom Command Buttons” in Chapter 9, “Launch Macros with Events.”

Restrict the Years to a Valid Range

Start by making it difficult to enter an invalid number of years. Typically, for car loans you can borrow for up to five years in units of a year. Just to be safe, allow values from 1 to 6 for the number of years. A spin button is an effective way to specify such integer values.

1. On the Developer tab of the Ribbon, click the Insert arrow, and then click the Spin Button (ActiveX Control) button.

   
   Spin Button (ActiveX Control)

2. Press and hold the Alt key, and then click near the upper-left corner of cell E6.

   Pressing the Alt key while you click makes the control snap to the cell grid line.

3. Release the Alt key, and then drag the lower-right corner of the new spin button to the bottom center of cell E6.

   The spin button rotates sideways so that it fits on the row.

   

4. On the Developer tab of the Ribbon, click the Properties button to display the Properties window.

   

   An ActiveX control has many properties. For most of the properties, you can simply accept the default values. Change only the properties for which you need a custom value.

5. Type 6 as the value of the Max property, and type 1 as the value of the Min property.

   

   You want the spin button to control the value in cell C6, which is named Years. The LinkedCell property determines which cell the control will change.

6. For the LinkedCell property, type Years and press Enter.

   

7. Click the Design Mode button to turn off design mode, and then click the spin button on the worksheet.

   

The number in cell C6 changes as you click the spin button, and the payment amount changes accordingly. Now your friend will find it easy to select only valid loan durations.

Restrict the Down Payment to Valid Values

Unfortunately, your friend can still enter an invalid value for the down payment percentage (–50%, for example, or Dog). You need to help out.

You specify the down payment as a percentage. A reasonable range of values for the down payment would be anywhere from 0 percent to 100 percent, counting in 5 percent increments. The minimum change for a spin button is an integer, but even though the down payment is a fraction, not an integer, you can still use a spin button as long as you utilize an extra cell to hold the intermediate value.

1. Click the Design Mode button to turn on design mode.

2. Press and hold both the Alt key and the Ctrl key, and drag the spin button from cell E6 to cell E3.

   Troubleshooting 

   Be sure to release the mouse button before releasing the Ctrl and Alt keys.

   When you release the mouse button, a copy snaps to the upper-left corner of cell E3. Copying the control makes both controls exactly the same size.

   

3. In the Properties window, type 100 as the value of the Max property, 0 (zero) as the value of the Min property, and H3 as the value of the LinkedCell property.

   Troubleshooting 

   If you try to enter H3 as the LinkedCell property before you set the Min value to 0, you will get an error message. That’s because the current Min value is 1 and H3 is empty, which is the same as 0.

   Cell H3 holds an intermediate-integer-value because the spin button can increment only in integers. Later, you will divide the value in cell H3 by 100 to convert the integer created by the spin button into a percentage suitable for use as a down payment.

4. As the value of the SmallChange property, type 5 and press Enter.

   This property controls how much the number will change each time you click the arrows on the spin button.

   

5. Click the Design Mode button to exit design mode, and then click the spin button.

   The value in cell H3 changes to a multiple of 5 between 0 and 100. Now you need a corresponding value in cell C3 of between 0 percent and 100 percent.

6. Select cell C3, type =H3/100 and press Enter.

   An appropriate percentage value appears in the cell.

   

7. Click the spin button to see both the integer in cell H3 and the derived value in cell C3 change in tandem. Because the loan and payment cells contain formulas that depend on the down payment, they also change. One simple click, so many effects.

Restrict the Interest Rate to Valid Values

The interest rate is another input value your friend might enter incorrectly. The interest rate is similar to the down payment rate; both are percentages. You probably want to allow interest rates to vary by as little as 0.25 percent, and within a range from 0 percent through about 20 percent. Because you’re allowing so many possible values, you’ll have many more increments than with the down payment rate, so you’ll use a scroll bar control instead of a spin button. Like a spin button, the scroll bar returns only integers, so you’ll link the control to an intermediate cell.

1. On the Developer tab of the Ribbon, click the Insert arrow, and then click the Scroll Bar (ActiveX Control) button.

   
   Scroll Bar (ActiveX Control)

2. Press and hold the Alt key as you click the upper-left corner of cell E5, and then continue to hold down the Alt key as you drag the lower-right corner of the new scroll bar to the lower-right corner of cell E5. Release the mouse before you release the Alt key.

   

3. In the Properties window, type 2000 as the value of the Max property, 25 as the value of the SmallChange property, 100 as the value of the LargeChange property, and H5 as the value of the LinkedCell property. Then press Enter.

4. Click the Design Mode button to turn off design mode, and then try the scroll bar control by clicking its arrows as well as the area between them.

   If you click one of the arrows on either end, the number in cell H5 changes by 25 (the SmallChange value). If you click between the box and the end, the number changes by 100 (the LargeChange value). You can also drag the scroll bar handle to get any value between 0 and 2000.

5. Select cell C5, type =H5/10000 and press Enter.

   

   You divide by 100 to turn the number from H5 into a percentage and by another 100 (100 * 100 = 10000 total) to allow for hundredths of a percent.

Now your friend can easily modify the number of years for the loan (using one spin button), the down payment percentage (using the other spin button), or the interest rate (using the scroll bar control).