Consolidated Portfolio Vs. Detailed Portfolio

There is a single “Consolidated Portfolio” in Google Sheets and an “Individual Portfolio” for each of the 8 beneficiary Portfolios, for a total of 9 Google Sheets. In this post I will explain 1) why you need to look at stocks this way and can’t just glance at a list of stocks in a standard tracker on your phone 2) what the consolidated sheet does vs. the 8 individual sheets.

Why Not Use a Simple Stock Tracking App?

Any standard stock tracking application let’s you put in the total number of shares and track them in real time. You can also put in your purchase price to show your unrealized gain in real time. That’s all you need, right? No, definitely not.

This sort of simple model ignores dividends and the accumulated impact of dividends on the life of your portfolio, which can be very significant over time. In a non-zero interest rate environment (where short term interest rates aren’t 0.1% and medium term rates under 0.5%) you can also model the impact of cash / interest held in your portfolio.

These simple applications also don’t show “snapshots” of where your portfolio was 3 or even 6 months ago. It is a “current snapshot” and with markets, it is easy to miss that you had a stock hit a high and retreat or vice versa.

Finally, these applications may show you total return (what you’ve invested vs. current market value) but they won’t calculate annualized returns that take into account the timing of cash flows. The returns over time are what you are interested in unless you are solely in the market for the short term.

Consolidated Portfolio Tracker

The consolidated portfolio tracker shows the current performance (real time) of all 8 portfolios. I also froze “snapshots” at various points on the main page and graphed all the total performance.

Consolidated Portfolio Main Page

This table is updated real-time using Google Sheets (OK, it is 20 minutes delayed) by this sheet at the back which has the ticker and the number of shares for each of the 8 portfolios. A pivot table then runs against this information to get the real time tracker in the “price” column. Some of the items need to be added manually (such as individual cash balances) but the rest all run “live”. If I notice that a portfolio looks odd, I will match it against a brokerage statement online and I can see something that has changed which could be a stock split or perhaps a ticker change or even a spin out of a stock which adds stock to the portfolio and reduces value of existing shares.

Consolidated Portfolio Share Tracker

I also use this “stock data” sheet to calculate the other metrics, like momentum (percent of 52 week high), dividend yield classification, and other elements. I also include a description about the stock when I periodically evaluate it (we do remove stocks from the portfolio through sales) and then this data appears automatically in the 8 individual sheets (so it makes it much easier to update the detailed sheets and keep them consistent). I also go through and update “yield” which is the anticipated dividend percent which changes with the price (as the stock price goes up the yield goes down and vice versa) and categorize stocks in the various ways including US / Foreign and sector (based on standard classifications). Note that it is surprisingly complicated to do this because different benchmarks classify stocks very differently (a more detailed topic).

Consolidated Portfolio All Stocks

This consolidated sheet I check pretty regularly and if there are anomalies like stock splits I see them here and fix them easily on the central sheet. I also periodically update the cash balances (which reflect dividends received). When I believe it is time to update the individual sheets, I will start here with the stock descriptions and then go through the individual portfolios one at a time (the next section).

Portfolio Analytics Example

I also calculate an analytic page for each portfolio in the consolidated portfolio and then this “copies” automatically into the detailed sheet (they are linked, like the main stock page, above). I just need to check it from time to time for percentage calculations when the portfolio changes to make sure it makes sense.

Individual Portfolios

I try to do as much as possible in the consolidated portfolio so that I can update the individual portfolios as quickly as possible. It typically takes at least an hour to update each portfolio and check all the results even with the changes I have made to make it efficient.

Detailed Portfolio Example Main Page

This is an example of an individual portfolio sheet for a beneficiary. There is more detail here than in the consolidated report, above, although many of the sheets and details come from the main sheet. You can see some insights:

– Total return for each stock (including dividends) since inception and by year. This is very important and let’s you see how each stock is performing since it was purchased
– Previous performance on stocks that were sold and their current price
– Total return for the portfolio, including an annual rate (which we show separately below)

For sales, it takes a while to align the sale to the original price and calculate the gain / loss that is realized and then align the dividends to calculate total return. I adjust the sheet and move it to the bottom and check the formulas.

Detailed Portfolio Dividends Example

Calculation of dividends takes a while. I go through the brokerage statement and pull out each individual dividend payment, and I code them by date and by ticker. For foreign dividends with witholding (i.e. the dividend was $30 but $10 was withheld for taxes) I put the “net” amount (in this case $20). The beneficiary should receive a tax credit for these savings on the tax return so theoretically I could put the full amount but this keeps it closer to the cash balance. I put “zero” in for all new stocks that either never pay dividends (lots of tech stocks) or haven’t yet because this makes the pivot table and lookup function work correctly (they need a value of zero).

