Students of Roger Montgomery's (Buffett's) intrinsic valuation method

[VSntchr]

Very nice work there!
how do you create a line chart that moves up in yearly increments? My excel knowledge is basic and my valuation line is an average line

Also nice work with putting in the share price for comparison, does that go in automatically for you?

 

[tinhat]

Hi All,

...

The following graphs are what my spreadsheet outputs at the moment. I'm interested to know some thoughts from others on what else I could include or how I could change these to look better or provide greater information.

...

All suggestions etc welcome!

Stock market pr0n. Awesome.

 

[kermit345]

VSntchr,

Historical prices aren't automatic unfortunately, i'm sure its possible with more advanced programs that probably require some coding and/or $$, but using excel means I have to manually enter the historical prices, using yahoo finance.

In terms of the incremental valuation, its a matter of maintaining the same figure for a number of lines. Fore instance say your valuation during 2011 is $5.74 for stock A.

The share price in May could be $4.64 in column B (in excel) so in column C you would have the $5.74. Then SP in June may become $4.75 but the valuation column in C remains $5.74 and only changes when there is a new years valuation.

Hope that makes sense, if you need help PM me and I can try explain better or provide an excel spreadsheet with an example.

Any suggestions for other graphs/information which I could include within the spreadsheet as an output? I was thinking of a chart where it shows the percentage of the margin of safety to the valuation. I'll work up an example and post it here as well.

 

[luckyforteja]

Yes there is.
Tick on NEWS & RESEARCH,
Then on COMPANY RESEARCH,
Then on ADVANCED SEARCH TOOL.

Can then make a list of whatever you like - ROE,D/E etc. etc.

Simple as that.

Good luck with it.

You are a star

 

[luckyforteja]

Hi All,

The following graphs are what my spreadsheet outputs at the moment. I'm interested to know some thoughts from others on what else I could include or how I could change these to look better or provide greater information.

All suggestions etc welcome!

Great Job and thanks you for sharing. I started working on something similar but you are way ahead of me.

Can you explain how your rating system works ? What metrics are being used etc ?

Cheers,
Sri

 

[kermit345]

Great Job and thanks you for sharing. I started working on something similar but you are way ahead of me.

Can you explain how your rating system works ? What metrics are being used etc ?

Cheers,
Sri

luckyforteja, the ratings system works by comparing all the companies researched using the following metrics:

- Margin of Safety (Current Valuation vs Current Share Price)
- Return on Equity
- Debt to Equity
- Growth
- Market Capitalisation
- Market Liquidity
- Capital Gain 1 Year (Same as Margin of Safety, but on 1 year forward valuation)
- Capital Gain 2 Year (Same as Margin of Safety, but on 2 year forward valuation)

I then allocate scores to each of the metrics to total 100 so for example:

- Margin of Safety - 22 points
- Return on Equity - 22 points
- Debt to Equity - 18 points (Reverse order, lower debt = higher points)
- Growth - 8 points
- Market Capitalisation - 6 points
- Market Liquidity - 6 points
- Capital Gain 1 Year - 10 points
- Capital Gain 2 Year - 8 points

I also remove outliers by taking away 15% at the higher and lower level of the stocks i've researched. So say i've researched 20 stocks, i'll remove the 3 highest and 3 lowest margins of safety to ensure no outliers have great effect on the scores of other stocks.

So for instance, with Margin of Safety, Stock A might have an MOS of 18% and Stock B is 10%.

Therefore Stock A would score 22 for MOS and Stock B would score 12.22.

Hope that makes some sense, probably the best I can explain it without showing you the full spreadsheet with all the calculations etc. Took me a while to develop.

 

[luckyforteja]

luckyforteja, the ratings system works by comparing all the companies researched using the following metrics:

- Margin of Safety (Current Valuation vs Current Share Price)
- Return on Equity
- Debt to Equity
- Growth
- Market Capitalisation
- Market Liquidity
- Capital Gain 1 Year (Same as Margin of Safety, but on 1 year forward valuation)
- Capital Gain 2 Year (Same as Margin of Safety, but on 2 year forward valuation)

I then allocate scores to each of the metrics to total 100 so for example:

- Margin of Safety - 22 points
- Return on Equity - 22 points
- Debt to Equity - 18 points (Reverse order, lower debt = higher points)
- Growth - 8 points
- Market Capitalisation - 6 points
- Market Liquidity - 6 points
- Capital Gain 1 Year - 10 points
- Capital Gain 2 Year - 8 points

I also remove outliers by taking away 15% at the higher and lower level of the stocks i've researched. So say i've researched 20 stocks, i'll remove the 3 highest and 3 lowest margins of safety to ensure no outliers have great effect on the scores of other stocks.

