Microsoft Office Tutorials and References

In Depth Information

**Chapter 18: Performing a what-if analysis**

interest rates. A one-variable data table, such as the one shown in Figure 18-1, can give you

the information you need.

You can find the Goal Seek.xlsx file with the other examples on the companion website.

To create this table, type the interest rates you want to test, as shown in cells B3:B9 in

Figure 18-1. This is the
input range
because it contains the input values you want to test.

Type the loan amount in a cell outside the data table area. We typed
$200,000
in cell C1.

By doing this, you can easily change the loan amount to test various scenarios. Enter the

formula that uses the input variable. In this case, type the formula
=PMT(A2/12, 360, C1)

in cell C2. In this formula,
A2/12
is the monthly interest rate,
360
is the term of the loan in

months, and
C1
refers to the cell containing the loan principal.

Figure 18-1
Begin building the data table by typing the interest rates, loan amount, and PMT

function in the worksheet, as shown on the Data Table tab of the Goal Seek.xlsx workbook.

Note

Notice that the formula in cell C2 refers to cell A2, which is blank. Because A2 is blank,

the function returns a spurious result: the payment required to amortize the loan at an

interest rate of 0 percent. This is a data table quirk, if you will. Cell A2 is a placeholder

through which Excel feeds the values in the input range to create the data table. You

need to designate a blank cell for this purpose. Because Excel never changes the

underlying value of this cell, this placeholder cell can be anywhere, as long as it is outside the

data table.

After you enter the inputs and the formula, select the data tableâ€”the smallest rectangular

block that includes the formula and all the values in the input range. In this case, select the

range B2:C9. On the Data tab, in the Data Tools group, click the What-If Analysis button

and then click Data Table.