Automated Stock Analyzer – Now with Reverse DCF Valuation

 

Automatically analyze stocks and determine their intrinsic value or fair value with this Excel sheet.

The analyzer pulls financials from morningstar, adjusted stock price from money control and calculates intrinsic value five different ways!

1) Price Multiple Model

2) Sustainable Growth Rate

3) Book Value Growth Rate (Buffett's approach to valuation)

4) Discounted Cash Flow (DCF) Thanks to Debasish Hazra  and Ninand Diveakr for pointing out a bug. This is now corrected

5) Reverse DCF

DCF

According to investopedia,

"The approach involves explaining how much free cash flow* the company will produce for investors over, say, the next 10 years, and then calculating how much investors should pay for that stream of free cash flows based on an appropriate discount rate. Depending on whether it is above or below the stock's current market price, the DCF-produced target price tells investors whether the stock is currently overvalued or undervalued".

The free cash flow is an amount available for the company to expand its operations and create value to share holder.

For a tutorial on DCF check out this  tutorial  by investopedia.

The trouble with this method is that it forecasts future (next 3/5Y) free cash flows. Since this can be unreliable, one tries to 'reverse engineer' the DCF.

Reverse DCF

Here the rate at which free cash flow would grow is not assumed. Instead it is calculated for the current market price.

That is, if the current market price is equal to the intrinsic value of the stock, what should the future growth rate of free cash flow be.

If this growth rate is too high or unrealistic, the stock is overvalued.

Do suggest ways in which the sheet can be improved. If you like it, do share this page. Here are some screenshots

automated-stock-analyzer-version-5

Automated-stock-analyzer-2
Input page for getting financials from Morning Star

 

 

 

Automated-stock-analyzer-3
Input page for getting stock price from moneycontrol
Excel-stock-analysis-spreadsheet-1
The sheet where Morning Star India data must be pasted by the user Morning Star India data
Excel-stock-analysis-spreadsheet-2
The analysis sheet
Excel-stock-analysis-spreadsheet-3
All metrics from the analysis sheet can be graphically analyzed
Excel-stock-analysis-spreadsheet-4
Valuation with price multiple model
Excel-stock-analysis-spreadsheet-5
The Warren Buffett approach to valuation
Dicsounted-Cash-Flow-Valuation
Discounted Cash Flow Valuation
excel-reverse-dcf
Reverse DCF

Download the automated stock analyzer version 5.0

Note: Always check if the stock price data downloaded from moneycontrol is correct by clicking on the link provided in the stock price page before proceeding. Let me know if there is a mis-match.

Thanks to Anish and Rakesh Jain for pointing out some incorrect stock codes. Also thanks to Atul Agarwal for pointing out a bug in the moneycontrol sheet.

References:

1) Reverse DCF:

http://www.investopedia.com/articles/fundamental-analysis/09/reverse-discount-cash-flow.asp

Install Financial Freedom App! (Google Play Store)

Install Freefincal Retirement Planner App! (Google Play Store)

book-footer

Buy our New Book!

You Can Be Rich With Goal-based Investing A book by  P V Subramanyam (subramoney.com) & M Pattabiraman. Hard bound. Price: Rs. 399/- and Kindle Rs. 349/-. Read more about the book and pre-order now!
Practical advice + calculators for you to develop personalised investment solutions

Thank you for reading. You may also like

About Freefincal

Freefincal has open-source, comprehensive Excel spreadsheets, tools, analysis and unbiased, conflict of interest-free commentary on different aspects of personal finance and investing. If you find the content useful, please consider supporting us by (1) sharing our articles and (2) disabling ad-blockers for our site if you are using one. We do not accept sponsored posts, links or guest posts request from content writers and agencies.

Blog Comment Policy

Your thoughts are vital to the health of this blog and are the driving force behind the analysis and calculators that you see here. We welcome criticism and differing opinions. I will do my very best to respond to all comments asap. Please do not include hyperlinks or email ids in the comment body. Such comments will be moderated and I reserve the right to delete  the entire comment or remove the links before approving them.

