Using Goal Seek in Excel

For Planning New Projects

Excel Goal Seek
 Screenshot

Excel's Goal Seek feature allows you to alter the data used in a formula in order to find out what the results will be with the alteration. This feature is useful when you are planning a new project. The different results can then be compared to find out which one best suits your requirements.

Using Excel's Goal Seek Feature

This example first uses the PMT function to calculate the monthly payments for a loan. It then uses Goal Seek to lower the monthly payment by altering the loan period.

First, enter the following data into the cells indicated:

Cell - DataD1 - Loan RepaymentD2 - RateD3 - # of PaymentsD4 - PrincipalD5 - Payment

E2 - 6%E3 - 60E4 - $225,000

  1. Click on cell E5 and type the following formula:
    =pmt(e2/12,e3,-e4)
    and press the ENTER key on the keyboard
  2. The value $4,349.88 should appear in cell E5. This is the current monthly payment ​for the loan.​​

Altering the Monthly Payment Using Goal Seek

  1. Click on the Data tab on the ribbon.
  2. Choose What-If Analysis to open a drop-down list.
  3. Click on Goal Seek.
  4. In the dialog box, click on the Set cell line.
  5. Click on cell E5 in the spreadsheet to alter the monthly payments for this loan.
  6. In the dialog box, click on the To value line.
  7. Type 3000 to lower the monthly payment to $3000.00.
  8. In the dialog box, click on the By changing cell line.
  9. Click on cell E3 in the spreadsheet to change the monthly payment by altering the total number of payments to be made.
  10. Click OK.
  11. At this point, Goal Seek should begin searching for a solution. If it finds one, the Goal Seek dialog box will inform you that a solution has been found.
  12. In this case, the solution is to change the number of payments in cell E3 to 94.25.
  13. To accept this solution, click OK in the Goal Seek dialog box, and Goal Seek alters the data in cell E3.
  14. To find a different solution, click Cancel in the Goal Seek dialog box. Goal Seek returns the value in cell E3 to 60. You are now ready to run Goal Seek again.