DCF model template
Change any input and the model recalculates: five years of free cash flow, a terminal value, enterprise value, then equity value per share. The Excel file has the same model in live formulas.
Inputs
Value per share$21.82
Value per share
$21.82
- Enterprise value
- $1,093.0m
- Equity value
- $873.0m
- From terminal value
- 75%
Free cash flow, $ millions
| Line | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
|---|---|---|---|---|---|
| Revenue | 530.0 | 561.8 | 595.5 | 631.2 | 669.1 |
| EBIT | 95.4 | 101.1 | 107.2 | 113.6 | 120.4 |
| Less taxes | (23.8) | (25.3) | (26.8) | (28.4) | (30.1) |
| NOPAT | 71.6 | 75.8 | 80.4 | 85.2 | 90.3 |
| Plus D&A | 21.2 | 22.5 | 23.8 | 25.2 | 26.8 |
| Less capex | (26.5) | (28.1) | (29.8) | (31.6) | (33.5) |
| Less NWC | (3.0) | (3.2) | (3.4) | (3.6) | (3.8) |
| Free cash flow | 63.3 | 67.0 | 71.1 | 75.3 | 79.9 |
| Discount factor | 0.917 | 0.842 | 0.772 | 0.708 | 0.650 |
| Present value | 58.0 | 56.4 | 54.9 | 53.4 | 51.9 |
From cash flow to a share price
- PV of five years of free cash flow
- $274.6m
- PV of terminal value (1,259.2 in year 5, growing 2.5%)
- $818.4m
- Enterprise value
- $1,093.0m
- Less debt
- ($300.0m)
- Plus cash
- $80.0m
- Equity value
- $873.0m
- Divided by 40.0m diluted shares
- $21.82
Cross-check: this terminal value is 8.6x year‑5 EBITDA.
Value per share if WACC or growth moves
Perpetuity growth method. WACC down the side, terminal growth across.
| WACC | 1.5% | 2.0% | 2.5% | 3.0% | 3.5% |
|---|---|---|---|---|---|
| 8.0% | $22.77 | $24.65 | $26.87 | $29.54 | $32.80 |
| 8.5% | $20.71 | $22.29 | $24.14 | $26.32 | $28.94 |
| 9.0% | $18.92 | $20.27 | $21.82 | $23.64 | $25.78 |
| 9.5% | $17.36 | $18.52 | $19.84 | $21.37 | $23.15 |
| 10.0% | $15.99 | $16.99 | $18.12 | $19.42 | $20.92 |
How the model works
Six steps, the same in the calculator and in the spreadsheet.
1. Project revenue
Revenue × (1 + growth)
Five years from the last full year. In the spreadsheet you can type a different growth rate or margin for any single year.
2. Get to NOPAT
EBIT × (1 − tax rate)
EBIT is revenue times the margin. Tax it as if the company had no debt, because interest belongs to the lenders.
3. Unlevered free cash flow
NOPAT + D&A − capex − increase in NWC
Add back the non-cash expense, then subtract the cash the business reinvests in equipment and working capital.
4. Discount it
FCF ÷ (1 + WACC)^year
Each year's cash flow is worth less today. The template discounts at the end of each year.
5. Terminal value
FCF₅ × (1 + g) ÷ (WACC − g), or EBITDA₅ × multiple
The value of every year after year five. Check one method against the other: the page shows the multiple a growth rate implies, and the reverse.
6. Enterprise to equity value
EV − debt + cash, then ÷ diluted shares
Enterprise value belongs to lenders and shareholders together. Take off the debt, add the cash, and divide what is left by the diluted share count.
In an interview you will say these steps, not build them. See how a strong “walk me through a DCF” answer sounds.
Now walk an interviewer through it
Building a DCF and explaining one out loud are different skills. Practice the walk-through and the follow-ups on WACC and terminal value with an AI interviewer.
Questions people ask
Is the Excel file free, and does it need macros?
It is free with no sign-up and has no macros. Blue cells are inputs and every other cell is a formula, so you can follow every number. It also opens in Google Sheets.
Should I use perpetuity growth or an exit multiple?
Use both and compare them. In the example, 2.5% growth implies about 8.6x year-5 EBITDA, so a 10x exit multiple gives a higher value. If the two disagree by a lot, one of the assumptions needs a reason.
Why is most of the value in the terminal value?
Because a healthy company keeps producing cash after year five. In the example about three quarters of enterprise value comes from the terminal value, which is normal for a stable business and is why the sensitivity table matters.
Does the template use the mid-year convention?
No. It discounts each cash flow at the end of its year, which is simpler to follow and to check. With the mid-year convention every discount period is half a year shorter and the value comes out a little higher.
What WACC should I use?
WACC blends the cost of equity and the after-tax cost of debt by their weights in the capital structure. The example uses 9% for an invented company; for a real one, build it from the company's own beta, debt cost and target leverage.
Is the example a real company?
No. The company and every input are invented to show how the model works. Replace them with your own forecast.