Hey, What If…?

our company is contemplating applying for a loan and the management team wants to know how the loan will affect cash flow. The task sounds easy until you learn that two critical facts are not yet known: the interest rate and the term of the loan. Fortunately, spreadsheet software contains several “what-if” functions that can handle such calculations easily.

To find out how to use what-if functions, follow along as this tutorial takes you step-by-step through several problems. Excel 2000 is used here to illustrate these concepts, but the process is similar in all spreadsheet programs.

ONE-VARIABLE TABLES

Let’s start with a simple what-if problem: You want to borrow $150,000 and repay the loan over five years. Different lenders offer different interest rates, ranging from 5% to 7%. You want to know what the payments would be at each interest rate in this range. You might be tempted to use a manual approach, simply substituting each of the interest rates one at a time in the original data and recording the new monthly payment. However, a spreadsheet tool—the one-variable data table—can automate this process.

Exhibit 1 shows the data for this scenario.

The formula entered in cell C10 of exhibit 1 is:

= -PMT (rate, Nper, PV)

where rate is the annual interest rate, Nper is the number of payment periods (months in our example) and PV is the present value, or principal. Since we are interested in a monthly payment, both the interest rate and loan term should be expressed in months. By default, the payment function in Excel returns a negative number because it represents an outflow of cash. To force the payment to be a positive number, we use a negative sign after the equal sign in the formula. Thus, in cell C10 enter =-PMT(C9/12,C8,C7). The interest rate and payment amounts are formatted for percent and currency, respectively.

Exhibit 2 shows how to set up a one-variable data table directly below the original data.

In a one-variable data table the input values (the variables) can be oriented either horizontally, in a row, or vertically, in a column. In this example the interest values are arranged vertically. A characteristic of one-variable data tables is that the leftmost cell of the first row of the data in the table (B17 in this example) must contain a formula referencing the input cell. Because C9 is the cell in the original data in which Excel will successively substitute different interest rates, it’s the input cell.

Therefore, in B17 type the formula =C9. The result cell, which will contain the results of varying interest rates, is C10. (Although Excel allows for multiple result cells, to keep this example simple we will use only one.) In C17 type the formula =C10. The rest of the interest rates are typed in, as shown in exhibit 2.

Now highlight the area from B17 to C25 and click on Data, Table. As shown in exhibit 3 the spreadsheet displays a screen that asks whether the input values appear in a row or a column.

Because we’ve oriented the interest rates vertically, in a column, enter the reference to the input cell, C9, in the blank space after Column input cell. (If we had chosen to orient the interest rates horizontally, in a row, you would have entered the cell identification in Row input cell and left the space next to Column input cell blank.) Now click OK, and Excel will calculate the information, substitute each interest rate in the original data and record the monthly payment in the table. This not only is an efficient way to perform what-if analysis, but the resulting table also provides instant documentation of the substitution process, as shown in exhibit 4 .

TWO-VARIABLE DATA TABLES

Now let’s complicate the problem a bit by varying the term of the loan as well as the interest rate. Conveniently, Excel offers a two-variable data table to accomplish this analysis. Exhibit 5 shows the data and the two-variable data table for this scenario.

As shown in exhibit 6 , we have placed various loan terms horizontally, across the top row of the Term of Loan table, and listed interest rates vertically, down the left column. In a two-variable input table, the result cell must be referenced in the top-left corner of the table, at the intersection of the row and column headings. As before, the result cell is C10. Therefore, place formula =C10 in B16 to assign the result cell.

Next, format B16 so it’s identified as “Interest Rate” while maintaining the underlying formula =C10 (see exhibit 7 ). To do this, place the cursor in B16 and in the toolbar choose Format, Cells; then click on the Number tab to open the formatting options there under Category. Select Custom and in the blank space under Type highlight General; in the box above it (where it says General ) type in “Interest Rate” and be sure to include the quotation marks. Hit OK or Enter.

Excel doesn’t remove the underlying formula (=C10). Instead, it hides it and labels the cell “Interest Rate.” If you click on B16, you’ll see the formula is still there (see exhibit 8 ).

Next, we must tell Excel how the table is laid out. Highlight the table from B16 to G25. It is important that the reference to the result cell is in the top-left cell of the highlighted area; otherwise Excel won’t know what to place in the body of the table when you execute the Table command. Select Data in the toolbar and then Table, which opens a dialog box (see exhibit 9 ).

Once again, Excel wants to know which input cell is in the row across the top of the table and which is in the leftmost column. As you’ll recall, the term of the loan is in the row across the top of the table, while the interest rate is in a column down the left side. So in the block asking for Row input cell, enter C8; in the block asking for Column input cell, enter C9. This tells Excel to substitute successively each combination of term and interest rate in the original data and record each resulting monthly payment in the body of the table. Click OK and the results are fully documented, as shown in exhibit 10 .

