How to Build a DCF Model in Google Sheets

On Assumptions, enter these labels in A2:A12 and values in B2:B12.

Enter the exit EBITDA multiple in B7 as the numeric value 8.0, not the text 8.0x. To display the suffix while preserving the numeric value, select B7, choose Format > Number > Custom number format, and apply:

0.0"x"

Enter =DATE(2025,12,31) in B2 and format the cell as a date. This approach avoids ambiguity in spreadsheet locales that interpret typed dates differently. You can also use the date picker or set a compatible spreadsheet locale.

The simplified bridge assumes that debt, cash, and other claims are mutually exclusive. In a real valuation, you may need to treat leases, minority interests, pensions, options, investments, tax assets, or other items separately.

Next, build the operating assumption schedule in A14:G19. Enter Date in A14, followed by this formula in B14:

=IFERROR(
  IF(
    AND(ISNUMBER($B$2),MONTH($B$2)=12,DAY($B$2)=31),
    $B$2,
    NA()
  ),
  NA()
)

The outer IFERROR causes unparseable text in B2 to return #N/A instead of a #VALUE! error from MONTH or DAY.

Enter this formula in C14 and copy it through G14:

=IFERROR(
  IF(ISNUMBER(B14),DATE(YEAR(B14)+1,12,31),NA()),
  NA()
)

Together, these formulas use B2 as the single valuation-date input and derive each subsequent calendar year-end. Don’t replace B14:G14 with manually entered dates.

Enter the remaining assumptions as follows:

| Row | Label | B: 2025 | C: 2026 | D: 2027 | E: 2028 | F: 2029 | G: 2030 | |---|---|---:|---:|---:|---:|---:| | 14 | Date | Formula | Formula | Formula | Formula | Formula | Formula | | 15 | Revenue growth | Blank | 10.0% | 9.0% | 8.0% | 7.0% | 6.0% | | 16 | EBITDA margin | Blank | 20.0% | 21.0% | 22.0% | 23.0% | 24.0% | | 17 | D&A / Revenue | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | | 18 | Capex / Revenue | 4.0% | 4.0% | 4.0% | 4.0% | 4.0% | 4.0% | | 19 | NWC / Revenue | 10.0% | 10.0% | 10.0% | 10.0% | 10.0% | 10.0% |

The 2025 column represents the opening period. Leave 2025 revenue growth and EBITDA margin blank because the model doesn’t value a 2025 cash flow.

For the following controls, use Data > Data validation > Add rule and select Reject the input:

• B2: custom formula =AND(ISNUMBER(B2),MONTH(B2)=12,DAY(B2)=31)
• B3 and B7:B10: number greater than or equal to 0
• B11:B12: number greater than 0
• B4, C16:G16, and B17:G19: number between 0 and 1
• B5: custom formula =AND(ISNUMBER(B5),B5>0,B5<=100%)
• C15:G15: number greater than -1
• B6: custom formula =AND(ISNUMBER(B6),B6>-100%,B6<$B$5)

Validation may not stop values introduced through pasted or imported data. For another layer of protection, the calculation formulas below inspect their source cells and return #N/A for blank, text, or out-of-range inputs.

Set up DCF!A2:A20 with these row labels:

In DCF!B2, enter this date link and copy it through G2:

=IF(ISNUMBER(Assumptions!B14),Assumptions!B14,NA())

Then add opening revenue in B3:

=IF(
  AND(ISNUMBER(Assumptions!B3),Assumptions!B3>=0),
  Assumptions!B3,
  NA()
)

For the first projected year, enter the revenue formula in C3 and copy it through G3:

=IF(
  AND(ISNUMBER(B3),ISNUMBER(Assumptions!C15),Assumptions!C15>-100%),
  B3*(1+Assumptions!C15),
  NA()
)

Now link the forecast growth and EBITDA-margin assumptions:

C4: =IF(AND(ISNUMBER(Assumptions!C15),Assumptions!C15>-100%),Assumptions!C15,NA())
C5: =IF(AND(ISNUMBER(Assumptions!C16),Assumptions!C16>=0,Assumptions!C16<=100%),Assumptions!C16,NA())

Copy both formulas through column G, leaving B4:B5 blank.

Calculate EBITDA in C6, then copy the formula through G6:

=IF(
  AND(ISNUMBER(C3),ISNUMBER(C5),C5>=0,C5<=100%),
  C3*C5,
  NA()
)

Enter the D&A percentage link in B7 and copy it through G7:

=IF(
  AND(
    ISNUMBER(Assumptions!B17),
    Assumptions!B17>=0,
    Assumptions!B17<=100%
  ),
  Assumptions!B17,
  NA()
)

Calculate D&A in C8 and copy through G8:

=IF(
  AND(ISNUMBER(C3),ISNUMBER(C7),C7>=0,C7<=100%),
  C3*C7,
  NA()
)

