Organizing Your Portfolio

In order to understand your investments, you need to put all of your investments on a single page. Most people have their data in various locations, such as Quicken or in other financial planning tools. However, few have them organized into a single view so that they can see the key variables at a glance. This is very important and here is how you can do it.

There are a few key elements to every investment. For the purpose of this blog post, I am massively simplifying investing and if you have a complicated portfolio you should likely see a professional. But everyone can benefit from attempting to put these elements on a single page.

The first thing you have to do is go and find all of your investments. This is often far harder than you would think. Let’s look at some of the common elements:

  1. Brokerage statement – if you have stocks and bonds and cash in a firm like Fidelity, Vanguard, eTrade, your band, etc… pull that out and get the detail
  2. IRA / 401(k) – as you switch jobs, you will accumulate IRA’s and 401(k)’s with different employers (unless you consolidate them, which I recommend). Pull all of these out, as well
  3. Bank – you will need your bank statement, for your balances in checking / savings / money market
  4. Stock apps – if you have (significant) stock in apps for trading, you need that information as well
  5. ESPP / Options – if you have employer stock or options that are in the money or restricted stock, you should include that too
  6. Deferred compensation – if you have any other form of deferred compensation, find those documents, and you also should determine what is vested
  7. Government debt – many people have treasuries or other types of government bonds that they own or were gifted
  8. Insurance / annuities – these get more complicated but if you have significant investments in these types of assets you should get them out, as well
  9. Land / Debt / Mortgage – this author is not an expert on debt / mortgages but you should include at least the net value as real estate here
  10. Crypto – if you have crypto that is significant, you should include that as well

Once you have all this information, you need to classify it a few ways. These include:

  • pre tax or post tax – it is important to classify assets as pre or post tax. Your pre tax investments are effectively “overstated” because at some point they will (likely) need to “wash” through and become taxed
  • By type of investment – you can get as detailed as you’d like but at a minimum I generally use “cash like”, “investment grade bonds”, “US equities”, “non-US equities”, “gold”, “real estate”, “other”. If you have a concentration in a particular stock, add that stock specifically (could be your employer, your ESPP, you have options with them, etc…) if it is more than 2% or so of your portfolio
  • By ticker – ideally, when you get the information together, get the specific “ticker” such as VTI for the vanguard ETF or the specific stock ticker. There also are tickers for most mutual funds. If you are in a proprietary fund, they still will have some sort of unique identifier, try to get that, because you can use it to track your # of shares, price per share, and then categorize the investment
  • For the sake of this analysis, I am assuming that you are “dollar based”. If you are based in a foreign currency, you also need to add what currency it is based in because moves in that currency vs. the dollar will be a very significant variable

When you have this information, you need to put it down into a spreadsheet (ideally a google sheet, so that you can auto-update it by price or update the ones such as proprietary investments periodically), and look at it and see if it makes sense.

When people look at the stocks that they are considering for investing, it really only makes sense in the total context of their portfolio. If you have a lot of cash or cash like investments elsewhere, then you can consider a higher portion of your stocks being “in the market” (and at risk). But you can only do this if you first put it all down on a sheet of paper (or in a spreadsheet).

Once you do this, ideally you would quarterly calculate your “net worth” (for this purpose, your debt is “net” on real estate and I’m not considering other forms of debt like student debt or credit card debt, but you can add that in as an offset) and physically look at it. Once you gather all the investments together, looking at this quarterly is not as difficult as you’d expect, but the first time is harder. Not only would you know your net worth, but you would be able to see at a glance your position in cash, investment grade debt, equities, etc… at a glance which is a critical part of the equation.

Portfolio One Updated November 2020

Portfolio One is our longest lived portfolio.  It is 19 years old.  The beneficiary contributed $5000 and the trustee $19,000 for a total of $24,000.  The current value is $72,519 for a gain of $48,519 or 202%, which is 8.6% / year adjusted for the timing of cash flows.  You can see details here or at the link on the right.

Portfolio one has 20 stocks.  The major gains have come from Taiwan Semiconductor Manufacturing (TSM), PayPal (PYPL), Nvidia (NVDA), and Procter & Gamble (PG).  Other winners include Alibaba (BABA), Accenture (ACN), Infosys (INFY), Toyota (TM), and American Electric Power (AEP).  Stocks hitting recent bumps include EA (earnings), CME (ZIRP), SAP (earnings), and BABA (delay of ANT IPO).

Portfolio Two Updated November 2020

Portfolio 2 is 16 years old.  The beneficiary contributed $13,000 and the trustee $17,200 for a total of $30,200.  The current value is $54,779 for a gain of $24,579 which is 81% or 7% / year when adjusted for the timing of cash flows.  Go here for details or at the link on the right.

The portfolio consists of mostly ETF’s with some recent stock purchases.  The portfolio is doing OK overall but Alibaba (BABA) went down recently due to the deferral of the ANT IPO by China.

Portfolio Three Updated November 2020

