DCF builder

Method

How it works

1. Start from a company or a blank sheet. Loading a company fills in its last fiscal years from its annual filings with the SEC (revenue, operating profit, depreciation and amortisation, capital expenditure, working capital, tax rate), plus its cash, debt, shares, share price and beta. The forecast starts from averages of those years; every blue cell can be changed.
2. Forecast free cash flow. Each year, revenue grows at the growth rate you set. Operating profit (EBIT) is a margin of revenue, taxed at the tax rate. Depreciation and amortisation are added back, capital expenditure is taken off, and so is any increase in working capital. What is left is the cash the business generates for all its investors: unlevered free cash flow.
3. Set the discount rate (WACC). The cost of equity comes from CAPM: the risk-free rate (the 10-year Treasury yield is suggested) plus beta times the equity risk premium, plus a country risk premium if you add one. It is blended with the after-tax cost of debt, weighted by market values or by a target debt level. An unlevered beta can be re-levered to that target.
4. Time the cash flows. With a valuation date, only the part of the first year still to come is counted (a stub period), and every cash flow is discounted from the valuation date: by default from the middle of each fiscal year, since cash arrives through the year rather than on its last day.
5. Add a terminal value. A separate terminal year (Term) represents the business once it settles into steady growth, with capital expenditure equal to depreciation by default. Its value is either its free cash flow growing forever at the terminal growth rate (perpetuity growth), or its EBITDA times an exit multiple (as a buyer might pay), discounted back to today.
6. Get to a value per share. The present values add up to the enterprise value. Taking off debt and adding back cash gives the equity value, which is divided by the number of shares and compared with today's share price.
7. Test the assumptions. The sensitivity tables show the value per share, the premium or discount to the share price and the enterprise value as the WACC and the terminal growth rate (or exit multiple) move. The Excel download rebuilds the whole model with live formulas so every number can be checked.

The calculations follow the conventions of CFI's DCF modelling course and are tested automatically against CFI's completed model, which they match to the cent.

Read before relying on it

Limitations

• The forecast is simplified. Each line is a percentage of revenue. A full model builds revenue from volumes and prices, working capital from days of receivables, inventory and payables, and taxes from a tax schedule with tax depreciation, losses carried forward and deferred taxes. None of those are modelled here.
• Company data needs checking. Figures are taken automatically from the tags companies use in their SEC filings, which are not always consistent: operating profit can include one-off items, depreciation is sometimes tagged differently, and working capital is estimated from current assets and liabilities. Check the numbers against the latest annual report (10-K).
• The bridge from enterprise value to equity is basic. Only debt and cash (with short-term investments) are counted. Lease liabilities, pensions, minority interests, preferred shares and long-term investments are not, and shares are those outstanding, not fully diluted for options and convertibles.
• The terminal value usually dominates. It is often 60–80% of the enterprise value, so small changes to the terminal growth rate, the exit multiple or the WACC move the answer a lot. Capital expenditure equal to depreciation in the terminal year suits a mature business but flatters one that is still investing heavily.
• The discount rate rests on estimates. Beta is measured from one year of daily prices against the S&P 500 and adjusted towards 1, and the 5% equity risk premium is a common convention, not a fact. Different reasonable choices give noticeably different values.
• Starting values are history, not a forecast. Averages of past years say nothing about a new product, a recession or a change of strategy. A DCF is only as good as the assumptions you put in.
• Not every company suits a DCF. Banks, insurers and property companies are better valued on book value and earnings, and companies with negative cash flow depend almost entirely on the terminal value. Only S&P 500 companies can be loaded, in US dollars.
• Data is not live. Company figures are refreshed about weekly and prices daily, after the market closes.
• This is a learning tool. It is not investment advice, and its values are not recommendations to buy or sell.