To value a stock with a DCF model in Excel, forecast the company's free cash flow to the firm (FCFF) for about five years, discount those cash flows and a terminal value at the weighted average cost of capital (WACC), subtract debt to get equity value, and divide by shares outstanding. The result is an intrinsic value per share. If it is well above the market price, the stock may be undervalued; if it is below, the stock may be overvalued. This article walks through the FCFF version of the model and shows which inputs MarketXLS can fill from fundamentals data.
A discounted cash flow (DCF) model is one of the most widely used equity valuation models. Its principle is that a business is worth the present value of its expected future cash flows, discounted at a risk-adjusted rate.
Three types of DCF models
There are three common DCF models: the Dividend Discount Model (DDM), Free Cash Flow to Equity (FCFE), and Free Cash Flow to Firm (FCFF). This article uses FCFF. FCFF is the cash flow available to all capital providers (equity holders and bondholders), so it is discounted at WACC and debt is subtracted at the end.
The five steps of an FCFF DCF valuation
- Forecast the firm's free cash flows for a short period (usually 5 years).
- Estimate a long-term growth rate and calculate a terminal value at the end of that period.
- Discount the forecast cash flows and the terminal value at WACC.
- Subtract debt from firm value to get equity value.
- Divide equity value by shares outstanding to get intrinsic value per share.
Forecasting cash flows far into the future is unreliable, so the model forecasts annual cash flows only for the near term and uses one long-term growth rate after that.
Step 1: Forecast free cash flow to the firm
Start from the most recent annual free cash flow and project it forward with a near-term growth rate. Free cash flow to the firm is calculated as:
Free Cash Flow = EBIT × (1 − tax rate) + Depreciation & Amortization − Change in Working Capital − Capital Expenditure
MarketXLS historical fundamentals functions return each component from reported financial statements. For example, for fiscal year 2024:
- EBIT:
=hf_Operating_Income("AAPL", 2024) - Depreciation & amortization:
=hf_Depreciation_Amortization_and_Accretion("AAPL", 2024) - Capital expenditure:
=hf_Capital_Expenditure("AAPL", 2024) - Reported free cash flow (operating cash flow minus capex), as a cross-check:
=hf_Free_Cash_Flow("AAPL", 2024)
Step 2: Calculate the discount rate (WACC)
Use the weighted average cost of capital because FCFF is cash flow to the whole firm, not just to equity. WACC needs these inputs:
- Capital structure: market value of equity and debt, which set the weights.
- Cost of equity: risk-free rate, market risk premium, and the stock's beta (
=Beta("AAPL")). - Cost of debt: pre-tax cost of debt and the effective tax rate.
The risk-free rate, market risk premium, and cost of debt are partly assumptions. Beta, market value of equity, and total debt (=hf_Total_Debt("AAPL", "ly")) can be pulled from data.
WACC = we × ke + wd × kd × (1 − t)
Where:
- we is the equity weight in the capital structure
- ke is the cost of equity
- wd is the debt weight in the capital structure
- kd is the pre-tax cost of debt
- t is the tax rate
Step 3: Calculate the terminal value
The terminal value captures the firm's value for all years after the forecast period. Calculate it as a growing perpetuity using a long-term stable growth rate (g):
Vt = CFt × (1 + g) / (k − g)
Where:
- CFt is the cash flow in the final forecast year
- k is the discount rate (WACC)
- g is the long-term growth rate, which must be lower than k
Step 4: Discount all cash flows to present value
Discount each forecast year's cash flow and the terminal value back to today at WACC, then add them. In Excel, =NPV(WACC, cash_flow_range) handles the forecast years; discount the terminal value separately by dividing it by (1 + WACC)^5. The sum is the intrinsic value of the entire firm.
Step 5: Calculate intrinsic value per share
Subtract debt from firm value to get equity value, then divide by shares outstanding:
Equity Value = Firm Value − Debt
Intrinsic Value per Share = Equity Value / Shares Outstanding
=Shares_Outstanding("AAPL") returns the current share count. Compare the intrinsic value per share with the market price (=Last("AAPL")) to judge whether the stock looks undervalued or overvalued. A DCF result is only as good as its growth and discount-rate assumptions, so test several scenarios. This is educational content, not investment advice.
How the DCF spreadsheet is laid out
The DCF model in Excel shown below values one stock at a time and can be modified for your own assumptions.
DCF Model in Excel
- The model uses a 5-year forecast period and calculates the terminal value at the end of year 5. You can change the length of the forecast period.
- Yellow cells are assumptions that you enter (growth rates, risk-free rate, market risk premium, cost of debt, tax rate).
- Green cells contain market and fundamentals data for the stock, fetched with MarketXLS historical fundamentals functions.
- White cells are formulas that calculate automatically.
On the Microsoft 365 add-in (Excel for Mac and Excel for the web), MarketXLS formulas use the mxls. prefix and some historical fundamentals functions have different names (for example =mxls.hf_freeCashFlow). For a fuller two-stage template, see the MarketXLS templates library; function details are at /formulas.