Portfolio 3 is 13 years old.  The beneficiary contributed $7000 and the trustee $14,200 for a total of $21,200.  The current value is $31,205 for a gain of $10,005 or 47%, which works out to 5% / year when adjusted for the timing of cash flows.  Go here or to the link on the right for more details.

The portfolio consists of 3 stock ETF’s, one bond ETF, a gold ETF, and cash.  They are all doing well.

Portfolio Four Updated November 2020

Portfolio Four is 11 years old.  The beneficiary contributed $6000 and the trustee $12,000 for a total of $18,000.  The current value is $30,032 for a gain of $12,032 or 67%, which is 7.7% / year adjusted for the timing of cash flows.  See the details here or on the link on the right.

Gains have come from Tesla (TSLA), Nvidia (NVDA), Procter & Gamble (PG), Oracle (ORCL), and Wal-Mart (WMT).  There are 19 stocks in the portfolio.  The others are generally doing OK although Box (BOX), Elbit (ESLT), and CME (CME) are down a bit recently.

Portfolio Five Updated November 2020

Portfolio Five is 11 years old.  The beneficiary contributed $6000 and the trustee $12,000 for a total of $18,000.  The current value is $31,765 for a gain of $13,865 or 77% which is 8.5% / year adjusted for the timing of cash flows.  Go here or to the link on the right for more detail.

Gains in the portfolio have been driven by Appian (APPN) which has had a huge run up lately, Cloudflare (NET), Nvidia (NVDA), OKTA (OKTA), Paypal (PYPL), Alibaba (BABA), Union Pacific (UNP) and Procter and Gamble (PG). There are 17 stocks in total in the portfolio, the others are doing OK or are too new to rate. SAP (SAP) was hit recently with their earnings report but is still up overall, and Alibaba (BABA) is still up significantly but fell with the deferral of the ANT IPO.

Portfolio Six Updated November 2020

Portfolio Six is eight years old.  The beneficiary contributed $4500 and the trustee $9000 for a total of $13,500.  The current value is $20,150 for a gain of $6,650 or 49%, which is 8% / year when adjusted for the timing of cash flows.  Go to the link on the right or here to see the details.

Gains have come from OKTA (OKTA), Taiwan Semiconductor Manufacturing (TSM), Nvidia (NVDA), Union Pacific (UNP) and Procter and Gamble (PG).  There are a total of 13 stocks in the portfolio; the others are doing OK or it is too early to tell.

Portfolio Seven Updated November 2020

Portfolio seven is five years old.  The trustee contributed $6000 and the beneficiary contributed $3000 for a total of $9000.  The current value is $13,919 for a gain of $4,919 or 57%, which is 13% / year adjusted for the timing of cash flows.  Go to the link on the right or here to see the details.

Portfolio gains have been driven by Mastercard (MA), Alibaba (BABA), Taiwan Semiconductor Manufacturing (TSM) and PayPal (PYPL).  There are 10 stocks in the portfolio.  The other stocks are doing OK or it is too early to tell.

Portfolio Eight Updated November 2020

Portfolio Eight is 5 years old.  The beneficiary contributed $3000 and the trustee $6000 for a total of $9000.  The current value is $14,361 for a gain of $5,361 or 60%, which is 13.5% / year when adjusted for the timing of cash flows.  Go here or the link on the right to see details.

Performance has been driven by gains in Mastercard (MA), Nvidia (NVDA), Paypal (PYPL) and OKTA (OKTA).  There are a total of 10 stocks in the portfolio.  Generally the other stocks are performing adequately or it is too early to tell.

Portfolio Update November 2020

It has been a wild ride for stocks with Covid.  About 3 months ago in August the market started getting choppy.  During September the trustee and the beneficiaries contributed $18,000 to the portfolios.  Thus the increase from August to November is about 7.5% ignoring the incremental contributions, which works out to about 35% / year.

Generally the portfolio selections have been doing well, with a few exceptions.  SAP, the German software maker, was hit based on recent quarterly results and future projections.  Alibaba (BABA), the Chinese e-commerce giant, went down when the IPO of Ant was delayed right before launch.  We also had a fall in CME due to predictions of low volatility, Elbit Systems (ESLT), and Gilead (GILD).  None of these are in crisis, but since there are 40+ individual stocks in the portfolio we would expect a few to be always lagging.

Stock Selections for Fall 2020

 It is time to make selections for Fall 2020.

For our 2020 stock picks, we will provide a variety of options:

  • There were a number of companies that went public recently.  We will offer some choices in this area
  • We want to have some options for non-US companies, usually via ADR’s (which allow you to buy on US exchanges, in US dollars)
  • We also have a number of “out of favor” companies that might be worth considering
  • We have a number of stock choices of well performing US and foreign companies

With the return on cash being almost zero, we may want to consider using the BND (Vanguard bond ETF) which has some low amount of risk but has a return over 2% instead of cash for money that does not want to be in the market.