So for instance, with Margin of Safety, Stock A might have an MOS of 18% and Stock B is 10%.

Therefore Stock A would score 22 for MOS and Stock B would score 12.22.

Hope that makes some sense, probably the best I can explain it without showing you the full spreadsheet with all the calculations etc. Took me a while to develop.

Thanks for the reply mate,

Wouldn't Capital Gain 1 Year and Capital Gain 2 Year be already factored into the IV calculation ?

You said that you are getting the values directly from Yahoo, do you look at Financial statements as well (Cash Flow, Income statement, balance sheet etc).

Suggestions: I would prefer to have IV separate to the rating system, to me having a matrix like RM makes more sense.

Cheers,
Sri

 

[kermit345]

The Value Rating system I've developed is simply for comparison puposes between the stocks i've identified and researched as 'value stocks'. It's a bit of a guide to say for example, Stock XYZ has a Value Rating of 75 which is quite high and therefore must be meeting my criteria to a high standard in comparison to the other 'value stocks' i've researched.

Capital Gain 1 and Capital Gain 2 aren't factord into the IV, they are in fact seperate IV's. IV = Instrinsic Value which is the valuation you or I have determined for a particular business/stock.

So the current year IV takes into account current Earnings, Debt, Equity, ROE etc etc. Capital Gain 1 is next years IV based on next years estimates of those metrics, Earnings, Debt, Equity, ROE etc etc. Same goes for Capital Gain 2. Hence why if you look at the charts I posted, you can see the Valuation increasing against the current share price as i've estimated earnings increases etc which increase the intrinsic valuation at a future point in time. Hope this makes sense.

I stated I get the historical share price data from yahoo. In terms of financial data, i'll usually obtain from etrade/yahoo first and if the details look promising i'll investigate further and use annual statements for a more 'exact' computation of intrinsic value.

I haven't read RM's book so can't exactly comment on is A1, B3 system but it is my understanding that a number of metrics go into his rating system. He has said in basic terms that it relates to the return opportunities of the business and its ability to repay debt or provide capital in any unforseen event. So not precisely sure what he includes within his system to arrive at his ratings.

Can understand where your coming from with keeping valuations external, however this is why in my points system i've allocated a smaller amount of points to Capital Gain 1 and 2 as they are less certain so less important. Roger continually states how important low D/E, high ROE and a high Margin of Safety is to value investments and seems to be the key themes of his blog posts. Hence i've allocated the most points to these areas and still included the MOS in my points system.

Each to their own I guess, its just a method i've developed to identify not only value investments, but the premium of these value investments as well. I should also re-iterate that my value ratings probably don't provide a good comparison until i've researched at least 20+ stocks for comparison.

Also, in case you were wondering how I identify what stocks to research in the first place. I run a filter against the ASX for stocks with the following properties:

Market Cap - greater than $100,000,000
Return on Equity - Greater than 15%
Debt to Equity - Less than 30%

I think Roger is slightly more relaxed on his Debt to Equity screen however might be a little more aggresive on his required Return on Equity.

Anyway look forward to more thoughts/discussion

 

[Macros]

Kermit,

Your graphs look great.

I see that you have the future valuation ahead of the price, as is with Roger's graphs. I prefer to match the 2 year forward valuation with the current price. It seems to me, based on looking at many many different companies, the current price is best matched with expectations of future value (2 years). I believe that in most cases the current price often factors in expectations of forward earnings. Obviously it isn't efficient and this is where we come in, but I strongly feel that this is the case.

I tried to make a post when Roger released images of his own system around a month ago. However, he seemed to block any comments regarding excel based systems - I think he is looking to monetize his system and doesn't want any discussion about alternatives.

I've built my own excel program. Initially I had a huge database of different sheets with exported Commsec financial data, combined with an automatic feed from Yahoo to track the share price for the graph. However, once this grew to over 80mb it started to chug! (It uses VB script).

Since then, I've restarted it with just a one page excel sheet. All I need to do is to put in the asx code and the required rate of return. The financials and share price data are all picked up from the web within 3 seconds so its a very quick process. It took me quite a while before I got it all down pat. The only problem is that some companies have their data reported based on the currency of the annual report - e.g. MML in USD. However, this doesn't seem to make too much of a difference for the valuation.

I do a commsec download with qualifying criteria, as others do, and then do a bulk filter with the valuations on each line. Once I've done the filter, I then plug the code into my excel tool to get a more specific understanding of the stock.

I'll post a copy of how mine looks later today and hopefully can get some feedback.

 

[Macros]

Here is an example of MML.

