DCF Valuation in Google Sheets: Financial Modelling Guide

The specific failure mode: hard-coded inputs scattered across 4-5 tabs, a WACC keyed as a decimal in row 3 of your DCF tab, and a terminal value formula that breaks the moment someone changes the exit multiple assumption. That's not a DCF model - that's a calculation that happens to be in a spreadsheet.

Tab Architecture: Where Most DCF Valuation Models Break First

The single biggest structural error in analyst-built DCFs is mixing input cells with formula cells. Once your FY2027 revenue growth rate lives in two different places, you'll get a tie-out error at the worst possible time (it's always the board pack).

The minimum viable structure for a bank-ready DCF:

Everything flows left-to-right. The only tab where you type a number is Assumptions. If you're typing a number anywhere else, you're building a maintenance problem.

The cross-tab reference pattern that keeps this disciplined:

// Revenue Build tab - projection period from Assumptions
=Assumptions!$B$3 * (1 + Assumptions!$C$5)

// P&L tab - gross margin assumption from Assumptions
='Revenue Build'!C4 * (1 - Assumptions!$D$3)

// FCFF Bridge - ties EBIT (P&L), NWC change (Balance Sheet), capex (Cash Flow)
=('P&L'!C22 * (1 - Assumptions!$B$12)) - ('Balance Sheet'!D18 - 'Balance Sheet'!C18) - 'Cash Flow'!C8

A reference like ='P&L'!C22 that doesn't trace back to Assumptions is a red flag. If a reviewer can change one cell in Assumptions and the entire model re-calculates cleanly, the structure is right.

One practical note on circulars: if your model includes an interest expense loop (debt balance feeds interest, which feeds net income, which feeds the balance sheet), Google's Sheets documentation states that iterative calculation is "off by default" and warns that if a formula loops back to its own cell, "Sheets can't calculate the formula normally." Enable it under File → Settings → Calculation, and cap iterations at 100. Without it, your revolver balance will throw a #VALUE! error in the middle of a live review.

WACC Inputs and the Numbers That Matter in June 2026

WACC is where models pick up quiet errors that don't surface until someone asks where the 9.2% discount rate came from.

The Federal Reserve H.15 release puts the 10-year Treasury yield at 4.3% as of June 2026. Aswath Damodaran's 2026 annual equity risk premium update sets the implied ERP at 4.6% for the US market. Those two numbers anchor the cost of equity calculation. If your model has a risk-free rate below 4.0% or an ERP below 4.0%, someone will ask about it in the first 5 minutes.

A typical WACC input table for a mid-market B2B SaaS target at $47.3M revenue and 22.8% EBITDA margin:

All of those inputs live in Assumptions. The WACC tab contains only formulas:

// Cost of equity (CAPM)
=Assumptions!B4 + Assumptions!B6 * Assumptions!B5

// After-tax cost of debt
=Assumptions!B8 * (1 - Assumptions!B9)

// Blended WACC
=(B3 * Assumptions!B11) + (B4 * Assumptions!B12)

A single hard-coded WACC creates a $12-18M valuation error at typical mid-market deal sizes when someone "quickly updates" the rate without realizing it isn't pulling from the input sheet.

FCFF Projections: The Core of Financial Modelling in Sheets

The FCFF Bridge is the tab most analysts under-build. It's the link between the three-statement model and the DCF, and if it doesn't tie to the penny, nothing downstream does either.

FCFF = NOPAT - change in net working capital - capex. Starting from EBIT:

// FCFF Bridge tab, Year 1 column (C)
// NOPAT
='P&L'!C22 * (1 - Assumptions!$B$12)

// Less: increase in NWC (a use of cash, so sign is negative)
=-('Balance Sheet'!C18 - 'Balance Sheet'!B18)

// Less: capex
=-'Cash Flow'!C8

// FCFF (sum the three rows above)
=C5 + C6 + C7

For the $47.3M revenue model, Year 1 FCFF:

• EBIT: $10.8M (22.8% margin)
• NOPAT at 26% tax: $7.99M
• Less NWC increase: ($1.2M)
• Less capex: ($0.9M)
• Year 1 FCFF: $5.89M

