Build a DCF Valuation Step by Step

Use a valuation date of December 31, 2026, followed by five annual forecast periods ending December 31, 2027 through December 31, 2031.

This example calculates unlevered free cash flow, or UFCF:

UFCF = NOPAT + D&A - Capital expenditures - Change in net working capital

NOPAT is EBIT after cash taxes:

NOPAT = EBIT - Cash tax

Because UFCF is available to debt and equity capital providers, discount it at WACC to calculate enterprise value. This model applies year-end discounting and doesn't use a midyear convention.

If you need to develop the operating forecast from integrated financial statements first, see Build a Startup 3-Statement Financial Model. For more detail on structuring forecast assumptions, see Build a 5-Year Projection in Google Sheets.

On Inputs, enter Label, Unit, and Value in A1:C1. Then add the following illustrative assumptions.

Use data validation to reject invalid typed entries:

For C2, you can use this custom validation formula:

=AND(ISNUMBER(C2),MONTH(C2)=12,DAY(C2)=31)

Pasted or imported values can bypass validation. For that reason, the formulas below also use ISNUMBER, error handling, and range checks. Invalid inputs return #N/A through NA() instead of being converted to zero.

On Valuation, enter these labels in A2:A4:

Cost of equity
After-tax cost of debt
WACC

Enter the cost-of-equity formula in B2:

=IF(AND(ISNUMBER(Inputs!C14),Inputs!C14>=0,Inputs!C14<=1,ISNUMBER(Inputs!C15),Inputs!C15>=0,ISNUMBER(Inputs!C16),Inputs!C16>=0,Inputs!C16<=1),Inputs!C14+Inputs!C15*Inputs!C16,NA())

This formula uses the capital asset pricing model:

Cost of equity = Risk-free rate + Beta × Equity risk premium
               = 4.0% + 1.0 × 7.0%
               = 11.0%

Next, enter the after-tax cost-of-debt formula in B3:

=IF(AND(ISNUMBER(Inputs!C17),Inputs!C17>=0,Inputs!C17<=1,ISNUMBER(Inputs!C10),Inputs!C10>=0,Inputs!C10<=1),Inputs!C17*(1-Inputs!C10),NA())

The expected result is:

8.0% × (1 - 25.0%) = 6.0%

Now enter the WACC formula in B4:

=IF(AND(ISNUMBER(B2),ISNUMBER(B3),ISNUMBER(Inputs!C18),Inputs!C18>=0,Inputs!C18<=1),B2*(1-Inputs!C18)+B3*Inputs!C18,NA())

The expected WACC is:

11.0% × 80.0% + 6.0% × 20.0% = 10.0%

This is a simplified illustrative WACC. A company-specific analysis may require current market values, comparable-company betas, country or size adjustments, several debt instruments, and a normalized tax rate.

Set up Forecast!A1:N1 with these headings:

Enter 0, 1, 2, 3, 4, 5 in A2:A7.

In B2, link the valuation date and apply the same date-boundary guard used for the forecast periods:

=IF(ISNUMBER(Inputs!C2),IFERROR(IF(AND(MONTH(Inputs!C2)=12,DAY(Inputs!C2)=31),Inputs!C2,NA()),NA()),NA())

Link base-year revenue in D2:

=IF(AND(ISNUMBER(Inputs!C3),Inputs!C3>0),Inputs!C3,NA())

Leave the other base-year calculation cells blank.

Enter the first forecast date in B3, then copy the formula through B7:

=IF(ISNUMBER(Inputs!$C$2),IFERROR(IF(AND(MONTH(Inputs!$C$2)=12,DAY(Inputs!$C$2)=31),DATE(YEAR(Inputs!$C$2)+A3,12,31),NA()),NA()),NA())

The outer IF prevents the date functions from evaluating text inputs. Meanwhile, IFERROR converts invalid numeric dates to #N/A. The resulting dates should be December 31, 2027 through December 31, 2031.

In C3, link the first growth assumption and copy the formula through C7:

=IF(ISNUMBER(Inputs!C4),Inputs!C4,NA())

The relative reference will advance from Inputs!C4 to Inputs!C8.

Enter the remaining formulas in row 3, then copy them through row 7.

Revenue in D3:

=IF(AND(ISNUMBER(D2),D2>=0,ISNUMBER(C3),C3>-1,C3<=1),D2*(1+C3),NA())

EBIT in E3:

=IF(AND(ISNUMBER(D3),ISNUMBER(Inputs!$C$9),Inputs!$C$9>=-1,Inputs!$C$9<=1),D3*Inputs!$C$9,NA())

Cash tax in F3:

