Look through portfolio - Google Sheets with live prices

[Dynamic]

View-only example look through portfolio

(set to GBP currency - only I can edit this but I think you can select and copy the cells into your own Google Drive spreadsheet):

https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing

 

Publicly editable version of look-through portfolio:

(might get corrupted by someone else's edits, but you can have a play with it and try changing currency and number of shares held):

https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing

 

At time of posting this is updated to 30th June 2016 holdings as declared in 15th August 2016 13-F and the shares oustanding at the same date from the 10Q.

 

Commentary:

 

I track our total personal portfolio and Watchlist using Google Sheets in Google Drive (the GOOGLEFINANCE function is very useful for currency conversion and delayed prices and basic financials such as EPS). I also use various tabs to track our investment ledgers and our monthly and annual deposits into our tax-advantaged investment accounts, and to keep an eye on our pension schemes.

 

I wanted to make a look-through total portfolio, partly to gauge our total exposure to companies we hold that are also held by Berkshire Hathaway, such as Wells Fargo, where our look-through stake is about half of our direct stake because we're fairly heavily into BRK.

 

As a by-product, I've created a Google Sheet which you might wish to look at, borrow from or select-All and Copy into your own Google Sheet on Google Drive. If you paste into Excel or LibreOffice Calc you'll probably lose the GoogleFinance functionality.

 

The link below is my personal version (only I can edit it, but anyone can view it and I believe could copy and paste the cells to another sheet they can edit):

https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing

 

The link below this is publicly editable by anyone (so you can play with it, collaborate on it or paste the contents of the other sheet into it):

https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing

 

If you enter in the blue cells your number of BRK class A shares and class B shares and your native currency (e.g. GBP, EUR, USD, CAD etc.) it converts it into equivalent B shares and it estimates look-through holding values from row 8 down, and in columns way over to the right, it includes the EPS reported by GoogleFinance. Some shares have an Error (N/A) so I set these to zero in totalling EPS per BRK.B, but these are mostly minor holdings, so the overall look-through EPS at the bottom right is about right.

 

As a crude indication, as for my full portfolio, I also include Low, Typical and High estimates of trading range based on some kind of fundamental and multiples of it. This also sets a colour scale to crudely indicate if the current market price is Low (DARK GREEN), Typical (BRIGHT YELLOW) or High (DARK ORANGE/BROWN). In my full portfolio I sometime use this colour scale to easily whittle down my list of candidates to sell at toppy prices in order to buy something else I think is cheap, and I use the Low/Typ/High idea to project long term retirement portfolio value without so much influence of current market-wide depression/euphoria.

 

For BRK this fundamental is Book Value Per Share. For most others it's EPS reported by GoogleFinance. Where EPS is negative or N/A I've estimated some other value such as normalised EPS - using very little effort of analysis!!!

 

Do your own research etc. and don't simply rely on the spreadsheet to make investment decisions!

 

Soon I expect to summarise the key figures near the top and the left of the sheet and perhaps make the look-through list filtered/sortable.

 

[Dynamic]

I've made a couple of changes to both spreadsheets.

 

I've inserted rows 7, 8 and 9 on the spreadsheet.

 

Row 7 splits out the equity holdings per BRK.B share. It tells you how much one share of BRK.B contains in current Market Price of equity holdings.

(Market Price is about $53.66 as I type this, with effective EPS of $3.05 per B share, P/E = 17.57 or E/P yield of 5.69% from column W). It also values your total holding in your native currency.

 

Row 8 splits out the operating side of Berkshire Hathaway only (it does not back out cash equivalent holdings, however). It shows the operating earnings excluding dividends (as per the annual report for now, not per the 10Q).

(Market Price is about $94.41 as I type this, with effective EPS of $8.86 per B share, P/E = 10.66 or E/P yield of 9.38% from column W). It also values your total holding in your native currency.

 

Row 9 is the sum of the two rows above - the total Look-Through BRK.B share, with total look-through EPS in column S.

(Market Price is the same as BRK.B = $148.07 as I type, Look-Through EPS = $11.91, P/E = 12.43 or E/P yield of 8.04%)

 

You can use this in a few ways to approach valuation, according to your tastes, such as:

 

• Apply an earnings yield or P/E multiplier to Total Look-Through EPS - see cell S9 then multiply by a fair P/E or divide by a fair earnings yield.

• Back out cash and equivalents per B share then do the above to value the non-cash portion.

• Apply a sum-of-the-parts valuation to both the portfolio's look-through earnings per B share (so you free yourself from it's current market price) and to the operating company's look-through earnings per B share.

