Author Topic: Look through portfolio - Google Sheets with live prices  (Read 13074 times)

Dynamic

  • Full Member
  • ***
  • Posts: 165
Re: Look through portfolio - Google Sheets with live prices
« Reply #10 on: November 19, 2016, 02:30:18 PM »
I don't think I can understand what you're asking as I cannot find 38,58,96/share or a number that looks like it in my spreadsheet or on the class A equivalent values. 38,58,96 per share isn't formatted like a typical number of dollars so I'm not sure what it ought to be.


scorpioncapital

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 1335
    • scorpion capital
Re: Look through portfolio - Google Sheets with live prices
« Reply #11 on: November 20, 2016, 12:21:10 AM »
Sorry, I meant 38, 58, 97 as look through earnings low, base, high cases.


Dynamic

  • Full Member
  • ***
  • Posts: 165
Re: Look through portfolio - Google Sheets with live prices
« Reply #12 on: November 20, 2016, 03:14:35 AM »
These Low, Typ, High valuations are just a crude estimate that I've baked into my stock-tracking spreadsheets.
In the columns Y, Z, AA I define the multiple used for the Low, Typ and High respectively.
These are multiples of the figure in column V, which is mostly EPS, but occasionally I use an alternative metric such as Book Value Per Share (as I do with BRK in rows 1 and 2).

For the full BRK.B valuation, I use BVPS in column V and set Low to 1.2 (the Buy-Back threshold), Typ to 1.5 and High to 2.0, which I estimate as a typical trading range that might be seen at various times during the course of a few years.

For other companies, I figure that a quality company with stable earnings that are similar to Free Cash Flow might be good value at an earnings yield of 8.5% (P/E = 11.76) which may represent a soft floor to the usual trading range, might typically trade at a P/E of 18 and might be richly valued at the High P/E of 30.

Thus, applying the multiples of 11.76, 18 and 30 to the look-through EPS of the investments per share, we obtain $38, $58 and $97 respectively in cells N7, O7 and P7.

These values are then used to color-code the actual look-through market price of the investments per BRK.B-share which as I type this is shown as $54.6592 in cell D7. This is just below the $58 corresponding to bright yellow, so is very slightly darker and greener than bright yellow.

Incidentally, W7 is the inverse of P/E so it's the Earnings Yield at the current Market Price expressed as a ratio rather than a percentage, currently 0.0591 which could be expressed as 5.91%.

Similarly, the same multiples are applied to the Operating Company earnings of BRK.B (net of dividends) to provide the Low, Typ, High values in cells N8, O8, P8 and these are used to color-code the cell D7 'effective market value of the operation company' which is currently $103.0908 (calculated as the current market price of BRK.B $157.75 minus the look-through market price of the investments per share in cell D7 $54.6592).

Row 9 adds up row 7 (look-through) and row 8 (operating). D9 shows that the market-values of these two add up to the market value of BRK.B. The effective EPS per BRK.B share (operating + look-through) is in cell S9 (and cell V9) at $11.1829. This is multiplied by 11.76, 18 and 30 for the Low, Typ and High estimated valuations/trading range. The effective Earnings Yield as a ratio is in cell W9, currently 0.0709 or 7.09%.

You will note that these valuations differ from the Book Value Per Share valuations on row 4 (or row 5) which come out lower, but these do allow a certain like-for-like valuation method so the degree of over-valuation of under-valuation can crudely be compared to other shares that can be valued on a multiple of earnings basis. This might help to some extent in deciding whether to sell some BRK.B to buy something else that's much more undervalued, for example.

By laying everything out on a per-row basis, I seem to have turned the two-column method into a two-row method.

By changing the valuation metrics for the operating part of BRK.B or the look-through shares, you could come up with a custom valuation of your own choosing.

If you wish to share your own estimate of placing a Low, Typical or High valuation on the operating part of BRK.B, I could implement that in another tab of the spreadsheet. Also, you could add a third element by choosing to value cash per share at a certain multiple (e.g. 0.9, 1.0, 1.1 corresponding to a cash drag discount at 0.9, valuing it as risk-free at 1.0 and applying an optionality premium at 1.1) then apply a different valuation of your choice to the operating company net of cash.

For example, if you have an estimate of IV calculated based on a rate you'd like to plug in, perhaps use that IV with a multiple of 1.0 as the High, use 0.75 for Typical and 0.5 as Low multiple. Typical is a 25% discount to IV and Low is a 50% discount to IV.

Variations of the two-column method seem to be:

1. Use the market value of the look-through shares and add a sensible valuation of the operating company
2. Use a non-market valuation of the look-through shares and add a sensible valuation of the operating company

I hope that the above clarifies how these figures came about and how crude they are - albeit that they're probably more 'consistent and objective' than the fluctuating market price of the portfolio constituents.
« Last Edit: November 20, 2016, 03:21:53 AM by Dynamic »

