A two-way data table on an illustrative five-year DCF, built step by step in Excel, and the diagonal that shows which cells are really different views.
Lay the WACC down the side and the perpetual growth rate across the top, recompute enterprise value in every cell, and centre the grid on the base case. On an illustrative five-year DCF worth 73.38m at a 9.0 per cent WACC and 2.0 per cent growth, a grid from 8 to 10 per cent and 1 to 3 per cent runs from 58.7m to 99.6m: the corners are 1.70 times apart, a range of 55.7 per cent of the base value.
Worked in full in Business Valuation by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →
The two-way table is the first thing a reviewer reads in a DCF, because the cost of capital and the terminal growth rate are the two assumptions that cannot be checked against a contract or a set of accounts. Built properly, it also shows something the single answer hides: which combinations of the two inputs are actually different views of the world, and which are the same view relabelled.
| Input | Value |
|---|---|
| Free cash flow, years 1 to 5 | 4.66, 4.90, 5.15, 5.40, 5.65 |
| WACC (base) | 9.0% |
| Perpetual growth after year 5 (base) | 2.0% |
| Net debt | 18.0 |
| Shares outstanding | 4.0 |
Step 1: the base case. Discount each year's cash flow at 9.0 per cent: 4.28, 4.12, 3.98, 3.83 and 3.67, a total of 19.87. The terminal value takes year 6 cash flow, 5.65 × 1.02 = 5.7630, over the 7.0 per cent spread between WACC and growth.
TV = 5.7630 / (0.09 − 0.02) = 82.33; PV of TV = 82.33 × 0.6499 = 53.51
EV = 19.87 + 53.51 = 73.38; equity = 73.38 − 18.0 = 55.38, or 13.85 a share
The terminal value is 72.9 per cent of the answer, which is why the grid matters.
Step 2: build the grid with an Excel data table. The model must run from two single input cells, one for WACC and one for growth, on the same sheet as the table (Excel will not accept input cells on another sheet).
1. In the top-left corner of the grid, link to the output: =EV.
2. Type the growth rates across the top row and the WACCs down the first column.
3. Select the whole block, then Data > What-If Analysis > Data Table: row input cell = growth, column input cell = WACC.
Excel fills the body with {=TABLE(g_cell,WACC_cell)}. If the workbook is slow, set calculation to "Automatic except for data tables" and press F9 when needed.
The rules on typed axis values and the centre-cell check are the same in any model; they are worked for a property model in how to build a two-way data table for property IRR.
| WACC \ growth | 1.0% | 1.5% | 2.0% | 2.5% | 3.0% |
|---|---|---|---|---|---|
| 8.0% | 75.9 | 80.5 | 85.8 | 92.1 | 99.6 |
| 8.5% | 70.7 | 74.6 | 79.1 | 84.3 | 90.5 |
| 9.0% | 66.2 | 69.6 | 73.4 | 77.8 | 82.9 |
| 9.5% | 62.3 | 65.1 | 68.4 | 72.2 | 76.5 |
| 10.0% | 58.7 | 61.2 | 64.1 | 67.3 | 71.0 |
| WACC \ growth | 1.0% | 1.5% | 2.0% | 2.5% | 3.0% |
|---|---|---|---|---|---|
| 8.0% | 14.48 | 15.62 | 16.95 | 18.52 | 20.41 |
| 8.5% | 13.19 | 14.16 | 15.28 | 16.58 | 18.13 |
| 9.0% | 12.06 | 12.89 | 13.85 | 14.95 | 16.23 |
| 9.5% | 11.06 | 11.79 | 12.61 | 13.54 | 14.62 |
| 10.0% | 10.18 | 10.81 | 11.52 | 12.32 | 13.24 |
Three readings. First, the grid is asymmetric: 50 basis points more WACC costs 6.8 per cent of value, 50 basis points more growth adds 6.0 per cent, and both effects grow as the spread narrows, which is why the top-right corner (99.6) is further from the centre than the bottom-left (58.7). Second, across the full range the WACC moves value more than growth: 64.1 to 85.8 holding growth at 2.0 per cent, a span of 21.7, against 66.2 to 82.9, a span of 16.7, holding WACC at 9.0. Third, leverage magnifies it per share: the bottom-left cell is 20.0 per cent below the base in enterprise value and 26.5 per cent below in equity.
| WACC | Growth | Enterprise value |
|---|---|---|
| 8.0% | 1.0% | 75.9 |
| 8.5% | 1.5% | 74.6 |
| 9.0% | 2.0% | 73.4 |
| 9.5% | 2.5% | 72.2 |
| 10.0% | 3.0% | 71.0 |
Along the diagonal where both inputs rise together, value barely moves: 75.9 to 71.0. The terminal value depends on the spread, not on either input alone. A higher inflation assumption that raises both the nominal WACC and nominal growth by the same amount is not a different valuation, provided the forecast cash flows are inflated too, and a grid that is quoted from corner to corner overstates the uncertainty that matters. The cells that represent a genuinely different view are those that move the spread.
Why the terminal value carries so much of the answer is covered in what percentage of a DCF the terminal value should be, and the WACC in the side column is built in how to calculate WACC step by step.
Drive the model from one WACC cell and one growth cell, run a two-way data table centred on the base, show value per share beside enterprise value, and read the diagonal before the corners. The free workbook for this book runs the same grid on its own discounted cash flow and ranks every input by the value it moves.
Steps small enough to be plausible and a range wide enough to be honest, typically 50 basis points over two points of WACC and growth. On the illustrative DCF, 8 to 10 per cent WACC and 1 to 3 per cent growth produce values from 58.7m to 99.6m around a 73.38m base.
Show both, because net debt is a fixed deduction and magnifies every move in equity. On the illustrative DCF the cell at a 10.0 per cent WACC and 1.0 per cent growth is 20.0 per cent below the base in enterprise value but 26.5 per cent below in equity: 10.18 a share against 13.85. A buyer of shares reads the second grid, not the first.
Over the same two-point range, usually the WACC, because it also discounts the forecast years. On the illustrative DCF, WACC from 8 to 10 per cent spans 21.7m of value at 2.0 per cent growth, while growth from 1 to 3 per cent spans 16.7m at a 9.0 per cent WACC.
This article is one calculation from Business Valuation. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
Get the book on Amazon →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Reading guide: corporate finance, valuation and markets → · All 1340 articles →
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.