DCF valuation in a spreadsheet, step by step with a worked example

A discounted cash flow (DCF) valuation estimates what a business is worth today as the present value of the cash it is expected to generate. The spreadsheet work has six steps: forecast unlevered free cash flow, compute the weighted average cost of capital (WACC), estimate a terminal value, discount everything to today, bridge from enterprise value to equity value per share, and test the result with a sensitivity table and sanity checks.

The example below is a hypothetical company with $1,000 million of revenue. Every input is illustrative. With the assumptions shown, enterprise value is $2,212.6 million, equity value is $1,612.6 million and the value per share is $26.88. Terminal value makes up 78.6% of enterprise value, which is the number to keep in mind while reading. For the concept itself, see what is discounted cash flow.

Step 1: forecast unlevered free cash flow

Unlevered free cash flow (UFCF) is the cash the operations generate before any payment to lenders or shareholders. For each year:

=EBIT*(1-tax_rate)+DA-Capex-ChangeInNWC

EBIT times one minus the tax rate is NOPAT, the operating profit after tax as if the company had no debt. Add back depreciation and amortization (D&A), because it reduced EBIT but cost no cash. Subtract capital expenditure (capex) and the increase in net working capital (NWC), because both consume cash. The change in NWC is this year's NWC minus last year's: =NWC_this_year-NWC_last_year, so growth in receivables and inventory reduces cash flow.

The example assumes a 25% tax rate, D&A at 4% of revenue, capex at 5% of revenue and NWC at 10% of revenue. Amounts are in $ millions and rounded to one decimal, so a column can differ from its components by 0.1.

Year 1 by hand: 151.2 × 0.75 = 113.4; plus 43.2; minus 54.0; minus 8.0 (10% of the 80.0 increase in revenue) gives 94.6.

Step 2: compute WACC

WACC is the discount rate for UFCF: the blended cost of the capital that funds the business.

• Cost of equity from the capital asset pricing model (CAPM): =rf+beta*ERP = 4.0% + 1.10 × 5.0% = 9.5%.
• After-tax cost of debt: =Kd*(1-tax_rate) = 6.0% × 0.75 = 4.5%.
• Weights: 70% equity and 30% debt.
• WACC: =We*Ke+Wd*Kd_after_tax = 0.70 × 9.5% + 0.30 × 4.5% = 6.65% + 1.35% = 8.00%.

The risk-free rate should match the currency and horizon of the cash flows, the equity risk premium (ERP) is an estimate, and beta comes from a regression or from comparable companies. Weights should reflect market values, which creates a circularity because the equity value depends on WACC. The usual way out is to use a target capital structure and check afterward that the result is consistent, as in step 5.

Step 3: estimate the terminal value

A five-year forecast does not end the business, so the cash flows after year 5 need a terminal value (TV).

Perpetuity growth (Gordon growth). Assume UFCF grows at a constant rate g forever: =FCF5*(1+g)/(WACC-g). With g = 2.5%, that is 131.96 × 1.025 / (0.080 - 0.025) = 135.26 / 0.055 = 2,459.3. Year-5 EBITDA is EBIT plus D&A, 200.64 + 53.51 = 254.15, so the implied multiple is 2,459.3 / 254.15 = 9.7x.

Exit multiple. Apply a multiple to final-year EBITDA: =EBITDA5*multiple. At 9.0x, TV = 254.15 × 9.0 = 2,287.3.

Use one method as the base case and the other as a cross-check. To translate an exit-multiple TV into the growth rate it implies on an end-of-year basis, use =(TV*WACC-FCF5)/(TV+FCF5). At 9.0x that is 2.11%, which sits close to the 2.5% assumption.

Step 4: discount to today

With end-of-year discounting, year t is discounted by =1/(1+WACC)^t. The mid-year convention assumes cash arrives evenly during the year, so the exponent is t - 0.5: =1/(1+WACC)^(t-0.5).

