Using Goal Seek in Excel

For planning new projects

Hispanic Manager in Warehouse
stevecoleimages / Getty Images

The Excel Goal Seek feature allows you to see what would happen if you alter the data used in a formula. With Goal Seek, you can compare different results to find out which one best suits your requirements.

Note: The instructions in this article apply to Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Mac.

Enter the Tutorial Data

This tutorial uses the PMT function to calculate the monthly payments for a loan. After the loan payments are calculated, Goal Seek is used to lower the monthly payment by altering the loan period.

To follow along with the tutorial, enter the following data into the cells indicated:

  • Cell D1: Loan Repayment
  • Cell D2: Rate
  • Cell D3: # of Payments
  • Cell D4: Principal
  • Cell D5: Payment
  • Cell E2: 6%
  • Cell E3: 60
  • Cell E4: 225,000
  • Cell E5: Leave this cell blank.

Here's what the tutorial data looks like in the worksheet:

A screenshot showing the tutorial data used with Goal Seek

Follow the steps below to implement the PMT function within your worksheet:

  1. Select cell E5 and type the following formula:

    =PMT(E2/12,E3,-E4)
  2. Press Enter.

    Screenshot of Excel showing PMT formula
  3. The value $4,349.88 appears in cell E5. This is the current monthly payment ​for the loan.​​

Alter the Monthly Payment Using Goal Seek

After the PMT function is created, use Goal Seek to peek into different data options.

In this tutorial, you'll see how Goal Seek is used to show the change in the total number of payments to be made when the monthly payment is lowered. We'll lower the monthly payment to $3000.00 to see the change in the number of payments.

  1. Select the Data tab.

  2. Select What-If Analysis to open a drop-down list.

    Screenshot of Excel showing the Goal Seek option
  3. Select Goal Seek.

  4. In the Goal Seek dialog box, place the cursor in the Set cell line.

  5. Select cell E5 in the worksheet.

  6. In the Goal Seek dialog box, place the cursor in the To value line.

  7. Type 3000.

  8. Place the cursor in the By changing cell line.

  9. Select cell E3 in the worksheet.

    A screenshot showing how to use Goal Seek to change the number of monthly payments
  10. Select OK.

  11. Goal Seek calculates the solution. When it finds one, the Goal Seek dialog box informs you that a solution has been found.

    A screenshot showing the result of a Goal Seek analysis
  12. Goal Seek shows that by lowering the monthly payment, the number of payments in cell E3 changes from 60 to 94.2355322.

  13. To accept this solution, select OK in the Goal Seek dialog box.

To find a different solution, select Cancel in the Goal Seek dialog box. Goal Seek returns the value in cell E3 to 60.