Health Check Your Holdings : Tool

Have you ever wondered if there is a easy way to find out how healthy your holdings are? I've been thinking about a way to do it for a long time. I'm not a full time investor and I don't have time to go through financial reports of 280+ companies. Don't get me wrong here, I go through financials before I buy anything. But that is after pinpointing on a particular counter or two. How about the period before that? If there is a one click way to measure the health and safety of an investment that would be great. In this post I'm going to introduce you a tool, which I've been developing to do that. 

What to check?

This is probably the most difficult part. It is important to figure out a universal set of parameters to measure the health of an investment. As a dividend growth investor, I did that according to the most important criteria of dividend investing. They are listed as follows.

1. P/E : Valuation. You don't want to buy an over valued counter. Check the P/E for valuation.
2. ROE : If they can't make money, they can't pay you. Always check the return on equity.
3. Dividend Yield : At the end of the day, that is what you get. No dividend yield? Then don't buy it.
4. 5 Year Dividend Growth Rate : This is the protection you have against inflation. Inflation proof your investment.
5. 5 Year EPS Growth Rate : Don't speculate. If they don't earn, you don't earn. Check the earnings.
6. 5 Year Average Dividend Yield : Because current yield could fool you. Always check the past.

How to Weight?

There you have six parameters to check. Now it's a question of importance. Is earnings are more important than dividends? Can higher growth rate justify the higher P/E? Does higher return on equity makes an investment safe for share holder? Honestly I don't know the answers. There are lot of parameters to consider. But I know my math very well.
Warning! Spoiler Alert. You can enjoy the Cartoon and skip to next heading :)

I simply used the standard deviation of each distribution to determine the bands  and weighted each band while eliminating the extremes. I have posted the distribution graphs of above parameters on a previous post here @ CSE Scanner. Will not go deep into math here, but following is the basic idea. 

Lets look at ROE for example. There are 107 companies in CSE which have ROE less than 5%. If ROE is lower it is not a good choice for long term investment. So I have given zero rating for those companies. If ROE is in between 5-10 then they are weighted so that they will have a rating of 0-25%. ROE 10-20 is mapped to 25-50%, 20-30 to 50-75% & 30-40 to 75-100%. Companies which have ROE higher than 40 is capped at 100%.  This is done to eliminate the extreme cases such as CTC, NEST, LLUB, etc.

Using the Tool

Download the tool [here].
1. If you can't download, Try Firefox or Chrome browser.
2. This is a macro enabled work book. Once you download check the extension. It should be ".xlsm"
3. Don't use "Open with" option, Download first and then double click to open.
4. You should enable macros from Trust Center settings for it to work.

5. This need data connectivity to work. Did I say that before? This too will fetch real time data from financial times servers. You need to enable data connections from Trust Center.

If you managed to do above and open it, you will get this interface.

Just select the counter you want to check from drop down list in [Cell C2] and click [Fetch Financials] button. It is simple as that. You will get a rating of 0-100% along with other important data of the counter you selected.

Graphical presentation will allow you to identify the strengths and weaknesses of a counter easily. Notice the difference of NDB with respect to LFIN and GLASS. I found this very helpful when ever I ended up in a situation of this or that.

I will be using this tool in future dividend analysis posts and hope you also will find it helpful. Let me know if you have any problems with getting it to work. Also this will be the last post for this year. Wishing you a good year ahead and enjoy the vacation....




Update : 2014/08/03
 Tool was updated to give a prediction of future market price, dividends, etc. based on past 5 years of data. When you click "Fetch Financials" button, you will get an additional table on the bottom left hand side as follows. Content is self explanatory I think. What I mainly look for is the last column(Inv.Recov) which simply answers the question "How long will it take to recover my capital?" 

Also there are couple of things I need to state.

1.  This is a conservative forecast. In reality, chances are high that you recover your capital faster than indicated with dividends being re-invested.
2.  As a safety measure, this will not give a forecast for some counters (due to negative growth or insufficient data)
3.  This was written in good faith. Back testing proved forecasts are reliable, but future can not be predicted with 100% accuracy. User due diligence is always advised.