After the nadir in Spring 2020, our portfolios have performed well. We are up about 15% in the last three months with no incremental capital added, which annualizes to a 60% rate (ignoring compounding).
We are reviewing all the stocks as we look at performance in 2020 for tax purposes. Generally they are performing well with a couple of exceptions that we will watch (AEP, the utility stock, and GILD the pharma company).
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.
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.
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.
The stock market has recovered almost all of the losses incurred with the pandemic. From peak to trough (Feb 2020 to March 2020) we lost about 23% of our value. Today, we are down about 7% from peak. Our percentages do not completely align with the market because some portfolios hold up to 20% cash and some bond investments (BND ETF from Vanguard) and Gold (IAU ETF ticker). This performance generally aligns with riding the market down and then back up again.
Whether by luck or design, our portfolios did not hold most of the industries that bore the brunt of the Covid impact, like airlines, hotels, and commodities. We did have some stocks that we recently sold in some of these hard hit areas.
I reviewed the rest of the portfolio and we are continually “pruning down” the list of stocks that we hold. There are about 30 stocks held across the portfolio right now, down from about 40 or so in the relatively recent past. This does not include ETF’s.
The stocks that have driven the most value in the portfolio that are not bought across all the portfolio (because every beneficiary selects individually) are:
It has been a difficult period for markets in March 2020 with the economic impacts of Coronavirus. The markets are generally down about 30% which is reflected in our portfolios, below. Activity is also changing quickly and there isn’t a clear pattern or floor on stock prices. Our total value of $164k is about what it was 2 years ago in mid-2018.
I am now in the process of reviewing stocks that have either performed significantly better than the market or significantly worse. A quick review of the portfolio has some stock categories that are facing major headwinds:
Oil and gas stocks have not only been hit by the market reductions, they’ve been battered by the decrease in oil prices down near $20 / barrel, an unprecedented number we haven’t seen in almost 20 years.
Auto stocks are sensitive to demand and have a heavy capital intensity. They have been hit very hard by this situation
Fortunately we did not have any airlines or aircraft manufacturers in the portfolio, or any hotel or cruise ship operators. These industries may be facing something near extinction in their current forms
Only a few stocks have bucked a near-30% decline – grocery retailers like Wal-Mart and some drug stocks (within our portfolio). Some of the tech stocks are also holding up reasonably well and may in fact be bargains
European and Asian stocks are getting a double-whammy in that the US dollar is increasing in value. This compounds their losses
We did not have a lot of exposure to banks, but did have some Canadian and Australian banks for currency diversification. The governments may intervene but most banks are likely insolvent when you look at the likely level of defaults and decreasing value of their loans and underlying assets
It is important to remember that we started these portfolios literally on September 12, 2001 – the day after 9/11. In the long term, equities and investing have been good to us and the method of the trustee making a deposit and then matching the beneficiary means that in almost all conceivable cases the beneficiary would lose “net” on their contributions.
However, it is time to revisit investment assumptions and refresh approaches to sectors and countries. A 30% reduction in values and an economic reset is an excellent time to re-think for a go-forward plan.
The portfolios are up about 5% for YTD 2020. This is roughly in line with benchmarks – US markets are up around 5% and non-US markets are down 3% (down 2% when you take out the additional impact of the strengthening US dollar). The markets have kept rising after the large gains in 2019 and with the headwinds of many international events.
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.
Since we moved the portfolios to Google Sheets and integrated them to a single data source, it has been much easier to review overall performance (in aggregate) and performance within a single one of the 8 portfolios. Like the rest of the overall market, we hit a peak in September at around $180k and have since declined to $168k (a loss of $12k or about 7%). Traditionally, October has been a month where difficult events occurred, so for whatever reason it happened again in 2018. Generally the Tech giants have been hit hard, and although we are not invested in many of the most prominent names (Amazon, Google), we do have some investment in Facebook as well as in the Chinese names like Alibaba, who also were impacted. When this settles down a bit (likely after the elections, for good or ill) we will look through the winners and losers and see if there are any significant actions to be taken among portfolio stocks. Note that even with recent losses, we are still up from about $137k in April 2017 to $168k in November 2018, which is approximately 20% (it is a bit less than the math would first appear because of the net effect of incremental investments and withdrawals during the period).
I moved the portfolios to Google Sheets to take advantage of Google’s ability to use financial functions like “price” by ticker so that my stock quotes could be updated with live data when you click on the spreadsheet. These sheets can also be shared with anyone (and it is free unlike Office 365).
I have been working with these sheets for a while to 1) simplify how they work 2) build them in such a way that I can “copy and paste” into other google spreadsheets 3) link the sheets together so that I only have to do selected work one time.
We are going to start with the “All Stocks” view because this page is crucial to understanding the entire model, including what can be linked to Google Sheets and what needs to be created and / or input from another source. Once this page is updated with key information, however, it can be used by all the other sheets and I only have to input data or do analysis one time and it automatically updates everything across all the portfolios.
The “ticker” symbol is used to derive values in google sheets using the “GoogleFinance” formula. I manually added (one time since this page is essentially a central database for all the other portfolios).
For each stock I use the GICS classification system (used by Vanguard) which I describe here. I map each of the stocks using this model as follows:
type (US or foreign)
“Yield” is something that the GoogleFinance formula doesn’t give you. This is the dividend % for the stock and it is important information. I look this up, manually, stock by stock, using google or yahoo finance or marketwatch. Some of the ADR’s are harder to find.
I also classify stocks by “dividend level” based on yield which I made up and categorized stocks as follow:
>3.9% – very high
>2.7% – high
> 1.4% – medium
Less than 1.4% – low
I have a “description” field which is my all in summary of the stock. This summary takes into account our original plan for the stock, its price against its 52 week high, potential as a takeover candidate, potential for a dividend cut, or any other information I feel is relevant. I write this once for each stock in any of the 8 portfolios and propagate it through all the portfolios.
The price is derived by the GoogleFinance function and the 52 week high. Then the stocks current price is compared to the 52 week high and shown as a percent. The categories are:
If > 94% of 52 week high then “near high”
If > 90% of 52 week high then “doing OK”
If > 80% of 52 week high then “at risk” (and colored yellow by conditional formatting)
If less than 80% of 52 week high then “on watch” (and colored red by conditional formatting)
This is the single most important page and contains most of the logic used for analytics as well as the summary description of how that stock is doing “all in”. In future posts we will review the other pages.