=IF(AND(ISNUMBER(E3),ISNUMBER(Inputs!$C$10),Inputs!$C$10>=0,Inputs!$C$10<=1),MAX(E3,0)*Inputs!$C$10,NA())

Using MAX(E3,0) assumes the company receives no current cash-tax benefit when EBIT is negative. A detailed tax schedule would need to account for tax-loss carryforwards and jurisdiction-specific rules.

NOPAT in G3:

=IF(AND(ISNUMBER(E3),ISNUMBER(F3)),E3-F3,NA())

D&A in H3:

=IF(AND(ISNUMBER(D3),ISNUMBER(Inputs!$C$11),Inputs!$C$11>=0,Inputs!$C$11<=1),D3*Inputs!$C$11,NA())

Capex in I3:

=IF(AND(ISNUMBER(D3),ISNUMBER(Inputs!$C$12),Inputs!$C$12>=0,Inputs!$C$12<=1),D3*Inputs!$C$12,NA())

Change in NWC in J3:

=IF(AND(ISNUMBER(D3),ISNUMBER(D2),ISNUMBER(Inputs!$C$13),Inputs!$C$13>=0,Inputs!$C$13<=1),(D3-D2)*Inputs!$C$13,NA())

UFCF in K3:

=IF(AND(ISNUMBER(G3),ISNUMBER(H3),ISNUMBER(I3),ISNUMBER(J3)),G3+H3-I3-J3,NA())

The expected operating forecast is:

Because this is an annual year-end model, use the entered year numbers as fixed discount periods. Enter the following formula in L3 and copy it through L7:

=IF(AND(ISNUMBER(A3),A3>=1,A3<=5,INT(A3)=A3),A3,NA())

With 1, 2, 3, 4, 5 in A3:A7, L3:L7 will contain exact integer periods from 1 through 5. This approach avoids differences in YEARFRAC calculations across applications and leap-year spans.

In M3, calculate the discount factor and copy the formula through M7:

=IF(AND(ISNUMBER(Valuation!$B$4),Valuation!$B$4>0,Valuation!$B$4<1,ISNUMBER(L3),L3>0),1/(1+Valuation!$B$4)^L3,NA())

Then calculate the present value of UFCF in N3 and copy the formula through N7:

=IF(AND(ISNUMBER(K3),ISNUMBER(M3)),K3*M3,NA())

The expected discounting results are:

The five present values sum to approximately $63.665936862 million, or $63.665937 million rounded to 6 decimal places.

Terminal value represents cash flows after the explicit forecast. Use both the perpetuity-growth and exit-multiple methods as cross-checks. They rely on different assumptions and aren't interchangeable.

Perpetuity-growth method

Add these labels to Valuation!A6:A9:

Terminal value - perpetuity growth
PV of terminal value - perpetuity growth
PV of forecast UFCF
Enterprise value - perpetuity growth

In B6, calculate terminal value:

=IF(AND(ISNUMBER(Forecast!K7),ISNUMBER(Inputs!C19),Inputs!C19>-1,ISNUMBER(B4),B4>0,B4<1,B4>Inputs!C19),Forecast!K7*(1+Inputs!C19)/(B4-Inputs!C19),NA())

The expected terminal value is $274.794793 million:

18.675374 × (1 + 3.0%) ÷ (10.0% - 3.0%)

The guard requires WACC to be greater than 0% and less than 100%, while terminal growth must be lower than WACC. Otherwise, the formula returns #N/A.

Discount terminal value in B7:

=IF(AND(ISNUMBER(B6),ISNUMBER(Forecast!M7)),B6*Forecast!M7,NA())

Expected result: $170.625946 million.

Next, sum the explicit cash-flow present values in B8:

=IF(COUNT(Forecast!N3:N7)=5,SUM(Forecast!N3:N7),NA())

Expected result: approximately $63.665936862 million, or $63.665937 million rounded to 6 decimal places.

Finally, calculate enterprise value in B9:

=IF(AND(ISNUMBER(B7),ISNUMBER(B8)),B7+B8,NA())

Expected result using the underlying spreadsheet values: $234.291883 million.

Exit-multiple method

Add these labels to Valuation!A16:A19:

Terminal EBITDA
Terminal value - exit multiple
PV of terminal value - exit multiple
Enterprise value - exit multiple

In B16, calculate 2031 EBITDA:

=IF(AND(ISNUMBER(Forecast!E7),ISNUMBER(Forecast!H7)),Forecast!E7+Forecast!H7,NA())

Expected terminal EBITDA: $30.765493 million.

Apply the exit multiple in B17:

=IF(AND(ISNUMBER(B16),ISNUMBER(Inputs!C20),Inputs!C20>0),B16*Inputs!C20,NA())