35 thoughts on “Automated Stock Analyzer – Now with Reverse DCF Valuation

  1. Anish Mohan

    Pattu-Sir, I am searching for TATA CONSULTANCY Services. The morning star is fetching date but Money Control is unable to bring TCS in dropdown

    Reply
        1. Anish Mohan

          Sorry, I needed to be more elaborative. When I went to Morning Star, and typed LUP, the dropdown is not opening up. I presume it is unable to fetch values from Morning star.

          Reply
          1. Anish Mohan

            Pattu-Sir, This is working fine now. As again, a work of fine art from you. Valuation with Price Multiple model is my favorite, the rest I am catching up with my limited knowledge.

  2. Anish Mohan

    Pattu-Sir, I am searching for TATA CONSULTANCY Services. The morning star is fetching date but Money Control is unable to bring TCS in dropdown

    Reply
        1. Anish Mohan

          Sorry, I needed to be more elaborative. When I went to Morning Star, and typed LUP, the dropdown is not opening up. I presume it is unable to fetch values from Morning star.

          Reply
          1. Anish Mohan

            Pattu-Sir, This is working fine now. As again, a work of fine art from you. Valuation with Price Multiple model is my favorite, the rest I am catching up with my limited knowledge.

  3. JD( Bharuch )

    Excellent effort which really need detailed research and time.
    Just Initial assessment.
    Piramal Enterprise data downloaded well by Morning Star. Not downloaded by Moneycontrol.
    Also Analysis sheet , please check O75,P75.
    Money Control Page: Check F3 formula.

    Rest once explore more.

    Keep It up Sirji.

    JIGNESH DAHERAWALA
    Bharuch

    Reply
  4. JD( Bharuch )

    Excellent effort which really need detailed research and time.
    Just Initial assessment.
    Piramal Enterprise data downloaded well by Morning Star. Not downloaded by Moneycontrol.
    Also Analysis sheet , please check O75,P75.
    Money Control Page: Check F3 formula.

    Rest once explore more.

    Keep It up Sirji.

    JIGNESH DAHERAWALA
    Bharuch

    Reply
  5. JD(Bharuch)

    Well, Its working fine with YES BANK.
    Valuation 1:
    Why Expected conservative EPS growth (a8) considered 15%? Any work behind it?
    E25, E26 should be 5 Yr, if i understand it correctly.

    Thanks

    Reply
  6. JD(Bharuch)

    Well, Its working fine with YES BANK.
    Valuation 1:
    Why Expected conservative EPS growth (a8) considered 15%? Any work behind it?
    E25, E26 should be 5 Yr, if i understand it correctly.

    Thanks

    Reply
  7. Tejas

    Dear Pattu
    Wonderful sheets. Thanks for all the efforts and sharing.

    I tried to pull data from Moneycontrol for ITC. Typed Code - ITC and then in the drop down menu, it gives me only 3 options -- ITC Hotels, ITC Classic and ITC Bhadrachala. ITC Limited is not in the drop down list. How do I get it corrected?

    Reply
  8. Tejas

    Dear Pattu
    Wonderful sheets. Thanks for all the efforts and sharing.

    I tried to pull data from Moneycontrol for ITC. Typed Code - ITC and then in the drop down menu, it gives me only 3 options -- ITC Hotels, ITC Classic and ITC Bhadrachala. ITC Limited is not in the drop down list. How do I get it corrected?

    Reply
  9. Praveen

    Hello All,

    I am not able to download data from Morningstar.in and also Moneycontrol for any of the stocks.
    I am using version 5 spreadsheet and i tried other versions as well. Please help me.

    It says it cannot connect server and asks to debug or retry..

    Reply
  10. Praveen

    Hello All,

    I am not able to download data from Morningstar.in and also Moneycontrol for any of the stocks.
    I am using version 5 spreadsheet and i tried other versions as well. Please help me.

    It says it cannot connect server and asks to debug or retry..

    Reply
  11. Haresh

    Hello Sir, I see lots of #N/A on Analysis tab for Payout ratio% ( A37) and Book Value Per Share INR( A31)

    I tried With multiple stocks( eg, Auro pharma, Sintex Ind etc) with same results. these #NA creates issues on Valuation-2, Valuation 3 & Valuation 4 tabs.

    Thanks
    Haresh

    Reply
    1. freefincal

      Can you please trace to the source in the morning star sheet? If that data is absent then not much can be done.

      Reply
  12. Haresh

    Hi,

    Just figured out that now morning start data is returning 'Payout Ratio %' as 'Payout Ratio % *' which causes MATCH function to fail.

    same in the case of 'Book Value Per Share INR' coming as 'Book Value Per Share * INR'.

    Thanks
    Haresh

    Reply

Do let us know what you think about the article