• Make a valuation of each significant holding individually and to each division of Berkshire's operating business.

• Value various business units within Berkshire Hathaway separately and combine them. E.G. Insurance, Utilities, Railways, Retail and Consumer, Financial Products, etc. to value the operating portion, then add in the shares.

• Use whatever crude metrics you normally employ to roughly gauge the relative attractiveness of different stocks. For example, my crude Low, Typical, High measure adjusted using columns Y, Z and AA.

[Dynamic]

For those who want to play or to enter private information, like your actual BRK holdings, you can copy the "View Only" version of the Google Sheet to a Google Sheet of your own.

 

[*]Open Google Drive or http://drive.google.com in the usual way.

[*]Go to or create the folder you want then press NEW and select Google Sheet.

[*]Your Untitled Spreadsheet will open. You can click in the title to change its name.

[*]Open my view only Sheet at https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing

[*]Once it has fully loaded, go to the tab marker at the bottom called "Look-Through BRK earnings and holdings" and click on the light downward arrow on the right of its name

[*]You will have only one option, namely "Copy". Click it.

[*]Select your newly created Google Sheet from step 3 above and hit OK.

[*]It will then say worksheet copied and offer a link "Do you want to open the target Google Sheet?". Say Yes and it will open your Google Sheet.

[*]It will probably be open on the blank tab you created it with, but at the bottom of your window you can click the arrow to delete that tab and leave the new tab called "Copy of Look-Through BRK earnings and holdings" which you can now edit to your heart's content.

 

I've tested this on someone else's Google Chrome and Google Account and it works fine. Feel free to share your own versions on this thread or to copy them to a new sheet you don't mind sharing publicly.

 

BTW, if you ask me for permission to edit my "View Only" version of the sheet, I'll allow you Comment access, but not Edit access, so it won't get corrupted.

 

If you want Edit access in the usual collaborative way, there's now a third version of my sheet to which you can request full access. Find it at: https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing

To request Edit Access, make sure you're signed into your Google account then click the blue View Only button to request edit access from me.

 

I've granted edit access for that to the two people who requested it on my View Only version.

 

Perhaps this version can be one that members of CBF forums can work on together when new financials come out. Whoever wants to can input, say, the Book Value per share, the shares outstanding (find these over to the right on the top rows) and the dividend income received from Berkshire's portfolio so that the right figures flow through. Post here or comment on the Google Sheet to say what you've updated.

 

Also, various minor holdings have no valid EPS figure so I just took Wild guesses at normalised EPS for the purposes only of the Low, Typical, High crude valuation of each stock. In some cases, a multiple of Book Value of other figure might be a better valuation yardstick for a specific stock, so feel free to edit columns Q to AA on any rows you like.

 

Do bear in mind that whatever you type there can be seen by anyone on the internet as can the edit history of the spreadsheet!!! Don't put anything proprietary or personally identifiable.

[physdude]

Thanks for the useful spreadsheet! Really appreciate your effort.

[arcube]

Well done. Thank you for sharing. Much appreciated.

Thanks for the useful spreadsheet! Really appreciate your effort.

[Dynamic]

All versions of the look-through spreadsheet are now updated to include the holding in BYD Company Limited which had been omitted from the original version. This holding was purchased by MidAmerican Energy which was since renamed to Berkshire Hathaway Energy.

 

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

[Dynamic]

All three versions now updated to include increased holding in Phillips 66 (PSX) as at 15 Sep 2016

[Dynamic]

I noticed an error in converting the Market Values and EPS of foreign holdings like BYD into USD, so the Total Market Value of the Look-Through Holdings was about $4 per B-share too high, and thus the implied market valuation of the operating part of BRK.B was about $4 too low.

 

I've now corrected this on all versions.

[Dynamic]

Hi,

 

Slightly delayed by a short vacation in the sun, I've updated the Spreadsheets above based on the latest 13-F and updated the Book Value based on the 30th Sep 2016 10-Q (I'd calculated a slightly different value of BVPS myself than Yahoo Finance shows, so I switched to theirs, which differs in about the 4th significant figure).

 

I'm glad to see from the revision history and changes that a few people have played around with the numbers of shares and native currency in the Publicly editable version at:

