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.
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
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.