Discounted Cash Flow (DCF) analysis is the gold standard for valuing companies — but most retail investors skip it because of the math.
I've been using a simple Google Sheets DCF model for years, and today I'll show you exactly how to set one up. By the end, you'll have a working template that values any publicly traded company in 10 minutes.
What Is DCF (In Plain English)?
A DCF model answers: "If I buy this stock today, how much are its future cash flows worth right now?"
The core formula:
Intrinsic Value = Σ (FCF_t / (1 + r)^t) + Terminal Value
Where:
- FCF_t = Free Cash Flow in year t
- r = Discount Rate (your required return)
- t = Year (1 through 5-10 years)
Step 1: Gather Your Inputs
You'll need five numbers from any company's financial statements:
- Revenue (latest year)
- Revenue Growth Rate (3-5 year CAGR)
- Operating Margin (EBIT / Revenue)
- Tax Rate (usually 21-25% for US companies)
- Capital Expenditures as % of Revenue
- Discount Rate (use 8-12% depending on risk)
Step 2: Build the Model in Google Sheets
Set up these columns in a new sheet:
| A | B | C | D | E |
|---|---|---|---|---|
| Year | Revenue | EBIT | Tax | FCF |
Cell formulas:
- B2 = C2 * (1 + D2) [where C2 = current revenue, D2 = growth rate]
- C3 = B3 * Operating_Margin
- D4 = C3 * Tax_Rate
- E5 = C3 - D4 - CapEx
Drag down for 5 years. Your terminal value formula:
= E5 * (1 + 0.03) / (Discount_Rate - 0.03) — assuming 3% perpetual growth.
Step 3: Discount Everything to Present Value
For each FCF and terminal value, divide by (1 + Discount_Rate)^Year.
Sum them all up — that's your Enterprise Value. Subtract net debt, divide by shares outstanding → Intrinsic Value Per Share.
Practical Example: A Mature Tech Company
Using real-ish numbers for a stable SaaS company:
- Revenue: $2B, growing 12%
- Operating Margin: 25%
- Tax Rate: 21%
- CapEx: 5% of revenue
- Discount Rate: 10%
My model spits out an intrinsic value of $48.50/share. If the stock trades at $38, there's a 28% margin of safety.
Where Most DCF Models Fail
❌ Perpetuity growth rate too high — 3% is risky enough
❌ Ignoring stock-based compensation — it's a real cost
❌ Using GAAP net income instead of FCF — earnings lie, cash doesn't
Free vs. Paid Tools
A basic DCF is easy to build yourself. But I've automated the entire process in a pre-built Google Sheets template that:
- Auto-pulls data from 3 statements
- Handles terminal value calculations
- Includes sensitivity tables
- Works for 500+ public companies
You can grab it here — I price it at $29 because it saves 2+ hours per analysis, and the sensitivity table alone is worth the price.
Last tip: Use DCF as a check, not a prediction. If the market price is 30% below your fair value range (after conservative assumptions), you've found a margin of safety.
Happy investing!
P.S. — I run MicroTools Studio, building Google Sheets tools for retail investors who want professional-grade analysis without the Bloomberg terminal price tag.
Top comments (0)