Look-through earnings and portfolio tracking using Google Docs

Author's Avatar
Aug 20, 2010
In my previous article titled "Using Google Docs to track your watchlist stocks", I introduced how Google docs (spreadsheets) can be used to get current stock prices and various other parameters to manage a watch list of stocks. In this article I talk about the concept of 'look-through earnings' made popular by Warren Buffett and present you a Google spreadsheet that automates the calculations.

Warren Buffett talked about the concept of 'Look-through earnings' in his share holder letters. In his 1991 letter, he showed how he computed the look-through earnings. Here is an extract from that letter "We also believe that investors can benefit by focusing on their own look-through earnings. To calculate these, they should determine the underlying earnings attributable to the shares they hold in their portfolio and total these. The goal of each investor should be to create a portfolio (in effect, a "company") that will deliver him or her the highest possible look-through earnings a decade or so from now. An approach of this kind will force the investor to think about long-term business prospects rather than short-term stock market prospects, a perspective likely to improve results."

Applying the look-through concept:

Say you own 100 shares of Intel and it earned $1.66 per share in the last year. Your look through earnings from Intel is 100 * 1.66 = $166. You should do this for all the companies you own in the portfolio. The way to think about this is that your stocks are really businesses that you hold in a conglomerate / holding company.

How the spreadsheet works

It can be used to track your portfolio holdings. You simply enter the stock symbol, the number of shares held, average purchase price and dividends per share. The sheet computes various values for each holding including market value, gains and losses, dividend yield. The sheet computes the look-through earnings and dividends for all your stocks.You can then see what all the businesses put together are earning for your conglomerate (Total look through earnings) and also paying out in terms of dividends (Total look through dividends)The sheet will compute what your total gain (or loss) is at the portfolio level and as a % of the purchase cost.Finally, the sheet computes some portfolio statistics such as the Portfolio P/E, weighted average market cap, earnings yield and dividend yield of the portfolio.

Access the spreadsheet here.

In this hypothetical portfolio, the total portfolio cost is $18,075.00 and the look through earnings are $1532.25 and the look through dividend is $675. The dividend number is useful as you know how much you can expect to be paid in dividends from your portfolio companies. With the earnings we can compute the earnings yield for the portfolio at 8.24%. That is pretty decent when you look at what you earn in interest in CD or a 10 year treasury bond.

The best part of this concept is that it can be easily extended to other metrics such as cash flow or free cash flow. In fact, it may be interesting to see what the weighted debt to equity ratio is for the entire portfolio.

I found an excellent article on the ‘look-through earnings’ concept by Zeke Ashton of Centaur Capital. Zeke manages the Tilson Dividend Fund (TILDX) and runs a hedge fund.

I hope you have found this article series useful. Any feedback or suggestions to make the spreadsheet better are welcome.