Year 5 FCFF at projected revenue of $89.4M (13.5% CAGR) and 26.1% EBITDA margin:

• EBIT: $23.3M
• NOPAT: $17.2M
• Less NWC increase: ($2.1M)
• Less capex: ($1.7M)
• Year 5 FCFF: $13.4M

To discount the full explicit projection period in one formula on the DCF tab:

// DCF tab - NPV of Years 1-5 FCFFs
=SUMPRODUCT('FCFF Bridge'!C8:G8, 1/(1+WACC!$B$15)^{1,2,3,4,5})

Terminal Value: Where 65-80% of Your Enterprise Value Lives

This is the number that actually drives your valuation, which is exactly why it should make you nervous. For most DCF models with a 5-year explicit period, terminal value accounts for 65-80% of total enterprise value. Small changes in the exit assumption move the output dramatically.

Two approaches, and they should roughly agree:

// Exit Multiple Method - terminal value using EBITDA multiple
=('P&L'!G24 * Assumptions!$B$20) / (1 + WACC!$B$15)^5

// Gordon Growth Method - capitalize Year 6 FCFF
=('FCFF Bridge'!H8 * (1 + Assumptions!$B$21)) / (WACC!$B$15 - Assumptions!$B$21) / (1 + WACC!$B$15)^5

For the $47.3M revenue model at a 14.2x EBITDA exit multiple and $23.3M EBITDA in Year 5:

• Terminal value (exit multiple): $330.9M
• Discounted terminal value at 8.32% WACC: $224.8M
• PV of explicit period FCFFs: $34.4M
• Less: net debt ($81.2M)
• Equity value: $178.0M

If your Gordon Growth method comes out at $165M and your exit multiple method comes out at $178M, that's a reasonable 7.4% spread - you bracket it in the IC deck. If they're $50M apart, something is broken in the growth or margin assumptions.

Sensitivity Tables That Cover the Real Decision Space

The WACC vs. exit multiple table is table stakes. Every bank-syndicate DCF has one. What most don't have is the second table that actually moves in due diligence: Year 5 EBITDA margin vs. revenue CAGR.

Sheets handles two-variable data tables natively via TABLE(). Set up your row inputs (WACC rates) and column inputs (exit multiples), put the enterprise value formula in the top-left intersection cell, then enter =TABLE(WACC!$B$15, Assumptions!$B$20) as an array formula with Ctrl+Shift+Enter. Sheets recalculates every combination automatically.

For the $47.3M model:

The spread from the low-WACC/high-multiple corner to the high-WACC/low-multiple corner is $99.1M - a 20.3% variance from the base case. That's the range you're defending.

To track actuals against the projection period as the quarters roll in, pull from your P&L with:

=SUMIFS('P&L'!C:C, 'P&L'!B:B, ">=" & Assumptions!$B$3, 'P&L'!B:B, "<=" & Assumptions!$B$4, 'P&L'!A:A, "Revenue")

Using AI in the DCF Build Process

The tedious part of DCF modelling isn't the valuation math - it's the setup. Writing FCFF Bridge formulas that correctly pull from the P&L, Balance Sheet, and Cash Flow tabs simultaneously. Getting the NWC calculation to reference the right Balance Sheet rows. Formatting the sensitivity output so it's legible for an IC deck and not just a wall of numbers.

ModelMonkey handles the formula generation and tab-wiring directly in Sheets. You describe what you need in plain language ("pull EBIT from P&L row 22, apply the tax rate from Assumptions B12, subtract the NWC change calculated from Balance Sheet rows 18"), and it writes the cross-tab formula. You can also set custom instructions once - "I'm building PE portfolio company valuations, use GAAP standards, format currency in $X,XXX,XXX, always include sensitivity tables" - and every formula it outputs follows those conventions without you repeating the context each session.

The practical payoff is in the iteration cycle. When WACC inputs change or a new revenue assumption arrives at 11pm before a 9am IC meeting, a structurally clean model takes 10 minutes to update instead of 90.

Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.