Expected terminal value: $246.123941 million.

Then discount the terminal value in B18:

=IF(AND(ISNUMBER(B17),ISNUMBER(Forecast!M7)),B17*Forecast!M7,NA())

Expected result: $152.823603 million.

Calculate enterprise value in B19:

=IF(AND(ISNUMBER(B18),ISNUMBER(B8)),B18+B8,NA())

Expected result using the underlying spreadsheet values: approximately $216.4895397 million, or $216.489540 million rounded to 6 decimal places.

Enterprise value represents the operating business before value is allocated among capital claims. The simplified bridge is:

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

Add these labels to Valuation!A10:A14:

Debt
Cash
Other claims
Equity value - perpetuity growth
Value per share - perpetuity growth

Link debt in B10:

=IF(AND(ISNUMBER(Inputs!C21),Inputs!C21>=0),Inputs!C21,NA())

Link cash in B11:

=IF(AND(ISNUMBER(Inputs!C22),Inputs!C22>=0),Inputs!C22,NA())

Link other claims in B12:

=IF(AND(ISNUMBER(Inputs!C23),Inputs!C23>=0),Inputs!C23,NA())

In B13, calculate equity value under the perpetuity-growth method:

=IF(AND(ISNUMBER(B9),ISNUMBER(B10),ISNUMBER(B11),ISNUMBER(B12)),B9-B10+B11-B12,NA())

Expected result:

$234.291883m - $40.000m + $10.000m - $0.000m
= $204.291883m

Now calculate value per share in B14:

=IF(AND(ISNUMBER(B13),ISNUMBER(Inputs!C24),Inputs!C24>0),B13/Inputs!C24,NA())

Expected result: $20.429188 per share, or $20.43 rounded to cents.

Add Equity value - exit multiple and Value per share - exit multiple to A20:A21.

In B20, enter:

=IF(AND(ISNUMBER(B19),ISNUMBER(B10),ISNUMBER(B11),ISNUMBER(B12)),B19-B10+B11-B12,NA())

Expected result: approximately $186.4895397 million, or $186.489540 million rounded to 6 decimal places.

In B21, enter:

=IF(AND(ISNUMBER(B20),ISNUMBER(Inputs!C24),Inputs!C24>0),B20/Inputs!C24,NA())

Expected result: $18.648954 per share, or $18.65 rounded to cents.

A company-specific equity bridge may also require preferred stock, noncontrolling interests, leases, pensions, investments, or other non-operating assets and claims. Include only items supported by the company’s financial information.

Set up Scenarios!A1:H1 as follows:

Enter these assumptions:

In D2, recalculate the present value of forecast UFCF at the scenario WACC:

=IF(AND(ISNUMBER(B2),B2>0,B2<1,COUNT(Forecast!$K$3:$K$7)=5,COUNT(Forecast!$L$3:$L$7)=5),SUMPRODUCT(Forecast!$K$3:$K$7,1/(1+B2)^Forecast!$L$3:$L$7),NA())

In E2, calculate the discounted terminal value:

=IF(AND(ISNUMBER(B2),B2>0,B2<1,ISNUMBER(C2),C2>-1,C2<B2,ISNUMBER(Forecast!$K$7),ISNUMBER(Forecast!$L$7)),Forecast!$K$7*(1+C2)/(B2-C2)/(1+B2)^Forecast!$L$7,NA())

In F2, calculate enterprise value:

=IF(AND(ISNUMBER(D2),ISNUMBER(E2)),D2+E2,NA())

In G2, calculate equity value:

=IF(AND(ISNUMBER(F2),ISNUMBER(Valuation!$B$10),ISNUMBER(Valuation!$B$11),ISNUMBER(Valuation!$B$12)),F2-Valuation!$B$10+Valuation!$B$11-Valuation!$B$12,NA())

In H2, calculate value per share:

=IF(AND(ISNUMBER(G2),ISNUMBER(Inputs!$C$24),Inputs!$C$24>0),G2/Inputs!$C$24,NA())

Copy D2:H2 through row 4. The expected values per share, rounded to cents, are:

These scenarios change only WACC and terminal growth. They don't test revenue growth, margins, capex, working capital, or other operating assumptions. When those factors are material to the valuation conclusion, vary them separately.

ModelMonkey can help with spreadsheet tasks in Google Sheets and Excel. For example, it can read ranges, analyze tables, work with formulas, organize content, and identify formula errors. It doesn't replace judgment about the forecast, discount rate, or terminal assumptions.

Prompts for this workbook could include:

