Data & spreadsheets · DCF Calculator
How to Run a DCF Sensitivity Analysis One Assumption at a Time
· How it works
dcf valuation spreadsheets
A DCF gives one number, but the honest answer is a range. This post shows how to build a sensitivity table by hand, which inputs to flex first, and how to read the result without over-interpreting it.
One valuation figure presented as if it were precise — why a point estimate hides how fragile the model is
One DCF result is a coordinate, not an answer independent of assumptions. A point estimate hides whether a small change to growth or the discount rate barely moves the model or sends it across a wide range. Sensitivity analysis makes that dependence visible and is more honest than adding decimal places to the base case.
One-at-a-time flexing — holding every input fixed and moving one (discount rate, growth, margin) through a plausible range
Begin by holding every input fixed and changing one assumption through a deliberately chosen hypothetical range. This isolates direction and magnitude: the repository tests confirm that a higher discount rate lowers value and higher terminal growth raises it. Do not change growth, margins and reinvestment together and then attribute the movement to one of them.
Building a two-way grid — pairing discount rate against terminal growth to see where the value swings hardest
A two-way grid repeats the model for each discount-rate and terminal-growth pair. ToolAcre builds exactly this matrix and preserves invalid cells as null with an error when growth meets or exceeds the discount rate. A blank corner is therefore evidence that the perpetuity formula is undefined there, not a missing favourable outcome.
Which inputs deserve attention — ranking assumptions by how much a small, realistic change moves the result
Rank inputs by the movement caused by a comparable change, not by how dramatic their labels sound. Long-dated models usually respond strongly to the discount rate and terminal growth because both affect terminal value, while forecast growth changes each compounded cash flow. The useful ranking is specific to the model and range you actually tested.
Worked example — a base case re-run at several discount and growth rates and tabulated into a simple grid with the base case marked
For five constant hypothetical cash flows of 100, the grid at discount rates 8%, 10% and 12% and terminal growth 1%, 2.5% and 4% ranges from 881.48 to 2,168.79. The 10% and 2.5% base cell is 1,227.67. These figures are arithmetic demonstrations, not plausible bounds for any security.
Reading the grid honestly — treating the spread as the answer and noticing when a corner of the table produces nonsense
Read the spread before the centre. A grid whose neighbouring cells move sharply says the model cannot support a narrowly stated conclusion. Also inspect invalid combinations and extreme corners rather than averaging them into a comforting midpoint. ToolAcre reports figures and errors only; it deliberately does not label cells as attractive or unattractive.
What this does not cover — Monte Carlo simulation, correlated assumptions and scenario probabilities are beyond a simple calculator
A rectangular grid does not assign probabilities, preserve correlation between assumptions or model a distribution. Monte Carlo analysis and scenario probabilities require evidence about how inputs move together. The calculator’s named scenarios and grid are deterministic reruns, useful for mechanics but not a forecast of how often outcomes occur.
Sensitivity is the point of the exercise — how re-entering assumptions in the ToolAcre DCF Calculator turns each cell of the grid into a quick recalculation
Sensitivity is not an appendix added after a valuation; it is the test of what the valuation means. Use the built-in grid to find high-movement cells, then rerun them and inspect the rows. The output depends entirely on the ranges chosen, so document those choices instead of presenting the widest or narrowest spread as objective.