Simple Modeling with Goal Seek in Excel

You can use the Goal Seek command to determine which input value for a formula pro- duces a specified formula result. Goal Seek, then, lets you quantify what needs to happen in order to achieve some specified result. Rather than supplying a set of formula inputs and letting Excel calculate the result, you supply a formula result, all of the input values except one, and then let Excel iteratively determine what the missing input value needs.

For example, suppose you want to determine the interest rate that would result in a $1,400- a-month payment on a $100,000 loan with a 10-year repayment term using the workbook shown in Figure 6-14. Cells B1, B2, and B3 hold values. Cell B5 holds the formula shown below to calculate the monthly interest payment:

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

To determine the rate input value required for your specified formula result, follow these steps:

1. Choose the Tools menu’s Goal Seek command.
   Excel displays the Goal Seek dialog box (see Figure 6-15).

   

2. Specify which cell’s formula you want to return a specified result.
   Use the Set Cell text box to specify the cell that holds the formula Excel will attempt to set to the specified value. For the example workbook shown in Figure 6-14, you would specify the Set cell as B5.
3. Specify this result.
   Use the To Value text box to specify the formula result you want. For the example work- book shown in Figure 6-14, you would specify the To Value as 1400.
4. Indicate which input cell should be adjusted.
   Use the By Changing Cell text box to specify the input cell that Excel should adjust in an attempt to calculate the formula result you want. For the example workbook shown in Figure 6-14, you would specify the By Changing cell to B1. Then click OK. Excel adjusts the input cell to a value that produces the desired formula result and displays the results in the Goal Seek Status dialog box (see Figure 6-16).

   

Goal Seek typically finds the correct input cell value in a fraction of a second. If your cal- culations are very cumbersome, however, Goal Seek may take longer. When this happens, you can click the Goal Seek Status dialog box’s Stop button to terminate the search. Or you can click the Pause button to temporarily suspend the search.