Inspect Inputs!A1:C24. Check that every assumption has a label and unit, and identify blank, text, or out-of-range inputs. Do not replace missing values with zero.

Review Forecast!B3:N7. Check whether the formulas are copied consistently across all five forecast years and identify formula errors or broken references. Do not change the assumptions.

Check Valuation!B2:B21. Confirm that enterprise value equals the present value of forecast UFCF plus discounted terminal value, and that each equity bridge subtracts debt, adds cash, and subtracts other claims.

Analyze Scenarios!A1:H4. Confirm that the downside, base, and upside rows use their own WACC and terminal-growth inputs, and flag any formula errors.

Format Inputs, Forecast, Valuation, and Scenarios so inputs, formulas, percentages, $ millions, multiples, and per-share values are visually distinct. Preserve all existing formulas.

When the workflow presents a preview, review the proposed changes before proceeding. Once changes are complete, rerun the checks in the next step instead of assuming the workbook is correct.

Perform the following acceptance tests after building the workbook. They are expected outcomes from the stated formulas, not claims that the workbook has been executed.

Base-case arithmetic check

With the original inputs, confirm:

Exit-method check

Confirm:

Zero-value checks

Run these tests separately, restoring the original input after each one:

• Set one forecast growth rate to 0%. Revenue for that year should equal the prior year, and change in NWC should equal 0.
• Set the cash tax rate to 0%. Cash tax should equal 0, and NOPAT should equal EBIT.
• Set debt, cash, or other claims to 0. The bridge should retain an explicit zero rather than return a blank or error.

Blank-input check

In a copy of the workbook without validation blocking the edit, clear each of these inputs one at a time:

• Base revenue
• A forecast growth rate
• A WACC component
• Terminal growth
• Exit multiple
• Diluted shares

A dependent guarded formula should return #N/A. It shouldn't treat the blank as zero.

Pasted-invalid-value check

Paste text such as abc into a numeric input in an unvalidated copy. Because of the ISNUMBER guards and error handling, the dependent formulas should return #N/A.

Don't wrap general model formulas in IFERROR(...,0) or use N() to convert invalid entries to zero. Either approach could conceal a broken assumption. The forecast-date formulas use IFERROR(...,NA()) only to expose malformed dates as #N/A.

Rate-bound check

Typed rates outside the permitted range should be rejected by validation. Pasted invalid rates should be caught by the formulas:

• A cash tax rate, debt weight, risk-free rate, equity risk premium, or debt cost above 100% should cause the relevant calculation to return #N/A.
• Terminal growth equal to or greater than WACC should make the perpetuity-growth terminal-value formula return #N/A.
• WACC equal to or above 100% should make discount factors and the perpetuity-growth terminal value return #N/A.

Denominator check

Test the following invalid inputs separately:

Date-boundary check

With a valuation date of 12/31/2026, verify:

• Forecast!B2 equals 12/31/2026.
• Forecast dates are exactly 12/31/2027 through 12/31/2031.
• Forecast!L3:L7 contains exact integer periods 1 through 5.

Next, paste text, an invalid numeric date, or a date that isn't December 31 into an unvalidated copy. The guarded formulas in Forecast!B2:B7 should return #N/A, not a different spreadsheet error.

Negative-operating-case check

Set EBIT margin below 0% but no lower than -100%.

The expected behavior is:

• EBIT becomes negative.
• Cash tax equals 0 under the stated no-current-tax-benefit assumption.
• NOPAT equals EBIT.
• UFCF retains the negative operating effect rather than replacing it with zero.

Scenario-direction check

Holding forecast cash flows constant, confirm this ordering:

11.0% WACC / 2.0% growth < 10.0% WACC / 3.0% growth < 9.0% WACC / 4.0% growth

The expected per-share sequence is:

$15.76 < $20.43 < $28.79

Manual identity checks

Recalculate at least one forecast row outside the model and verify:

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

Then confirm one present-value calculation manually:

PV of UFCF = UFCF ÷ (1 + WACC)^period

Before rounding, check these model identities:

Enterprise value = PV of forecast UFCF + PV of terminal value

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

For a positive WACC, discount factors should decline over time. Revenue should compound from the preceding year rather than repeatedly referencing base-year revenue.

The example produces a range of $18.65 to $20.43 per share across the two base-case terminal-value methods. That difference isn't an error. The perpetuity-growth method assumes continuing cash-flow growth, while the exit-multiple method applies a market-based multiple to terminal EBITDA.

The scenario analysis produces a wider range of $15.76 to $28.79 per share because terminal value is sensitive to small changes in WACC and terminal growth. Treat the scenarios as conditional outputs, not estimates of probability or valuation certainty.

Document these limitations with the model: