Look through portfolio - Google Sheets with live prices

[LongTermView]

Dynamic,

I appreciate you replying with these details.

This part is key:

So you can say that for every $1.00 of investee dividends received at Berkshire Hathaway in 2016 there's about an additional $1.81 retained which should eventually accrue to Berkshire as a combination of capital appreciation and increased future dividends.

Of course we're talking about dividends from stocks like KO (as opposed to subsidiaries like BNSF) and we're not including things like interest on treasury bills. Where did you get the total of these KO type dividends on the 2016 annual report?

Thanks Again.

[Dynamic]

I looked at the Annual Report p84 and section 'Insurance—Investment Income' then used the figure for Dividends received which I sanity checked by calculating a yield against the equity portfolio valuation that seemed reasonable. That number goes in the first part of the formula in cell AH3 where the formula divides it by equivalent number of shares of BRK.A in circulation which is in cell AC6.

Might be worth me looking up the dividends paid by the investees representing 85-90% of the weighted portfolio value to verify it's about right or even the whole lot. It's a shame GoogleFinance functions don't include dividends.

I realise I had the 2015 figure originally and still needed to lower it just a fraction to match 2016's and should do so every February when going through all the blue cells. The 2016 figure is 3,552 in millions of dollars and had been 3,662 in 2015 so I was able to search the 2016ar.pdf for exactly that string of characters to find the number for the next year.

I'm crazy busy at work after a week off but hope to update the look through sheets soon with this figure.

There will be discrepancies if I include the BAC holding in the EPS but not the dividends as it had been warrants and preference shares previously. I haven't worked out how significant they would be, though these calculations aren't the main purpose of the spreadsheet.

[LongTermView]

Got it, thanks again.

So if we make some rough estimates/assumptions for 2016 then we can say the GAAP income of $3,552 million on page 84 is around $9,946 million in economic terms for a difference of about $6,394 million, right?

[SlowAppreciation]

Here's v1 of the Berkshire look-through earnings portfolio: http://minesafetydisclosures.com/individual-investor-portfolio

I will be doing a lot more to improve this over the coming months, but just wanted to share the progress so far for initial feedback.

[CassiusKing1]

Thanks for your continued work and sharing of this!

[Dynamic]

Very impressive work, SlowAppreciation!

[DW]

Completely agree; very impressive!

Very impressive work, SlowAppreciation!

[SlowAppreciation]

Thanks everyone. I'm taking suggestions/feedback too, so if there's anything additional you'd like to see, please let me know and I can see if it can be included.

[Dynamic]

Got it, thanks again.

So if we make some rough estimates/assumptions for 2016 then we can say the GAAP income of $3,552 million on page 84 is around $9,946 million in economic terms for a difference of about $6,394 million, right?

Just had a chance to look your figures over, LongTermView. I make it roughly $9,520 million of economic income from the investments -  a difference of roughly $5,970 million over the GAAP income. It would probably be better to judge in February when the Annual Report comes out, then the ratio (currently 2.82) will be nearer to matching that based on the look-through earnings of the portfolio held for the year included in the Annual Report, also taking account of adjusted holdings. It would also be a bit out if massive new positions were purchased for cash, say, half way through the year, as only half a year's dividends would be received, while a full year's look-through earnings would be counted.

Maybe one day I'll get round to using the YahooFinance import functions so I can also obtain dividend info that's missing from the GoogleFinance functions. This thread will probably help me do so: http://www.cornerofberkshireandfairfax.ca/forum/general-discussion/google-spreadsheet-tips-and-tricks/

Before I do that I hope to get time to include the latest 13-F in my spreadsheets.

[Dynamic]

The latest 13-F is now included in all three spreadsheets.

You can enter your currency and number of shares in the blue cells near the top-left of the first Worksheet tab.

I've also added a new tab to each spreadsheet called Look-Through Summary - which has a red stripe below the tab name.

This is a somewhat simplified view of the Look-Through portfolio. Rather than re-calculate, it just grabs most of the info from the main sheet, so don't go entering your holdings on this sheet and expect it to work. It adds on the right hand side a couple of columns showing the Look Through earnings of your holding of BRK and showing each constituent stock's percentage of these earnings (just as column J show's its percentage of the BRK portfolio at current prices)

I still include WABCO for now even though the position has now been sold and thus contains 0 shares.

This version is the one nobody but I can edit. It contains instructions for copying them to your own private spreadsheet so you can edit to your heart's content:
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit#gid=292668837


This version is editable by anyone, but be aware that anyone can see your edit history, so if you enter private information there, it's potentially disclosed to the whole internet! It could also get corrupted by anyone!
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing


There's another version that you may request permission to edit, here:
https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing


If you'd like a simplified overview of the BRK portfolio, I can recommend http://minesafetydisclosures.com/individual-investor-portfolio and for CNBC's Look Through, https://www.cnbc.com/berkshire-hathaway-portfolio/