DCF Valuation Model Step-by-Step in Google Sheets

Before a single formula goes in, map the tab structure. A DCF valuation model lives or dies by how cleanly inputs flow into outputs — one circular reference or hard-coded number buried in a formula and the audit trail breaks.

The Assumptions tab is the single source of truth. Hard-code nothing in calculation tabs. If you're ever copy-pasting a number between tabs instead of referencing Assumptions!, stop.

For this model — a SaaS company with $42.8M LTM revenue, 72% gross margin, and 18% EBITDA margin — the core driver block looks like this:

SaaS CapEx benchmarks typically run 1–4% of revenue for pure software businesses, per Meritech Capital's annual public SaaS benchmarks — anything above that usually signals hardware or data center spend worth breaking out separately.

Revenue projects forward from LTM actuals. The formula structure in Revenue!C4 (Year 1 revenue):

=Revenue!B4 * (1 + Assumptions!$C$5)

Where B4 is LTM revenue ($42.8M) and Assumptions!$C$5 is the Y1 growth rate. Years 2–5 follow the same pattern, each referencing the prior year's revenue cell and the corresponding column in Assumptions.

P&L flows directly from Revenue:

='Revenue'!C4                                     ← Total Revenue
='Revenue'!C4 * Assumptions!C7                    ← Gross Profit (gross margin %)
='Revenue'!C4 * Assumptions!C9                    ← EBITDA (EBITDA margin %)
=P&L!C8 - SUMIFS('Revenue'!C:C,'Revenue'!A:A,"D&A")  ← EBIT
=P&L!C9 * (1 - Assumptions!$B$12)                ← NOPAT

At $42.8M LTM, Y1 revenue comes to $54.8M (28% growth). EBITDA at 18% margin is $9.9M. Keep D&A on the P&L tab and feed it into the UFCF tab — it flows back as a non-cash add-back.

Unlevered free cash flow is the engine of the DCF valuation. Build it from NOPAT, not from net income — you want the pre-debt, pre-tax-shield cash generation.

UFCF = NOPAT
     + D&A
     - CapEx
     - Change in NWC

In Google Sheets, with Year 1 in column C:

=P&L!C10                                              ← NOPAT
+P&L!C11                                              ← D&A add-back
-(Revenue!C4 * Assumptions!C11)                       ← CapEx (% of revenue)
-(Revenue!C4 * Assumptions!$B$13 - Revenue!B4 * Assumptions!$B$13)  ← ΔNWC

Working that through the SaaS example:

UFCF growing from $7.1M in Y1 to $20.7M in Y5 reflects operating leverage kicking in as the margin profile expands — exactly what you'd expect in a scaling SaaS business with relatively fixed cost structure.

WACC deserves its own tab because the inputs are defensible claims, not made-up numbers. Every line should be sourced.

The CAPM formula for cost of equity (Sharpe 1964, extended by Fama-French 1992):

Ke = Rf + β × ERP

For this model as of April 2026:

The WACC formula in Sheets:

=WACC!C5*(1-WACC!C12) + WACC!C9*WACC!C12

Where C5 is Ke, C9 is after-tax Kd, and C12 is the debt weight. At 10.1% WACC, you're discounting a SaaS business that's growing 13–28% annually — that's within the range you'd see from most buy-side models in 2026.

With UFCF projections and WACC established, the DCF tab does three things: discount the projection-period cash flows, calculate terminal value, and bridge to equity value.

Discount factors use the mid-year convention (cash flows received throughout the year, not at year-end):

=1 / (1 + WACC!$C$14) ^ (col - 0.5)

Present value of FCFs:

=SUMPRODUCT(UFCF!C4:G4, DCF!C5:G5)

Terminal value using the Gordon Growth Model:

=UFCF!G4 * (1 + Assumptions!$B$20) / (WACC!$C$14 - Assumptions!$B$20)

At a 3.0% terminal growth rate and 10.1% WACC, terminal value on $20.7M Y5 UFCF comes to $295.7M. Discounted back 4.5 years: $190.6M PV of TV.

The full bridge:

Terminal value represents approximately 81.5% of enterprise value — well within the 77–83% range typical for high-growth SaaS businesses at this stage, per standard DCF convention.

A single-point DCF output is a false precision problem. The sensitivity table is where the model becomes defensible — and where you show the range instead of hiding behind one number.

In Google Sheets, use a two-variable data table (not a native feature, so you build it with array formulas). Set up a grid with WACC across the top row and terminal growth rate down the left column, then reference the EV formula:

WACC axis (row 3, columns C–G): 8.5%, 9.3%, 10.1%, 10.9%, 11.7%

Terminal growth axis (column B, rows 4–8): 1.5%, 2.0%, 2.5%, 3.0%, 3.5%

Each cell in the grid recalculates EV using the intersection values:

=UFCF_Y5 * (1 + tg) / (wacc - tg) / (1 + wacc)^4.5 + PV_FCFS_adj

The resulting EV sensitivity table (in $M):

The base case sits at $199.4M enterprise value. The full range across reasonable assumptions runs $141.4M to $268.3M — a spread that reflects actual uncertainty rather than false precision around a single number.

For the board pack or IC memo, show the shaded center band (±1 step on each axis) as your "central range" and the full grid in the appendix.