Archive for category Tools

Power of Compounding – An Illustration

We all have heard multiple quotes and quips on the power of compounding by great investors. Hence, I will not give any gyaan on the compounding theme.

What I do want to illustrate is a quick and dirty table to evaluate investments. But that comes later. Trouble-sharing first 🙂

I have had some trouble in evaluating investments. The trouble is/was two-fold:

a) Quality companies vs Cigar butt companies: Assuming a fixed level of cash that you have, there are some cigar butts in the market which can give you a quick 20-40% return if things turn out well versus a few quality companies which can compound at 15% year on year. The deal though is you need to get out once the cigar butt is fairly valued versus continuing to stay invested in quality companies. Let’s say in an optimistic scenario we do get out of this cigar butt investment successfully, we have the problem of re-investment. The problem of re-investment is not simple since you may no longer have any undervalued stocks to invest in. You’ll probably earn 10% on a RD (or a ultra short term debt fund) and that’s about it. Compare this scenario with investing in a quality company (say a Page/GSK etc) where you don’t have to worry about getting out at the right moment/constantly monitor stuff/significant re-investment problem. These are steady compounders, which if some basic stuff goes well will compound atleast 15-20% for the next few years.

b) Falling markets: I have had this peculiar fascination with opportunity cost (which sometimes has stumped me into stupor). In a falling market, I want to invest in say Stock A which can give me say 20% returns for the next 5 years from that invested price. If the market falls some more, I might have another stock, say Stock B which can give me 30% returns for the next 3 years  from that invested price. Assuming I already invested in Stock A, should I get out and invest in Stock B? Or should I stay invested in Stock A? If the stock market falls further, will I have a Stock C that can give me higher returns and ad infinitum. I need to invest based on which company can generate higher returns for a longer period of time. (In general, to avoid the stupor I have learnt to just invest if I can get a 20% CAGR for 5 years).

Net-net, the question I am trying to answer based on the two-fold trouble is – how many times will my money multiply by, in say x years?

I have come up with a matrix for a quick and dirty check of how many times will my money multiply by, given a CAGR and number of years (instead of calculating every single time). Here it is –


Taking the example above, a 20% CAGR for 5 years (refer grey shaded areas) – my money multiplies by 2.5 times. 30% CAGR for 3 years – my money multiplies by only 2.2 times. Hence, the prudent way is to invest in 20% for 5 years.

Also the reason why investing in quality companies for a longer timeframe helps better even though the CAGR for a year or two might be less than investing in a cigar butt for a similar time frame.

As I said earlier, no genius in the matrix. We can calculate it using the compound interest formula every single time. I am lazy though, and hence this matrix helps me for a quick check 🙂

P.S: If you find a stock with 60% CAGR for 10 years, please drop me a mail. We both can retire comfortably 🙂


, , , , ,


MCX IPO Analysis – Expected Value analysis spreadsheet

This post might be coming in a little late (today is the last date for the IPO). Better late than never, as they say.

Anyway, I am not going to analyse the MCX IPO in depth. This article is pretty much the best I have read so far on the IPO issue.

The purpose of this post is to evaluate whether to invest in this IPO for listing gains on an expected value framework (and share a re-usable excel sheet that can be used for further IPO issues) which has not been covered anywhere else (as far as I know).

Expected value = Probability of x% gain (loss) * Actual gain (in Rs.) on x% gain (loss)

I see people around me thinking on the lines of ‘15% gain pukka’, ‘Rs.300 grey market premium’ etc while investing in this IPO (I think any IPO in a bull market would come up with such terms). However, a good process would involve calculating the expected value by plugging in probabilities for different gain (or loss)% and then calculating the actual return on the investment rather than a wild guess. I have endeavored to do just that.

I have attached an excel sheet which analyses the MCX IPO on this expected value framework. Assigning % gains and probabilities is a matter of guesswork (but primarily based on experience, current market conditions, pricing of the issue, marquee etc.).

The expected value depending on various probabilities (based on my little experience) if I invest in this IPO comes around 13% p.a. I have taken a pass though.

