AUTOMATED LOAN CALCULATION WORKSHEET

Drop-down Boxes

Pull up the Exercise 12-Dropdowns spreadsheet. There are two worksheets in the workbook. The one entitled LoanAmort is where the actual loan amortization table is calculated and displayed. It is just a simpler version (yearly) of the loan amortization spreadsheet that we created in Exercise 11. The worksheet entitled MacroPage will ultimately be hidden. It will be used in this exercise as a depository for the user-input data that is needed and to perform some calculations. We will also use it in Exercise 13 to calculate some information that we will need for the macro that we will create in that exercise.

Drop-down Boxes

You will need to make sure that the Developer tab is showing on the ribbon. If it is not, click on the Microsoft Office Button (top-left with colored squares) and select Excel Options at the bottom. In the form that appears, check the Show Developer tab in the Ribbon option under the Top Options section and then click OK.

Click on the MacroPage worksheet tab. At the top of the spreadsheet is a list of five types of loans that we will make to customers. The interest rates for each type of loan are included along with a code that will be used depending upon the type of loan that is selected. In addition, the actual calculation for the interest rate and length of the loan will be calculated on this worksheet and then used on the LoanAmort page. Ultimately, the MacroPage will be hidden (and possibly locked away) from the user of the file.

On the LoanAmort worksheet, click on cell B2. Now click on the Developer tab to display the ribbon. In the Controls group, click on Insert and find theCombo boxicon under the Form Controls (second icon from the left in the top row). The title “Combo Box – Form Control will show when you let the cursor hover over it. Click the Combo Box icon. Now, use the cross-hairs to outline cell B2. You’ll probably want to increase the height of the row so that the box fits properly. Click in cell A2 and format the row height by clicking Home tab ribbon Format – Row Height and entering 15.75 in the box, or just use the curser at the far left to change the row height.

Click on the Developer tab again. Next, right-click cell B2 and then click on Format Control. In the dialogue box, click the right-hand side icon of Input Range. Highlight cells A2 through B6 on MacroPage and click the right-hand side box again. This highlights what will show in the drop-down box. Now click the right-hand side icon of Cell Link. Go to MacroPage and click on cell B9 and click the right-hand side icon again. This sets the cell where the drop-down box selection code will be stored. Set the Drop Down Lines to 5. Click OK. Now click in another cell (C2 for instance). Use the cursor to click on the drop-down box and select 30-year Jumbo Mortgage. Not all of the description will show. Right click on the drop-down box and then left click anywhere on the box (cell B2) again. This will leave an outline of the drop-down box with white circles at the corners and the middle of the edges.

Place the curser over one of the white circles on a right-hand edge of the cell and drag it to the right to make all of the description appear. You may have to repeat this a couple of times to get a good size. Use the cursor to widen column B and row 2 to fit the drop-down box. You may also want to format rows 1 – 4 to match the height of row 2 for aesthetic purposes.

Now let’s setup the calculations on the MacroPage worksheet. Notice that cell B9 has the code 5 in it. This is from the selection of Jumbo Mortgage in the drop-down box. Let’s write an equation in cell B11 that selects the appropriate interest rate from the array of mortgages at the top of the worksheet based upon the code in cell B9. The VLOOKUP equation works well here. The equation in cell B11 would be

=VLOOKUP(B9,B2:D6,2)

It is useful to have already formatted cell B11 to 2 decimals so we can check to make sure that it selected the appropriate rate of interest.

Set the number of years for the selected loan also using a VLOOKUP function in cell B12.

=VLOOKUP(B9,B2:D6,3)

Since the interest rate and loan duration on the LoanAmort worksheet are drawn from the MacroPage, we can check to see if everything is working properly. You might also want to extend the format of cell B3 on LoanAmort to two decimals. Change the choice in the drop-down box to 4-year New Car. You’ll see that the loan now amortizes over 4 years only. Check with the other choices to make sure that everything is working properly.

For a nice printout, we should set the print area of the loan amortization table. Highlight the cells from C7 to G38. Click on the Page Layout tab on the ribbon. Now, click the Print Area icon and then click on the Set Print Areaoption in the box that appears. Next, click on the Marginsicon in the ribbon and click the Custom Margins at the bottom. In the dialogue box, set the left and right margins to 1 and then click the Horizontally check box at the bottom. Then click OK. This has centered the amortization in the middle of the page as you can see if you use the Print Preview icon at the very top (you may need to use the arrow to the right to select the Print Preview icon to appear).

The printout would look even nicer if we could hide all of the rows with zeroes in them for periods less than thirty years. You can do this manually, but it means you would have to unhide them and re-hide them every time you use the spreadsheet for a different loan. That’s where a macro comes in handy. We’ll do that in Exercise 13.