Discounted Cash Flow Model (DCF)

We've all heard of EPS, EBIT, EBITDA, Free Cash Flow and Operating Cash Flow. They are all used by analysts in trying to value a stock. What the analysts are trying to get at is cash flow. Ultimately, every financial valuation ends with a cash projection into the future, then discounting it back into today's dollars. Hence the term discounted cash flow (or DCF).

In my weekly stock analyses one of the methods I use to value a stock is using a 20-Year DCF. The model looks at cash flow from dividends and assumes the stock is sold in year 20. The Excel model [20-Year-DCF.xls] is available in my Toolbox which can always be accessed by clicking Tools on the menu above.

The model is divided into four sections. Each are described below:

I. Input: All cells requiring your input are shaded yellow. They include:

• Symbol: Enter the stock's symbol here.
• Year: The current year.
• Discount Rate: Rate at which future cash flows are discounted back (see note below) .
• Max Div. Growth: Enter here the cap (maximum) for Div. Growth Rate.
• EPS Growth Rate: Calculated historical EPS growth rate. [NOT AN INPUT]
• Div. Growth Rate: Calculated historical dividend growth rate. [NOT AN INPUT]
• P/E: Calculated P/E (Price/Earnings) ratio. [NOT AN INPUT]

In addition, certain calculated fields can be over-written when better information is available. They include:

• EPS Growth Rate: Override rate for the historically calculated EPS Growth Rate.
• Div. Growth Rate: Override rate for the historically calculated Div. Growth Rate.
• Current Year EPS: Override the calculation for the current year's EPS.
• P/E: Override P/E ratio for the historically calculated P/E.

You will also need to enter historical information for these items:

• E.P.S: Historical earnings per share.
• Annual Dividend/Share: Annual dividend per share and the current year's estimate.
• P/E Ratio - High: High price/earnings ratio for the year.
• P/E Ratio - Low: Low price/earnings ratio for the year.
• Year Over Year Dividend Growth Rate: Calculated annual dividend growth rate.
• Compound Annual Dividend Growth Rate: Calculated compound annual dividend growth rate.

Note on Cost of Capital: The DCF calculation is very sensitive to the Cost of Capital. The default 15% is a rate that I have found fairly consistent with what the market has historically used. In short, the discount rate is inversely related to the calculated fair value of the stock. Its derivation can be quite complex and is beyond the scope of this article. For those interested, I will refer you this detailed explanation on Wikipedia.

II. Projected Information: This section projects 20 years dividends and EPS using the EPS Growth Rate and Div. Growth Rate above.

III. Share Price Value Based on Discounted Cash Flow: This section calculates the estimated fair value of the stock (i.e. the answer).

IV. Disclaimer: This model is for illustrative and educational purposes only. The author and Dividends4Life makes no claims or assertions as to the model's accuracy, completeness, appropriateness of use, or any other claim or assertion. You should not rely on this model or base any financial decisions on it.

A DCF model is a great tool in helping to determine an entry (or sales) point for a stock. As with with any model, it is only as good as its inputs. Since it is forward looking, there are no hard and fast sources for the inputs. I hope you enjoy using it as much as I do!


Related Articles:

• My Dirty Little Secret
• Dividend Investing With ETFs
• It Was An Odd Odyssey
• The Magnificent Marvelous Money Machine
• Rising Dividends = Rising Returns