Next, calculate EBIT in C9 and copy through G9:

=IF(AND(ISNUMBER(C6),ISNUMBER(C8)),C6-C8,NA())

Link the tax rate in C10, then copy through G10:

=IF(
  AND(
    ISNUMBER(Assumptions!$B$4),
    Assumptions!$B$4>=0,
    Assumptions!$B$4<=100%
  ),
  Assumptions!$B$4,
  NA()
)

The absolute reference prevents the assumption from shifting to another cell when you copy the formula to the right.

Finally, calculate NOPAT in C11 and copy through G11:

=IF(
  AND(ISNUMBER(C9),ISNUMBER(C10),C10>=0,C10<=100%),
  C9*(1-C10),
  NA()
)

The expected operating forecast is:

For 2026:

Revenue = $100.0 × (1 + 10.0%) = $110.0
EBITDA = $110.0 × 20.0% = $22.0
D&A = $110.0 × 3.0% = $3.3
EBIT = $22.0 - $3.3 = $18.7
NOPAT = $18.7 × (1 - 25.0%) = $14.025

Unlevered free cash flow excludes interest and debt repayments because the model accounts for financing through WACC and the enterprise-to-equity bridge.

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

Enter this capex-rate link in B12, then copy it through G12:

=IF(
  AND(
    ISNUMBER(Assumptions!B18),
    Assumptions!B18>=0,
    Assumptions!B18<=100%
  ),
  Assumptions!B18,
  NA()
)

In C13, enter the capex formula and copy it through G13:

=IF(
  AND(ISNUMBER(C3),ISNUMBER(C12),C12>=0,C12<=100%),
  C3*C12,
  NA()
)

Link the NWC rate in B14 and copy through G14:

=IF(
  AND(
    ISNUMBER(Assumptions!B19),
    Assumptions!B19>=0,
    Assumptions!B19<=100%
  ),
  Assumptions!B19,
  NA()
)

Calculate the opening NWC balance in B15, then copy through G15:

=IF(
  AND(ISNUMBER(B3),ISNUMBER(B14),B14>=0,B14<=100%),
  B3*B14,
  NA()
)

To calculate the annual change in NWC, enter this formula in C16 and copy through G16:

=IF(AND(ISNUMBER(C15),ISNUMBER(B15)),C15-B15,NA())

An increase in NWC is a use of cash, so the UFCF calculation subtracts it.

Enter the UFCF formula in C17 and copy through G17:

=IF(COUNT(C11,C8,C13,C16)=4,C11+C8-C13-C16,NA())

The expected cash flow schedule is:

The 2026 UFCF check is:

$14.025 NOPAT
+ $3.300 D&A
- $4.400 capex
- ($11.000 NWC - $10.000 opening NWC)
= $11.925 UFCF

Keep 2025 UFCF blank. The model uses the opening NWC balance once to calculate the 2026 change; it isn’t a separate cash flow.

WACC should match the risk and financing basis of the unlevered cash flows. One framework is:

Cost of equity = Risk-free rate + Beta × Equity risk premium

After-tax cost of debt = Pre-tax cost of debt × (1 - Tax rate)

WACC = Equity weight × Cost of equity
     + Debt weight × After-tax cost of debt

Base the equity and debt weights on the capital structure relevant to the valuation. Document the source date and basis for every input. This example uses the final 10.0% WACC in Assumptions!B5, but it doesn’t imply that 10.0% is suitable for another company.

Derive the discount periods from the dates. Enter this formula in DCF!B18 and copy it through G18:

=IFERROR(
  IF(
    AND(ISNUMBER(B2),ISNUMBER($B$2)),
    YEAR(B2)-YEAR($B$2),
    NA()
  ),
  NA()
)

The expected values in B18:G18 are 0, 1, 2, 3, 4, 5.

Add Date and Period Check in DCF!A21 and enter this formula in B21:

=IFERROR(
  IF(
    AND(
      COUNT(B2:G2)=6,
      B2=Assumptions!B2,
      B2=DATE(YEAR(B2),12,31),
      C2=DATE(YEAR(B2)+1,12,31),
      D2=DATE(YEAR(B2)+2,12,31),
      E2=DATE(YEAR(B2)+3,12,31),
      F2=DATE(YEAR(B2)+4,12,31),
      G2=DATE(YEAR(B2)+5,12,31),
      COUNT(B18:G18)=6,
      B18=0,
      C18=1,
      D18=2,
      E18=3,
      F18=4,
      G18=5
    ),
    "OK",
    NA()
  ),
  NA()
)

This control confirms that the schedule contains 6 consecutive calendar year-ends and assigns each forecast cash flow to the correct discount period. It checks the resulting values, not whether each source cell still contains its original formula. If formula lineage matters, review Assumptions!B14:G14 separately.