https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing

...so I hope it remains useful to some of you. By copying over my new version and deleting the old one, I've undone the most recent change by a member of the public.

 

And don't forget there are instructions earlier in this thread to copy the latest sheet to your own Google Drive spreadsheets. If you copy it, use the version only I can edit:

https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing

 

Assumptions made include that the BYD holding remains unchanged since its original report. I assume that as a foreign holding it doesn't get included in the 13-F but is still held.

 

I note that three airlines have been added to the portfolio - AAL, DAL, UAL

and that two companies seem to have been removed - MEG, SU

 

I usually make a note of my latest update in the green cell Q6, which now reads:

Latest major change: 2016-11-19. 13-F on 15 Sep 16. Add AAL, DAL, UAL. Remove MEG, SU. BV as at 30 Sep

 

Various other positions have changed size, such as a reduction in Walmart and various Liberty Media adjustments.

 

You can compare the previous version of the spreadsheet with the current one as I retained the old version as a separate tab in the Resticted Editing version:

https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing

 

[scorpioncapital]

Just wan't to understand something, is the look through earning per share of 38,58,96/share embodied in the investments per share part of the 2 column method so that the difference between this calculation and that one is the implied under or overvaluation of the market value of the investment portfolio?

 

 

[Dynamic]

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]

Sorry, I meant 38, 58, 97 as look through earnings low, base, high cases.

 

 

[Dynamic]

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.

[scorpioncapital]

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]

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]

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]

LMCK is FWONK now. And it was never priced anywhere near $3.42.

[Dynamic]

Thanks, Jurgis. I've now included FWONK so the sheets work again.

[Dynamic]

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]

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.

[Dynamic]

The 13-F is here: https://www.sec.gov/Archives/edgar/data/1067983/000095012317002417/xslForm13F_X01/form13fInfoTable.xml

 

However, although relatively immaterial, I have a few differences compared to, say, the CNBC Portfolio tracker: http://www.cnbc.com/berkshire-hathaway-portfolio/.

 

Foreign holdings never shown on the 13-F:

[*]BYD Company Limited (SHE:002594) is assumed to be a permanent holding at 225 million shares.

[*]Sanofi (SNW on EuroNext Paris) - I understand that some shares bought in Paris are held, but only the ADRs (NYSE:SNY) are shown on the 13-F. Does anyone know how many SNW are held so I can record this as an assumed permanent holding?

 

I seem to be OK with US holdings that have been sold:

[*]Deere & Co (DE)

[*]Now Inc (DNOW)

[*]Kinder Morgan Inc (KMI)

[*]Lee Enterprises (LEE)

 

Differences to CNBC Portfolio tracker & name-changes/ticker-changes:

[*]Liberty Media Corp Delaware, Class COM A SIRUSXM - this seems to have been discontinued or ceased trading on the market since 31 Dec 2016. Anyone sure how many shares of what ticker the 10,058,800 shares turned into?

[*]Liberty Media Corp Delaware, Class COM C SIRUSXM - this seems to have been discontinued or ceased trading on the market since 31 Dec 2016. Anyone sure how many shares of what ticker the 22,236,109 shares turned into?

[*]All the Liberty Global plc and Liberty Media things seem confusing, but only the plain Sirius XM shares reported seem OK in the 13-F

 

If anyone can advise on any of these points, I'd be grateful, and can probably update the look-through sheet in a short while.

 

One version of my sheet has a second tab where I've entered the 13-F details as best I can:

https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit#gid=2050477249

[scorpioncapital]

I see both tickers traded today -

NASDAQ:LSXMA

NASDAQ:LSXMK

 

 

[Dynamic]

All the version of the spreadsheet are up-to-date with the latest figures and I've got my head around all the Liberties and their tickers. I've also made some substantial changes to how I handle odd cases, like Sanofi and Bank of America Warrants.

 

• The one only I can edit:

https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing

• The one only approved users can edit (click the Share tab to request permission from me):

https://drive.google.com/open?id=10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s

• The one anyone on the internet can edit without signing in:

https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing

 

Again, please remember that if you edit the public versions, your data can be seen by anyone on the internet (including the use of File/See Revision History to revive previous versions), so keep your privacy in mind.

 

