Learning
Steps for Performing a DCF Model in Excel
Step 1: Gather Financial Information
- Obtain the company's financial statements, including the income statement, balance sheet, and cash flow statement.
- Collect relevant financial metrics and forecasts, such as revenue growth rates, operating margins, capital expenditures, and changes in net working capital.
Step 2: Forecast Free Cash Flows (FCF)
- Forecast Revenue Growth: Project the company's revenue for the next 5-10 years based on historical growth rates or analyst estimates.
- Estimate Operating Margins: Project future operating margins to calculate operating income.
- Calculate Taxes: Apply the corporate tax rate to the operating income to find the after-tax operating income.
- Adjust for Non-Cash Items: Add back non-cash expenses (like depreciation) to the after-tax operating income.
- Subtract Capital Expenditures: Estimate future capital expenditures necessary for maintaining or growing the business.
- Adjust for Working Capital: Calculate changes in working capital that affect cash flows.
Free Cash Flow Formula
$ \text{FCF} = (\text{Net Income} + \text{Depreciation/Amortization}) - \text{Changes in Working Capital} - \text{Capital Expenditures} $
Step 3: Calculate the Discount Rate (WACC)
- Calculate the Weighted Average Cost of Capital (WACC) which serves as the discount rate in the DCF model. The WACC is a weighted average of the cost of equity and the after-tax cost of debt.
WACC Formula
$ \text{WACC} = (\frac{E}{V} \times \text{Cost of Equity}) + (\frac{D}{V} \times \text{Cost of Debt} \times (1 - \text{Tax Rate})) $ Where:
- $ (E) $ is the market value of the equity,
- $ (D) $ is the market value of the debt,
- $ (V) $ is the total market value of the firm (equity + debt),
- $ (\text{Cost of Equity}) $ can be estimated using the Capital Asset Pricing Model (CAPM),
- $ (\text{Cost of Debt}) $ is the average interest rate the company pays on its debt.
Step 4: Discount Future Cash Flows to Present Value
- Use the WACC calculated in Step 3 as the discount rate.
- Discount the forecasted FCFs over the forecast period back to their present value.
Present Value of a Single Future Cash Flow Formula
$ \text{PV} = \frac{\text{FCF}}{(1 + \text{WACC})^t} $ Where:
- $ (PV) $ is the present value,
- $ (FCF) $ is the free cash flow in year (t),
- $ (WACC) $ is the weighted average cost of capital,
- $ (t) $ is the time in years.
Step 5: Calculate Terminal Value
- After the forecast period, assume the company grows at a constant rate forever (perpetual growth rate).
- Calculate the terminal value using the Gordon Growth Model.
Terminal Value Formula (Gordon Growth Model)
$ \text{TV} = \frac{\text{FCF}_{n+1}}{(\text{WACC} - \text{G})} $ Where:
- $ (\text{TV}) $ is the terminal value,
- $ (\text{FCF}_{n+1}) $ is the free cash flow in the first year beyond the forecast period,
- $ (G) $ is the perpetual growth rate.
Step 6: Calculate Enterprise Value
- Discount the Terminal Value back to its present value.
- Sum up the present values of the forecasted FCFs and the present value of the Terminal Value to get the Enterprise Value.
Step 7: Determine Equity Value
- Subtract the net debt from the Enterprise Value to get the Equity Value.
- Divide the Equity Value by the number of outstanding shares to get the intrinsic value per share.