Leave B19:B20 blank. In C19, enter this discount-factor formula and copy it through G19:

=IF(
  AND(
    $B$21="OK",
    ISNUMBER(Assumptions!$B$5),
    Assumptions!$B$5>0,
    Assumptions!$B$5<=100%,
    ISNUMBER(C18),
    C18>0
  ),
  1/(1+Assumptions!$B$5)^C18,
  NA()
)

Then enter this formula in C20 and copy it through G20:

=IF(AND(ISNUMBER(C17),ISNUMBER(C19)),C17*C19,NA())

This example uses year-end discounting.

The expected sum of forecast-period present values is approximately $60.1442 million.

Terminal value captures cash flows after the explicit forecast. Calculate the perpetuity-growth and exit-multiple methods as separate cases.

Set up DCF!A23:C29 as follows:

For the perpetuity-growth method, enter this formula in B24:

=IF(
  AND(
    $B$21="OK",
    ISNUMBER(G17),
    ISNUMBER(Assumptions!B5),
    Assumptions!B5>0,
    Assumptions!B5<=100%,
    ISNUMBER(Assumptions!B6),
    Assumptions!B6>-100%,
    Assumptions!B5>Assumptions!B6
  ),
  G17*(1+Assumptions!B6)/(Assumptions!B5-Assumptions!B6),
  NA()
)

With the example inputs:

Terminal value
= $20.831960754 × (1 + 3.0%) ÷ (10.0% - 3.0%)
= $306.5274

For the exit-multiple method, enter this formula in C24:

=IF(
  AND(
    $B$21="OK",
    ISNUMBER(G6),
    ISNUMBER(Assumptions!B7),
    Assumptions!B7>=0
  ),
  G6*Assumptions!B7,
  NA()
)

The calculation produces:

$35.248758336 terminal-year EBITDA × 8.0x = $281.9901

To discount each terminal value, enter this formula in B25 and copy it to C25:

=IF(AND(ISNUMBER(B24),ISNUMBER($G$19)),B24*$G$19,NA())

Enter this formula in B26 and copy it to C26:

=IF(
  AND(COUNT($C$20:$G$20)=5,ISNUMBER(B25)),
  SUM($C$20:$G$20)+B25,
  NA()
)

The expected results are:

Don’t add the 2 terminal values together. They’re alternative valuation cases, not separate sources of value.

The simplified bridge is:

Equity value = Enterprise value - Debt + Cash - Other claims

Implied share price = Equity value in USD millions
                    ÷ Diluted shares in millions

Enter this formula in DCF!B27 and copy it to C27:

=IF(
  AND(
    ISNUMBER(B26),
    COUNT(Assumptions!$B$8:$B$10)=3,
    MIN(Assumptions!$B$8:$B$10)>=0
  ),
  B26-Assumptions!$B$8+Assumptions!$B$9-Assumptions!$B$10,
  NA()
)

For implied share price, enter this in B28 and copy it to C28:

=IF(
  AND(
    ISNUMBER(B27),
    ISNUMBER(Assumptions!$B$11),
    Assumptions!$B$11>0
  ),
  B27/Assumptions!$B$11,
  NA()
)

Calculate the market-price comparison in B29 and copy it to C29:

=IF(
  AND(
    ISNUMBER(B28),
    ISNUMBER(Assumptions!$B$12),
    Assumptions!$B$12>0
  ),
  B28/Assumptions!$B$12-1,
  NA()
)

When you copy the formulas from column B to column C, the valuation references move from B26:B28 to C26:C28. The locked debt, cash, other-claims, share-count, and market-price references remain in Assumptions!B8:B12.

The expected bridge is:

These outputs are examples, not price targets or investment recommendations. Current market price is used only for comparison; it doesn’t determine the DCF value.

On Sensitivity, enter WACC cases in B2:F2:

8.0% | 9.0% | 10.0% | 11.0% | 12.0%

Enter terminal-growth cases in A3:A7:

1.0%
2.0%
3.0%
4.0%
5.0%

In B3, enter this formula and copy it across B3:F7:

=IF(
  AND(
    DCF!$B$21="OK",
    COUNT(DCF!$C$17:$G$17)=5,
    ISNUMBER(B$2),
    B$2>0,
    B$2<=100%,
    ISNUMBER($A3),
    $A3>-100%,
    B$2>$A3,
    COUNT(Assumptions!$B$8:$B$10)=3,
    MIN(Assumptions!$B$8:$B$10)>=0,
    ISNUMBER(Assumptions!$B$11),
    Assumptions!$B$11>0
  ),
  (
    SUMPRODUCT(
      DCF!$C$17:$G$17,
      1/(1+B$2)^(DCF!$C$18:$G$18)
    )
    +DCF!$G$17*(1+$A3)/(B$2-$A3)/(1+B$2)^DCF!$G$18
    -Assumptions!$B$8
    +Assumptions!$B$9
    -Assumptions!$B$10
  )/Assumptions!$B$11,
  NA()
)