I think I have considered most parameters (let me know if I have missed any). They include –

a) I assume money goes out from my account today, 24-Feb-12. I invest Rs.1 lakh.

b) I assume MCX will list 15-17 days later. In the excel sheet, I have taken it as 11-Mar-12 (and I sell on the listing day itself).

c) In case of non-ASBA (based on various twitter discussions, I have found that if you are applying online through your broker, it will be non-ASBA), you lose interest on the invested 1 lakh for 15-17 days (since you need to transfer this amount to your broker who obviously wouldn’t pay you this interest).

d) We need to pay short term gains tax on selling on listing.

Please plug in your values based on your experience and let me know what you think would be the approx. gain p.a if I invest in this issue.

Also, this spreadsheet can be re-used for any IPO (not just MCX IPO) by just changing the dates and the fields in green. Let me know your feedback on the spreadsheet.

IPO Analysis spreadsheet_Expected value analysis

, , , , ,


Graham’s Last Will Screen–List of Stocks

Benjamin Graham is probably one of the foremost gurus in Value Investing. His tenets have been proved true over and over, for the past 75 years. His ‘Intelligent Investor’ (II) is a must read for any investor. The more financially inclined can also peruse ‘Security Analysis’ (SA) although it is way more involved than the former one.

Anyway, in this blog post, I am listing out stocks which have passed the Graham’s Last Will statement. What does the ‘Last Will’ mean? Well, the story goes that when Graham initially wrote II&SA, he came up with 10 criteria to find undervalued stocks, which over long term would comfortably beat the market. However, as the years passed by, Graham shortened the 10 criteria and just before his death (and hence Last Will), he came up with 4 criteria which he claimed would comfortably beat the market.

I have come up with the list of stocks using the wonderful ValuePickr screener.

Graham’s 10 criteria for picking up undervalued stocks (written way back in the 1930s and still serve as an extremely good guidance to pick up stocks) were –

1. An earnings-to-price yield at least twice the AAA bond rate

2. P/E ratio less than 40% of the highest P/E ratio the stock had over the past 5 years

3. Dividend yield of at least 2/3 the AAA bond yield

4. Stock price below 2/3 of tangible book value per share

5. Stock price below 2/3 of Net Current Asset Value (NCAV)

6. Total debt less than book value

7. Current ratio greater than 2

8. Total debt less than 2 times Net Current Asset Value (NCAV)

9. Earnings growth of prior 10 years at least at a 7% annual compound rate

10. Stability of growth of earnings in that no more than 2 declines of 5% or more in year end earnings in the prior 10 years are permissible.

Needless to say, when I ran these criteria over today’s list of Indian stocks, the number of stocks dwindled down to 0 by Criteria No. 5.

(I took the recent SBI Bond rate as the AAA bond rate. SBI bonds were being offered at 9.25% for a 10 year bond (I approximated the AAA bond rate to be 9% for easier calculations))

However, Graham in his Last Will (it’s actually called Last Will and Testament) suggested 4 simple criteria which would stand the test of time and comfortably beat the market (and thanks to Jae Jun’s incredible back testing skills on the American Stock Market (you can check out the performance using these 4 criteria on US markets; I would wager that similar performance can be observed in the Indian markets too!) – btw, Jae Jun is fantastic. You have to follow his blog). The four criteria were –

1. An earnings-to-price yield at least twice the AAA bond rate

3. Dividend yield of at least 2/3 the AAA bond yield

6. Total debt less than book value

7. Current ratio greater than 2

Well, I took these 4 criteria  and ran it on ValuePickr screener (and I did try to back test on ValuePickr, but couldn’t – I wasn’t able to get values for previous years on the screener) and these were the results –