scorpioncapital

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 1335
    • scorpion capital
Re: Look through portfolio - Google Sheets with live prices
« Reply #13 on: November 20, 2016, 10:12:58 AM »
Do you compare look through earnings per B-share to Investments per B-share to see what the market is implying for the yields in the 3 cases?

Dynamic

  • Full Member
  • ***
  • Posts: 165
Re: Look through portfolio - Google Sheets with live prices
« Reply #14 on: November 20, 2016, 02:58:20 PM »
Do you compare look through earnings per B-share to Investments per B-share to see what the market is implying for the yields in the 3 cases?

Sure do. See cells W7 W8 W9 for the earnings Yields in question. You just need to shift the decimal point two places to the right to express it as a percentage.

The implied Earnings Yield of the look through portfolio is 5.91% at current prices and reported Earnings, the Earnings being obtained via Google where available including negative contributions from loss makers and zero where an error code is returned.

The implied price if the operating company is $103.0908 with Earnings/Share of $7.9516, and the implied earnings yield in cell W8 is 7.71%.

The total of BRK.B has combined earnings of $11.1829 and current price of $157.75 so Earnings Yield in cell W9 is 7.09%  based on total operating and look through earnings per share.

Dynamic

  • Full Member
  • ***
  • Posts: 165
Re: Look through portfolio - Google Sheets with live prices
« Reply #15 on: January 25, 2017, 09:08:19 AM »
Just to mention that LMCK - Liberty Media Group Class C - is no longer showing a price on Google Finance or any other platform, which threw up an error on the look-through market price totals. I think this is a matter of restructuring various classes of share, probably relating to the Formula 1 deal. I'm not inclined to delve into the minutiae now as we'll be getting the 2016Q4 list of investments in a few weeks which will account for such changes, and quite possibly many more significant changes and save a lot of effort.

To get round this I've simply set the LMCK price to a fixed $3.42 and set that line of the speadsheet to RED. This makes of the order of 0.1%, so isn't materially wrong.

After 27 Feb 2017, the new portfolio should soon be available and I'll update it properly.

QUICK LINKS:
The version only I can edit (but you can copy it into your own Google Sheet - see above):
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing

The version anyone on the internet can see and edit (so it could get corrupted, but you can enter your own currency and number of shares):
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing

Restricted editing - for this version you can request Edit Access and I'll grant it. It sends me an email from your Google account:
https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing

In all case, anyone on the internet can view them so be wary of entering private information. If you want a private copy, copy the sheet to your own private Google Sheet using the instructions in post 3 of this thread at http://www.cornerofberkshireandfairfax.ca/forum/berkshire-hathaway/look-through-portfolio-google-sheets-with-live-prices/msg272206/#msg272206

Jurgis

  • Hero Member
  • *****
  • Posts: 3546
    • Porfolio
Re: Look through portfolio - Google Sheets with live prices
« Reply #16 on: January 25, 2017, 12:29:52 PM »
LMCK is FWONK now. And it was never priced anywhere near $3.42.

Dynamic

  • Full Member
  • ***
  • Posts: 165
Re: Look through portfolio - Google Sheets with live prices
« Reply #17 on: January 25, 2017, 02:19:15 PM »
Thanks, Jurgis. I've now included FWONK so the sheets work again.

Dynamic

  • Full Member
  • ***
  • Posts: 165
Re: Look through portfolio - Google Sheets with live prices
« Reply #18 on: February 15, 2017, 07:32:12 AM »
I hope to update the look-through tomorrow, if I have time, but work intervenes. For now, I've put an extra tab on this version where I've entered up the 13-F data in crude fashion: https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit#gid=1273825621

As yet, I haven't fully matched up with ticker symbols and name changes (especially around Liberty Sirius XM and similar names) to determine which holdings appear to have been sold, renamed or reduced.

Some shares show 0, but among these is BYD (China), which we assume is still held, but not reportable to the SEC.

For a summary, various sources are available including this on SeekingAlpha:
http://seekingalpha.com/article/4046111-tracking-warren-buffetts-berkshire-hathaway-portfolio-q4-2016-update
and this indicates a couple of exits such as Deere and Kinder Morgan, reductions like Walmart and increases such as Apple.

The CNBC portfolio tracker hasn't yet been updated, which I find a handy sortable table to compare & check my own work. http://www.cnbc.com/berkshire-hathaway-portfolio/

scorpioncapital

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 1335
    • scorpion capital
Re: Look through portfolio - Google Sheets with live prices
« Reply #19 on: February 16, 2017, 09:04:33 AM »
Just looking at the share prices of some banks and IBM (although BAC are held as warrants), as well as profits in industrials, I have a feeling Q4 could be an incredibly big quarter. If mark to market gains flow through the income statement it could even be a $10 billion quarter.