The five present values add to 473.2. The terminal value needs matching treatment. Under the mid-year convention, a Gordon growth TV is discounted at t = 4.5 (equivalently, multiplied by (1 + WACC)^0.5 and discounted at 5). An exit-multiple TV is a sale price at the end of year 5, so it is discounted at t = 5.

Mid-year discounting lifts every present value by (1.08)^0.5 = 1.0392, so enterprise value is 3.9% higher. It is a better description of cash flows that arrive through the year, but it is also an assumption. State which one you used. An easy mistake is discounting a perpetuity-growth TV at t = 5 inside an otherwise mid-year model, which understates the present value of the terminal value by about 3.8%.

Step 5: from enterprise value to value per share

Enterprise value (EV) belongs to all capital providers, so subtract the claims that come before shareholders and divide by diluted shares:

=(EV-Debt+Cash-Preferred-MinorityInterest)/DilutedShares

The example has debt of $690.0 million, cash of $90.0 million, no preferred stock or minority interests, and 60.0 million diluted shares. Net debt is 600.0.

• Equity value: 2,212.6 - 600.0 = 1,612.6
• Value per share: 1,612.6 / 60.0 = $26.88

Check the WACC weights: debt / (debt + equity) = 690.0 / (690.0 + 1,612.6) = 30.0%, which matches the 30% assumed in step 2. Use diluted shares (options and convertibles counted by the treasury stock method), and treat leases and pension deficits consistently with how the cash flows were defined.

Step 6: build the sensitivity table

Small changes in WACC and g move value a lot. Show a grid of value per share. Excel's Data Table feature does this, but the result depends on the application. A portable alternative is to write the full valuation as one formula per cell:

=(SUMPRODUCT(FCF/(1+w)^(t-0.5))+FCF5*(1+g)/(w-g)/(1+w)^4.5-NetDebt)/Shares

where FCF and t are the ranges holding the five cash flows and the five year numbers, w refers to the column header (WACC) and g to the row header (growth).

With g at 2.5%, moving WACC from 8.0% to 7.0% raises the value by 30.3% to $35.02, and moving it to 9.0% lowers it by 21.0% to $21.24. The corner values differ by a factor of 2.6. Present the table, not just the center cell. See how to calculate percentage change for the formula behind those percentages.

Sanity checks

• g must be below WACC. The Gordon formula breaks as g approaches WACC. At WACC = 8.0% and g = 7.9%, this model returns $1,676.36 per share. Add a cell that flags =g>=WACC.
• g should not exceed long-run nominal growth of the economy the company operates in. A higher rate implies the company eventually outgrows it.
• Terminal value share of EV. Here, 78.6%. Across the sensitivity grid it ranges from 72.4% to 85.6%. The higher the share, the more the answer rests on a few terminal assumptions.
• Implied exit multiple. The Gordon TV implies 9.7x EBITDA. Compare it with where similar companies trade.
• Reinvestment consistency. In year 5, the company reinvests (capex - D&A + increase in NWC) / NOPAT = 18.5 / 150.5 = 12.3% of NOPAT. Growth of 2.5% on that reinvestment implies new capital earns 2.5% / 12.3% = 20.3%, well above the 8.0% WACC. That is a generous assumption; to be more conservative, raise terminal capex relative to D&A.
• Normalized terminal year. Year 5 still carries NWC investment for 4% growth. Rebuilding the terminal year at 2.5% growth gives UFCF of 137.2 instead of 135.3 and lifts enterprise value by 1.1%.

A checklist

• Define UFCF once and use the same definition for every year.
• Keep inputs (rates, growth, margins) in one block and calculations in another. The inputs, calculations, outputs pattern applies directly.
• Write down the discounting convention and apply it to the terminal value as well.
• Check that WACC weights agree with the capital structure the valuation produces.
• Report the sensitivity grid, the terminal value share and the implied multiple next to the point estimate.
• Label every input as an assumption with a source and a date.

The Discounted cash flow (DCF) valuation model template from Sheet Reserve follows this structure with example data, so you can replace the inputs with your own.