Company Current Ratio D/E Div Yield% Earnings Yield % Industry
Alufluoride 3.22 0 8.82 25.21 Inorganic Chemicals
Amrutanjan Health Care 7.95 0 8.3 42.78 Pharma
Anuh Pharma 2.39 0.01 6.85 18.68 Pharma
Bhagiradha Chemicals & Industries 2.52 0.48 7.06 44.33 Pesticides & Agro Chemicals
Ecoboard Industries 3.6 0.41 11.35 19.36 Other Machinery
Flex Foods 3.27 0.41 6.42 21.62 Food & Beverages
Helios & Matheson Information Technology 8.92 0.57 6.5 33.8 Computer Software
Indage Vintners 4.34 0.71 9.76 121.36 Food & Beverages
K C P 2.57 0.72 33.17 154.44 Construction Materials
Nissan Copper 3.85 0.95 16.3 102.62 Non Ferrous Metals
Oriental Hotels 2.48 0.67 23.37 40.38 Hotels & Resteurants
Panoramic Universal 27.4 0.88 12.17 71.49 Computer Software
Rajkumar Forge 6.93 0.45 6.39 19.57 Castings & Forgings
Zenith Birla (India) 2.04 0.65 39.6 21.56 Steel

The deal with the results is that these stocks need to be moved out of the portfolio every year and re-invested in stocks which fulfill these criteria the following year (much like Magic Formula investing)

(Total debt less than Book Value gives the exact same results as D/E < 1 (obvious) – so, you can run this screen either way on other screeners)

Any thoughts on the stock above? Should we (or can we) refine this further? Any way to back test these criteria? Inputs appreciated.

, , , ,


Importing Financial Data into Excel – Indian Stock Market

Siddharth Shukla of Thrify Investor fame (@shuklasiddharth – are you following him yet?) and I had a short discussion on importing financial data from various financial websites into Excel. Some websites do not have an option of downloading financial information from their portals directly into Excel (unless you do copy-paste and then reformat in excel to adjust for columns etc., which is very cumbersome. Come to think of it, even downloading into Excel is an issue, because for one company, you’ll have three excel files atleast (BS, CF and P&L) and you need to consolidate them into one excel for easier analysis – painful!). As it is, financial analysis is strenuous. We would not want the trouble of downloading and then formatting etc, do we?

This post will explain a very simple method to import financial data (in fact, any data from any website, but financial data for this discussion) into excel seamlessly. I learnt this trick very recently. If you already know this trick, do suggest any improvements on this one (or accede to my request at the end of this post).

I will explain this with an example of importing Balance Sheet data of Infosys from Economic Times website. (I will go through each small step. Some steps might look too basic and hence increase the number of steps (there are only 2 main steps though). It might also look like these 21 steps is too long a process, but believe me you, the entire process will not take more than a minute or two).

1) The first step is to open up Economic Times website ( and type in ‘Infosys’ in the Stock Quote text box in the top right hand corner.

2) In the Search Results page that gets displayed, click on the hyperlink of ‘Infosys Technologies’

3) The next page details Infosys under different tabs like Summary, Prices, Financials, Reports etc etc.

4) Click on the Financials tab

5) The sub-tab of P&L, Cash Flow, Balance Sheet etc etc appear below the Financials tab.

6) Note the url. It is – If you observe closely, just before companyid in the URL, you have ‘profitandlose’. This indicates that Economic Times treats this as a Profit and Loss page of Infosys Techologies.

7) Since we want to download Balance Sheet data for this example, we will go ahead and click on BalanceSheet subtab hyperlink beside the Cash Flow subtab.

8) Once BalanceSheet hyperlink is clicked, notice the URL. It is – Observing closely again, just beside companyid in the URL, it says ‘balancesheet’. This indicates that Economic Times treats this as the BalanceSheet page of Infosys Techologies. We are in the right location.

9) We now minimize the browser (IE/Mozilla/Chrome/Safari – works for all browsers) and open Excel (for this example, I am going to explain it for Excel 2003. Works equally for Excel 2007 and Excel 2010)

10) In the menu bar, there is an option called ‘Data’.

11) Under that ‘Data’ menu, go to ‘Import External Data’. Another sub-side bar opens up which has options like ‘Import Data’, ‘New Web Query’, ‘New Database Query’ etc.

