Goal Seek
Executive Summary
Key Takeaways
- ✓ Goal Seek is an Excel function that finds the input value required to produce a specified output.
- ✓ It adjusts a single input cell iteratively until a formula result reaches a target value.
- ✓ It is widely used for debt sizing, IRR analysis, and break-even calculations.
- ✓ Goal Seek leaves no record of what was changed; undocumented Goal Seek inputs are a significant audit risk.
- ✓ Auditors should identify Goal Seek outputs, verify the underlying relationship, and flag any pattern where outputs have been backed into rather than derived.
Definition¶
Goal Seek is a built-in Excel what-if analysis function that iteratively adjusts a single input cell until a specified formula cell reaches a target value. It solves a single-variable equation numerically: given a desired output, what input is required?
Goal Seek is accessed in Excel via: Data → What-If Analysis → Goal Seek.
In financial models, Goal Seek is used for tasks such as:
- Finding the debt amount that produces a target DSCR
- Finding the sale price at which equity IRR reaches a hurdle rate
- Finding the operating cost level at which a project breaks even
- Finding the interest rate at which an investment becomes unviable
Why It Matters¶
Goal Seek is a standard tool in financial modelling practice. Its importance in a model audit context arises not from its correct use but from its misuse:
Goal Seek does not store its methodology. When Goal Seek is run, it modifies the input cell directly. If no record is kept of what was changed, why, and with what result, the model cannot be audited. A model in which critical inputs have been set by Goal Seek — without documentation — presents a significant audit risk, because the auditor cannot determine from inspection alone whether the input is formula-derived, assumption-driven, or Goal Seek output.
Additionally, Goal Seek can be used to produce a desired output by backing into an input, rather than by correctly computing the relationship from first principles. This is a common route to model manipulation: setting an input by Goal Seek to make an IRR or DSCR reach a target figure, rather than allowing the model to calculate it naturally.
Technical Background¶
How Goal Seek Works¶
Goal Seek uses an iterative numerical method (effectively binary search or Newton-Raphson iteration) to find the input value that minimises the difference between the formula result and the target value. It stops when either:
- The formula result is within a specified precision threshold of the target
- The maximum number of iterations is reached
The default settings in Excel are 100 iterations and a precision of 0.001. These can be adjusted under File → Options → Formulas → Iteration.
Using Goal Seek: Step-by-Step¶
- Navigate to Data → What-If Analysis → Goal Seek
- In the Set Cell field, enter the cell reference for the formula output (e.g. the DSCR result)
- In the To Value field, enter the target (e.g. 1.20)
- In the By Changing Cell field, enter the input cell to be adjusted (e.g. the debt amount)
- Click OK
Excel will iteratively adjust the changing cell until the set cell reaches the target value (or the iteration limit is reached).
Limitations of Goal Seek¶
| Limitation | Description |
|---|---|
| Single variable only | Goal Seek can only adjust one input at a time. Use Excel Solver for multi-variable optimisation. |
| No memory | Goal Seek modifies the cell directly and leaves no record of the pre-run value or the methodology |
| Convergence failure | If the relationship between the input and output is non-monotonic, Goal Seek may not converge or may find the wrong solution |
| Circular reference interaction | If the model contains iterative circular references, Goal Seek may interact unpredictably with the circular calculation |
| Precision limit | Default precision settings may not be sufficient for high-precision financial calculations |
Goal Seek vs Solver¶
| Feature | Goal Seek | Solver |
|---|---|---|
| Variables | Single input only | Multiple inputs |
| Constraints | None | Multiple constraints can be specified |
| Objective | Single target value | Minimise, maximise, or reach a target |
| Access | Built-in | Add-in (must be enabled) |
| Use case | Simple back-solving | Complex optimisation |
Common Financial Modelling Uses¶
Debt sizing: Find the maximum debt amount such that minimum DSCR equals the covenant threshold.
Set Cell: Minimum DSCR result cell
To Value: 1.20 (or applicable covenant threshold)
By Changing Cell: Total debt amount
IRR hurdle check: Find the revenue required to achieve a target equity IRR.
Set Cell: Equity IRR result cell
To Value: 0.15 (15% hurdle rate)
By Changing Cell: Revenue per unit or total revenue
Break-even analysis: Find the minimum revenue at which the project achieves cash flow breakeven.
Set Cell: Annual net cash flow
To Value: 0
By Changing Cell: Revenue assumption
Audit Considerations¶
1. Identify Goal Seek Outputs¶
There is no native Excel method to identify which cells have been set by Goal Seek. An auditor must identify these cells by:
- Reviewing model documentation or assumption registers for references to Goal Seek
- Inspecting input cells for values that appear calculated (e.g. 47,382,916) rather than round assumptions (e.g. 50,000,000) — though this is indicative, not conclusive
- Asking the model builder directly
2. Verify the Underlying Relationship¶
Where a cell has been set by Goal Seek, verify that the relationship between the input and output is correctly specified. The most common error is backing into a wrong input because the formula path from input to output contains an error.
3. Documentation Requirement¶
A model that relies on Goal Seek for material inputs should document this in its assumption register:
- Which input was set by Goal Seek
- What output was targeted
- What the target value was
- What the result was before Goal Seek was run
- Why Goal Seek was used rather than a formula-driven calculation
4. Goal Seek and Manipulation¶
Flag models where Goal Seek appears to have been used to back-solve a key metric (equity IRR, DSCR) to a specific target. This pattern — where the output is the starting point and the input is derived to fit it — is a common route to presenting a model that meets a threshold without genuinely analysing whether the transaction supports that threshold.
5. Repeatability¶
A model that requires Goal Seek to be re-run to produce valid outputs is fragile. Best practice is to derive all outputs from formula-driven calculations. Where Goal Seek is used for analysis, the result should be hard-coded with documentation, or the relationship should be solved algebraically and built into the model as a formula.
Common Errors¶
| Error | Description | Risk |
|---|---|---|
| Undocumented Goal Seek inputs | Key inputs set by Goal Seek with no record | Model cannot be audited or reproduced |
| Wrong changing cell | Goal Seek modifies the wrong input cell | Output reaches target but for the wrong economic reason |
| Circular reference conflict | Goal Seek run in model with active circular references | Result may be unreliable or non-convergent |
| Goal Seek used as analysis substitute | Team concludes project is viable because Goal Seek found an input that makes IRR work | Reversal of analytical logic |
| Precision too low | Default precision produces a result that is close but not exactly correct | Small errors in high-stakes outputs |
Best Practices¶
Use Goal Seek for analysis and sense-checking, not as a primary modelling method. Where Goal Seek reveals the input required to meet a target, analyse whether that input is realistic given the project's economics — rather than accepting it as the target assumption.
Document every Goal Seek run that results in a material input being set. Maintain a log of what was changed, what the target was, and what the result was before and after.
Where the analysis genuinely requires solving for an input given a target output (such as maximum debt under a DSCR constraint), consider building the relationship as an algebraic formula or using a toggle cell that allows the model to be switched between solving modes.
Continue Reading¶
Prerequisites¶
- What Makes an Excel Financial Model Reliable? — the parent pillar
Related Pillars¶
Related Technical Guides¶
Related Glossary¶
How OXXON tests thisRun a free structural check with FMAE
Frequently Asked Questions
Is Goal Seek the same as Solver?
No. Goal Seek adjusts a single input cell to reach a single target. Solver is a separate Excel add-in that can optimise across multiple variables subject to multiple constraints. Goal Seek is appropriate for simple back-solving; Solver is required for multi-variable optimisation.
Does Goal Seek work with circular references?
Goal Seek can be run in a model with circular references if iterative calculation is enabled. The interaction between Goal Seek and iterative calculation is complex and can produce unreliable results. Models relying on Goal Seek within a circular reference structure should be reviewed carefully.
How can I tell if a cell was set by Goal Seek?
Excel provides no native indicator. Auditors typically identify Goal Seek outputs by reviewing model documentation, inspecting cells with non-round values in assumption sections, or asking the model builder. This is one reason why documentation of Goal Seek use is an important best practice.
Can Goal Seek fail to find a solution?
Yes. If the target value is outside the range that the formula can produce by adjusting the input, Goal Seek will report that it cannot find a solution. It can also fail to converge if the formula relationship is non-monotonic or highly non-linear within the relevant range.
Related Articles
Circularity in Debt Models
Circularity in debt models arises from the interdependence of interest expense and cash availability in the same period. In a project finance model, interest is charged on the drawn debt balance; the interest payment reduces available cash; available cash determines the repayment amount; the repayment amount determines the closing debt balance; and the closing balance determines the next period's interest charge. When a model calculates interest on the average of opening and closing balances, or when a cash sweep mechanism uses the same period's interest cost in determining sweep amounts, a circular dependency is introduced. The two principal resolution techniques are: calculating interest on the opening balance rather than the average balance, and using a defined debt repayment algorithm that determines the repayment amount without reference to the closing interest charge.
Data Table
In Excel, a data table is a range of cells that performs a series of what-if calculations by substituting a set of input values into one or two designated cells and recording the resulting output from a specified formula. Data tables are the standard mechanism for producing sensitivity matrices in financial models. A one-variable data table varies one input and shows the output for each value; a two-variable data table varies two inputs simultaneously. Data table results are stored as array formulas using the TABLE function and update automatically when the model recalculates, unless the workbook's calculation mode excludes data tables from automatic recalculation.
Equity IRR
Equity IRR (Equity Internal Rate of Return) is the discount rate at which the net present value of all equity cash flows — comprising the initial equity investment as a negative cash flow and subsequent distributions and terminal proceeds as positive cash flows — equals zero. It measures the annualised return earned by equity investors on capital contributed to a project or transaction, calculated on post-debt-service cash flows only. Equity IRR is distinct from Project IRR, which is calculated on total project cash flows before financing. Equity IRR is always higher than Project IRR in a positively leveraged transaction because debt amplifies equity returns. It is lower than Project IRR when leverage is negative — that is, when the cost of debt exceeds the unlevered return of the project.
What Makes an Excel Financial Model Reliable?
An Excel financial model is a structured spreadsheet used to represent, calculate, and forecast the financial mechanics of a business, investment, or transaction. Reliability is not a function of how sophisticated a model looks; it is a function of its structure, discipline, and consistency. This page defines what an Excel financial model is, the structural characteristics that separate a reliable model from a fragile one, and the standards and terminology that underpin every other page in the FMAE Knowledge Centre that references a specific modelling concept. This is a crowded educational topic, and most existing content in this space is course marketing rather than a neutral reference. This page is written as the latter: a vendor neutral definition of reliable modelling practice, not a sales page for a training course.