Historically I have had 2 separate sheets for each portfolio 1) a “summary” consolidated sheet that is updated real time 2) “detailed” sheets for each individual portfolio that I updated periodically (every 6 months or so).
I recently combined the useful functions of the individual portfolios and added them to the consolidated portfolio sheet. As a result of this, when you go to the consolidated tracker, you can see enriched real-time data about your portfolio.
For instance – let’s start with the summary.
This is the same summary that I have been using for several years. The stock prices are updated with a 20 minute delay from Google Sheets (they have a finance function). I track the # of shares and periodically update the cash balance as dividends get paid out but that is relatively minor and the values should stay close to the brokerage balance (unless there are stock splits or other periodic changes).
Here is the new “unique” sheet. There are a host of new items that I’ve updated that are essentially real-time (or within 20 min or so if the markets are live).
- Balance
- Contributions by the trustee and the beneficiary
- Total return
- Total returns to beneficiary based on what they’ve contributed vs. total balance
- Effective yield on the portfolio
- Metrics on the portfolio including dividend yield category, type of investment, US / Foreign, and by category (doing well, on watch, etc…)
- For each individual security (some stocks, some are ETF’s) you can see the purchase price per share, the current price, and the current gain or loss
- for each security, the expected annual dividends (or interest paid in the case of cash)
In the past I resisted doing this because it overvalues the stock price change (gain or loss) and undervalues the cumulative impact of dividends. However, by looking at yield and realizing that this is an important part of the process, we can cover the same effective goals.
Trust Funds For Kids 