Understanding Your Asset Allocation with Spreadsheets and Personal Capital

Every few months I share all of the investments I’m in through my quarterly investment report. One of the recommendations I’ve heard when choosing an investment advisor has always been to ask what they’re invested in, as that goes to show what they really believe. Although I’m not an investment advisor (that should be a big red text by the way), since I write about investments I think it’s important to practice what I preach and be transparent about it.

Asset allocation is one of the most important things to get right with your investments. Investing long-term in good, low-fee index funds is the most important thing – in my view at least. Asset allocation is a great way to keep yourself accountable for that. I personally use two tools to understand my asset allocation and determine if it’s time to make a change – by manually tracking it and through Personal Capital (both free for this case). Let’s start with Personal Capital since that’s a little more pretty.

Asset Allocation Through Personal Capital

If you’re not aware of Personal Capital, they provide an online tool that will pull in data from each of your accounts and display it in one nice dashboard (similar to Mint). By adding your investment, checking, retirement and other accounts, they provide a single a single place to view those balances over time. They even allow for manual accounts, which has been handy for tracking my offline Bitcoin balances.

I like Mint’s interface for tracking spending, but I prefer Personal Capital for anything related to investments by a mile. Their tools for understanding what you’re invested in are the best once I’ve seen so far from anywhere. After adding all of your accounts, you’ll have access to a handy “Asset Allocation” page that breaks down what you’re invested in. Mine looks something like this:

This chart shows a lot of information. Just in the bottom row alone, you can see my overall allocation. About half of my investments are in the US Stock market, which is right about on track my own goal for how I want to invest.

Considering my target allocation is something like this, I’m not toooo far off.

I’m a little underinvested in International Funds and Bonds at the moment, and overinvested in “Unspecified” – which in this case is Bitcoin/Ethereum. I’m dollar cost averaging the sales of these as soon, and if, they are 5% off their target.

What Personal Capital Can’t-Do

This is great for seeing that snapshot of where I am now, but there are a few things it doesn’t do. For one, it doesn’t match up exactly with my target allocation. REITs and “speculative assets” don’t have a place on the asset allocation chart, except as “alternatives” and “unspecified”.

For another, it doesn’t combine my cash accounts! The “cash” portion through Personal Capital only shows “cash” that is in your investment accounts. I prefer seeing my cash position across all of my accounts. I try to keep cash within investment accounts as close to zero as possible but with a 6-months cash cushion in my checking.

Secondly, it doesn’t show you where you want to be. This would probably be overkill for this tool anyway, as it’s providing information rather than recommendations. They do have a separate “Investment Checkup” feature which does show this. I actually think this feature is extremely nifty.

If you were using this tool and weren’t at all aware of index funds, I have a feeling this chart would look a lot different.

I knew I was underweight in bonds, but if I didn’t this would have been a huge help in understanding it. the “Target Allocation” here is based on my personal risk tolerance set within Personal Capital. My personal setting is about a 7 out of 10, where 10 is most aggressive. Their definition of a 7 is “Moderate Growth”

Moderate Growth – A long term growth strategy with meaningfully lower volatility than an all stock portfolio. The allocation is globally diversified and features all six major liquid asset classes.

For comparison, the definition of their most aggressive setting looks like this:

Aggressive – An aggressive growth strategy intended for those with a long time horizon and willing to assume equity-like volatility in pursuit of higher returns. The allocation is globally diversified and features all six major liquid asset classes.

Personal Capital is a great place to start when you’re trying to understand your own investments and want to get feedback. I feel Personal Capital is a little fuzzy on the details, and I love seeing exact numbers. That’s the reason I created my own spreadsheet for tracking investments and understanding my target allocation.

Spreadsheet Asset Allocation

In my quarterly posts, I share a screenshot that shows my current asset allocation next to my ideal allocation that looks a little something like this:

There’s a lot to unpack here. Here’s an overview of this sheet.

• The left side is my current asset allocation (with a few things changed to make it more interesting)
• The right side is my target asset allocation.
• Yellow means my target allocation is off by more than 3%.
• Red means my target allocation is off by more than 5%.

I use this to easily see which assets are out of whack, and which I should take steps to change. For this screenshot, it’s super clear that I’m underinvested in bonds (see why I invest in bonds) and overinvested in speculative (see why I invest in speculative assets).

My personal rule (recently stress tested with the rise of cryptocurrencies) is to buy new funds in order to bring these assets closer in line with my target allocation. So in this case, any new investments I would make would be in bonds.

But what about for items in the red? For those, I want to bring them back to yellow sooner than later. This was a takeaway since my 2017 Q4 Winter Investment Report, where during the span of about 2 weeks, my speculative assets lost half their value. If I had clearer rules and insight into my investments during that time, I would have had better indicators to sell.

Grab Investments and Asset Allocation from Google Docs for free

This sheet has 3 tabs – Variables, Holdings, and Holdings Overview.

Variables

“Variables” is self-explanatory, and adjusts the asset allocation based on my target allocation. Head over

Holdings

“Holdings” is everything you’re invested in, with one per line. You’ll want to go in and add in each fund and copy the number of shares you own. This sheet doesn’t track anything related to cost-basis because that gets super-complicated fast. This sheet is made to be updated in 5 minutes whenever you want want to copy/paste your share counts from your brokerages.

The two key things here are “sector” and “share value”. “Sector” is what drives the “Holdings Overview” tab. Google Sheets also has a handy GoogleFinance function which can be used to get the current value of a fund.

This will go out to Google Finance and get the current value of “VTSAX” to use in your spreadsheet.

Holdings Overview

“Holdings Overview” sums all similar investments from the Holdings sheet. This is handy when you have investments in the same sector in multiple accounts.

Most of the rest of this sheet is basic math. The only other “special” part is the conditional formatting used to color code cells. Conditional formatting is handy when you’re wanting to highlight values outside of a range. I use the same strategy on my monthly budgeting sheet in order to see which budgets are exceeding my target.

Monitor Your Asset Allocation

The important part of having your asset allocation calculated is being able to monitor and act on it. Depending on what you’re invested in, you may not need to monitor it too often. For example, if you’re using a three-fund portfolio, you may only need to check on your allocation a few times a year – maybe even less. The more simple your allocation, the less you’ll need to monitor and change it.

Most of the time, monitoring my asset allocation won’t trigger any immediate changes. Instead, it’ll impact what my automatic investments are going forward in my 401k and my brokerage account. As long as these new automatic investments are in line with what I’m underinvested in, then things are going well!

How do you track your asset allocation? How often do you check it? Do you use it to make any changes?