Forecast with the Goal Seek Command in Microsoft Excel

By Peter Weverka

In a conventional formula, you provide the raw data, and Excel calculates the results. With the Goal Seek command, you declare what you want the results to be, and Excel tells you the raw data you need to produce those results. The Goal Seek command is useful in analyses when you want the outcome to be a certain way and you need to know which raw numbers will produce the outcome you want.

The formula in the following worksheet finds the monthly payment on a mortgage using the PMT function. The worksheet determines that the monthly payment on a $250,000 loan with an interest rate of 6.5 percent to be paid over a 30-year period is $1,580.17. Suppose, however, that the person who calculated this monthly payment determined that he or she could pay more than $1,580.17 per month? Suppose that the person could pay $1,750 or $2,000 per month. Instead of an outcome of $1,580.17, the person wants to know how much he or she could borrow if monthly payments — the outcome of the formula — were increased to $1,750 or $2,000.

image0.jpg

To make determinations such as these, you can use the Goal Seek command. This command lets you experiment with the arguments in a formula to achieve the results you want. In the case of this worksheet, you can use the Goal Seek command to change the argument in cell C3, the total amount you can borrow, given the outcome you want in cell C6, $1,750 or $2,000, the monthly payment on the total amount.

Follow these steps to use the Goal Seek command to change the inputs in a formula to achieve the results you want:

  1. Select the cell with the formula whose arguments you want to experiment with.

  2. On the Data tab, click the What-If Analysis button and choose Goal Seek on the drop-down list.

    You see the Goal Seek dialog box. The address of the cell you selected in Step 1 appears in the Set Cell box.

    image1.jpg

  3. In the To Value text box, enter the target results you want from the formula.

    In the monthly payment example, you enter -1750 or -2000, the monthly payment you can afford for the 30-year mortgage.

  4. In the By Changing Cell text box, enter the address of the cell whose value is unknown.

    To enter a cell address, click a cell on your worksheet. In mortgage example, you select the address of the cell that shows the total amount you want to borrow.

  5. Click OK.

    The Goal Seek Status dialog box appears. It lists the target value that you entered in Step 3.

    image2.jpg

  6. Click OK.

    On your worksheet, the cell with the argument you wanted to alter now shows the target you’re seeking. In the example worksheet, you can borrow $316,422 at 6.5 percent, not $250,000, by raising your monthly mortgage payments from $1,580.17 to $2,000.

    image3.jpg