How to Build a DCF Valuation Model
Master the art of company valuation using discounted cash flow analysis. Learn to forecast cash flows, calculate WACC, determine terminal value, and arrive at an intrinsic value estimate.
The Discounted Cash Flow (DCF) model is the gold standard for intrinsic valuation in corporate finance. It estimates a company's value by forecasting future free cash flows and discounting them to present value using the Weighted Average Cost of Capital (WACC). While conceptually straightforward, building an accurate DCF requires careful attention to assumptions, proper cash flow calculation, and understanding common pitfalls. A good DCF model can take days to build manually — between gathering data, calculating WACC components, building supporting schedules, and running sensitivity analyses. This guide walks you through the entire process step-by-step, explaining the theory behind each component and showing where you might use spreadsheet tools or AI to speed up the mechanical parts while you focus on the analytical judgment.
What You'll Need
- Strong understanding of financial statements (Income Statement, Balance Sheet, Cash Flow Statement)
- Knowledge of corporate finance concepts (WACC, cost of equity, cost of debt, beta)
- Understanding of time value of money and present value calculations
- Google Sheets or Excel proficiency, including NPV and financial functions
- Access to company financial data (10-K filings, annual reports, or financial databases)
- Optional: AI assistant like ModelMonkey for formula creation and data analysis
Step-by-Step Guide
Gather Historical Financial Data and Industry Context
Collect 5-10 years of historical financials and understand the company's industry dynamics.
- Download the company's last 5-10 years of annual reports (10-K filings from SEC EDGAR or company investor relations)
- Extract historical Income Statements, Balance Sheets, and Cash Flow Statements
- Research industry trends: Is this a mature, stable industry or high-growth? What's the typical revenue growth rate for competitors?
- Identify the company's competitive position: market share, competitive advantages, risks
- Note any major one-time events in historical data (acquisitions, restructurings, impairments) that might skew trends
- Calculate historical free cash flow for each year to establish baseline trends
- If using AI: Ask it to "analyze the last 5 years of financial data and calculate historical revenue growth rates, operating margins, capital expenditure as % of revenue, and free cash flow"
Pro Tip
Your DCF is only as good as your assumptions. Spend time understanding the business model and industry before making projections. Read management commentary in the 10-K about future strategy and risks.
Set Up Your Model Structure
Create a clean, organized spreadsheet structure following financial modeling best practices.
- Create separate tabs or sections: (1) Assumptions, (2) Historical Financials, (3) Projections, (4) WACC Calculation, (5) DCF Valuation, (6) Sensitivity Analysis
- Use consistent formatting: blue font for inputs, black for formulas, bold for section headers
- In the Assumptions tab, create clearly labeled inputs for: forecast period (typically 5-10 years), revenue growth rates, operating margins, tax rate, CapEx assumptions, working capital assumptions, terminal growth rate, WACC components
- Set up column headers for your explicit forecast period (e.g., Year 1 through Year 10)
- Add a "Checks and Balances" section to validate calculations
- If using AI: Ask it to "create a DCF model template with sections for assumptions, projections, WACC calculation, and valuation output"
Pro Tip
Keep all assumptions in one place at the top of your model. This makes it easy to run scenarios by changing inputs without hunting through formulas.
Build Revenue and Operating Projections
Forecast the Income Statement items needed to calculate operating cash flow.
- Start with revenue projections: Use historical growth rates, management guidance, industry analysis, and your own judgment. Common approach: higher growth in early years tapering to industry growth rate
- Project COGS (Cost of Goods Sold) as a percentage of revenue based on historical gross margins
- Project Operating Expenses (SG&A, R&D) as percentages of revenue or using absolute dollar growth
- Calculate EBIT (Earnings Before Interest and Tax) = Revenue - COGS - Operating Expenses
- Apply the tax rate to EBIT to get taxes on operating income: Tax = EBIT × Tax Rate (typically 21-25% in the US)
- Calculate NOPAT (Net Operating Profit After Tax) = EBIT × (1 - Tax Rate). This is the starting point for free cash flow
- If using AI: Ask it to "project the income statement for 10 years using 15% revenue growth in years 1-3, 10% in years 4-6, and 5% in years 7-10, with gross margin of 60% and operating expenses at 35% of revenue"
Pro Tip
Be conservative with growth rates. A common mistake is over-optimistic projections. Cross-check your assumptions against industry peers and historical performance.
Calculate Unlevered Free Cash Flow (UFCF)
Convert accounting earnings into cash flow available to all investors.
- Start with NOPAT (from Step 3)
- Add back Depreciation & Amortization (D&A): these are non-cash expenses, so add them back to get cash flow
- Subtract Capital Expenditures (CapEx): cash spent on PP&E. Use historical CapEx as % of revenue as a guide, or use Depreciation as a proxy
- Subtract Increase in Net Working Capital (NWC): Calculate NWC = (Accounts Receivable + Inventory) - Accounts Payable. If NWC increases year-over-year, it's a cash outflow; if it decreases, it's a cash inflow
- Formula: Unlevered Free Cash Flow = NOPAT + D&A - CapEx - Increase in NWC
- Validate: Free cash flow should generally be positive in mature companies. Negative FCF in growth companies might be okay if they're investing heavily
- If using AI: Ask it to "calculate unlevered free cash flow for each projection year starting with NOPAT of $X million, adding back depreciation, subtracting CapEx at Y% of revenue, and accounting for working capital changes"
Pro Tip
Don't confuse cash flow with earnings. The biggest adjustments are: (1) add back D&A, (2) subtract actual CapEx, (3) account for working capital timing differences.
Calculate the Weighted Average Cost of Capital (WACC)
Determine the discount rate that reflects the company's risk and capital structure.
- Cost of Equity (Re): Use the Capital Asset Pricing Model (CAPM): Re = Risk-Free Rate + Beta × Market Risk Premium. Risk-free rate = 10-year Treasury yield (~4-5% currently). Beta = measure of stock volatility vs. market (find on Yahoo Finance, Bloomberg). Market Risk Premium = historical equity return premium over bonds (~6-8%)
- Cost of Debt (Rd): Use the company's effective interest rate = Interest Expense ÷ Total Debt from recent financials. Alternatively, use the yield on the company's bonds
- Market Value of Equity (E): Current stock price × shares outstanding
- Market Value of Debt (D): Use book value of debt from the balance sheet as an approximation
- Calculate weights: Weight of Equity (We) = E ÷ (E + D), Weight of Debt (Wd) = D ÷ (E + D)
- WACC Formula: WACC = (We × Re) + (Wd × Rd × (1 - Tax Rate)). The (1 - Tax Rate) accounts for the tax shield on debt
- Typical WACC range: 8-12% for stable companies, higher for riskier/growth companies
- If using AI: Ask it to "calculate WACC given a risk-free rate of 4.5%, beta of 1.2, market risk premium of 7%, cost of debt of 5%, tax rate of 25%, with equity value of $10B and debt of $2B"
Pro Tip
WACC is one of the most critical (and debated) inputs. Small changes in WACC can dramatically affect valuation. Use market-based data (not book values) for the capital structure weights.
Calculate Terminal Value
Estimate the company's value beyond the explicit forecast period.
- Terminal value typically represents 60-80% of total DCF value, so this step is critical
- Method 1 - Perpetuity Growth Method (most common): Terminal Value = (Final Year FCF × (1 + g)) ÷ (WACC - g), where g = perpetual growth rate (typically 2-3%, should not exceed GDP growth)
- Method 2 - Exit Multiple Method: Terminal Value = Final Year EBITDA × Exit Multiple (use industry average EV/EBITDA multiple)
- Best practice: Calculate both methods and use the average or the more conservative estimate
- Common mistake: Using a terminal growth rate higher than long-term GDP growth. Be conservative — even great companies can't grow faster than the economy forever
- If using AI: Ask it to "calculate terminal value using both the perpetuity growth method with 2.5% growth and the exit multiple method using 12x EBITDA, then show me both results"
Pro Tip
The perpetuity growth rate (g) must be less than WACC, or the formula breaks. And it must be realistic — no company grows at 5%+ forever. Use 2-3% to be safe.
Discount Cash Flows to Present Value
Calculate the present value of projected free cash flows and terminal value.
- For each year's free cash flow, calculate: PV = FCF ÷ (1 + WACC)^n, where n = year number (1, 2, 3, etc.)
- In Google Sheets/Excel, use the NPV function or manually calculate using the formula above
- Discount the Terminal Value: PV of Terminal Value = Terminal Value ÷ (1 + WACC)^N, where N = final year of projection
- Sum all discounted cash flows: Enterprise Value = PV(Year 1 FCF) + PV(Year 2 FCF) + ... + PV(Terminal Value)
- Best practice: Use the XNPV function if you have specific dates for cash flows (handles mid-year discounting)
- If using AI: Ask it to "discount the projected free cash flows to present value using a WACC of 10% and calculate the sum of present values"
Pro Tip
Convention: assume cash flows occur at year-end. For more precision, use mid-year discounting (discount by n-0.5 instead of n) since cash flows actually occur throughout the year.
Calculate Equity Value and Price Per Share
Bridge from Enterprise Value to Equity Value and derive the intrinsic share price.
- Start with Enterprise Value (sum of discounted cash flows from Step 7)
- Add: Cash and Cash Equivalents (from the most recent balance sheet)
- Add: Market value of non-operating assets (investments, excess real estate, etc.)
- Subtract: Total Debt (short-term + long-term debt from balance sheet)
- Subtract: Preferred Stock (if any)
- Subtract: Minority Interest (if any)
- Result = Equity Value (value belonging to common shareholders)
- Divide Equity Value by Diluted Shares Outstanding to get Intrinsic Value Per Share
- Compare to Current Stock Price: If intrinsic value > current price, stock may be undervalued. If intrinsic value < current price, may be overvalued
- If using AI: Ask it to "calculate equity value starting with enterprise value of $X, adding cash of $Y, and subtracting debt of $Z, then divide by shares outstanding to get price per share"
Pro Tip
Use diluted shares outstanding (includes stock options and convertible securities) not basic shares. The number is in the company's 10-K.
Run Sensitivity Analysis
Test how changes in key assumptions affect your valuation.
- Identify the 2-3 most critical assumptions: typically WACC, terminal growth rate, and revenue growth
- Create a sensitivity table (data table in Excel/Sheets) showing valuation across different scenarios
- Example: Create a two-way table with WACC on one axis (8%, 10%, 12%) and terminal growth on the other (2%, 2.5%, 3%)
- Look at the range of outcomes: How much does valuation change? This shows your margin of error
- Best practice: Also run scenario analysis (Bull Case, Base Case, Bear Case) with different combinations of assumptions
- If using AI: Ask it to "create a sensitivity table showing how equity value per share changes when WACC varies from 8% to 12% and terminal growth rate varies from 2% to 3%"
Pro Tip
A DCF gives you a point estimate, but reality is a range. Sensitivity analysis shows you how confident you should be in your valuation. Wide ranges mean high uncertainty.
Validate and Sanity-Check Your Model
Review your model for errors and ensure the output makes sense.
- Check: Does your balance sheet balance in all projection years?
- Check: Are free cash flows reasonable compared to historical FCF and net income?
- Check: Is WACC within a reasonable range for the industry? (Compare to peers)
- Check: Does the implied valuation make sense compared to the company's market cap and peer multiples?
- Common errors to avoid: (1) Including historical cash flows in projections, (2) Using cost of equity instead of WACC to discount, (3) Forgetting to discount terminal value, (4) Using terminal growth rate ≥ WACC, (5) Depreciation > CapEx in perpetuity
- Compare DCF result to other valuation methods (comparable companies, precedent transactions) — they should be in the same ballpark
- If using AI: Ask it to "review my DCF model and identify potential errors, inconsistencies, or assumptions that seem unrealistic compared to industry norms"
Pro Tip
Never trust a DCF blindly. If your DCF says a stock is worth 3x its current price, you're probably wrong, not the market. Revisit your assumptions with skepticism.
