What is the correct DCF formula in Google Sheets?

The most accurate approach is manual discounting: calculate a period-specific discount factor as `=1/(1+WACC)^period` for each projected year, then multiply each cash flow by its discount factor and sum the results. Don't use `=NPV()` unless your cash flows are evenly spaced and start exactly one period from today. For irregular cash flow dates, `=XNPV(rate, cashflows, dates)` is more accurate because it discounts to the exact date rather than assuming annual periods.

Why does `=NPV()` give the wrong DCF answer?

`=NPV()` in Google Sheets assumes each cash flow arrives at the end of a uniform period starting one period from the valuation date. Any model with a stub period, mid-year convention, or a valuation date that isn't the start of a fiscal year will be mis-discounted. On a $42M FCFF stream at 9.8% WACC, the timing error can exceed $1.5M on the projection period PV alone, before terminal value. Manual discounting or `=XNPV()` avoids this entirely.

What terminal growth rate should I use in a Gordon Growth DCF?

Cap it at nominal GDP growth for the economy you're operating in. Damodaran's January 2026 estimate for US nominal GDP growth is 2.3-2.4%, which is the practical upper bound for a mature, domestically-focused business. High-growth companies transitioning to maturity by the terminal year might justify 2.5-3.0%, but anything above that implies your business will eventually be larger than the economy, which isn't a model - it's an assumption that will get flagged in any serious diligence process.

How do I build a WACC-vs-growth sensitivity table for a DCF in Google Sheets?

Google Sheets doesn't have Excel's native Data Table feature, so you build it with `=MAKEARRAY()` and `=LAMBDA()` (available as of 2022). Put your WACC scenarios across a header row and terminal growth rates down a header column, then use `OFFSET()` to feed each combination into your terminal value formula. A 5x5 table covering WACC from 8.5-11.5% and growth from 1.5-3.5% gives you the full EV range without hardcoding any scenario. On most mid-market models, that range spans $20-30M, which matters for price negotiations.

What's the difference between XNPV and NPV for a DCF model?

`=XNPV(rate, values, dates)` discounts each cash flow based on the exact number of days between the valuation date and the cash flow date, using the formula `CF / (1+rate)^((date - date[0])/365)`. `=NPV(rate, values)` assumes uniform annual periods. For a DCF with a clean January 1 valuation date and December 31 cash flows, the difference is negligible. For any model with a mid-year close, stub period, or quarterly projections, XNPV is materially more accurate - the delta can be $3-5M on a $50M enterprise value at typical private-market discount rates. [Try ModelMonkey free for 14 days](/install) - it works in both Google Sheets and Excel.

DCF Formulas in Google Sheets (2026)

Here's how to build each one correctly, wired across tabs the way a real model needs.

Why `=NPV()` Gives You the Wrong Answer

Google Sheets' =NPV() function assumes cash flows arrive at the end of each period, starting one period from today. If your model starts with a cash flow in Year 1 and you feed it directly into =NPV(), you're fine. But if you have a stub period, mid-year convention, or any timing offset, =NPV() silently mis-discounts every single flow.

On a $42M FCFF stream at 9.8% WACC with a stub year, the error on the PV of the projection period alone can exceed $1.5M. That's before terminal value.

The fix isn't a workaround. It's rebuilding the discounting manually.

Core DCF Formulas: Manual PV Discounting

The right approach: calculate a discount factor for each period explicitly, then multiply it by the corresponding cash flow.

In a typical model, Assumptions!$B$3 holds your WACC, and your DCF tab has years running across columns (D through H for Years 1-5). The discount factor row looks like this:

=1/(1+Assumptions!$B$3)^DCF!D$4

Where D$4 holds the period number (1, 2, 3...). With mid-year convention, that row reads:

=1/(1+Assumptions!$B$3)^(DCF!D$4-0.5)

The PV of each year's FCFF then becomes:

='FCFF'!D12 * DCF!D$8

Where row 12 on the FCFF tab is unlevered free cash flow and row 8 on the DCF tab holds the discount factors. Sum across the projection period for your PV of FCFFs:

=SUMPRODUCT('FCFF'!D12:H12, DCF!D8:H8)

Realistic output on a 5-year model projecting $42M in cumulative FCFF at 9.8% WACC with mid-year convention: PV of FCFFs around $29-31M depending on the growth shape.

Terminal Value DCF Formulas (Gordon Growth & Exit Multiple)

Terminal value typically represents 70-80% of total enterprise value in a healthy-growth business. Getting the formula right matters more here than anywhere else.

Gordon Growth Model

The Gordon Growth formula: TV = FCFFn × (1 + g) / (WACC - g)

In a multi-tab model where Year 5 FCFF lives on the FCFF tab and growth rate and WACC live in Assumptions:

='FCFF'!H12 * (1 + Assumptions!$B$5) / (Assumptions!$B$3 - Assumptions!$B$5)