All of this is automated. All I have to insert is the code and required return. It is up to date. In this case, I've used consensus EPS forecasts (I think it can exceed them).

 

[kermit345]

Macros, here I was thinking I had a decent handle on excel, but if you've managed to get all of that flowing through simply by typing in the code etc then that is amazing. Everything is manually entered on mine in terms of historical prices (copy pasted from yahoo) and financial data (grab it from etrade and enter myself making adjustments where i see fit).

I have a seperate sheet per company, however only have around 20-30 sheets as I filter it down to this number that i monitor.

Would be very interested to email and discuss with you further as it sounds like your achieving exactly what i'm after. Also if your into value investing have some other things i'm putting some time/effort into that may be of interest to you.

If you could PM me with your email address i'd very much look forward to emailing you and discussing further.

Also agree with you i.e. Roger. Recently someone mentioned on his blog regarding the tedious task of manual entry into excel and looking for easier software. He replied saying to 'stay tuned' which would indicate as you say, he has something cooking that will probably charge a fee.

Look forward to hearing from you, that output from your excel spreadsheet has me very intrigued.

 

[Macros]

Kermit,

Thanks for the feedback. I originally used Commsec for both prices and historical data. However I quickly grew tired of this process and the time involved (repetition).

The next step was to automate prices, which helped significantly. After that, it took me ages to figure out how to obtain the historical financials - almost everything that I've seen online (US based) is focused on prices and technical trading.

I've got to the point where I'm very happy with the outcome. I'm continuing to tweak it, but overall it now seems to do the job very quickly and easily.

 

[Macros]

Oh, and I received an email today relating to Roger's blog and the imminent release of his service.

 

[skc]

Here is an example of MML.

All of this is automated. All I have to insert is the code and required return. It is up to date. In this case, I've used consensus EPS forecasts (I think it can exceed them).

Not sure about you saying today's price target should be the IV in 2 years time. At a minimum that IV should be discounted by the risk free rate, if not the actual rate of return. E.g. IV in 2013 is $10.59, then one would pay $9.4 (at 6% risk free rate) or $8.44 (at 12%).

Otherwise, very very impressive Excel and VB skills. Thanks for sharing.

 

[Macros]

Skc,

Everyone has a different view. I understand why you disagree. The reason why I think the forward 2 year value is important is that, based on my calculations, it tends to be reflected in price within 12 months in many cases.

I've attached an example of Coca Cola. You can see the price history very closely matches the forward 2 year earnings. It is one of many examples that I have found.

I'm not suggesting that the price will necessarily hit the forward target within 12 months, however it is possible and provides me with an indication of risk/reward. Refer to Decmil. For me, the price action definitely tends towards the forward expectations.


View attachment CCL 26.05.11.pdfView attachment DCG 26.05.11.pdf

 

[skc]

Skc,

Everyone has a different view. I understand why you disagree. The reason why I think the forward 2 year value is important is that, based on my calculations, it tends to be reflected in price within 12 months in many cases.

I've attached an example of Coca Cola. You can see the price history very closely matches the forward 2 year earnings. It is one of many examples that I have found.

I'm not suggesting that the price will necessarily hit the forward target within 12 months, however it is possible and provides me with an indication of risk/reward. Refer to Decmil. For me, the price action definitely tends towards the forward expectations.

It sounds like you know where I am coming from and I trust that you are aware of time value of money concept. Happy to just agree to disagree

Can I ask where can I see on your screen shot the rate of return used in the calculations?

 

[Macros]

It sounds like you know where I am coming from and I trust that you are aware of time value of money concept. Happy to just agree to disagree

Can I ask where can I see on your screen shot the rate of return used in the calculations?

The asx code and RR are to the left of the screenshot and are excluded from the print area. In my examples MML is 14%, CCL is 9% and DCG is 12%.

 

[luckyforteja]

Here is an example of MML.

All of this is automated. All I have to insert is the code and required return. It is up to date. In this case, I've used consensus EPS forecasts (I think it can exceed them).

Awesome Work mate. Very Impressed.

Can you let us know the data source for your valuation?

Thanks in advance

Cheers,
Sri

 

[Macros]

Awesome Work mate. Very Impressed.

Can you let us know the data source for your valuation?

Thanks in advance

Cheers,
Sri

Thanks for the feedback.

Data is from a combination of Yahoo Finance and MSN Money Central.

 

[skc]

The asx code and RR are to the left of the screenshot and are excluded from the print area. In my examples MML is 14%, CCL is 9% and DCG is 12%.

Thanks. Those are very fair RRs for the respective companies. BTW whoever priced the IPO for DCG should receive no business ever again.