If you wish to copy both sheets to your own private Google Sheet, start with the 13-F worksheet tab and hit Copy to... from the menu on the tab, then rename it to remove the added words "Copy of ", the copy the Look-Through worksheet which picks up data from the 13-F worksheet, hence the need to rename it to match the name used in each such formula). Here's the full procedure to copy it:

 

[*]Open Google Drive or http://drive.google.com in the usual way.

[*]Go to or create the folder you want then press NEW and select Google Sheet.

[*]Your Untitled Spreadsheet will open. You can click in the title to change its name.

[*]Open my view only Sheet at https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing

[*]Once it has fully loaded, go to the tab marker at the bottom called "13-F 2017-05-15" and click on the light downward arrow on the right of its name

[*]Click on the option, "Copy to..."

[*]Select your newly created Google Sheet from step 3 above (e.g. you can find it in Recent) and hit OK.

[*]It will then say worksheet copied and offer a link "Do you want to open the target Google Sheet?". Say Yes and it will open your Google Sheet.

[*]At the foot of the new tab called "Copy of 13-F 2017-05-15" click the arrow then "Rename" and delete the words "Copy of " including the space at the end.

[*]If you return to my sheet, go to the tab marker at the bottom called "Look-Through BRK earnings and holdings" and click on the light downward arrow on the right of its name and again click Copy To... and select your new Google Sheet. There's no need to rename this sheet if you don't want to.

[*]Close my version and feel free to edit your own in private. If you adjust the blue shaded cells to your preferred currency and holdings, that will cover most use cases.

 

While going through the 13-F compared to the previous one I noticed a couple of things:

• IBM - was listed in various ownership codes on the previous 13-F but is now all under code 4,11 on a single line

• A couple of other holdings under various codes seem to have moved around including Wells Fargo (WFC).

Could these changes indicate tax-efficient selling and/or buying in certain divisions of Berkshire?

 

Some items are not in the 13-F but I've made my own entries on my 13-F worksheet in gray background, and I'll summarise them as I've changed how I handle them:

 

1. BYD (China), as before is included on the assumption it is still held at the same position size.

 

2. Sanofi ADR. The 2016 Annual Report p19 (Chairman's Letter) mentions the total holdings including both ADR (ticker SNW) and direct shares traded on Paris Euronext. The latter are not reportable on 13-F, but I've put the equivalent number of ADRs (2 ADRs = 1 share of SNY) on the assumption they're still held. I sanity-checked this and the holding is about 1.76% of the market cap, which is about the 1.7% shown in the AR.

 

3. Bank of America warrants being included in effect as if it were BAC exposure.

 

Berkshire holds preferred stock with 700 million warrants to purchase BAC common stock at $7.14 until mid 2021.

 

I'm including these in the look-through effective exposure by using ticker BAC but adjusting the number of shares as if this was a cashless exercise at the current price.

 

e.g. at $23.99 for BAC, the warrant is worth $23.99 - $7.14 = $16.85

700 million of these amount to $11.795 billion.

 

That will buy 491,663,193 shares of BAC, so that's the number of shares I put in the look-through portfolio.

 

In fact, I run this calculation to adjust the effective number of BAC shares ‘live’ to give some sort of indication of the effective exposure and total value of the warrant.

 

The formula for the effective number of BAC shares I've used is:

No of shares = 700 million x (BACprice - $7.14) / BACprice

 

Thus, it reflects the current value of the warrants reasonably well, and gives at least some indication of the exposure to BAC - so if you held BAC yourself, you could work out the additional effective exposure via BRK approximately, although you'll be aware that it will increase in 2021 unless the warrants expire worthless.

 

In reality Berkshire will exchange $5 billion of cash from the operating company to exercise the warrants before they expire, but this will reduce the cash held and reduce the value of the operating component, which I’ve called BH.Own on line 8 of the spreadsheet, by $5 billion while the portfolio BH.Portf will increase in value.

[Jurgis]

• The one only approved users can edit (click the Share tab to request permission from me):

https://drive.google.com/open?id=10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s

• The one anyone on the internet can edit without signing in:

https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing

 

Hey,

 

Thanks for your work.

I think you messed up "anyone can edit" and "approved users can edit" URLs. Now they both point to the same sheet that requires approval. Looking at your previous posts, this was the URL for "anyone can edit", but no longer...

[Dynamic]

This is now the one anyone can edit (with corrected editing rights):

https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing

 

And this is the one people with permission can edit:

https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing

 

Sorry for the mistake, and thanks, Jurgis for pointing it out.