What is goal seeking?
Goal seeking is the process of working backward from a desired output (result) to find the input value that will produce that output. It’s commonly used in spreadsheets and other software to answer “what if the result must be X — what must be the input?” rather than the usual forward calculation of output from known inputs. (Source: Investopedia / Ellen Lindner)
Why use goal seeking?
– To determine a single unknown input when you know the desired result (e.g., interest rate needed to produce an affordable monthly payment; hourly rate needed to reach an annual income goal).
– To perform targeted what‑if analysis without algebraically solving the formula.
– To explore scenarios quickly when an analytic inverse is difficult or tedious.
How goal seeking works (conceptually)
– You must have a cell (or expression) that calculates the output from inputs.
– You tell the tool which output cell must equal a target value and which single input cell it may change.
– The tool iteratively adjusts the input until the formula in the output cell evaluates (within a tolerance) to the target value. If the relationship is not well-behaved (non-monotonic, discontinuous, multiple roots) the tool may fail or return a local/non‑meaningful result.
Practical steps in Microsoft Excel (Goal Seek)
1. Build the worksheet
– Put your known inputs in cells.
– Put the input you want Excel to change in its own cell (e.g., annual interest rate in cell B2).
– Put the formula that computes the output in a cell (the “Set cell”). The Set cell must contain a formula that depends (directly or indirectly) on the input cell you will change.
2. Run Goal Seek
– On Excel’s ribbon: Data → What‑If Analysis → Goal Seek.
– In the Goal Seek dialog:
– Set cell: choose the output cell that contains the formula.
– To value: enter the desired output value.
– By changing cell: choose the single input cell Excel will alter.
– Click OK. Goal Seek will iterate and report a solution or say it couldn’t find one.
Step‑by‑step example — find the interest rate for a loan
Goal: Find the annual interest rate that results in a $1,200 monthly payment for a $250,000 loan over 30 years.
1. Enter values:
– A1: Principal = 250000
– A2: Nper (months) = 360
– A3: Rate (annual, the cell to be found) = 0.04 (initial guess)
2. Payment formula (A4):
– A4: =PMT(A3/12, A2, -A1)
– This yields the monthly payment as a positive number.
3. Use Goal Seek:
– Set cell: A4
– To value: 1200
– By changing cell: A3
Result: Goal Seek will return the annual rate that makes the monthly payment $1,200. (In this example you should get a rate around 4.06% annually.)
Notes and practical tips
– The “Set cell” must contain a formula that depends on the “By changing cell.” If the formula is independent, Goal Seek can’t find a connection.
– Goal Seek only changes one cell. To solve for two or more unknowns, use Excel Solver (or other add‑ins).
– Goal Seek works best when the underlying relationship between input and output is continuous and monotonic around the solution. If multiple solutions exist, Goal Seek may return any one or fail.
– If Goal Seek reports “no solution,” try:
– Changing the initial guess (enter a different starting value in the changing cell).
– Checking formula signs (e.g., PMT returns negative payments if PV is positive—use consistent sign convention).
– Verifying there is an actual solution for the given target.
– Solver is the recommended alternative when:
– You need to change multiple input cells.
– Constraints (bounds, integer variables) are required.
– You require optimization (maximize/minimize) rather than matching a target value.
Other contexts for goal seeking
– Personal finance: Determine how much you must save monthly to reach a retirement balance target.
– Business planning: Find the price or sales volume required to hit a revenue or profit target.
– Entrepreneurship: If you want to gross $100,000 per year and plan to work 2,000 hours, the required hourly rate is $100,000 / 2,000 = $50/hour — a simple manual goal seek.
– Engineering and science: Solve for a parameter when only the output constraint is specified (often done with specialized solvers).
Limitations and cautions
– Only one input variable can be determined directly with Goal Seek.
– Convergence is not guaranteed; the underlying function may be non‑invertible, discontinuous, or have multiple roots.
– Goal Seek’s result is numeric and approximate; verify the solution and consider sensitivity analysis.
– For complex or constrained problems use Solver or programming approaches (e.g., bisection, Newton‑Raphson methods, or optimization libraries).
Quick reference: alternatives in Excel
– Goal Seek: single-variable target match (Data → What‑If Analysis → Goal Seek).
– Data Table: examine how changes in one or two variables affect results (good for batch what‑if).
– Scenario Manager: save and compare multiple named scenarios.
– Solver: solve for multiple variables, add constraints, optimize objectives.
Further reading and source
– Investopedia, “Goal Seeking,” Ellen Lindner. https://www.investopedia.com/terms/g/goal-seeking.asp
If you want, I can:
– Create a ready-to-use Excel example file (.xlsx) with the loan example pre-built.
– Walk through a different example (e.g., determining required savings rate to reach a retirement target).
– Show how to use Solver for a multi‑variable case.