Fair Value Estimate – Discounted Cash Flow Method
In my stock analysis process, among others, one of the methods I use to estimate fair value of a given stock is using 15 year discounted cash flow (DCF). At a fundamental level, what DCF does is, it uses future cash flow estimates and then discounts it to determine the present value. Let us discuss both of these parameters.
Future cash flow estimate: There are myriad of different ways to estimate future cash flow of any corporation. These are based of EPS, free cash flow, operating cash flow, net profit, pre-tax profit, etc. I am not qualified to judge or make any comment on the correctness or appropriateness of using any of above parameters. I believe based on a specific objective any or all could be correct. I look at DCF methodology from my own investing situation and objectives.
I am a long term dividend investor and hence, I use estimates of cash flow from dividends. In addition, I also include an estimation of cash I would receive from selling the stock after 15 years.
Discount Rate: This is the rate at which future cash flow is discounted to determine present value. The general practice is to use cost-of-capital that is available in any given market. I have observed that, typically, discount rate is in the range of 12% to 18% depending upon individual scenarios.
In my calculation, I tend to use 12% in most cases.
The excel model of DCF calculation is available in my toolbox menu. It will be always available here so that you may access it any time you want.
Data Input Cells:
All the fonts that are in red color are inputs to this calculation sheet. These include:
- Symbol: This is either the stock’s symbol or company name.
- Year: The current year.
- Discount Rate: Rate at which future cash flows are discounted back to present value. I discussed this briefly above in Discount Rate.
- EPS: Historical earnings per share for each year.
- Annual Dividend per Share: Historical dividends paid on per share basis. This is actual Rupee amount and not a percentage value.
- PE Ratio: Historical PE ratio for each year.
Auto Calculated Parameter
These values of these parameters are automatically calculated.
- EPS Growth Rate: This is calculated using historical EPS growth rate. I use [average EPS growth rate] – [0.5 x Standard Deviation]
- Dividend Growth Rate: This is calculated using historical dividend growth rate. I use [average dividend growth rate] – [0.5 x Standard Deviation]
- Estimated PE: This is calculated using historical PE ratio. I use [Minimum of (current PE ratio) and (Historical Average PE ratio)]
Many times, I decide to ignore auto calculated values, and hence I have provided additional cells to manually override the auto calculations. These parameters include EPS growth rate, Dividend Growth Rate, Current Year EPS, and PE ratio.
Future Cash Flow Estimation: Here, I am estimating two parameters viz. (1) dividend cash flow for 15 years; and (2) EPS over the 15 year. For both parameters, I use the dividend growth rate and EPS growth rates that we estimated in above step.
Share Price Value Based on Discounted Cash Flow: Here, I am estimating the present fair value of the stock. This is our final answer.
As mentioned earlier, I use DCF model to estimate the fair value of the stock. I also use other criteria, which I will discuss in future posts. As with any model it is only as good as its inputs (i.e. garbage in, garbage out).
Disclaimer: TIP Guy does not claim model’s accuracy, appropriateness of use, assertions or any other definitiveness. TIP Guy’s intention in sharing this description and model is for illustration and education purpose only. TIP Guy uses this model and takes full responsibility of its consequences. If you use this model, you take full responsibility for all its consequences.
cash flow, DCF, discount rate
Can you please point some sites where i can get the historical data that is required for this excel? last 10… 12 years
You’re doin a great job… the articles are of real interest
Thanks
Sam
Sam,
All information is not at single resource. You may refer, moneycontrol, rediff moneywiz, NSE website. Between these three you should be able to find all information.
For this calculation, many parameters need to be calculated (or deduced) from three financial statements.
Best Wishes,
Hi,
Very useful post. I usually calculate IV and then discount 40-50% on that as safety margin. I also calculate Enterprise value of the company to make sure Iam buying the company at a discounted rate.
I have couple of questions on this method
1.Do you apply discount to this value ( I dont think so but wanted a confirmation)
2. The final value is is USD. Do we have to make any changes to the formulae to make it applicable to Indian scenarios?
Hello Karthik,
(1) No, I do not apply discount to IV or EV. I apply discount to cash flow value (cash flow value = dividends + P/E based share price).
(2) That was an error. It is display format of the cell numbers. It does not affect numerical value. I have now updated it to INR. Thank you for bringing this to my attention. Appreciate it.
Best Wishes,
Sir,
Few questions regarding the calculations:
–> For Historical PE ,do we take closing price as on 31st Mar of each financial year or the average of prices for that year.
–> No dividends are paid during some years,In such cases the calculation shows error(since it attempts do div by 0 for div growth rate).How do we take care of it.
Thanks
Hello Saif,
(1) I take average price for that year
(2) I consider it as zero for calculations.
Hope this helps.
Best Wishes,
Tip Guy,
you said you take the average price of the scrip for the entire year. what about the EPS? it would be the year end EPS, right?
Hello Nikhil,
Yes, year end EPS.
Hi,
Can you please explain to me the EPS and dividend growth rate calculation? I have not understood why (0.5*Standard deviation) is used. What standard deviation explains. deviation from the mean??