Yes, you can build a DCF model in Excel. But if your terminal value is more than 70% of total equity value and you haven’t stress-tested the WACC input, your model is already misleading stakeholders.
Quick Answer
You do DCF in Excel by forecasting free cash flows (FCF), discounting them using WACC, calculating a terminal value (TV), and summing everything — but the real work happens in assumptions: revenue growth must align with industry benchmarks (e.g., SaaS companies rarely sustain >25% CAGR past Year 5), and WACC inputs need explicit sourcing (not just ‘8.2%’ typed into B12).
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Manual Formula Build | Input revenue, margins, capex, NWC → calculate FCF → apply WACC discount → Gordon Growth TV | Learning fundamentals, small private deals | No sensitivity tools built-in; error-prone with long forecasts |
| DCF Template + Data Validation | Use pre-built structure (A1:E25) with dropdowns for growth rates, locked WACC cell (D5), dynamic TV toggle | Mid-market M&A teams, PE analysts | Requires setup time; validation rules can break copy-paste workflows |
| XNPV + XIRR Hybrid | List FCFs with exact dates (e.g., 2024-06-30, 2025-06-30) → use XNPV(B2,B4:B12,A4:A12) → add TV separately | Deals with irregular cash flow timing (e.g., biannual acquisitions) | Terminal value still needs manual date alignment — easy to misalign by 6 months |
| Power Query + DCF Dashboard | Import historicals → generate 5-year forecast table → export to worksheet → apply formulas | Teams refreshing models monthly; integrated FP&A workflows | Overkill for one-off valuations; steep learning curve for non-technical users |
Method 1 Deep Dive
We’ll build a clean, auditable DCF from scratch — no templates, no add-ins. Start with this data in Sheet1:
| Year | Revenue | EBITDA Margin | CapEx % Rev | NWC Change |
|---|---|---|---|---|
| 2024 | $12.4M | 24.5% | 4.2% | $182K |
| 2025 | $14.1M | 25.1% | 4.0% | $209K |
| 2026 | $15.9M | 25.7% | 3.8% | $221K |
| 2027 | $17.6M | 26.0% | 3.5% | $195K |
| 2028 | $19.2M | 26.2% | 3.2% | $178K |
Now compute FCF row-by-row in column F. In F4, enter: =B4*C4*(1-0.21)-B4*D4-E4 (assuming 21% tax rate). Drag down to F8. That’s unlevered FCF — no debt interest, no financing costs.
Next, set WACC in cell B11: 9.4%. This isn’t arbitrary — it came from CAPM (risk-free = 3.8%, beta = 1.2, market premium = 5.2%). Then in G4, discount each FCF: =F4/(1+$B$11)^1. In G5: =F5/(1+$B$11)^2, and so on. Don’t use NPV() here — it assumes equal periods, and we’re doing year-end discounting.
For terminal value, use Gordon Growth: =F8*(1+0.025)/(0.094-0.025) in H8 (2.5% perpetual growth). Then discount it: =H8/(1+$B$11)^5 in I8. Sum G4:I8 → $127.8M enterprise value. Subtract net debt ($14.2M in B13) → $113.6M equity value.
Here’s the counterintuitive tip: Never hardcode the terminal year multiplier. Instead, reference the last FCF cell dynamically: =F8*(1+B14)/(B11-B14), where B14 holds growth and B11 holds WACC. That way, changing growth updates TV instantly — and you’ll catch it when B14 ≥ B11 (Excel returns #NUM!, not a silent wrong number).
Method 2 Deep Dive
Let’s use XNPV for precision — especially useful when your forecast starts mid-year or includes acquisition timing. Assume your FCFs sit in B4:B9, and their corresponding dates are in A4:A9: 2024-06-30, 2025-06-30, ..., 2029-06-30.
In C4, type: =XNPV(B2,B4:B9,A4:A9) where B2 holds WACC (9.4%). That gives you the present value of years 1–5. Now handle terminal value carefully: its date must match the *end* of Year 5 — so use =XNPV(B2,{TV_value},{A9}) — not A10 or a guessed date. Yes, you’re discounting TV to the same date as Year 5 FCF. That’s correct.
To lock the WACC cell while building: select B2, press Alt + H + P + L (Home → Format → Lock Cell), then protect the sheet (Alt + R + A + P). This stops interns from overwriting your cost of capital.
Sample output for this method (using same numbers): XNPV of FCFs = $89.3M; discounted TV = $38.1M; total EV = $127.4M — $0.4M less than Method 1, because XNPV accounts for exact day counts (183 days vs. 365).
Cheat Sheet
| Task | Formula / Action | Cell Reference | Shortcut |
|---|---|---|---|
| Unlevered FCF | =Revenue × EBITDA% × (1−Tax%) − CapEx − ΔNWC | F4:F8 | — |
| Discount FCF | =FCF / (1+WACC)^Year | G4:G8 | — |
| Terminal Value | =Last_FCF × (1+g) / (WACC−g) | H8 | — |
| XNPV of FCFs | =XNPV(WACC, FCF_range, date_range) | C4 | Alt+M+V+X |
| Lock WACC cell | Format Cells → Protection → Locked → Protect Sheet | B2 | Alt+H+P+L |
| Check TV sanity | TV should be 50–70% of total EV, not 85% | H8 vs. sum(G4:I8) | — |