Recent IPO’s

  1. Rocket Companies (RKT) is an online mortgage provider that has grown to be the largest mortgage originator in the USA with a digital platform.  It’s market cap is $45B and I don’t believe that they will pay a dividend.
  2. Sumo Logic (SUMO) is a data and analytics company with strong growth.  The CEO has had positive history with other companies.  SUMO market cap is $2B with no dividend.

Non-US Companies

  1. Roche Holding AG (RHHBY) is a Swiss healthcare and pharmaceutical company and is the world’s largest biotech company.  Roche market cap is $300B and they have a 2.5% dividend.
  2. Spark New Zealand (SPKKY) is a New Zealand wireless company that is well run and poised to benefit with continued digital transformation.  Spark’s market cap is $5B and they have a 5-6% dividend.

Out of Favor Companies

  1. Kraft Heinz (KHC) is a world-wide food and beverage company made up of acquisitions that had some difficulties and write-offs.  They have a good looking turn around plan and have promised many improvements.  Kraft Heinz’s market cap is $38B and they have a 5% dividend
  2. Chevron (CVX) is a large oil company that is well run and has taken the mantle of leading US oil company from Exxon-Mobil.  While oil now remains at the low price of $40 / barrel and may remain there for many years, the world still is powered by oil and will be for several decades to come and this can be an opportunity to buy shares at a low price.  The market cap is $135B and the dividend is around 7%.

Core Holding Candidates

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:

  1. Electronic Arts (EA) – videogames
  2. Mastercard (MA) – electronic payments and credit cards
  3. CME Group (CME) – financial services
  4. Alibaba (BABA) – Chinese e-commerce giant
  5. Nvidia (NVDA) – semiconductors
  6. OKTA (OTKA) – SAAS provider of security services
  7. Paypal (PYPL) – electronic payments
  8. Procter & Gamble (PG) – iconic brand company
  9. Taiwan Semi-Conductor (TSM) – Asian chip giant
  10. Union Pacific (UNP) – railways
  11. Facebook (FB) – social media platforms
  12. TESLA (TSLA) – electric cars
  13. Wal-Mart (WMT) – massive retailer
  14. Cloudflare (NET) – new cloud stock with potential
  15. Gold ETF (IAU) – tracks price of gold

Stock Picks By Portfolio:

  • Portfolio One – DONE
  • Portfolio Two – DONE
  • Portfolio Three – DONE
  • Portfolio Four – pick one
  • Portfolio Five – pick one
  • Portfolio Six – pick one
  • Portfolio Seven – pick two
  • Portfolio Eight – pick two

Portfolio Update October 2020

All of the beneficiaries have made their contributions and they have been matched and it is about time to select new stocks for fall 2020. There were some delays because one of the portfolios was moved from the trustee to the beneficiary and then we had to set up agency and connect to other accounts. These sorts of activities generally take a long time with external financial institutions and require a few interactions with support so they demand patience and focus. We are still working through a few tweaks even now.

Performance Oct 2020

Before we select stocks for 2020, we will look back on performance so far. Our low, like most portfolios, occurred in March 2020. Since that point, ignoring new investment, we are up 46%. If you take into account the portions of the portfolio that are in cash or bonds, our portion “in the market” is up even more, at 56%, in about six months (half a year).

Given that the long term expectation of stock market performance is 6-7%, we basically received perhaps 8 or so years of returns in six months, albeit from a reduced base (measuring from the lowest point in the markets).

This performance shows the importance of remaining in the market during risky times – if we had sold off and gone into cash / bonds during the lowest point, we would have missed out on this period of exceptional gains. On the other hand, we have no expectations of the future and equities could have entered an era of low gains and poor performance such as occurred after the dot.com crash near the year 2000 or the 2007-8 financial crisis.

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.

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.

Portfolio Three Updated August 2020

Portfolio Three is 13 years old. The beneficiary contributed $6500 and the trustee $13,200 for a total of $19,700. The current value is $28,842 for a gain of $9142 or 46%, which is 5.3% / year adjusted for the timing of cash flows. See details here or at the link.

The portfolio is 26% BND, which surprisingly has returned almost 9% annualized (the dividend yield is 2%, it increased in value as interest rates came down post Covid to near zero). The portfolio is 11% cash, which now unfortunately returns almost nothing (0.1%).

Portfolio Two Updated August 2020

Portfolio Two is sixteen years old. The beneficiary contributed $8000 and the trustee $16,200 for a total of $24,200. The value is $46,803 for a gain of $22,603 which is 93% or 7.4% / year when adjusted for the timing of cash flows. See the summary here or at the link.

The portfolio is one of the two that has moved to ETF’s. The portfolio is 28% cash and 11% BND, meaning that almost 40% of the value is out of the market (and also effectively can’t go down). Given that zero interest rate policy (ZIRP) is back, cash is now effectively yielding zero (0.1%) so may consider moving it into BND where it at least makes 2% (with some risk on the principle if rates rise).