ModeldrillDCF model steps
A DCF values a business as the present value of its future unlevered free cash flows plus a terminal value, both discounted at WACC. Steps: forecast UFCF for 5 to 10 years, estimate WACC, compute terminal value, discount cash flows and terminal value to today, subtract net debt to get equity value, divide by shares.
Syntax
EV = sum of UFCF_t / (1 + WACC)^t + TV / (1 + WACC)^n| Part | What it is |
|---|---|
UFCF_t | Unlevered free cash flow in forecast year t: EBIT x (1 - tax) + D&A - capex - change in NWC |
WACC | Weighted average cost of capital, the discount rate for unlevered cash flows |
TV | Terminal value at the end of year n, by perpetual growth or exit multiple |
n | Number of explicit forecast years |
Examples
UFCF: 100, 110, 120 (years 1 to 3); TV at year 3 = 1,500; WACC = 10%
=NPV(10%, 100, 110, 120 + 1500)Result: 1,398.95
PVs are 90.91 + 90.91 + 90.16 for the cash flows and 1,126.97 for the terminal value.
Terminal value by Gordon growth, g = 2%:
=120 * (1 + 2%) / (10% - 2%)Result: 1,530.00
Uses the final year cash flow grown one more year.
Equity bridge: EV 1,399, net debt 300, 100 shares
=(1399 - 300) / 100Result: 10.99 per share
Common mistakes
- Discounting levered cash flows at WACC: WACC matches unlevered (pre-debt) cash flows; levered cash flows need the cost of equity.
- Terminal value is 70% to 90% of the total and is built on an unrealistic final year, so normalize capex, D&A and NWC in the last forecast year.
- Forgetting to discount the terminal value: TV sits at year n and must be divided by (1 + WACC)^n like any other year n amount.
Practice DCF model steps
These drills check your answer on live data, free, no account needed.
- Unlevered free cash flowFill NOPAT (row 6: EBIT after tax at the rate in B2) and unlevered free cash flow (row 10: NOPAT plus depreciation, less capital spending, less the increase in working capital).
- Discount factors and present valuesFill the discount factor for each year (row 6: 1 divided by (1 + rate) to the power of the year), the present value of each cash flow (row 7) and the total present value in B8.
- Enterprise value to equity valueFor three valuation cases, bridge from enterprise value to equity value (row 10: less debt, plus cash, less minority interest), then value per share (row 11) and the upside against the current price (row 12).
Questions
How many years should a DCF forecast?
Typically 5 years, up to 10 when the business is still growing fast or has a long build-out. The explicit period should end when cash flows are stable enough for a perpetuity or exit multiple.
What discount rate does a DCF use?
An unlevered DCF discounts UFCF at WACC. A levered DCF (FCFE) discounts cash flows to equity at the cost of equity and gives equity value directly.
Is a DCF enterprise value or equity value?
An unlevered DCF gives enterprise value. Subtract net debt, preferred stock and minority interest to reach equity value, then divide by diluted shares for a per share value.
Related
- Unlevered free cash flowUnlevered free cash flow = EBIT x (1 - tax rate) + D&A - capex - increase in net working capital. Examples from EBIT and from EBITDA, plus the sign rules.
- WACCWACC = E/V x cost of equity + D/V x cost of debt x (1 - tax rate). Cost of equity from CAPM, market value weights, an example giving 9.0% and common errors.
- Terminal valueTerminal value captures all cash flows after the forecast period. Perpetuity growth and exit multiple formulas with examples, and how to discount it right.
- Enterprise value to equity valueEquity value = enterprise value - debt + cash - preferred stock - minority interest. The EV to equity bridge step by step, with examples and per share value.