Cash Flow Return Example

I also calculate the return adjusted for the timing of cash flows. It uses the formula listed above. For instance, your total portfolio could have a 40% gain over 4 years which would seem like 10% / year but actually it is higher because you didn’t put all that money in on day one 4 years ago, you put it in even payments, so it could be 15% / year (this is just a fictitious example). This gets kind of complicated but it is an important concept in investing.


In summary, I try to do as much as possible in the consolidated work sheet to minimize the amount of work I need to do in each individual sheet. The sheets are linked and the consolidated sheet drives most of the formulas in the detailed sheets. I do need to update individual dividends by portfolio and also calculate the impact of buys / sales and their own unique timing of cash flows to calculate their annual return.

Each of the beneficiaries should have a link to the main consolidated google sheet tab and then their own sheet. I also make a copy of them to keep locally in case the formulas get messed up for whatever reason (you can adjust them).

Google sheets works on phones and computers and everywhere and updates real time. Periodically the formulas don’t work, but it usually corrects itself quickly.

Consolidated Portfolio View, December 2019

Trust fund performance December 2019
Trust fund performance December 2019

We run eight portfolios that range in age from 4 to 18 years.  The portfolios are primarily invested in individual US and world-wide stocks, with approximately 17% of the $203,475 in cash / bonds.

The 8 portfolios combined have grown about 18% during the year 2019 (not including additional contributions).

This is a bit lower than the 29% return for VTI (a proxy for the US market) and 21% return for VEU (a proxy for the non-US market) weighted for cash (since we have 17% cash & bonds, we are only 83% in the market),  but reasonably competitive.  An average benchmark for us (split between US and non-US) and weighted for equity participation would be about 20%.

Don’t forget dividends… often when people look at market performance they focus on the share prices and not the total returns.  Our portfolio in total across all 8 beneficiaries returns about $4000 / year in dividend and interest income or a 2% yield.  While this seems small in a time of rising markets, dividends & interest are a substantial component of total market earnings over time.

These results have been satisfying but our eyes are substantially on the long term.  The markets have been moving up but they could turn and as such we will remain watchful.

Portfolio Seven Updated Feb 2018, and It’s Tax Time

Portfolio 7 is 3 1/2 years old.  The beneficiary contributed $1500 and the trustee $3000 for a total of $4500.  The current value is $5991 for a gain of $1491 or 33%, or about 14% / year when adjusted for the timing of cash flows.  Go here for portfolio detail or go to the link on the right.

During 2017 we sold Spirit Airlines (SAVE) for a long term capital loss of $84 and had dividends of $40.  The portfolio is doing well, with holdings in BABA (Alibaba) and MA (Mastercard) doing very well.  The portfolio has mostly recovered from early February market activity.

Portfolio Six Updated Feb 2018, and It’s Tax Time

Portfolio Six is 5 1/2 years old.  The beneficiary contributed $3000 and the trustee $6000 for a total of $9000.  The current value is $10,716 for a gain of $1716 or 19%, which is 5% / year across the life of the portfolio.  Go here to see detail, or use the link on the right.

We had $157 in dividends and a sale of TTM for a long term gain of $35.  The portfolio has bounced back from February market activity and is doing OK.

Portfolio Four Updated February 2018, and It’s Tax Time

Portfolio Four is 8 1/2 years old.  The beneficiary contributed $4500 and the trustee $9000, for a total of $13,500.  The current value is $18,309 for a gain of 36%, which is 6% / year adjusted for the timing of cash flows.  Go here for details or use the link on the right.

For tax purposes, during 2017 we sold 2 stocks, Devon (DVN) and Spirit Airlines (SAVE) for $465 in long term capital losses, and earned about $297 in dividends.

The portfolio is doing pretty well and has bounced back from recent market losses.

Portfolio Three Updated February 2018, and It’s Tax Time

Portfolio three is 10 1/2 years old.  The beneficiary contributed $5500 and the trustee $11,000 for a total of $16,500.  The current value is $23,251 for a gain of $6751 or 41%, which is 5.6% / year when adjusted for the timing of cash flows. Click here for details or use the link on the right.

During 2017 there were no sales and there was dividends of approximately $322.  The portfolio is generally doing OK and has bounced back from the recent market activity.

Portfolio Two Updated February 2018, and It’s Tax Time

Portfolio Two is our second longest lived portfolio, at 13 1/2 years.  This portfolio is unique because the individual stocks have been sold off and replaced with ETF’s and a CD.  See the details here or at the link on the right.

The beneficiary has invested $7000 and the trustee $14,200 for a total of $21,200.  The current value is $38,428 for a gain of $17,228 or 81%, which is 7.7% a year when adjusted for the time value of cash flows.

