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.
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.
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).
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).
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.
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.
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).
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.
Conclusion
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.
Trust Funds For Kids 
Leave a Reply