Sensitivity!D5, where WACC is 10.0% and terminal growth is 3.0%, should equal the base perpetuity-growth result of $10.5237 per share.

A cell returns #N/A when WACC is less than or equal to terminal growth. With every other input held fixed, a lower WACC or higher terminal growth should increase the calculated value.

A second table can isolate changes to terminal-year revenue growth and EBITDA margin.

Enter the 2030 revenue-growth cases in Sensitivity!B11:F11:

4.0% | 5.0% | 6.0% | 7.0% | 8.0%

Enter the 2030 EBITDA-margin cases in A12:A16:

22.0%
23.0%
24.0%
25.0%
26.0%

In B12, enter this formula and copy it across B12:F16:

=IF(
  AND(
    DCF!$B$21="OK",
    ISNUMBER(B$11),
    B$11>-100%,
    ISNUMBER($A12),
    $A12>=0,
    $A12<=100%,
    ISNUMBER(Assumptions!$B$4),
    Assumptions!$B$4>=0,
    Assumptions!$B$4<=100%,
    ISNUMBER(Assumptions!$B$5),
    Assumptions!$B$5>0,
    Assumptions!$B$5<=100%,
    ISNUMBER(Assumptions!$B$6),
    Assumptions!$B$6>-100%,
    Assumptions!$B$5>Assumptions!$B$6,
    COUNT(Assumptions!$B$8:$B$10)=3,
    MIN(Assumptions!$B$8:$B$10)>=0,
    ISNUMBER(Assumptions!$B$11),
    Assumptions!$B$11>0,
    COUNT(Assumptions!$G$17:$G$19)=3,
    MIN(Assumptions!$G$17:$G$19)>=0,
    MAX(Assumptions!$G$17:$G$19)<=100%,
    COUNT(DCF!$C$20:$F$20)=4,
    ISNUMBER(DCF!$F$3),
    ISNUMBER(DCF!$F$15)
  ),
  LET(
    rev,DCF!$F$3*(1+B$11),
    da,rev*Assumptions!$G$17,
    ebitda,rev*$A12,
    nwc,rev*Assumptions!$G$19,
    fcf,(ebitda-da)*(1-Assumptions!$B$4)
        +da
        -rev*Assumptions!$G$18
        -(nwc-DCF!$F$15),
    pvExplicit,SUM(DCF!$C$20:$F$20)
        +fcf/(1+Assumptions!$B$5)^5,
    tv,fcf*(1+Assumptions!$B$6)
        /(Assumptions!$B$5-Assumptions!$B$6),
    (
      pvExplicit
      +tv/(1+Assumptions!$B$5)^5
      -Assumptions!$B$8
      +Assumptions!$B$9
      -Assumptions!$B$10
    )/Assumptions!$B$11
  ),
  NA()
)

This table changes only the 2030 assumptions. The 2026-2029 forecasts remain fixed.

Sensitivity!D14, using 6.0% revenue growth and a 24.0% EBITDA margin, should equal $10.5237 per share. A matching result confirms that the center case follows the base model’s operating logic.

The checks below haven’t been executed here. Run them in a completed workbook and record each result.

The date tests serve different purposes. Changing Assumptions!B2 tests the valuation-date input and its downstream error handling. Changing Assumptions!D14 to June 30 tests the schedule-value control. Neither test proves that the schedule cells contain their intended formulas, so formula inspection remains a separate audit procedure.

Categorical-enum, approval-status, no-match lookup, transfer-pairing, and progress-direction tests don’t apply because this workbook contains no such fields or processes.

These checks address common DCF errors such as:

• Discounting levered cash flow at WACC
• Including interest expense or debt repayment in UFCF
• Reversing the sign of the change in NWC
• Treating opening NWC as another cash flow
• Using an inconsistent valuation date or discount-period sequence
• Allowing terminal growth to equal or exceed WACC
• Adding both terminal-value methods
• Mixing debt, cash, or claims from different dates
• Double-counting claims in the enterprise-to-equity bridge
• Dividing by basic shares when diluted shares are required
• Mixing whole-share counts with financial amounts in millions
• Copying formulas without locking assumption references
• Treating sensitivity outputs as evidence that assumptions are reasonable

ModelMonkey can inspect spreadsheet context and help read formulas, identify formula errors, analyze ranges, and propose cell or formatting changes in Google Sheets and Excel. For this workbook, ask it to review DCF!C17:G29, DCF!B21, Assumptions!B14:G14, and both sensitivity grids against the equations above. Review the assumptions and proposed edits, then rerun every control before relying on the valuation.

Use ModelMonkey to help review the DCF formulas and sensitivity ranges.