Walking through the detailed transactions often helps you to find items you’ve overlook – we noted that the biotech ETF IBB had a stock split (3-1) in December 2017 so I have been understating the value of this portfolio by almost $2000 since that time on my consolidated view.

There were no stock sales last year so the only tax impacted item is dividends which were approximately $632 during 2017.

The portfolio is doing well.  It is interesting to see that the VEO ETF has returned 33% including dividends since we’ve owned it but the HEFA ETF has returned 19% including dividends… the difference is due to the 10% or so fall in the US dollar vs a basket of other world wide currencies.  HEFA is hedged so you get returns in original currencies while VEO also includes the net effect of the dollar on returns (which magnified returns in this case).


Portfolio One Updated February 2018 – And It’s Tax Time

Portfolio One is our longest lived portfolio, at over 16 1/2 years.  The Portfolio began right after 9/11.

The beneficiary has contributed $2000 (net of withdrawals) and the trustee has contributed $16,000 for a total of $18,000.  The current value of the portfolio is $43,441 for a gain of $25,441 or 141%, which is 7.2% / year adjusted for the time value of cash flows.

Portfolio One is the most advanced in that 1) I’ve transferred the account over to the beneficiary 2) I have switched to an “agent” mode where I can still make transactions like buys or sells (and this still benefits from my free commissions) 3) the beneficiary is starting to “draw down” some of the assets from the portfolio in order to fund purchases (capital assets and the like).

Go here for a summary of Portfolio One or click on the link on the right.

There were three sales last year (BOX, KO, TATA) and one purchase (NVDA).  Generally the portfolio has done well, although we (obviously) sold far too earlier on AMZN and MSFT.  The three sales had a net long term gain of $948, which will be subject to capital gain taxes.

It is important to recognize the positive impact of dividends on a portfolio like this – to date it has earned $6894 in dividends and $805 in 2017.  When you just look at stock prices against original purchase cost you miss the significant impact (over time) of dividends.  One of the major purposes of going through all this work on the portfolio is to align dividends with the stocks that drove the dividends, to see total returns.

The portfolio is generally doing OK; like everyone else we had a scare when the stocks went down in early 2018 but they’ve (mostly) come back since then.  In an earlier post we discussed moving some of the funds into cash / gold to reduce overall portfolio risk.  This is still being considered.

Portfolio Two Updated August 2017

Portfolio Two is our second longest lived portfolio.  This portfolio has been converted to ETF’s and a CD.  Beneficiary investment is $6500, trustee investment is $13,000 for a total of $19,500.  Current value is $34,290 for a gain of $14,790 or 76%, which is 7.8% over the life of the fund annualized.  Go here or to the link on the right for the portfolio detail.

This portfolio is different from the others in that there is a 1.55% CD for $10,000 and the rest are ETF’s.  The largest ETF is VTI (US total index) with VEU (all world ex US) and HEFA (non US, hedged).  We also have a small position in IBB for biotech.  All seem to be doing well.

It is a symptom of ZIRP that our CD returns less than the US or European stock funds, which are around 2.5% / year.

Portfolio Four Updated July, 2017

Portfolio four is almost 8 years old.  The beneficiary contributed $4000 and the trustee $8000 for a total of $12,000.  The current value is $15,082 for a gain of $3,082 or a 5% rate of return, adjusted for the timing of cash flows.  See detailed PDF here or go to the link on the right side of the page.

The portfolio has some technology stocks that are doing quite well, which include Box and Oracle (you could also call Tesla a partial technology stock, as well).  The oil stocks of Devon, Shell and Statoil have generally been hit by the continued fall in oil prices.  The stock prices of Shell and Statoil have held up better than Devon because they kept their high dividends; Devon cut their dividend and has continued to fall (there are other factors at play as well).

It is important in all these portfolios not to just look at the current share price when compared with the purchase price; you need to take into account dividends, as well.  The oil stocks look bad on stock price alone but when cumulative dividends paid are tracked as well, the situation is much better.  That does not mean that we should hold stocks just for the dividends, but it is a very important factor in long run performance.  To date this portfolio has earned $1735 in dividends, which makes up more than half of the total return earned to date.

New Google Sheets Analytics – Sector, US / Foreign, and Dividend Views

I really enjoy working with Google Sheets and the Google Finance portfolio functions.  Recently I moved tracking from excel to Google Sheets and sent links to the beneficiaries so that when they open the file, the stocks update automatically.  I made 8 of these sheets and sent them to each individual beneficiary, and learned a lot along the way.

There still is some manual and redundant work done within each spreadsheet and for me to track performance, I had to open each sheet individually.  Thus I went to work and built a summary sheet that taps into each of the 8 individual portfolios and shows performance against a 4/30/17 baseline (I just hard coded that baseline).

