How to Build a Complete DCF Model from Scratch
Theory is important, but application is where learning crystallizes. You've now encountered the principles of DCF, the mechanics of free cash flow calculation, the sources of error, and the data quality challenges that derail analysis. Now comes the practical work: building an actual DCF model.
This article walks through a complete, concrete example. You'll see the exact structure of a DCF spreadsheet, the logic flow, where to plug in assumptions, how to organize calculations, and how the pieces connect. By the end, you'll have a template you can adapt to any company. The goal is to demystify DCF—to show that despite its mathematical trappings, a basic model is straightforward, and that complexity usually adds little value to the core analysis.
This is not a theoretical walkthrough. Every section is tied to specific inputs and calculations you'll actually perform. Follow along, and you'll have a working model you can use for real investment decisions.
Quick definition: A simple DCF model is a systematic spreadsheet that projects a company's free cash flows over an explicit forecast period, discounts them to present value, adds terminal value, and converts to a per-share intrinsic value—integrating assumptions about growth, margins, capital efficiency, and risk into a single valuation framework.
Key Takeaways
- A basic DCF model needs only five core sections: historical data, explicit forecast period (typically 5–10 years), terminal value, discount rate calculation, and final valuation output
- The forecast period should assume declining growth rates, approaching a normalized perpetual growth rate by year 5 or 10
- Terminal value typically represents 60–80% of total intrinsic value, so its calculation deserves special attention and conservative assumptions
- A working DCF model should include sensitivity tables showing how valuation changes with different discount rates and terminal growth rates
- The model structure should clearly separate assumptions from calculations, making it easy to audit, update, and understand the logic
- Converting enterprise value to equity value per share requires careful adjustment for net debt and share dilution
The Five Core Sections of a DCF Model
A complete DCF model has five interconnected sections:
- Assumptions Panel: Key inputs that drive the model (revenue growth, margins, CapEx, discount rate, terminal growth)
- Historical Data: Past performance used to baseline projections
- Forecast Period (5–10 years): Detailed year-by-year cash flow projections
- Terminal Value: Value of all cash flows beyond the explicit projection period
- Valuation Output: Present value of forecasts plus terminal value, converted to per-share intrinsic value
Let's build each section concretely.
Section 1: The Assumptions Panel
Create a dedicated area in your spreadsheet for assumptions. This serves two purposes: it makes your drivers explicit (no buried assumptions in formulas), and it allows quick sensitivity testing.
Here's the structure:
ASSUMPTIONS PANEL
================================================
REVENUE & GROWTH
Historical Revenue (latest FY) $2,500M
Revenue Growth Year 1-2 12%
Revenue Growth Year 3-5 8%
Revenue Growth Year 6-10 4%
Terminal Growth Rate (perpetual) 2.5%
MARGINS & PROFITABILITY
EBITDA Margin (normalized) 22%
D&A as % of Revenue 3%
Tax Rate (normalized) 21%
CapEx as % of Revenue 6%
Working Capital Change Rate 1% of revenue growth
DISCOUNT RATE
Risk-free Rate 4.5%
Equity Market Risk Premium 5.5%
Company Beta 1.2
Cost of Equity 11.1%
Cost of Debt (after-tax) 3.5%
Debt % of Capital 30%
WACC 8.4%
BALANCE SHEET (Current)
Net Debt (Total Debt - Cash) $800M
Shares Outstanding (diluted) 120M
Notice that many line items are calculated from inputs (Cost of Equity, WACC). These formulas are part of the model, but the panel clearly shows what's what.
Section 2: Building the Historical Data Baseline
Before projecting forward, document recent financial performance. This provides context and helps you spot whether your projections are realistic.
HISTORICAL FINANCIALS (Last 5 Years)
================================================
FY2020 FY2021 FY2022 FY2023 FY2024
Revenue (M) $1,800 $2,000 $2,200 $2,350 $2,500
EBITDA (M) $352 $430 $506 $550 $550
D&A (M) $54 $60 $66 $73 $75
EBIT (M) $298 $370 $440 $477 $475
Tax (21%) $63 $78 $92 $100 $100
NOPAT (M) $235 $292 $348 $377 $375
Add: D&A $54 $60 $66 $73 $75
Less: CapEx $120 $130 $144 $160 $150
Less: WC Change $5 $10 $12 $10 $8
FCF (M) $164 $212 $258 $280 $292
FCF Margin 9.1% 10.6% 11.7% 11.9% 11.7%
Observation: Revenue is growing 6–10% annually. EBITDA margin is around 22%, and FCF margin has stabilized around 11–12% after a steady ramp. This gives you a baseline: your forward projections should be consistent with these historical trends, not wildly different.
Section 3: The Explicit Forecast Period (Years 1–10)
Now project forward. The structure is mechanical: apply growth and margin assumptions to calculate revenue, apply margins to get EBITDA, subtract D&A and taxes to get NOPAT, add back D&A, subtract CapEx and working capital changes to get FCF.
Here's years 1–5 (year 10 follows the same pattern):
EXPLICIT FORECAST PERIOD (10 Years)
================================================
2025E 2026E 2027E 2028E 2029E
Growth Rate 12% 12% 8% 8% 8%
Revenue (M) $2,800 $3,136 $3,387 $3,658 $3,950
EBITDA Margin 22% 22% 22% 22% 22%
EBITDA (M) $616 $690 $745 $804 $869
D&A (%) 3.0% 3.0% 3.0% 3.0% 3.0%
D&A (M) $84 $94 $102 $110 $119
EBIT (M) $532 $596 $643 $694 $750
Tax (21%) $112 $125 $135 $146 $158
NOPAT (M) $420 $471 $508 $548 $592
Add: D&A $84 $94 $102 $110 $119
Less: CapEx (6%) $168 $188 $203 $219 $237
Less: WC Change $8 $11 $3 $9 $12
FCF (M) $328 $366 $404 $440 $462
Discount Factor 0.922 0.850 0.783 0.722 0.665
PV of FCF (M) $302 $311 $316 $318 $307
Key mechanics:
- Revenue grows at assumed rates
- Margins are held constant (conservative; you could model compression)
- D&A is calculated as 3% of revenue (based on historical ratio)
- NOPAT is EBIT × (1 - Tax Rate)
- FCF = NOPAT + D&A - CapEx - Change in NWC
- Discount factors = 1 / (1 + WACC)^year
Sum the PV of all 10 years of FCF: roughly $3,050M in this example.
Section 4: Terminal Value Calculation
Terminal value captures the value of all cash flows from year 11 onward. Use the perpetual growth formula:
TERMINAL VALUE
================================================
Year 10 FCF $462M
Terminal Growth 2.5% (perpetual)
WACC 8.4%
Terminal Value (at end of Year 10) = FCF Year 10 × (1 + Growth) / (WACC - Growth)
= $462M × 1.025 / (0.084 - 0.025)
= $473.5M / 0.059
= $8,025M
Discount to Today (PV of Terminal Value)
Discount Factor (Year 10) 0.465
PV of Terminal Value = $8,025M × 0.465
= $3,732M
Note: Terminal value ($3,732M) is larger than the PV of explicit forecasts ($3,050M), representing 55% of total value. This is reasonable for a 2.5% perpetual growth assumption. If terminal value exceeded 80% of total value, you'd question whether your explicit forecast assumptions are too conservative.
Section 5: From Enterprise Value to Equity Value
Enterprise value is the sum of PV of explicit forecasts and PV of terminal value. But enterprise value is what the entire company is worth, not what equity is worth. To get per-share intrinsic value:
VALUATION OUTPUT
================================================
PV of Forecast Period FCF (Yrs 1-10) $3,050M
PV of Terminal Value $3,732M
Enterprise Value $6,782M
Less: Net Debt ($800M)
Equity Value $5,982M
Shares Outstanding (diluted) 120M
Intrinsic Value per Share $49.85
Current Market Price (example) $45.00
Upside / (Downside) 10.8% / (9.8%)
The calculation is straightforward: Enterprise Value (worth of the entire company) minus Net Debt (what is owed to creditors, not available to equity holders) equals Equity Value. Divide by diluted shares to get per-share intrinsic value.
Section 6: Sensitivity Analysis
Your point estimate is $49.85. But how confident are you? Sensitivity analysis shows this.
Build a two-way table varying discount rate and terminal growth rate:
SENSITIVITY TABLE: Intrinsic Value per Share
Terminal Growth Rate →
Discount Rate ↓ 2.0% 2.5% 3.0% 3.5% 4.0%
7.4% (base - 1%) $63.2 $69.4 $77.8 $89.1 $105.2
8.4% (base) $49.8 $54.6 $60.8 $69.2 $81.5
9.4% (base + 1%) $41.2 $44.9 $49.9 $56.8 $66.9
10.4% (+2%) $34.8 $37.9 $41.8 $47.3 $55.1
11.4% (+3%) $29.9 $32.4 $35.6 $39.9 $46.0
Observations: At your base case (8.4% discount rate, 2.5% growth), intrinsic value is $54.60. But if discount rate rises to 9.4% (reflecting higher risk), value falls to $49.90. If terminal growth is only 2% instead of 2.5%, value falls to $49.80. This range—from $35 to $80 across reasonable assumption variations—is your true margin of uncertainty. The market price of $45 is near the lower end of the range, suggesting modest upside, but also significant downside risk if discount rates rise.
Building Your Own Model: Step-by-Step Instructions
Here's a checklist to build your first model:
Step 1: Gather Data
- Get the company's last 5 years of financial statements (10-K or annual report)
- Extract: Revenue, EBITDA or EBIT, D&A, Tax rate, CapEx, changes in working capital
- Calculate historical growth rates, margins, CapEx intensity, and FCF trends
Step 2: Set Up the Spreadsheet
- Create separate sections for Assumptions, Historical Data, Forecast Period, Terminal Value, and Output
- Use clear labels and organize vertically (years across columns, metrics down rows)
- Separate inputs (assumptions) from calculations (formulas)
Step 3: Build the Assumptions Panel
- Revenue growth: Look at historical growth, industry benchmarks, and management guidance. Be realistic; most companies eventually slow
- EBITDA margins: Use normalized historical margins as a baseline. Model compression or improvement only if justified
- CapEx and working capital: Calculate as % of revenue based on historical norms
- Discount rate: Calculate WACC from first principles or use 8–10% if you lack data
- Terminal growth: Use 2–3%, tied to long-term GDP growth. Only justify higher rates with specific analysis
Step 4: Build the Forecast Period
- Years 1–5: Use higher growth rates (12–15% if justified), but declining toward normalized rates
- Years 6–10: Transition to slower growth (4–6%), approaching terminal growth
- Margins: Be conservative. Model stable margins or slight compression rather than expansion
- Calculate FCF each year: NOPAT + D&A - CapEx - WC Change
Step 5: Calculate Terminal Value
- Use perpetual growth formula with conservative terminal growth (2–3%)
- Discount to present value using the same WACC
- Check that terminal value is 60–80% of total value; if much higher, question whether explicit forecasts are too conservative
Step 6: Convert to Per-Share Value
- Enterprise Value = PV of forecast + PV of terminal
- Equity Value = Enterprise Value - Net Debt
- Per-share Value = Equity Value / Diluted Shares
Step 7: Sensitivity Test
- Build a two-way table varying discount rate (±2%) and terminal growth (±1%) around your base case
- Use this range to assess confidence and risk
Real-World Example: A Concrete Walk-Through
Let's value a mid-cap software company, Acme Soft, trading at $60/share.
Historical performance: Revenue is $500M, growing at 15% last three years. EBITDA margins are 30%. CapEx is minimal (3% of revenue). Tax rate is 21%.
Assumptions:
- Revenue growth: 15% years 1–2, declining to 8% by year 5, 3% perpetual
- EBITDA margins: Hold at 30% (conservative; didn't model expansion)
- CapEx: 3% of revenue
- Working capital: Minimal changes, 0.5% of revenue growth
- Tax rate: 21%
- D&A: 1% of revenue (small software company)
- WACC: 9% (cost of equity ~11%, minimal debt)
Forward projections:
- Year 1 (FY2026): Revenue $575M (15% growth), EBITDA $173M, FCF ~ $145M
- Year 5 (FY2030): Revenue $856M, EBITDA $257M, FCF ~ $210M
- Year 10 (FY2035): Revenue ~$1,180M, EBITDA $354M, FCF ~$290M
Terminal value: Year 10 FCF of $290M, growing at 3%, WACC 9%: Terminal Value = $290M × 1.03 / (0.09 - 0.03) = $4,983M
PV of forecast period: ~$850M
PV of terminal value: ~$2,400M
Enterprise Value: ~$3,250M
Net Debt: $100M, so Equity Value = $3,150M
Diluted shares: 50M, so Intrinsic Value = $63/share
Market price is $60, so the stock is fairly valued with modest upside. Sensitivity analysis shows that if WACC is 10% (higher risk than assumed), intrinsic value drops to $51. If terminal growth is 2% instead of 3%, intrinsic value drops to $58. The range is $51–$68, so $60 is in the middle of reasonable outcomes. Not a compelling buy, but not expensive either.
Avoiding Over-Complexity
A tempting error is building an overly complex model with dozens of metrics, multi-year margin progression schedules, and elaborate working capital models. Resist this. Complexity creates the illusion of precision without improving accuracy. In fact, more moving parts usually mean more opportunities for error.
A good DCF model is:
- Understandable. You can explain every line to someone else and defend it
- Auditable. Someone else can follow your logic and spot errors
- Realistic. Assumptions reflect business fundamentals, not wishful thinking
- Parsimonious. It includes key drivers but avoids unnecessary detail
If you build a DCF with fifty line items and the output is $49.50, but a simpler DCF with ten line items produces $48.75, that doesn't mean the complex model is better. They're probably estimating the same underlying value within normal uncertainty bounds. The simpler model is more likely to be correct because there are fewer places for subtle errors to hide.
Common Mistakes to Avoid
Typing instead of linking. If you manually type a number in the forecast period instead of linking to assumptions, sensitivity testing breaks. If you change an assumption but forget to update all the places it affects, your model becomes internally inconsistent. Always use formulas and links.
Inconsistent tax treatment. If you calculate NOPAT using a 21% tax rate in one section and a 24% rate in another, your model has inconsistent logic. Be meticulous about consistency.
Forgetting inflation. If you project nominal cash flows (in future dollars), your assumptions should be nominal. If inflation rises, revenues might grow faster nominally but not in real terms. Make sure your growth and margin assumptions account for inflation consistently.
Using rounded numbers. $500M revenue, exactly 30% margins, exactly 5% growth. Real businesses have variation. Don't use suspiciously round assumptions—they suggest you haven't done the work.
Never stress-testing. A model with no sensitivity tables is a model you don't fully understand. Always build sensitivity tables.
Frequently Asked Questions
Q: Should I use a monthly, quarterly, or annual model? A: Annual is standard and sufficient for valuation. Quarterly adds complexity without usually improving accuracy. Monthly is rarely needed unless you're doing a near-term cash flow analysis (e.g., for lending decisions).
Q: How many years should I forecast explicitly? A: Five to ten years is standard. Five years is adequate for a mature company with stable growth. Ten years can be appropriate for a growing company that will transition significantly. Beyond ten years, assumptions become too speculative to be reliable. That's what terminal value captures.
Q: Can I use somebody else's DCF model and just change assumptions? A: You can learn from published models, but building your own from scratch is crucial. It forces you to understand the logic and think through each assumption. Using a template is fine; mindlessly tweaking someone else's model is not.
Q: What if my model produces a valuation very different from the market price? A: Don't immediately assume the market is wrong. First, audit your model for errors. Second, investigate: Has the market seen information you haven't? Is there a reason the market is priced higher/lower than your DCF? Or have you found a genuine mispricing? This tension is where real analysis happens.
Q: Should I DCF every company? A: No. DCF works for stable, profitable companies with predictable cash flows. It's less useful for startups, distressed companies, or businesses with highly uncertain cash flows. For those, other methods (comparables, precedent transactions, liquidation value) might be more appropriate.
Q: How do I update my model as the business develops? A: Rebuild it annually or when material information changes (earnings miss, new product launch, management change). Compare your historical projections to actual results. Did growth, margins, or CapEx differ? Learning why your predictions were wrong improves future analysis.
Related Concepts
- Free Cash Flow to Firm (FCFF) — Understanding and calculating the cash flows you project
- Terminal Value Explained — Deep dive on perpetual value calculation
- Weighted Average Cost of Capital (WACC) — How to calculate the discount rate
- Sensitivity Analysis for DCF — Stress-testing assumptions
- 10 Common DCF Mistakes — Errors to avoid in model construction
Summary
Building a DCF model is not complicated, but it is demanding. It requires gathering accurate data, forming realistic assumptions, organizing calculations clearly, and testing conclusions rigorously. The model itself—the spreadsheet and formulas—is relatively straightforward. A complete DCF requires five sections: assumptions, historical baseline, explicit forecast, terminal value, and output valuation.
The discipline of building a model forces you to think clearly about a business: How fast will it grow? What margins will it earn? How much capital will it require? How risky is it? These questions are harder than the mathematics. Anyone can build an Excel model. Far fewer can build assumptions that reflect business reality.
The template provided in this article is a starting point. Adapt it to different industries, business models, and time horizons. But the core structure—project cash flows, discount to present value, add terminal value, convert to per-share value—is universal. Master this framework, and you have a valuation tool applicable to any company.
Next: Depreciation & Amortization in DCF
The next article dives into a specific, often-misunderstood aspect of DCF: how to properly account for depreciation and amortization. These non-cash charges complicate cash flow calculations and are a frequent source of error.