How to Build a DCF Model in Excel

A discounted cash flow model values a business as the present value of the cash it will generate. You project unlevered free cash flow for a forecast horizon, discount each year at the weighted average cost of capital, add a terminal value for the years beyond, and sum to enterprise value. From there you bridge to equity value and divide by shares to reach a per share figure.

The structure of a DCF

A DCF has four moving parts: a free cash flow forecast, a discount rate, a terminal value, and a bridge from enterprise value to equity value. The core present value relationship is PV = FCF / (1 + WACC) ^ t, where t is the number of years from the valuation date.

Use unlevered free cash flow so the cash flows match the discount rate. Unlevered FCF is EBIT * (1 - tax) + D&A - capex - change in NWC. Because it is before financing, you discount it at WACC, which blends the cost of equity and the after tax cost of debt.

Worked example: a five year forecast

Assume unlevered free cash flow grows from 100 in year 1 to 146 in year 5, a WACC of 10 percent, and a terminal growth rate of 2.5 percent. The terminal value uses Gordon growth: TV = FCF5 * (1 + g) / (WACC - g).

1. Lay the forecast years across C5:G5 and put each year's unlevered FCF in C6:G6.
2. Build a discount factor row with =1/(1+$B$2)^C5 where $B$2 holds WACC and C5 holds the period number.
3. Multiply FCF by its factor: =C6*C7 to get the present value of each year, then sum them with =SUM(C8:G8).
4. Compute terminal value at year 5: =G6*(1+$B$3)/($B$2-$B$3) where $B$3 holds g.
5. Discount the terminal value back with the year 5 factor: =TV*G7.
6. Enterprise value is the sum of discounted FCFs plus the discounted terminal value.

Sum of discounted FCF is about 454. Terminal value is 146*1.025/(0.10-0.025) = 1996, discounted at 0.621 to about 1239. Enterprise value is roughly 1693.

Bridging to equity value and per share

Enterprise value belongs to all capital providers. To reach the value of common equity, subtract net debt and other claims that rank ahead of common shareholders.

• Equity value = enterprise value - net debt - preferred - minority interest + investments in associates.
• Net debt is total debt minus cash and equivalents; pull it from the latest balance sheet, not the forecast.
• Divide equity value by diluted shares outstanding to get value per share.
• Keep WACC and growth in a clearly labeled assumptions block so a sensitivity table can flex them.
• Add a two way data table on WACC and g to show the value range rather than a single point estimate.

Common DCF errors

The most frequent mistake is discounting the wrong cash flow with the wrong rate. Unlevered FCF pairs with WACC; levered FCF pairs with the cost of equity. Mixing them double counts or omits the financing effect.

A second trap is a terminal growth rate above long run nominal GDP. A perpetual 5 percent grower eventually exceeds the whole economy, so keep g near expected long run inflation plus modest real growth, often 2 to 3 percent.