Template · Finance
Discounted cash flow (DCF) valuation model
Five-year free cash flow, WACC build-up, terminal value by two methods and a sensitivity grid.
Downloads are included in the $19.85 yearly membership. Sign in
XLSXCSVODSXMLNumbers| DCF valuation model | |||||
| Example data — replace the blue input cells with your own. | |||||
| Fictional company. Figures are in $ millions except per-share values. | |||||
| Company | |||||
| Company | Example Industrial Co. | ||||
| Valuation date | Dec 31, 2026 | ||||
| Base year (last full fiscal year) | 2026 | ||||
| Base-year revenue ($ millions) | 480.0 | ||||
| Base-year net working capital (% of revenue) | 12.0% | ||||
| Projection drivers | FY2027 | FY2028 | FY2029 | FY2030 | FY2031 |
| Revenue growth | 8.0% | 7.5% | 7.0% | 6.0% | 5.0% |
| EBIT margin | 14.0% | 14.5% | 15.0% | 15.5% | 16.0% |
| Depreciation and amortization (% of revenue) | 4.0% | 4.0% | 4.0% | 4.0% | 4.0% |
| Capital expenditure (% of revenue) | 4.6% | 4.5% | 4.4% | 4.2% | 4.0% |
| Net working capital (% of revenue) | 12.0% | 12.0% | 11.8% | 11.8% | 11.5% |
Showing the first 16 of 29 rows and 6 of 6 columns. Cells with formulas show the formula on hover.
| Weighted average cost of capital (WACC) | ||
| Build-up of the discount rate. Example inputs — use current market data for your own valuation date. | ||
| Cost of equity (CAPM) | ||
| Risk-free rate | 4.25% | e.g. the 10-year government bond yield on the valuation date |
| Equity risk premium | 5.50% | |
| Levered beta | 1.10 | |
| Size or company-specific premium | 0.00% | Optional; 0 if not used |
| Cost of equity | 10.30% | Risk-free rate + beta × equity risk premium + premium |
| Cost of debt | ||
| Pre-tax cost of debt | 6.00% | |
| Tax rate | 25.00% | From the Assumptions tab |
| Tax shield | 1.50% | |
| After-tax cost of debt | 4.50% | |
Showing the first 16 of 21 rows and 3 of 3 columns. Cells with formulas show the formula on hover.
| DCF valuation | ||||||
| Unlevered free cash flow, discounting and value per share. All cells are formulas — edit the Assumptions and WACC tabs. $ millions. | ||||||
| $ millions | FY2026 base | FY2027 | FY2028 | FY2029 | FY2030 | FY2031 |
| Free cash flow | ||||||
| Revenue | 480.0 | 518.4 | 557.3 | 596.3 | 632.1 | 663.7 |
| Revenue growth | 8.0% | 7.5% | 7.0% | 6.0% | 5.0% | |
| EBIT | 72.6 | 80.8 | 89.4 | 98.0 | 106.2 | |
| EBIT margin | 14.0% | 14.5% | 15.0% | 15.5% | 16.0% | |
| Less: taxes on EBIT | -18.1 | -20.2 | -22.4 | -24.5 | -26.5 | |
| NOPAT (EBIT after tax) | 54.4 | 60.6 | 67.1 | 73.5 | 79.6 | |
| Plus: depreciation and amortization | 20.7 | 22.3 | 23.9 | 25.3 | 26.5 | |
| Less: capital expenditure | -23.8 | -25.1 | -26.2 | -26.5 | -26.5 | |
| Net working capital (balance) | 57.6 | 62.2 | 66.9 | 70.4 | 74.6 | 76.3 |
| Less: increase in net working capital | -4.6 | -4.7 | -3.5 | -4.2 | -1.7 | |
| Unlevered free cash flow | 46.7 | 53.2 | 61.2 | 68.0 | 77.9 |
Showing the first 16 of 47 rows and 7 of 7 columns. Cells with formulas show the formula on hover.
| Sensitivity analysis | ||||||
| Value per share for a range of assumptions. Built with ordinary formulas (not data tables), so it recalculates in any spreadsheet app. | ||||||
| WACC step | 0.50% | |||||
| Terminal growth step | 0.25% | |||||
| Exit multiple step (times) | 1.0 | |||||
| Value per share, Gordon growth: WACC (down) × terminal growth rate (across) | ||||||
| WACC \ growth | 2.00% | 2.25% | 2.50% | 2.75% | 3.00% | |
| WACC | 7.85% | $22.13 | $23.09 | $24.13 | $25.27 | $26.53 |
| 8.35% | $20.10 | $20.90 | $21.75 | $22.69 | $23.71 | |
| 8.85% | $18.37 | $19.04 | $19.76 | $20.54 | $21.38 | |
| 9.35% | $16.88 | $17.44 | $18.05 | $18.71 | $19.42 | |
| 9.85% | $15.57 | $16.06 | $16.58 | $17.14 | $17.74 | |
| Value per share, exit multiple: WACC (down) × EV/EBITDA multiple (across) |
Showing the first 16 of 24 rows and 7 of 7 columns. Cells with formulas show the formula on hover.
| Discounted cash flow (DCF) valuation model |
| Notes: what this workbook does, how to use it and the method behind it. |
| What it does |
| Values a company from the unlevered free cash flow it is expected to generate over five years plus a terminal value, discounted at the weighted average cost of capital (WACC), then bridges enterprise value to equity value and value per sha… |
| How to use it |
| 1. On the Assumptions tab, enter the base year, base-year revenue and the five-year drivers: growth, EBIT margin, D&A, capital expenditure and net working capital as a share of revenue. |
| 2. Enter the tax rate, terminal growth rate, exit multiple and whether to use the mid-year convention. |
| 3. Enter debt, cash, other claims and diluted shares at the valuation date. |
| 4. On the WACC tab, enter the risk-free rate, equity risk premium, beta, pre-tax cost of debt and target debt weight, using market data for your valuation date. |
| 5. Read enterprise value, equity value and value per share on the DCF tab, then test the result on the Sensitivity tab. |
| Formulas and method |
| Revenue(t) = revenue(t−1) × (1 + growth). EBIT = revenue × EBIT margin. NOPAT = EBIT − EBIT × tax rate. |
| Unlevered free cash flow = NOPAT + D&A − capital expenditure − increase in net working capital (net working capital = revenue × NWC %). |
Showing the first 16 of 35 rows and 1 of 1 columns. Cells with formulas show the formula on hover.
What does this template do?
This model values a company from the free cash flow it is expected to produce over five years, plus a terminal value for the years after that. Free cash flow is built from revenue, EBIT margin, tax, depreciation, capital expenditure and working capital, all set on the Assumptions tab. The WACC tab builds the discount rate from the cost of equity (CAPM) and the after-tax cost of debt.
The terminal value is calculated two ways, with the Gordon growth formula and with an exit multiple. Each method implies the other as a check. The DCF tab bridges enterprise value to equity value and then to value per share. The mid-year convention can be switched on or off.
The Sensitivity tab repeats the valuation across grids of discount rates against growth rates or multiples, using ordinary formulas. The example company is fictional and its inputs are illustrative, not market data. The output is an arithmetic result from the stated assumptions, not a price target.
What’s inside
- Five-year unlevered free cash flow built from revenue, margin, tax and working capital
- WACC from the CAPM cost of equity, the after-tax cost of debt and target weights
- Terminal value by Gordon growth and by exit multiple, with the implied rate or multiple
- Mid-year convention switch and an enterprise-to-equity bridge
- Two sensitivity grids of value per share, built with plain formulas
Which tabs does the workbook have?
| Tab | What it holds |
|---|---|
| Assumptions | Company, base year, five-year drivers, tax, terminal value inputs and balance sheet items. |
| WACC | Cost of equity, after-tax cost of debt, capital structure and the resulting discount rate. |
| DCF | Free cash flow build, discounting, terminal values, enterprise and equity value, and value per share. |
| Sensitivity | Value per share across WACC and terminal growth, and across WACC and exit multiple. |
| Notes | Purpose, steps, formulas used, assumptions and limits. |
What formulas does this template use?
This template holds 201 formulas in 388 cells across 5 tabs, so 52% of its cells calculate. They use 5 distinct functions; the longest formula is 244 characters and 133 of them read from another tab.
| Function | Uses | What it does |
|---|---|---|
IF | 48 | one result when a test is true, another when false |
ISNUMBER | 10 | true for a number |
AND | 3 | true when every test is true |
ABS | 2 | absolute value |
SUM | 2 | adds numbers |
Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.
How do you use it?
- On the Assumptions tab, enter the base-year revenue and the five-year drivers for growth, EBIT margin, D&A, capital expenditure and working capital.
- Enter the tax rate, terminal growth rate, exit multiple and the mid-year switch.
- Enter debt, cash, other claims and diluted shares at the valuation date.
- On the WACC tab, enter the market inputs: risk-free rate, equity risk premium, beta, cost of debt and target debt weight.
- Read enterprise value, equity value and value per share on the DCF tab, then test them on the Sensitivity tab.
What is it good for?
- Estimating the value of a private company for a financing discussion
- Testing how much a valuation depends on the discount rate
- Checking a market price against a set of stated assumptions
- Teaching how free cash flow, WACC and terminal value fit together
Questions about this sheet
What is the mid-year convention?
It discounts each year's cash flow from the middle of the year rather than the end. Set the switch on the Assumptions tab to 0 to turn it off.
Why do the two terminal values differ?
They rest on different assumptions. The Gordon value depends on growth and WACC, and the exit value depends on the multiple. The DCF tab shows the growth implied by the multiple and the multiple implied by growth, so you can compare them.
What happens if terminal growth is at or above WACC?
The Gordon formula does not work in that case, so the cell shows n/a. Terminal growth must be below WACC.
Where do the market inputs come from?
You supply them. Use the risk-free rate, equity risk premium and beta for your valuation date. The example values are illustrative and are not current market data.