Discounted Cash Flow Model: A Step-by-Step Excel Walkthrough

Photograph of an analyst

A discounted cash flow (DCF) model values a business by forecasting its free cash flow and discounting those cash flows back to today at a rate that reflects risk. This guide builds one in five steps from Procter & Gamble’s fiscal 2026 annual report, then shows the mistakes that most often distort the result.

Key Takeaways

  • A DCF model has five steps: forecast free cash flow, build the discount rate, add terminal value, subtract net debt, then stress the result.
  • Terminal value supplies 79.2% of enterprise value in the worked example, so the terminal growth rate and discount rate drive the answer.
  • Terminal growth cannot exceed the growth rate of the overall economy; the example uses 2.5%, well below a 5.10% US 10-year Treasury yield.
  • In the example, a half-point change in the discount rate moves value per share by roughly 10% to 12%, so show a sensitivity table.
  • Every input should trace to a filing or a labelled assumption, and one check cell should confirm the model reconciles.

A DCF Model Converts Forecast Cash Into a Value Today

A DCF model is a valuation method that adds up the free cash flow, which is the cash left after taxes and the investment needed to keep the business running and growing, of each future year after discounting it back to the present. The discount rate is the annual return investors require for bearing the risk of owning that cash flow.

The model produces two headline numbers. Enterprise value is the value of the whole operating business before subtracting debt, and equity value is the part left for shareholders after net debt is deducted. Terminal value is the value of all cash flows after the explicit forecast period. Because terminal value is large, it often carries most of the enterprise value, as the worked example below shows. The eFinancialModels DCF model collection lists Excel templates built around this structure.

Worked Example: Valuing Procter & Gamble in Five Steps

The example uses reported figures from the Procter & Gamble fiscal 2026 annual report on Form 10-K, for the year ended 30 June 2026. All amounts are in USD millions, and every forecast driver is an illustrative assumption, not a prediction.

Step 1: Anchor the forecast in reported results

Start from three years of reported history so that each assumption can be compared with what the business actually did.

Item202420252026
Net sales84,03984,28487,032
Operating income18,54520,45119,748
Operating cash flow19,84617,81719,556
Capital expenditures3,3223,7734,409
Depreciation and amortization2,8962,8473,160
Operating cash flow less capital expenditures16,52414,04415,147
Inputs sheet of the discounted cash flow workbook listing reported Procter & Gamble figures, market inputs and analyst assumptions

Step 2: Forecast unlevered free cash flow

Unlevered free cash flow is the cash generated by operations before any payment to lenders. The Calculation sheet grows net sales 3.0% a year for five years and applies a 22.5% operating margin. Earnings before interest and taxes (EBIT) is taxed at the 20.77% effective rate implied by 4,233 of income taxes on 20,377 of pre-tax income, giving net operating profit after tax (NOPAT). Depreciation and amortization (3,160 in fiscal 2026) and capital expenditures are held at their fiscal 2026 shares of sales, 3.63% and 5.07% respectively, as an illustrative assumption. Depreciation and amortization is added back, capital expenditures are deducted, and working capital, the cash tied up in inventory and receivables net of payables, is assumed to absorb 5% of each year’s sales increase.

Every line is rounded to a whole number and each column adds exactly, so the first forecast year yields 14,564 of free cash flow, rising to 16,391 in year five. That path sits close to the 14,044 to 16,524 range of operating cash flow less capital expenditures reported over 2024 to 2026. This benchmark is levered, because operating cash flow is after interest, so it is only an approximate sanity check.

Line20272028202920302031
Net sales89,64392,33295,10297,955100,894
EBIT at 22.5% of sales20,17020,77521,39822,04022,701
Tax on EBIT at 20.77%4,1894,3154,4444,5784,715
NOPAT (EBIT less tax)15,98116,46016,95417,46217,986
Depreciation and amortization at 3.63% of sales3,2553,3523,4533,5573,663
Capital expenditures at 5.07% of sales4,5414,6774,8184,9625,111
Increase in working capital131134139143147
Unlevered free cash flow14,56415,00115,45015,91416,391
Discount factor at 7.45%0.93070.86610.80610.75020.6982
Present value of free cash flow13,55512,99212,45411,93911,444
Calculation sheet showing the five-year free cash flow forecast, the WACC build and the bridge from enterprise value to equity value

Step 3: Build the discount rate

Free cash flow to the whole business is discounted at the weighted average cost of capital (WACC), the blended after-tax cost of equity and debt, as the Chartered Financial Analyst (CFA) Institute free cash flow valuation reading states for firm-level cash flows. The cost of equity is the return shareholders require, built from the risk-free rate (the yield on a government bond), beta (how strongly the stock moves with the overall market) and the equity risk premium (the extra return investors demand for holding stocks instead of bonds).

The inputs are a 5.10% US 10-year Treasury yield on 17 November 2026 and a 4.23% US equity risk premium at the start of 2026 from Damodaran. The premium dates from January 2026 and the yield from November 2026, a simplifying pairing.

With an assumed beta of 0.65, the cost of equity is 5.10% + 0.65 × 4.23% = 7.85%, rounded from 7.8495%. An assumed 5.7% pre-tax cost of debt, which is the interest rate on borrowing, becomes 5.7% × (1 − 20.77%) = 4.52% after tax. Weighting equity 88% and debt 12%, an assumed capital structure, gives a WACC of 0.88 × 7.85% + 0.12 × 4.52% = 7.45%.

Step 4: Add terminal value and bridge to equity value

