Complete DCF Template
The discounted cash flow model is the industry standard for intrinsic valuation. While concept is straightforward—project future cash flows, discount them to present value, adjust for debt, and divide by shares—execution in a spreadsheet involves dozens of interconnected decisions. A professional DCF template combines all elements cohesively: input assumptions, explicit cash flow buildup (revenue → EBITDA → NOPAT → free cash flow), terminal value calculation, enterprise value computation, and per-share valuation. This article walks through a complete, production-ready DCF template, explaining each component so you can build or customize one for any company.
Quick definition: A DCF model projects annual free cash flows over an explicit forecast period (typically 5–10 years), discounts them at the weighted average cost of capital, adds a terminal value representing the company's value beyond the forecast period, and divides by shares outstanding to calculate intrinsic value per share.
Key takeaways
- Five-year explicit period balances accuracy (near-term forecasts are more reliable) against the need to capture normalized growth
- Revenue growth and margin progression form the foundation; all downstream cash flows depend on these top-line drivers
- Unlevered free cash flow removes financing decisions, isolating the company's operating performance
- Terminal value typically accounts for 60–80% of total enterprise value; small changes in terminal assumptions create large value swings
- Weighted average cost of capital (WACC) is the discount rate; misestimation directly biases your valuation
- Multiple validation checks ensure the model is internally consistent and free of structural errors
DCF template structure
A professional DCF spreadsheet is organized into distinct sections:
Section 1: Input Assumptions
- Historical financials and ratios
- Growth rates (revenue CAGR)
- Margin progression (EBITDA, NOPAT)
- Capital intensity (CapEx, working capital changes)
- Terminal assumptions
Section 2: Explicit Forecast Period (Years 1–5)
- Revenue projections
- EBITDA and EBITDA margin
- Depreciation & amortization (D&A)
- NOPAT (tax-adjusted operating profit)
- CapEx requirements
- Changes in net working capital
- Unlevered free cash flow (FCF)
Section 3: Terminal Value
- Perpetuity growth method: FCF × (1 + g) / (WACC − g)
- Or multiple method: Terminal EBITDA × Exit Multiple
Section 4: Valuation
- Sum of PV(FCFs Years 1–5) + PV(Terminal Value)
- Enterprise Value
- Less: Net Debt (Debt − Cash)
- Equals: Equity Value
- Divided by: Shares Outstanding
- Equals: Intrinsic Value per Share
Section 5: Sensitivity & Scenario Analysis
- One-way sensitivity tables (WACC, terminal growth, revenue growth)
- Two-way sensitivity tables
- Scenario comparison (pessimistic, base, optimistic)
Section 1: Input assumptions
Start with a dedicated "Inputs" or "Assumptions" worksheet. Document everything a user needs to modify:
HISTORICAL FINANCIALS & RATIOS
Year 0 (Base Year): 2024 Actual
Revenue: $500M
EBITDA Margin: 22%
EBITDA: $110M
Tax Rate: 25%
CapEx % of Revenue: 3%
NWC % of Revenue: 8%
GROWTH & MARGIN ASSUMPTIONS
Revenue CAGR (Years 1–5): 8%
Margin Expansion Path:
Year 1 EBITDA Margin: 22%
Year 5 EBITDA Margin: 24%
Terminal Revenue Growth: 2.5%
Terminal EBITDA Margin: 24%
CAPITAL INTENSITY
CapEx as % of Revenue: 3%
D&A as % of Revenue: 2%
NWC as % of Revenue: 8%
Tax Rate: 25%
WACC & DISCOUNT RATE
Risk-Free Rate: 3.0%
Equity Risk Premium: 5.5%
Beta: 1.0
Cost of Equity: 8.5%
Cost of Debt (post-tax): 3.5%
Weight of Equity: 75%
Weight of Debt: 25%
WACC: 7.2%
BALANCE SHEET ADJUSTMENTS
Total Debt: $100M
Cash & Equivalents: $50M
Net Debt: $50M
Shares Outstanding: 50M
Use data validation (dropdowns for growth scenarios, ranges for percentages) to guide input entry and prevent errors.
Section 2: Explicit forecast period (Years 1–5)
Build revenue projections first, then cascade margins and investments through to free cash flow:
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
(Actual) (Forecast)
Revenue ($M) 500.0 540.0 583.0 630.0 680.4 735.0
Revenue Growth % 8.0% 8.0% 8.0% 8.0% 8.0%
EBITDA Margin % 22.0% 22.5% 23.0% 23.5% 24.0% 24.0%
EBITDA ($M) 110.0 121.5 134.1 148.1 163.3 176.4
D&A ($M) 10.0 10.8 11.7 12.6 13.6 14.7
EBIT ($M) 100.0 110.7 122.4 135.5 149.7 161.7
Tax Rate % 25.0% 25.0% 25.0% 25.0% 25.0% 25.0%
NOPAT ($M) 75.0 83.0 91.8 101.6 112.3 121.3
Add: D&A ($M) 10.0 10.8 11.7 12.6 13.6 14.7
Less: CapEx ($M) 15.0 16.2 17.5 18.9 20.4 22.1
Less: Δ NWC ($M) 0.0 3.2 3.5 3.8 4.0 4.4
Unlevered FCF ($M) 70.0 74.4 82.5 91.5 101.5 109.5
Revenue growth: Start with historical CAGR. For a company growing 8–10% historically, forecasting 8% for years 1–5 is reasonable. As the company matures, margin expands due to operational leverage and scale.
EBITDA margin: Typically improves gradually as the company scales. A 2-year path to normalized margins (Year 1: 22.5%, Year 5: 24%) is more credible than jumping immediately to 24%.
D&A: Project as a percentage of prior-year revenue. If historical D&A is 2% of revenue, use 2% forward. This captures the depreciation of accumulated prior investments.
CapEx: Project as percentage of revenue. Maintenance CapEx (to replace depreciation) typically equals D&A. Growth CapEx (to fund expansion) is incremental. A company growing 8% annually may need CapEx of 3% of revenue (1% replacement CapEx + 2% growth CapEx).
Net working capital: Changes in NWC represent cash tied up in accounts receivable, inventory, and other current assets, less current liabilities. If NWC is 8% of revenue and revenue grows, NWC increases by 8% of the revenue growth. Example: If revenue grows $50M and NWC is 8% of revenue, NWC increases by $4M, a cash outflow.
Unlevered FCF: The cash available to all investors (debt and equity holders), after taxes and reinvestment:
FCF = NOPAT + D&A − CapEx − Δ NWC
= $83.0M + $10.8M − $16.2M − $3.2M
= $74.4M (Year 1)
Section 3: Terminal value
Terminal value represents the company's value in Year 6 and beyond, typically the largest component of enterprise value. Two methods:
Perpetuity Growth Method (most common):
Terminal Year (Year 5) Free Cash Flow: $109.5M
Terminal Growth Rate: 2.5%
WACC: 7.2%
Terminal Value = FCF_Year5 × (1 + g) / (WACC − g)
= $109.5M × 1.025 / (0.072 − 0.025)
= $109.5M × 1.025 / 0.047
= $112.2M / 0.047
= $2,387M
This formula assumes the company grows at a constant rate (2.5%) in perpetuity after Year 5. WACC must exceed the perpetuity growth rate; otherwise, the denominator is negative or zero (nonsensical). The formula is sensitive to both inputs: a 0.5% higher growth rate or 0.5% lower WACC significantly increases terminal value.
Exit Multiple Method (useful when comparable multiples are available):
Terminal Year (Year 5) EBITDA: $176.4M
Exit EV/EBITDA Multiple: 10x
Terminal Enterprise Value = $176.4M × 10x = $1,764M
Use exit multiples if trading multiples for comparable companies are stable and reliable. If the industry average EV/EBITDA is 9–11x, an exit multiple of 10x is defensible.
Choosing between methods: Perpetuity growth is theoretically cleaner but sensitive to terminal growth assumptions (±0.5% creates ±10% valuation swings). Exit multiples ground terminal value in market comparables but assume multiples remain stable. Use both and reconcile; if they diverge significantly, investigate the source of disagreement.
Section 4: Valuation build to per-share value
Discount all future cash flows to present value, combine with terminal value, and convert to per-share terms:
DISCOUNT RATE (WACC): 7.2%
Year 1 FCF: $74.4M PV = $74.4M / 1.072^1 = $69.4M
Year 2 FCF: $82.5M PV = $82.5M / 1.072^2 = $71.7M
Year 3 FCF: $91.5M PV = $91.5M / 1.072^3 = $74.5M
Year 4 FCF: $101.5M PV = $101.5M / 1.072^4 = $77.1M
Year 5 FCF: $109.5M PV = $109.5M / 1.072^5 = $76.2M
Sum of PV(FCF): $368.9M
Terminal Value: $2,387M PV = $2,387M / 1.072^5 = $1,665M
Enterprise Value (EV): $368.9M + $1,665M = $2,033.9M
BALANCE SHEET BRIDGE
Enterprise Value: $2,033.9M
Less: Total Debt: $100.0M
Plus: Cash & Equivalents: $50.0M
Less: Net Debt: $50.0M
Equity Value: $1,983.9M
Shares Outstanding: 50M
Intrinsic Value per Share: $39.68
Sanity checks:
- Does intrinsic value exceed Year 1 EV? Yes ($39.68 > $39.48 approximate Year 1 EV of $2,035M ÷ 50M = $40.7M). This makes sense; intrinsic value is the sum of all future years.
- What percentage of value comes from terminal value? $1,665M ÷ $2,033.9M = 82%. High but reasonable for a stable, growing company.
- Does the valuation seem reasonable relative to multiples? EV/EBITDA ≈ $2,034M ÷ $110M base-year EBITDA = 18.5x. Is this in line with peer multiples? If peers trade at 12–15x, investigate why your valuation implies 18.5x.
Section 5: Sensitivity and scenario tables
Once the base DCF is complete, test robustness through sensitivity and scenario analysis.
One-way sensitivity: Create tables testing WACC, terminal growth, and revenue growth individually:
WACC Sensitivity (holding terminal growth at 2.5%, revenue growth at 8%)
WACC Intrinsic Value per Share
6.5% $47.20
7.0% $43.85
7.2% $39.68 (Base)
8.0% $35.10
8.5% $31.95
Terminal Growth Sensitivity (holding WACC at 7.2%, revenue growth at 8%)
Terminal Growth Intrinsic Value per Share
1.5% $32.40
2.0% $35.50
2.5% $39.68 (Base)
3.0% $44.95
3.5% $51.20
Two-way sensitivity: Create a matrix for WACC vs. terminal growth:
Intrinsic Value per Share
Terminal Growth → 6.5% 7.0% 7.2% 8.0% 8.5%
1.5% $38.20 $35.00 $32.40 $28.50 $25.95
2.0% $43.50 $39.55 $35.50 $30.20 $26.85
2.5% $50.10 $44.95 $39.68 $32.40 $27.95
3.0% $58.80 $51.20 $44.95 $35.10 $29.20
3.5% $69.90 $59.40 $51.20 $38.10 $30.85
Scenario comparison: Build pessimistic, base, and optimistic scenarios with consistent logic:
Scenario Comparison
Pessimistic Base Case Optimistic
Revenue Growth 4% 8% 12%
Year 5 Margin 22% 24% 26%
WACC 8.5% 7.2% 6.5%
Terminal Growth 1.5% 2.5% 3.5%
Intrinsic Value $25.95 $39.68 $58.80
Upside/Downside -35% Base +48%
Real-world example: Tech SaaS company DCF
Value a cloud software company with the following characteristics:
- Base year (2024) revenue: $200M
- Historical revenue CAGR: 25% (strong growth)
- Current gross margin: 85% (SaaS-typical)
- Operating margin: 18% (path to 25% profitability)
- WACC: 7.8% (growth stock, higher risk than the prior industrial example)
Assumptions:
- Revenue growth: 20% Years 1–2, 15% Years 3–4, 10% Year 5, then 3% terminal
- Operating margin: 18% Year 1 → 25% Year 5
- CapEx as % of revenue: 2% (software = low capital intensity)
- NWC as % of revenue: 5% (strong cash collections)
- Tax rate: 15% (R&D tax credits)
DCF build:
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Revenue ($M) 200.0 240.0 288.0 331.2 379.8 417.8
Operating Margin % 18.0% 19.0% 21.0% 23.0% 24.0% 25.0%
Operating Income 36.0 45.6 60.5 76.2 91.2 104.5
NOPAT (15% tax) 30.6 38.8 51.4 64.8 77.5 88.8
Plus: D&A (2% revenue) 4.0 4.8 5.8 6.6 7.6 8.4
Less: CapEx (2%) 4.0 4.8 5.8 6.6 7.6 8.4
Less: Δ NWC (5%) 0 2.0 2.4 2.2 2.4 1.9
Unlevered FCF 30.6 36.8 49.0 62.0 75.1 86.9
Terminal Value: $86.9M × 1.03 / (0.078 − 0.03) = $1,823M
PV(Terminal Value): $1,823M / 1.078^5 = $1,269M
PV of Years 1–5 FCF: $36.8M + $49M + $62M + $75.1M + $86.9M discounted = $225M
(More precisely: $34.1M + $42.1M + $50.3M + $57.1M + $58.8M = $242.4M)
Enterprise Value: $242.4M + $1,269M = $1,511.4M
Net Debt: $200M (assume high debt for growth funding)
Equity Value: $1,311.4M
Shares Outstanding: 40M
Intrinsic Value: $32.79 per share
If the stock trades at $35, it appears fairly valued. A 15% revenue growth assumption (vs. your 20%) would reduce intrinsic value to ~$28 per share (intrinsic negative at current price), highlighting the sensitivity to growth assumptions for high-growth companies.
Common mistakes
Mistake 1: Unrealistic margin progression Projecting a company's operating margin from 5% today to 30% in Year 5 assumes a transformation rarely achieved. Margin expansion happens gradually (1–2 percentage points annually) and requires execution on multiple fronts (scale, pricing, cost control). Be conservative; if you project 10+ point margin expansion, justify it with specific operational improvements.
Mistake 2: Terminal value assumptions divorced from long-term fundamentals If you project 8% terminal growth but GDP growth is 2.5%, the company assumes perpetual outperformance. More credible: terminal growth near GDP (2–3%) for mature companies, or one point above GDP for companies with persistent competitive advantages.
Mistake 3: Forgetting to discount terminal value Terminal value is calculated in Year 5 but represents value at that future date. Discount it to present value using (1 + WACC)^5. Failure to discount overstates intrinsic value by 40–50%.
Mistake 4: Ignoring reinvestment requirements If you project strong earnings growth but low CapEx, FCF appears inflated. A company growing 20% annually typically requires CapEx of 5–7% of revenue (higher than stable-state 2–3%). Underestimating CapEx overstates FCF and intrinsic value.
Mistake 5: Using an inconsistent WACC If your cost of equity is 9% but you use 7% as your discount rate (WACC), something's inconsistent. Reconcile WACC with cost of equity via the WACC formula: WACC = (E/V) × Re + (D/V) × Rd(1 − Tc). If inputs are consistent, your WACC should be defensible.
FAQ
Q: How many years should my explicit forecast period be? A: Five years is standard for most companies. Mature, stable companies may use 3–5 years. Early-stage, high-growth companies may use 7–10 years if you can credibly forecast that far. Longer periods reduce reliance on terminal value but increase forecast uncertainty.
Q: What if I project negative FCF in some years (high growth + heavy CapEx)? A: That's realistic for growth companies. In Year 1, if a company invests heavily in CapEx to support future growth, FCF can be negative. The DCF still works; you're discounting negative cash flows to present value (reducing value), which is appropriate.
Q: Should I use levered or unlevered FCF? A: Use unlevered FCF for the base DCF. Unlevered FCF is financing-independent, isolating operating performance. If capital structure is highly leveraged or changing significantly, consider a levered DCF as a separate check, but unlevered is the standard starting point.
Q: How sensitive is my valuation to terminal growth rate? A: Very sensitive. Each 0.5% change in terminal growth rate typically changes intrinsic value by 8–12%. This is why sensitivity analysis on terminal growth is critical—terminal value often represents 70–80% of enterprise value.
Q: What WACC should I use for a private company with no public debt? A: Estimate WACC as though the company has a target capital structure (e.g., 70% equity, 30% debt). Use cost of equity (CAPM or build-up method) and a market-based cost of debt (peer companies' rates or estimate based on risk). Private company WACC is typically 1–3% higher than public peer companies due to illiquidity and size risk.
Q: Should I adjust FCF for stock-based compensation? A: Yes. Stock-based compensation is a real economic cost. If it's already expensed (reducing net income), it's already captured in NOPAT. But many analysts add it back because it's non-cash. Be consistent: either include it as an expense throughout, or add it back consistently. Avoid double-counting.
Related concepts
- WACC calculation is foundational to DCF. See Chapter 12 for cost of capital estimation.
- Terminal value methods (perpetuity growth vs. exit multiples) both deserve testing; reconcile them to validate terminal assumptions.
- Comparable company multiples (P/E, EV/EBITDA) provide external validation of DCF value; if your DCF intrinsic value implies 30x EV/EBITDA but peers trade at 12x, investigate the disconnect.
- Asset-based valuation and precedent transactions are alternative methods; use them as triangulation against your DCF.
- Scenario weighting (assigning probabilities to pessimistic, base, optimistic scenarios) converts a DCF range into an expected value.
Summary
A complete DCF template combines assumptions, explicit forecasts, terminal value, and validation checks into a cohesive model. Start with conservative revenue growth, project margin progression gradually, account for all reinvestment needs (CapEx and working capital), and validate terminal value using both perpetuity growth and exit multiple methods. Test sensitivity to WACC and terminal growth (the highest-impact assumptions), and run scenario analysis to show the valuation range. This structured approach produces a defensible intrinsic value estimate.