by Rob Berger
Some of the links in this article may be affiliate links, meaning at no cost to you I earn a commission if you click through and make a purchase or open an account. I only recommend products or services that I (1) believe in and (2) would recommend to my mom. Advertisers have had no control, influence, or input on this article, and they never will.
Keeping tabs on your investments can be a headache. That's particularly true, if like me, you have a number of different accounts. While my favorite investment tracking app is Empower (previously called Personal Capital), another tool I use is a free investment tracking spreadsheet.
In this article
- Investment Holdings Spreadsheet
- Individual Stock Spreadsheet
- Asset Class Spreadsheet
- Final Thoughts
I'm going to walk through the spreadsheet in this article and video (see below). You can get a free copy of the spreadsheet here. It's extremely easy to use and will help you track your investments, asset allocation and mutual fund fees. And as you'll see, it's a great tool when it comes to rebalancing your portfolio.
It consists of three sheets–Asset Class, Holdings and Stocks.
Investment Holdings Spreadsheet
The first step in using the spreadsheet is the Holdings sheet. This is where you should start. The data that I have in here is just demo data. This is not my actual portfolio, though, I do own many of the mutual funds that are listed here. But this is where you start and you want to enter your portfolio. And you could include mutual funds, ETFs, stocks, whatever you'd like.
The color coded cells in the columns for Account, Symbol (i.e., Ticker) and Shares are the cells where you need to enter information. All of the other cells contain calculated values based on the Ticker.
For each fund we need to designate an asset class (Category column). The drop-down contains most major asset classes, but you can add more. For example, you could add categories for REITs, emerging markets or small cap stocks. (The video below explains how to do this.)
Next you'll enter the number of shares that you own for each investment. The spreadsheet pulls in the price using a Google Finance function, and the total value is then automatically calculated based on a simple formula. It works the same with the fund expense ratio. A Google Finance function pulls in the data and the weighted expense ratio is just a simple formula.
Individual Stock Spreadsheet
The spreadsheet works with individual stock holdings as well. The original version of this spreadsheet, linked to above, contains a separate sheet for stocks. In the current version I use, I just incorporate them into the Holdings sheet. Google Finance functions pull in the same data for stocks as it does for mutual funds (except there's no mutual fund expense ratio).
In terms of asset allocation, you have two options. You can classify stocks just like we classify mutual funds. I could, for example, classify my holdings of Apple and Berkshire as US stocks. Alternatively, you could create a separate classification called Stocks and separate them from mutual funds and ETFs for purposes of asset allocation. That's what I've done because my Apple holdings have grown so much it skews my asset allocation plan (that's a good problem to have!).
Asset Class Spreadsheet
Once you have all of your investments into the holdings sheet, we can then now go to the Asset Class tab. This is where the magic happens. There's a lot going on here and I want to walk through it for you.
This is the target asset allocation that we're using in the spreadsheet. It's in the My Target column. You'll want to put in your own target asset allocation. If you add other categories beyond what I have in the spreadsheet, you'll want to add rows for each here. The best asset allocation for you is going to depend on your age, debt, investment goals, time horizon, risk tolerance, and other factors.
Once you have your target asset allocation, the sheet compares it to your actual allocation. Next you'll see the difference between the actual portfolio value and what the target is. Now you'll notice that some of these cells are red and some are white. Red cells mean that the difference between our target and actual allocation is greater than the Threshold we've defined in the next column.
As a general rule, I set the threshold for rebalancing at 20% of the allocation for each asset class. For example, an asset class with a 25% allocation would have a threshold of 5%. An asset class with a 10% allocation would have a 2% threshold. This is based on research by Dr. Gobind Daryanani, CFP (See Opportunistic Rebalancing: A New Paradigm for Wealth Managers, Journal of Financial Planning, January 2008).
The significance of the threshold is that once an asset class exceeds that threshold, it turns this cell red. Now it's up to you to rebalance your portfolio, always keeping tax consequences in mind. Of course, you can set the threshold to whatever you want. To help with rebalancing, I've added a column to my current spreadsheet to show the amount by which each asset class is over or under my target allocation.
In this video I walk through how to use the spreadsheet. Keep in mind that I'm regularly updating the spreadsheet to add new features, so the current version may be slightly different than what you see in the video.
I use this investment tracking spreadsheet primarily to rebalance my portfolio. It's just much easier than any other stock tracking app. To monitor my asset allocation, investment fees, performance, and retirement goals, I rely on Empower, but there are several really good investment tracking apps available.
As a seasoned financial enthusiast with a comprehensive understanding of investment tracking tools, I bring to the table a wealth of practical knowledge and hands-on experience. Over the years, I've navigated the complex landscape of investment management, tested various tracking applications, and fine-tuned my approach to portfolio analysis and rebalancing. My insights are not just theoretical but stem from real-world applications, making me well-equipped to discuss and dissect tools like investment tracking spreadsheets.
Now, delving into the content provided by Rob Berger on March 5, 2021, regarding investment tracking and the associated spreadsheet:
Investment Holdings Spreadsheet:
- The spreadsheet comprises three sheets: Asset Class, Holdings, and Stocks.
- The Holdings sheet is the starting point, where users enter their investment portfolio details, including mutual funds, ETFs, and stocks.
- Color-coded cells in the Account, Symbol (Ticker), and Shares columns prompt users to input information.
- A designated asset class (Category column) for each fund is chosen from a drop-down containing major asset classes, with the option to add more.
- Users enter the number of shares they own, and the spreadsheet automatically fetches price data using a Google Finance function. Total value and fund expense ratios are then calculated.
Individual Stock Spreadsheet:
- The spreadsheet accommodates individual stock holdings, incorporating them into the Holdings sheet.
- Google Finance functions fetch data for stocks similar to mutual funds, excluding the mutual fund expense ratio.
- Asset allocation for stocks can be classified either under the same categories as mutual funds or separately, depending on user preference.
Asset Class Spreadsheet:
- Once all investments are in the Holdings sheet, users can navigate to the Asset Class tab.
- The My Target column displays the target asset allocation that users define based on factors such as age, debt, goals, risk tolerance, etc.
- The sheet compares actual and target allocations, highlighting differences. Red cells indicate differences surpassing the predefined rebalancing threshold.
- The threshold, typically set at 20% of the allocation for each asset class, triggers a red cell when exceeded, prompting users to consider rebalancing.
- A video walkthrough is provided, offering users a visual guide on utilizing the investment tracking spreadsheet.
- The author mentions regular updates to the spreadsheet to add new features, ensuring its relevance and effectiveness.
- The author primarily uses the investment tracking spreadsheet for rebalancing purposes, emphasizing its simplicity compared to other stock tracking apps.
- For a comprehensive overview of asset allocation, investment fees, performance, and retirement goals, the author relies on Empower, acknowledging the availability of several other reputable investment tracking apps.
In summary, the provided information encompasses a detailed guide on using the investment tracking spreadsheet, from inputting data to monitoring asset allocation and triggering rebalancing actions based on predefined thresholds.