12) Click on ‘New Web Query’. A new pop up window opens up within Excel.

13) Now, we use the Economic Times Balance Sheet web page URL (step 8), copy that URL and paste it in the Address field of this ‘New Web Query’ pop up window. Hit ‘Go’

14) The Balance Sheet page of ET should open up in this pop up window. (If you get some error saying, ‘Scripts error’ etc., just press ‘Yes’ on the error window and go ahead – no impact)

15) Scroll down within this pop up window to the Balance Sheet table.

16) On the top left corner of the Balance Sheet table, there is an image of a little Arrow inside a Yellow box. Click on it. The Arrow will change to a tick mark.

17) Click on ‘Import’ (on the bottom right hand corner of the pop up window)

18) Excel will prompt you with a window ‘Import Data’ and ask you to specify where it has to import the data.

19) Let’s say it’s a new excel sheet and you want to import it in cell A1. Specify that cell as A1 and click Ok.

20) Excel will say, ‘Getting data’ etc (it must be pulling data now).

21) In a couple of seconds, Voila! You have the BalanceSheet data imported into Excel sheet without any effort J

You can do similar stuff for P&L, CashFlow and practically any page and enjoy your results.

Few Additional Points:

1) You can do this for Money Control too. They have 10 years of Cash Flow data which is obviously more useful in analysis than just the 5 years in ET.

2) I am not too sure about Consolidated vs Standalone results in ET vs Moneycontrol vs Valuenotes etc. Please check the same before downloading. The only condition is they should have different URLs (with Ajax and XHTML and what not these days, you can have a lot of data within/between tabs within the same webpage without affecting the URL – so please check). Between, it is always recommended to do analysis off consolidated results (unless you want to do a Sum of Parts valuation using Standalone results and then combining your analysis).

3) Siddharth’s tip – Smallcap/Microcaps usually don’t have subsidiaries. They only have Standalone results. MoneyControl’s results will work better since they have 10yrs of Standalone data if you are researching small cap/microcap stocks. (Warning: Please do check the Annual Report if these small cap/microcap have any subsidiaries before downloading data of standalone vs consolidated)


If anybody reading this is already an expert in Excel/Macro, I have a request (post a link if you have done this already). Can you create a web query file (*.iqy is the extension I think – I am not much of a techno person) that would prompt me to enter the Stock Quote/Ticker symbol from Excel and then this query pulls BS/CF/PL data from ET/Moneycontrol automatically and populate it in Excel (just like above)? (Steps 1-21 are really simple and repetitive for each page – macros should ideally work, as far as I know. However, entering the stock quote/ticker query is a challenge – can somebody crack this please – it would lift a huge burden for many of us).


, , , ,


Interesting Reads: Enhanced Magic Formula and Regulation Changes

1. Enhanced Magic Formula: I recently came across the forum The Equity Desk. Lots of interesting ideas out there, along with some smart analysis.

One such analysis was done by ‘SmartCat’, which is probably an extension to Magic Formula (promulgated by Joel Greenblatt). I ran this extension by my list of stocks and found that, more or less, the results are almost the same as the Magic Formula. Yet, I do think this particular analysis gives a ‘higher level of comfort’ in terms of parameters used rather than just two in the Magic Formula. I think it’d be a great research paper if someone could use this enhanced Magic Formula to say, the BSE small cap/mid cap universe, and compare its returns with just the original Magic Formula for the same universe over the past 15 years and conclude if the results are similar/one model is better than the other. ‘SmartCat’ can probably write a book then 🙂 [Smart investment by the way – write once, reap cashflows for atleast the next 5 years 🙂 ]

{Between, can anyone point out to me any kind of results/research if Magic Formula actually works for the Indian markets? I’d be grateful!]

The original forum post (lots of good thoughts in response to this forum post too, do read them!]:

An excerpt of the enhanced Magic Formula investing by SmartCat:

Basically, this is a quantitative analysis of a set of stocks, based purely on their fundamentals. I’m only using the TED XI as an example – this methodology can be used on any portfolio to determine the best and the worst.
All the 11 stocks will be analysed based on –