MULTIPLE VARIABLES

What if you’re faced with more than two variables? That’s a job for Excel’s Scenario Manager, which can handle many variables, saving the results of each scenario. Here’s how it works: Let’s say that in addition to varying the interest rate and the term of the loan, you want to look at different loan amounts. Exhibit 11 shows the data and three scenarios.

When using Scenario Manager, it’s best to give a name to the input and results cells. That way, the Scenario Manager report will refer to the cells by name, instead of by cell location. To name a cell, place the cursor in the cell you want to name, click on Insert, highlight Name, Define and then type in the name. Shortcut: Highlight the box directly above the A row (in exhibit 12 it reads E23); click on it; then type in the name and press the enter key.

As shown in exhibit 12, the range names were assigned: C7 = amount; C8 = term; C9 = rate; and C10 = payment. Note: Do not use quotation marks when typing in range names.

We have chosen and typed in data for three different scenarios: a low-, medium- and high-cost loan. Each reflects a different amount borrowed, term and interest rate. The function allows you to explore as many scenarios as you wish.

Now we’ll set up the scenario conditions. From the toolbar, select Tools, Scenarios; that opens the Scenario Manager dialog box (see exhibit 13 ).

Click on Add to specify the conditions of the first scenario. Choose a scenario name to input into the block; make the name sufficiently descriptive so you’ll remember the nature of the assumptions for each scenario.

We’ve selected “Low-Cost Loan” as the name of the first scenario. Now Excel wants you to identify the Changing cells (see exhibit 14 ). This is the same as input cells in the one- and two-variable input tables. You can select as many changing cells as you wish. Since we want to vary three parameters—amount borrowed, term of loan and interest rate—we have three changing cells. To indicate this, click on the block asking for the changing cells. Backspace if necessary to delete any cell reference that may have appeared automatically and then drag your cursor from C7 to C9. Excel automatically places these cell references in the Changing cells block. In the Comment block include documentation about the assumptions for this scenario, such as who created it and its purpose.

In our example, the changing cells are next to each other, so we can easily select the changing cells by dragging. However, if the changing cells are not adjacent, designate the location by pointing to a target cell and holding down the control key and clicking on each changing cell individually.

After clicking OK, Excel wants to know what assumptions to include for this scenario. Enter the values for our low-cost scenario (see exhibit 15 ). Use the tab key to advance from block to block and choose Add to add the remaining two scenarios.

After adding all three scenarios, click OK. Excel will return to the Scenario Manager screen ( exhibit 13 ). You can view the results of each scenario individually by highlighting the scenario name and clicking on Show. You also can edit or delete existing scenarios by choosing the designated buttons, and you can print the results of the scenarios in a report. Excel gives the option of seeing the report in a side-by-side Scenario Summary format or a PivotTable format. To see the Scenario Summary report, select Summary from the Scenario Manager dialog box. Finally, either click on cell C10 or type in =C10 in the Result cells block and choose OK (see exhibit 16 ), which brings up the Scenario Summary screen ( exhibit 17 ).

Excel places this report on a separate sheet in the workbook and names the sheet tab Scenario Summary. The changing cells and results cells are easily identified in the report if you have named those ranges earlier. If you failed to name the ranges, the changing cells are identified by cell location, for example, C7, C8, C9, and the results cell will show up as C10. The report is much easier to read if you take the time to name the ranges at the beginning.

USING GOAL SEEK

There’s one final what-if tool you should know about. What if the most important thing to you is cash flow, and you want a monthly payment of no more than $1,200? Say you have decided to borrow $150,000 and have chosen a lender with a 6% interest rate. You want to know what term you should seek to have a monthly payment of no more than $1,200. The Excel tool Goal Seek can answer this question in a flash (see exhibit 18 ). To use Goal Seek choose from the toolbar Tools, Goal Seek.

When the Goal Seek dialog box appears (see exhibit 19 ), in the Set cell box, enter the location of the target cell—this is the monthly payment that you want to fix at $1,200. Reference cell C10 here. In the To value block enter 1200, since that is the monthly payment target. Finally, in the third block, By changing cell, enter C8 because you want Excel to disclose what loan term will give the desired monthly payment.

After hitting the enter key or OK in the dialog box, Excel calculates the term that will give a $1,200 payment as approximately 197 months (see exhibit 20 ).

As you can see, computerized spreadsheets contain an assortment of powerful tools that perform analytical tasks quickly and easily. Try to use them regularly. The more you use them, the more jobs you’ll find for them to do, and that will expand your areas of professional expertise.