Recently I expanded that model to take each individual stock in any portfolio and make a consolidated view that included 1) sector information 2) US vs. Foreign 3) Yield 4) description of stock and reason for buying.  Now I can update that table in one place and re-do each of the portfolios 1-8 so that these fields are updated and consistent across each portfolio (I still have to do that, but I will in the relatively near future).  Here is a link to the data in PDF form.

 Portfolio 5/15/17 4/30/17 Change $ Change %
Portfolio One $42,377.71 $41,514.50 $863.21 2.08%
Portfolio Two $33,665.44 $33,334.33 $331.11 0.99%
Portfolio Three $17,972.62 $17,761.07 $211.55 1.19%
Portfolio Four $14,677.84 $14,625.89 $51.95 0.36%
Portfolio Five $14,479.56 $14,582.35 -$102.79 -0.70%
Portfolio Six $7,823.01 $7,834.26 -$11.25 -0.14%
Portfolio Seven $3,941.67 $3,879.91 $61.76 1.59%
Portfolio Eight $3,233.08 $3,258.89 -$25.81 -0.79%
Total $138,170.93 $136,791.20 $1,379.73 1.01%

Continue reading “New Google Sheets Analytics – Sector, US / Foreign, and Dividend Views”

Portfolio Four Updated April 2017

Portfolio Four is 7 1/2 years old.  The beneficiary contributed $4000 and the trustee $8000 for a total of $12,000.  The current fund value is $14,444 for a gain of $2444 or 20%, which is 4% / year when adjusted for the timing of cash flows.  You can see the detail here or go to the links on the right.

We have a couple of stocks on watch.  Devon Energy (DVN) got waxed with the downturn of the oil industry a couple of years ago and has recovered a lot of its losses but cut its dividend significantly in the interim.  DVN seems to be plateauing and is thus on watch.  Oracle (ORCL), the technology company famous for its database software (although they own many other products, including cloud software) is in a long term price and technology war with AWS and other cloud providers (including Microsoft’s Azure).  They have been performing well but are on watch as a result.

There are analytics on the “analytics” sheet.  A few worth paying attention to is the price as a % of its 52 week high, which shows its relative strength over the last year (obviously anything near 100% means that the stock is moving up and hitting highs regularly).  The portfolio is 68% US stocks and 44% of them are “high dividend” (meaning a dividend around 3% and higher).


Portfolio Five Updated April 2017

Portfolio 5 is 7 1/2 years old.  The beneficiary contributed $4000 and the trustee $8000 for a total of $12,000.  The current value is $14,151 for a gain of $2151 or 18%, which is about 3.6% / year adjusted for the timing of cash flows.  You can go here to see the portfolio or go to the links on the right side of the page.

The portfolio is about 50/50 with US and foreign stocks.  Almost half the stocks are considered “high dividend” with a dividend of near 3% or greater.  Gilead (GILD) is a recent drugmaker purchase and Anheuser Busch InBev (BUD) are both pretty well run companies on or near watch.

On a side note, this is one of the first portfolio to be almost totally run by formulas in Google Sheets.  I incorporated vLookups and re-arranged the sheets a bit to have more of the information on buys and sells populate automatically.  Due to these changes, it will be much easier to update this portfolio in the future and it will be the template that I will apply to the other portfolios as I migrate them to Google Sheets.

Portfolio Six Updated April, 2017

Portfolio 6 is 4 1/2 years old.  The beneficiary has contributed $2500 and the trustee $5000 for a total of $7500.  The current value of the portfolio is $7806 for a gain of $306 which is about 4% or 1% / year over the life of the fund.  You can see a PDF of the details here or on the link to the right.

The portfolio is generally doing well.  The stock mix is 55% US stocks and the largest exposure area is oil, at 27% of the total.  High dividend stocks (3% yield or greater) make up 44% of the portfolio.  We sold off Coca Cola Femsa (KOF) recently.  The oil sector has generally been hit by the decline in oil prices per barrel but they have come up significantly from their lows.

Portfolio Six Updated January 2017 – Tax Time

Portfolio six has been around for about 4 1/2 years, with the beneficiary depositing $2500 and the trustee $5000, for a total of $7500.  The current value is $7917 for a gain of $417, or about 6% or 1.8% / year when adjusted for the timing of cash flows.  You can see the detail at the link on the right or download the spreadsheet here.

During the year we had about $136 in dividends for a yield of about 1.7%.  We had one sale, of Coca Cola FEMSA with a long term capital loss of ($321).  Our sales still look relatively good in hindsight.  For the portfolio as a whole, it has mostly recovered from the fall in oil prices.