How to Use Goal Seek in a Spreadsheet

What is Goal Seek in a spreadsheet?

If you have a target value in mind and need to determine the corresponding input value to meet that target, use the Goal Seek feature in the spreadsheet. It works backward and changes variables to match your specified target. The Goal Seek tool is especially useful for financial modeling, business analysis, or solving other complex problems. For example, Goal Seek can tell you sales forecasts to reach the profit goal, find the exam passing score, or the votes required to secure an election, etc.

How to use Goal Seek in a spreadsheet?

Using Goal Seek in a spreadsheet is a straightforward process. You simply have to specify these three parameters:

• Formula to manipulate

• Target value

• Input value to change to achieve the target

Note: Goal Seek can process only one variable input value at a time.

Let's walk through Goal Seek step-by-step with the following example.

Example: Loan Repayment Calculation

Suppose you want to pay $1,500 for a month and want to know the loan term in months needed to fully repay the loan.

1. Set up your data with a "formula cell", "target cell" and a "by changing cell".

In this case, the formula cell (B5) uses the PMT function to calculate the monthly payment amount.

=PMT(B3/12,B2,B1)

2. Go to the "Data" tab on the ribbon, click the "What-If Analysis" button, and select "Goal Seek."

3. In the Goal Seek dialog box, select the cells and type your target value.

• Formula cell: B5 (monthly payment formula)

• Value to find: -1,500 (desired monthly payment amount)

Note: This number is negative because it represents a payment.

• By changing cell: B2 (input terms in month)

4. Goal Seek runs and finds the answer. The value in the 'By changing cell' would be replaced with a new one. Click ‘OK’ to accept the new value or ‘Cancel’ to restore the original one.

In this example, Goal Seek has found the answer that you need 82 months (rounded up to the next integer) to reach the desired monthly payment of $1,500.

Goal Seek Precision

Goal Seek operates through iterative approximation to find the desired result. Therefore, it's essential to understand that the calculated solution may not always be exact. You can change the iteration settings in your spreadsheet to find a more precise solution.

Go to the "File" tab, click "Options" and choose "Formula." Enable "Iterative" calculations and control the settings to get a more precise result.

• Increase "Maximum Iterations" to test more possible solutions.

• Decrease "Maximum Change" to decline the amount of your acceptable change between iterations. For example, change the default 0.01 to 0.0000001.

Goal Seek Troubleshooting

Sometimes, Goal Seek may not produce accurate results or fail to converge. Here are some common reasons:

• Circular References: Ensure there are no circular references in your worksheet, as Goal Seek cannot handle circular dependencies.

• Complex Formulas: Simplify complex formulas or break them down into smaller steps to improve Goal Seek accuracy.

• Data Constraints: Check if your input data falls within realistic constraints. Unrealistic or extreme values can lead to convergence issues.