Excel Tutorials: How to use Goal Seek in Excel

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.

For more Excel tips and tricks go to my static page:  Excel Tutorials and Tricks Series.

The different results can then be compared to find out which one best suits your requirements.

Example Using Excel 2007's Goal Seek feature:

Note: For help with this example, see the image to the right.

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

Enter the following data into the cells indicated:
Cell - Data

D1 - Loan Repayment
D2 - Rate:
D3 - # of Payments:
D4 - Principal:
D5 - Payment:

E2 - 6%
E3 - 60
E4 - $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.
   
   
2. Choose What-If Analysis from the ribbon to open the drop down list.
   
   
3. Click on Goal Seek... in the list to bring up the Goal Seek dialog box.
   
   
4. In the dialog box, click on the Set cell: line.
   
   
5. Click on cell E5 in the spreadsheet since we are interested in altering the monthly payments for this loan.
   
   
6. In the dialog box, click on the T0 value: line.
   
   
7. Type 3000 since we would like 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 since we want 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 95.25.
    
    
13. To accept this solution, click OK in the Goal Seek dialog box and Goal Seek will alter 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.