For the terminal value to make mathematical sense, WACC must exceed the terminal growth rate. According to Damodaran's January 2026 estimate, US nominal GDP growth runs 2.3-2.4%, which is the practical ceiling for a mature-business terminal growth rate. Using 3.5% when WACC is 9.8% is defensible; using 5.5% is not.

Exit Multiple

The multiple approach: TV = EBITDAn × Exit Multiple

='P&L'!H28 * Assumptions!$C$12

Where H28 is Year 5 EBITDA on the P&L tab and C12 is your exit multiple assumption. On an LBO with entry leverage of 6.5x dropping to 3.0x at exit, your exit multiple should be stress-tested against the entry multiple, the current trading range for comps, and precedent transactions. A 12.0x exit on 14.2x entry is a hope, not a model.

Discounting terminal value back to today:

=DCF!C14 / (1 + Assumptions!$B$3)^Assumptions!$B$8

Where C14 is the undiscounted terminal value and B8 is the final projection year number (or 4.5 for mid-year convention on Year 5).

XNPV: The Right DCF Formula for Real Cash Flow Dates

If you're valuing a business with a mid-year close, a stub period, or uneven quarterly cash flows, =XNPV() is more correct than manual discounting because it uses exact dates.

Google Sheets' =XNPV() documentation defines it as: "calculates the net present value of an investment based on a specified series of potentially irregularly-spaced cash flows and a discount rate."

The formula:

=XNPV(Assumptions!$B$3, 'FCFF'!D12:H12, DCF!D3:H3)

Where D3:H3 on the DCF tab holds the actual cash flow dates in YYYY-MM-DD format. This is the formula for bank syndicate DCFs where the valuation date is mid-quarter and every projection date needs to be exact.

One limitation worth flagging: =XNPV() doesn't handle terminal value directly. You need to add terminal value as the final cash flow in your series, discounted to the terminal date, then let XNPV bring the whole thing back to valuation date. A common pattern:

=XNPV(
  Assumptions!$B$3,
  {'FCFF'!D12:H12, DCF!C18},
  {DCF!D3:H3, DCF!H3}
)

Where C18 is the undiscounted terminal value and H3 is the terminal date. You're appending the terminal value to the cash flow array.

Wiring DCF Formulas Across Tabs

A DCF that lives in a single tab is a toy. A real model has FCFF calculations rolling up from the P&L and Balance Sheet, with the DCF tab doing nothing except discounting - no line items, no operating assumptions hardcoded.

The FCFF calculation itself belongs on its own tab. A condensed version:

=('P&L'!D28 * (1 - Assumptions!$B$6))
  + 'P&L'!D35
  - 'CapEx'!D8
  - (('Balance Sheet'!D18 - 'Balance Sheet'!C18)
     - ('Balance Sheet'!D24 - 'Balance Sheet'!C24))

That's NOPAT + D&A - CapEx - change in net working capital, pulling from four separate tabs. If your FCFF formula doesn't look roughly like this, you're probably conflating levered and unlevered cash flows somewhere.

The equity bridge at the bottom of the DCF tab:

=DCF!C22 + 'Balance Sheet'!C8 - 'Balance Sheet'!C32

Enterprise value plus cash minus debt, all pulling current-period balance sheet cells. When the quarterly board pack updates and someone pastes new actuals into the Balance Sheet tab, the equity value updates automatically.

Sensitivity Tables on WACC and Growth Rate

Any DCF you'd show a board or an investor needs a sensitivity table. The =GOOGLEFINANCE() function can't build one for you; you need =INDEX() referencing a data table.

In Google Sheets, a two-variable data table requires manual construction (unlike Excel's native Data Table). A clean approach: put WACC values across a header row (J3:N3) and terminal growth rates down a header column (I4:I8), then:

=INDEX(
  MAKEARRAY(5, 5,
    LAMBDA(r, c,
      ('FCFF'!H12 * (1 + OFFSET($I$4, r-1, 0)))
      / (OFFSET($J$3, 0, c-1) - OFFSET($I$4, r-1, 0))
      / (1 + OFFSET($J$3, 0, c-1))^Assumptions!$B$8
    )
  ),
  1, 1
)

On a $42M FCFF business with 9.8% WACC and 2.5% terminal growth, the EV sensitivity across WACC ranging 8.5-11.5% and growth 1.5-3.5% typically spans $55M to $85M, a $30M range that tells you exactly how much precision matters in your WACC build.

Where ModelMonkey Fits

The formulas above aren't complicated to understand. They're tedious to build correctly across a 10-tab model from scratch, particularly the XNPV date array construction and the multi-tab FCFF roll. ModelMonkey can draft the formula structure across tabs given a description of your model layout - the kind of setup work that takes 45 minutes by hand and 3 minutes when you can describe it in plain language and review the output.

It won't replace your judgment on terminal growth rate or exit multiple. It will stop you from wiring D12 to the wrong tab for the third time.