Summarizing Loan Options Using a Data Table

If you're faced with making a decision about borrowing money, you have to choose between many variables, not the least of which is the interest rate. Fortunately, Excel's Data Table command (Data Data Tools What-If Analysis Data Table) can help by summarizing the results of calculations using different inputs.

On the CD 

The workbook  loan data tables.xlsx contains the examples in this section and can be found on the companion CD-ROM.

The data table feature is one of Excel's most under-utilized tools. A data table is a dynamic range that summarizes formula cells for varying input cells. You can create a data table fairly easily, but data tables have some limitations. In particular, a data table can deal with only one or two input cells at a time. This limitation becomes clear as you view the examples.

Creating a One-Way Data Table

A one-way data table shows the results of any number of calculations for different values of a single input cell. Figure 13-5 shows the general layout for a one-way data table.

Figure 13-5: The structure for a one-way data table.

Figure 13-6 shows a one-way data table (in D2:G9) that displays three calculations (payment amount, total payments, and total interest) for a loan, using eight interest rates ranging from 6.75% to 8.50%. In this example, the input cell is cell B2. Note that the range E1:G1 is not part of the data table. These cells contain descriptive labels.

Figure 13-6: Using a one-way data table to display three loan calculations for various interest rates.

To create this one-way data table, follow these steps:

1. In the first row of the data table, enter the formulas that are used to return the results.

   The interest rate will vary in the data table, but it doesn't matter which interest rate you use for the calculations, as long as the calculations are correct. In this example, the formulas in E2:G2 contain references to other formulas in column B

    E2: =B6 F2: =B7 G2: =B8 

2. In the first column of the data table, enter various values for a single input cell.

   In this example, the input value is interest rate, and the values for various interest rates appear in D2:D9. Note that the first row of the data table (row 2) displays the results for the first input value (in cell D2).

3. Select the range that contains the entries from the previous steps.

   In this example, select D2:G9.

4. Choose the Data Data Tools What-If Analysis Data Table.

   Excel displays the Data Table dialog box, as shown in Figure 13-7.

   
   Figure 13-7: The Excel Data Table dialog box.

5. For the Row Input Cell field, specify the formula cell that corresponds to the input variable.

   In this example, the Column Input Cell is B2.

6. Leave the Row Input Cell field empty, and click OK.

   Excel inserts an array formula that uses the TABLE function with a single argument.

Tip 

If you like, you can format the data table. For example, you might want to apply shading to the row and column headers.

Note that the array formula is not entered into the entire range that you selected in Step 4. The first column and first row of your selection are not changed.

Creating a Two-Way Data Table

A two-way data table shows the results of a single calculation for different values of two input cells. Figure 13-8 shows the general layout of a two-way data table.

Figure 13-8: The structure for a two-way data table.

Figure 13-9 shows a two-way data table (in B7:J16) that displays a calculation (payment amount) for a loan, using eight interest rates and nine loan amounts.

Figure 13-9: Using a two-way data table to display payment amounts for various loan amounts and interest rates.

To create this two-way data table, follow these steps:

1. Enter a formula that returns the results that will be used in the data table.

   In this example, the formula is in cell B7 is a reference to cell B5, which contains the payment calculation: B7: =B5

2. Enter various values for the first input in successive columns of the first row of the data table.

   In this example, the first input value is interest rate, and the values for various interest rates appear in C7:J7.

3. Enter various values for the second input cell in successive rows of the first column of the data table.

   In this example, the second input value is the loan amount, and the values for various loan amounts are in B8:B16.

4. Select the range that contains the entries from the preceding steps.

   In this example, select B7:J16.

5. Choose the Data Data Tools What-If Analysis Data Table.

   Excel displays the Data Table dialog box.

6. For the Row Input Cell field, specify the cell reference that corresponds to the first input cell.

   In this example, the Row Input Cell is B2.

7. For the Column Input Cell field, specify the cell reference that corresponds to the second input cell.

   In this example, the Row Input Cell is B1.

8. Click OK.

   Excel inserts an array formula that uses the TABLE function with two arguments.

After you create the two-way data table, you can change the formula in the upper-left cell of the data table. In this example, you can change the formula in cell B7 to

 =PMT(B2*(B3/12),B4,-B1)*B4-B1 

This causes the TABLE function to displays total interest rather than payment amounts.

Tip 

If you find that using data tables slows down the calculation of your workbook, choose Formulas Calculation Calculation Options Automatic Except for Data Tables.