How to Build a DCF in Google Sheets (2026)
Build a complete, investor-ready DCF in Google Sheets - six linked tabs from Assumptions through equity bridge and sensitivity tables. Real formulas included.
Build a complete, investor-ready DCF in Google Sheets - six linked tabs from Assumptions through to an equity value bridge and sensitivity tables - with every formula cross-referencing correctly and terminal value checks that won't embarrass you in a bank syndicate review. This guide assumes you're modeling a B2B SaaS or recurring-revenue business with $24.3M LTM revenue, 18.7% EBITDA margin, and a target 5-year projection horizon. Swap in your own assumptions and the structure holds regardless of sector.
What You'll Need
- Google Sheets with at least one existing data tab (actuals or LTM financials)
- LTM P&L with revenue, COGS, opex, D&A, and interest expense
- Balance sheet for net debt position
- Beta source (Bloomberg, Damodaran, or comparable public company set)
- Basic working knowledge of named ranges and absolute references
Step-by-Step Guide
Design Your DCF Tab Architecture
Getting the tab order right before you write a single formula saves you from circular reference hell later. A clean DCF model in Google Sheets runs left to right, with data flowing from Assumptions through to the output tabs. Nothing should reference a tab to its right.
- Assumptions** - all input cells live here; every other tab reads from here, never the reverse
- Revenue & OpEx** - P&L build through EBITDA, driven entirely by Assumptions
- FCF** - unlevered free cash flow calculation, pulling from Revenue & OpEx
- WACC** - cost of capital build, referencing only Assumptions inputs
- DCF** - discounting engine, TV calculation, and EV/equity bridge
- Sensitivity** - data tables driving WACC and terminal growth against EV output
Pro Tip
Put a "Model Version" cell and date in the top-left of Assumptions. When three people are emailing DCF_v7_FINAL_2.xlsx around, you'll know which is live.Build the Assumptions Tab (Single Source of Truth)
Every driver that could change goes here. Nothing hardcoded anywhere else. If your revenue growth assumption lives in both Assumptions and Revenue & OpEx, you have a model that will break silently.
Structure Assumptions in clearly labeled blocks: Revenue Drivers, Margin Drivers, Capex & NWC, Tax, and WACC Inputs. Give key cells named ranges so formulas read like documentation.
Named ranges to create:
RevGrowth → Assumptions!$B$5 (revenue CAGR: 12.5%)
EBITDAMargin → Assumptions!$B$8 (18.7%)
TaxRate → Assumptions!$B$12 (26.0%)
TerminalGrowth→ Assumptions!$B$15 (3.0%)
NetDebt → Assumptions!$B$20 (6,200,000)
For the projection years, use a single start year in Assumptions and derive all column headers dynamically. Your Revenue tab headers then look like:
='Assumptions'!$B$2 (base year, e.g. 2025)
='Assumptions'!$B$2+1 (2026)
='Assumptions'!$B$2+2 (2027)
This means moving your base year forward for an annual refresh takes one cell edit, not a find-and-replace through 6 tabs.
Pro Tip
Add a "Check" section at the bottom of Assumptions. One cell that reads "OK" or "ERROR" based on whether your balance sheet ties and your FCF sign conventions are consistent. It takes 10 minutes to build and catches fat-finger errors before they reach a PDF.Project Revenue and EBITDA
Revenue & OpEx is where the model earns its credibility. A single growth rate applied to the whole P&L is adequate for a quick screen; a real model segments revenue by product line or cohort and flows margin assumptions separately from volume assumptions.
For a 5-year projection with a single growth rate pulling from Assumptions:
Revenue row (B5:F5, where B5 is Year 1):
B5: ='Assumptions'!$B$3*(1+RevGrowth)
C5: =B5*(1+RevGrowth)
EBITDA row (B8:F8):
B8: =B5*EBITDAMargin
D&A row (B10:F10, as % of revenue):
B10: =B5*'Assumptions'!$B$9
If you're modeling multiple revenue streams, SUMIFS across a Cohorts or SKU-level tab makes this audit-friendly:
=SUMIFS('Revenue Detail'!$D:$D,
'Revenue Detail'!$B:$B, ">="&'Assumptions'!$B$3,
'Revenue Detail'!$C:$C, "Recurring")
At $24.3M LTM revenue compounding at 12.5% annually, Year 5 exits at approximately $44.0M. EBITDA at a stable 18.7% margin reaches $8.2M by Year 5 - before any margin expansion assumptions kick in.
Pro Tip
Include an actuals column to the left of your projections and hardcode a variance row. When LTM actuals update quarterly, the variance row tells you immediately how far your model has drifted from reality.Calculate Unlevered Free Cash Flow for the DCF
Unlevered FCF (UFCF) strips out financing - no interest, no debt repayment. You're valuing the business as if it's all-equity financed, which is why capital structure lives in WACC, not here.
The formula is: UFCF = NOPAT + D&A - Capex - Change in Net Working Capital
EBIT row (B5:F5) references Revenue & OpEx:
B5: ='Revenue & OpEx'!B12 - 'Revenue & OpEx'!B14
NOPAT row (B7:F7):
B7: =B5*(1-TaxRate)
UFCF row (B12:F12):
B12: =B7
+'Revenue & OpEx'!B10 (D&A add-back)
-'Assumptions'!$B$11*'Revenue & OpEx'!B5 (Capex as % revenue)
-(('Revenue & OpEx'!B5-'Revenue & OpEx'!A5)*'Assumptions'!$B$13) (ΔNWC)
A few things that kill UFCF models silently: sign conventions on NWC (an increase in NWC is a cash use, so it subtracts), treating stock-based compensation inconsistently, and including interest income in EBIT. Double-check your EBIT line against your P&L before discounting anything.
For this model, UFCF ramps from approximately $2.2M in Year 1 to $3.9M in Year 5.
Pro Tip
Build a "UFCF Check" row that independently calculates UFCF from EBITDA - EBITDA less taxes on EBIT less capex less ΔNWC - and compares it to your formula-driven row. If they don't match to the dollar, find out why before you discount.Build the WACC Tab
WACC is where models go wrong most often, usually because the inputs are stale or the capital structure weights are based on book values instead of market values.
As of mid-2026, the 10-year US Treasury sits at approximately 4.3%. Damodaran's January 2026 US equity risk premium estimate is 5.5% (updated annually at pages.stern.nyu.edu). For beta, use a Bloomberg raw beta adjusted toward 1.0 using the Blume adjustment: Adjusted Beta = 0.67 × Raw Beta + 0.33. The comparable set for a mid-market B2B SaaS business yielded a median levered beta of 1.35 in Bloomberg as of May 2026.
WACC tab formulas, all inputs pulling from Assumptions:
RiskFreeRate: ='Assumptions'!$B$25 (0.043)
ERP: ='Assumptions'!$B$26 (0.055)
BetaLevered: ='Assumptions'!$B$27 (1.35)
CostOfEquity: =RiskFreeRate + BetaLevered*ERP
→ 4.3% + 1.35 × 5.5% = 11.7%
PreTaxCostDebt: ='Assumptions'!$B$28 (0.082)
AfterTaxCostDebt: =PreTaxCostDebt*(1-TaxRate)
→ 8.2% × 0.74 = 6.1%
EquityWeight: ='Assumptions'!$B$30 (0.90)
DebtWeight: =1-EquityWeight (0.10)
WACC: =CostOfEquity*EquityWeight + AfterTaxCostDebt*DebtWeight
→ 11.7% × 0.90 + 6.1% × 0.10 = 11.2%
Use market-value weights, not book. If you're doing a private company deal with no market cap, the standard approach is to iterate: assume a capital structure, compute WACC, compute EV, re-derive implied market cap, update weights, repeat until it converges (usually 2-3 rounds).
Pro Tip
Add a sensitivity note in the WACC tab itself showing WACC output at ±50bps on ERP and ±0.2 on beta. Reviewers always push on these two inputs first.Discount Cash Flows and Complete the DCF Valuation
This is where the model produces its output. The DCF tab pulls in UFCF from the FCF tab, discounts it at WACC, calculates a terminal value using the Gordon Growth Model, and bridges from enterprise value to equity value.
Discount factors (row 5, B5:F5):
B5: =1/(1+WACC)^1
C5: =1/(1+WACC)^2
...
F5: =1/(1+WACC)^5
PV of FCF (row 6, B6:F6):
B6: =FCF!B12*B5
Sum of PV of FCFs:
=SUM(B6:F6) → ~$11.9M
Terminal Value (perpetuity growth):
=FCF!F12*(1+TerminalGrowth)/(WACC-TerminalGrowth)
→ 3.9 × 1.03 / (0.112 - 0.030) = $49.0M
PV of Terminal Value:
=TerminalValue * F5 → ~$29.1M
Enterprise Value:
=SUM(B6:F6) + PVTerminalValue → $41.0M
Equity Value bridge:
=EnterpriseValue - NetDebt → $34.8M
Terminal value accounts for approximately 71% of enterprise value here - standard for a 5-year horizon at this growth profile, but worth flagging to any investor who's only used to 10-year models where TV% runs lower. A TV above 75% usually means the projection period is too short or the terminal assumptions are doing too much work.
Add a "Checks" section below the bridge: TV as % of EV, implied EV/EBITDA exit multiple (Enterprise Value / Year 5 EBITDA), and implied perpetuity growth rate using the exit multiple to back-calculate. If those numbers aren't internally consistent, something is wrong.
Pro Tip
UseXNPV instead of NPV if your cash flows don't land on exact year-end dates. XNPV('DCF'!$B$3, FCF!B12:F12, FCF!B4:F4) handles mid-year timing correctly, which matters on deals where the close date isn't January 1.Build Sensitivity Tables
A DCF without sensitivity tables isn't a model, it's a point estimate. The two axes that matter most: WACC on one axis, terminal growth rate on the other, equity value in the cells. Build it as a two-variable data table so it recalculates automatically.
Set up the table with WACC values running down column headers (9.2%, 9.7%, 10.2%, ..., 13.2%) and terminal growth rates running across row headers (2.0%, 2.5%, 3.0%, 3.5%, 4.0%). The top-left cell of the data table references your equity value output from the DCF tab.
Top-left reference cell (e.g., B20):
='DCF'!$B$22 (your equity value output)
To build the table:
1. Highlight the full range including row/column headers
2. Data → Data Validation → ... (or use: Data > What-If Analysis in Excel)
In Google Sheets, data tables require Apps Script. The lightweight workaround:
- Create a helper tab with WACC and growth inputs explicitly listed
- Use INDEX/MATCH to pull equity value for each scenario combination
Helper formula in sensitivity grid (cell C22, where B22=WACC override, C21=growth override):
=IFERROR(
(NPV(B22, FCF!$B$12:$F$12) +
FCF!$F$12*(1+C21)/(B22-C21) / (1+B22)^5) -
Assumptions!$B$20,
"N/A")
The 25-cell grid running from 9.2% to 13.2% WACC and 2.0% to 4.0% terminal growth gives your $34.8M equity value base case, with a range roughly from $19.4M (high WACC, low growth) to $54.2M (low WACC, high growth). That spread is your negotiating range on valuation.
Pro Tip
Add a second sensitivity table with revenue growth versus EBITDA margin. Investors almost always stress the top line before they stress the discount rate.Wrapping Up
A DCF model done right in Google Sheets is 6 linked tabs, roughly 200-300 formula cells, and a sensitivity grid that shows the full range of outcomes rather than pretending precision exists where it doesn't. The most common failure mode isn't bad math - it's hardcoded assumptions scattered across tabs that get stale and never get updated.
The architecture matters as much as the formulas. Assumptions feeds everything. FCF never references WACC. The DCF tab is read-only output. If you build it that way, the model stays auditable when your associate hands it to a junior analyst six months after close.
The one thing Google Sheets doesn't handle gracefully natively is the data table refresh - which is where ModelMonkey closes the gap, rebuilding sensitivity grids automatically when upstream assumptions change without requiring manual recalculation or Apps Script workarounds.
Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.
Frequently Asked Questions
How many years should a DCF projection period cover?
Most buy-side and sell-side models use 5 years for high-growth businesses and 10 years for mature, stable-cash-flow companies. The right answer is "however many years until the business reaches a steady state," but in practice 5 years is the standard. Going beyond 10 years adds false precision - your Year 8 EBITDA margin assumption is a guess, and the model treats it like a fact.
What's a reasonable terminal growth rate for a DCF?
Terminal growth should not exceed long-run nominal GDP growth - typically 2.5% to 3.5% for a US-domiciled business as of 2026. Using 4% or higher implies the company will eventually be larger than the US economy, which is a math problem that shows up sooner than you'd think. For conservative deal work, stress-test down to 1.5%.
Why does terminal value account for 70%+ of enterprise value in most DCFs?
Because you're discounting near-term cash flows heavily and the terminal value captures all cash flows beyond the projection period, which is most of the company's life. A 5-year horizon at 11.2% WACC means Year 5 cash flows are already worth only $0.59 per dollar of face value. The TV% being above 70% isn't a red flag - it's arithmetic. The flag is when TV% hits 85%+ because the projection period is too short or terminal margins are too optimistic.
How do I handle net debt in the equity value bridge?
Net debt = total interest-bearing debt minus cash and cash equivalents. Subtract it from enterprise value to get equity value. Common mistakes: including operating leases (correct treatment depends on whether you capitalized them in EBITDA), ignoring earnout liabilities, and using book value of debt instead of market value. For most private company deals, book and market value of debt are close enough unless the company is distressed.
Can I build a DCF directly in Google Sheets without Apps Script?
Yes - the core discounting, terminal value, and equity bridge all work with native Google Sheets formulas. The only area where native Sheets falls short is two-variable data tables for sensitivity analysis, which Excel handles natively but Sheets doesn't. The workaround is either a formula-driven grid (as shown in Step 7) or a short Apps Script function that iterates through WACC and growth combinations and writes results to a range.