P/E Ratio
Return on Equity
Last 4 years CAGR Sales Growth
Last 4 Years CAGR Profit Growth
Market Cap
P/BV Ratio
Debt to Equity Ratio
Dividend Yield
Last 4 Years CAGR dividend per share growth
Dividend Payout Ratio

Basically, we will be looking at parameters typically used by both “growth” and “value” investors.

Ranking Methodology:

– All the 11 stocks are ranked based on each parameter – say P/E Ratio to begin with. A stock with the lowest P/E Ratio gets the highest 11 points, while a stock with the highest P/E Ratio gets the lowest 1 point.

– Similarly, for other parameters like RoE, marketcap etc, each stock is awarded between 1 points and 11 points depending on where it stands compared to eachother.

– The scores are added at the end, and the stocks are ranked from 1 to 11.

Might not make much sense now, but as we go along, you’ll know what I mean.

Flaw in the Methodology:

Looks into the past rather than the future to rank the stocks.

If your portfolio has 50 stocks, then your highest point would be 50 and lowest would be 1. Do analyse your portfolio and let me know.

2) Regulation Changes: With lots of regulations coming in, regulations changing, I thought it’d be a good time to read up on some of the rules/regulations which are changing (feel free to add any new links in the comments): [GST, IFRS, DTC] [GST, IFRS, DTC] [IFRS, US GAAP and Indian GAAP]$FILE/Comparative%20statement%20on%20Indian%20GAAP%20and%20IFRS.pdf [[IFRS, US GAAP and Indian GAAP]


3) The list of ET Top 100 fastest growing companies 2010


4) Free Small Cap and Mid Cap Research links by BSE and NSE. Great starting points without worrying about the target price –


5) Aswath Damodaran’s Valuation Spreadsheets:

This entire web site makes for fascinating reading.

, , , , , , , , , , , , , , , ,


Summary of Investment Criteria

So, what do all those posts mean? Give me the meat. Give me the funda. Summarize.

Ok, here we go. This is an initial list we’ll work with and fine tune it and update it as we go along (Frankly, haven’t had the time to read up on and understand the significance of Price/Sales ratio, PEG ratio, Current ratio, Interest cover, Dividend yield and what’s a good EPS growth number, Insiders holding (according to Walter Schloss, it must be greater than 50%). Will learn about these and update this criteria as we move along in life).

Basically, Fundamentally and all -lly-s, here’s the real deal (click to enlarge) –

Now that we have covered some basics of value investing, we shall proceed to screening stocks based on some of these criteria and see how the results turn up. I hope to have something by tomorrow. Happy Investing!

, ,

1 Comment

Stock Screeners

My goal is to somehow build some customized stock screeners. There are various value investing themes, for a concentrated as well as a diversified portfolio. Since I currently am starting on the competency of building screeners from scratch which is more customizable to me, I’d not bet too hard on this. Meanwhile, there are existing stock screeners which I think should serve some purpose atleast (again, I restrict these to Indian markets). Here are a list of good stock screeners that I have, in no particular order –

1) screener (One of the better ones) –

2) AskKuber (One of the better ones) –

3) Edelweiss (People tell me its really good) –

3) BSE Stock Screener (Quick and Dirty) –

4) ICICI Research screener (Didn’t research this yet!) –

5) Shibui Markets (Didn’t research this yet!) –

6) Buzzing stocks (for Tech analysis) –

7) Way2wealth (Decent one) –

8) MoneyControl (it’s ok – too much clutter) –

I’ve learnt a ton from Jae Jun, who has stock screeners for the US market. Jae hasn’t got one for any international market, so we are left with a void (a market opportunity?) to customize his spreadsheets to the Indian market. Here are Jae’s screeners (Old School Value is probably one of the top blogs for investing out there!) –

We have our own Dalal Street excel, but the data is not updated yet. Still, something’s better than nothing –

To scan results/BSE announcements –

If you need 10 years of financial data for a particular Indian company –

, , , ,