Terminal value at the end of year five equals the final year’s free cash flow, grown once at 2.5% and divided by the gap between the WACC and that growth rate: 16,391 × 1.025 ÷ (7.45% − 2.5%) = 339,410. Discounted back five years at a factor of 0.6982, it is worth 339,410 × 0.6982 = 236,976 after rounding. Adding the 62,384 present value of the five forecast years gives an enterprise value of 299,360.

Subtracting net debt of 24,196 (debt of 34,138 less cash of 9,942) leaves an equity value of 275,164. Divided by 2,422.5 million diluted weighted-average shares, used as a simplifying proxy for period-end shares, that is 113.59 USD per share. Leases, pension deficits and noncontrolling interests are outside this simplified bridge, and a full valuation should deduct them. Terminal value is 79.2% of enterprise value.

Column chart showing the present value of each forecast year's free cash flow next to a far taller bar for terminal value

The workbook cells behind these steps use plain formulas, rounded as in the tables.

LineCellFormula
Free cash flow, year 1Calculation!D13=D9+D10-D11-D12
Discount factor, year 1Calculation!D14=1/(1+$C$21)^D4
WACCCalculation!C21=C20*C18+Inputs!C22*C19
Terminal valueCalculation!C25=H13*(1+Inputs!C26)/(C21-Inputs!C26)
Enterprise valueCalculation!C27=C24+C26

Step 5: Stress the result with sensitivity analysis

A sensitivity analysis is a table that shows how the output changes when key inputs move. The Results sheet recomputes value per share for five WACC levels and five terminal growth rates using live formulas. Lowering WACC by half a point lifts value per share to 127.50, up 12.2%, while raising it by half a point cuts it to 102.23, down 10.0%. Raising terminal growth from 2.5% to 3.0% adds 10.1%, reaching 125.11.

Results sheet with key valuation outputs, a five by five sensitivity grid and a column chart of present value of free cash flow by year

A check cell on the Calculation sheet reads OK only when enterprise value reconciles two ways, WACC exceeds terminal growth, and equity value equals enterprise value less net debt.

Common DCF Mistakes and the Check for Each

The discount rate and the terminal value are where input errors have the largest effect, because small changes compound there. The sensitivity grid above shows the effect in numbers. Two anchors: the 5.10% US 10-year Treasury yield on 17 November 2026 and a 4.23% US equity risk premium at the start of 2026 show the rates a model should stay consistent with, and the example’s terminal growth sits below that yield.

MistakeWhy it distorts valueCheck
Terminal growth above economic growthDamodaran writes that the constant growth rate cannot be greater than the overall growth rate of the economyCompare growth with long-run economic growth and the Treasury yield
Discounting firm-level cash flow at cost of equityOverstates the rate and understates valueUse WACC for unlevered cash flow
Valuing a peak or trough year foreverOne abnormal year gets capitalized into the terminal valueAverage margins across a cycle
Capital expenditures below depreciation in perpetuityA growing business cannot shrink its asset baseKeep terminal reinvestment above depreciation
Forgetting net debtEnterprise value is not shareholder valueSubtract debt, add cash

Frequently Asked Questions

How many years should a DCF forecast cover?

Five to ten years is typical, long enough for growth and margins to settle before the terminal value takes over. A mature, stable company can use five explicit years, as in this example, while a fast-growing business may need ten so growth fades gradually instead of dropping to the perpetual rate at once. The test is whether the final forecast year could repeat indefinitely.

Should the discount rate change from year to year?

Most models hold one rate across all years, which is the simplest approach and usually adequate. A changing rate becomes useful when the capital structure is expected to shift materially, for instance during a leveraged acquisition that is repaid over time. In that case the model needs a separate rate for each period, and discount factors are built by multiplying the yearly factors together.

Is a higher equity value per share the same as a buy signal?

No. The 113.59 figure is an output of stated assumptions and was never compared with a market price, because a DCF answers what the cash flows are worth under those inputs. Investors then compare that estimate with the traded price and with ranges from other methods. If a plausible change in WACC or growth flips the conclusion, the model is signalling uncertainty, not a clear opportunity.

Why discount at year end instead of mid-year?

Year-end discounting treats each year’s cash flow as arriving on the final day, which is easy to follow and slightly conservative. Mid-year discounting assumes cash arrives evenly through the year and shifts every exponent down by half a year, raising the present value of the forecast cash flows by about 3.7% at a 7.45% WACC. Either convention is acceptable if applied consistently and disclosed with the other assumptions.

Which cells in a DCF workbook should be hard-coded?

Only inputs should be typed in: reported figures, market data and labelled assumptions. Every other cell should be a formula, including the sensitivity grid, so changing one input updates the whole model. A single check cell that confirms the valuation reconciles catches broken links early. Pasted values over formulas quietly break the link between inputs and results, so no reviewer can trust the output.

Conclusion

A DCF model is only as reliable as its discount rate, its terminal assumptions and the discipline of its inputs, and the Procter & Gamble example shows that terminal value alone can carry 79.2% of enterprise value. Practitioners who want a ready structure can start from the eFinancialModels Free Discounted Cash Flow (DCF) Valuation Model Template.

author avatar
eFinancialModels Team Content Manager
The eFinancialModels Team showcases the combined expertise of seasoned professionals in financial modeling, valuation, and business analysis. Our goal is to share practical knowledge, insights, and best practices drawn from real-world experience across industries such as renewable energy, real estate, SaaS, manufacturing, and finance. Through our articles and templates, we aim to make complex financial modeling concepts accessible and actionable—helping entrepreneurs, investors, and finance professionals make smarter business decisions.
Leave a Reply