Asset Allocation and Rebalancing Spreadsheet

One of the most important aspects of investing is setting your asset allocation. There are a variety of factors that go into your asset allocation including your goals, your investing time horizon, your risk tolerance, and the rest of your financial situation. Once you’ve set an asset allocation you need to pay attention to it because, over time, your actual allocations will likely stray from your target allocation because different asset classes will have different returns over time.

For example, let’s say your target asset allocation was 50% U.S. Total Stock Market Index, 30% International Total Stock Market Index, and 20% U.S. Bond Index (just as an example). If you had set this allocation 10 years ago and then left it alone (no additional contributions and no rebalancing), it would currently be at 69%, 21% and 10%. That’s not (necessarily) bad, but it is very different than your original allocation. Assuming your original target allocation was really what you wanted, then you would’ve wanted to rebalance along the way to keep reasonably close to those initial percentages.

When I talk about this with people, a typical response is, “Okay, that makes sense, but how do I do it? What do I do to rebalance?” They have question about how often they should rebalance, in which accounts to rebalance, and what to actually do to rebalance. So here’s more or less what I suggest to folks.

How Often Should I Rebalance?

There is lots of research around the optimal way to do this but, for most people I think there are two ways that work perfectly fine. The first way is simply to rebalance once a year. Pick a date, check your balances, and sell some of the asset classes that are higher than your target allocation and buy the ones that are lower – this is selling high(er) and buying low(er).

The second way is a bit more complicated, but not too bad. You set a threshold for each asset class and, as long as your current allocation is reasonably close to your target allocation (target allocation plus or minus the threshold), you don’t need to rebalance. Only if one or more asset classes exceed the threshold is it time to rebalance. (For example, if your allocation for Total U.S. Stock Market Index was 50% and your threshold was 10%, you would only rebalance if it exceeded 60% or was less than 40%.)

There’s actually sort of a third way to rebalance, which is simply not to. While this messes with your asset allocation and your risk tolerance, for many folks it will work out just fine. I’m not necessarily recommending it, but as long as you are regularly saving and investing, and adding the new investment amounts to the under-allocated asset classes, it will probably be okay.

Which Accounts Do I Rebalance?

In general, you want to do your rebalancing in your tax-advantaged accounts (Traditional and Roth IRA/401k/403b/457b and HSAs). If you rebalance within these accounts, there are no tax consequences. If you rebalance in your taxable brokerage account, there likely will be taxes you have to pay, which is why we usually try to avoid it. That doesn’t mean you don’t ever rebalance in a taxable brokerage account, but you would prefer to rebalance in taxable accounts by strategically adding new money to the under-allocated asset classes.

How Do I Figure Out How Much (and What) To Sell/Buy?

For those of us who think about this a lot, figuring out how much (and what) to sell and how much (and what) to buy is a pretty straightforward calculation. But I’ve found that this is often a big sticking point for a lot of people. So much so that some people end up not investing at all because they are worried they will get this part wrong (which obviously is a big problem). Some investment platforms make this pretty easy. Within a given account (say your traditional 403b) you set an asset allocation and then there will be a button that says, “Rebalance” (or something like that).

But, unfortunately, not all investment platforms have this functionality. In addition, most of us have investments on multiple platforms (due to having some investments through our employer and some on our own, as well as various other legacy decisions we’ve made along the way). This can sometimes make it difficult to see the “big picture” of all of your accounts. Some people use tools like Empower (free, but you might get some marketing) to aggregate all of their accounts. This can work well, but can sometimes be overwhelming (and you can’t always connect all of your accounts to Empower).

So I’ve created a spreadsheet that is based on work by Rob Berger.

Before I share more information about this spreadsheet, a word of caution. This spreadsheet can appear complicated and may be overwhelming for some (many?) people. I’m sharing it because I think some people will find it helpful but, if you find it overwhelming, please don’t try to use it. It’s not worth the stress.

Here is my Asset Allocation and Rebalancing Spreadsheet. Simply choose File–>Make a Copy to get your own, editable version. The spreadsheet contains 15 (!) different sheets (which is one of the reasons it may be too complicated for some folks). But the reason for the 15 sheets is because there are several challenges in creating a spreadsheet that is actually useful for rebalancing. I had to make some important choices along the way in order to overcome those challenges. Not everyone will like the choices I made, so you may find ways to modify the spreadsheet to better fit your needs. Let’s take a look at some of those choices.

Separate Sheets for Each Type of Account

The first choice I made was to create separate sheets for Roth accounts, HSAs, Traditional (pre-tax) accounts, and Taxable accounts. While it would be much easier to just combine them all on one sheet, the problem is that when you go to rebalance, you can only sell and buy within any given account. For example, you can’t sell part of an asset class in your Roth IRA and then use that money to buy part of a different asset class in your Traditional 401k; the money has to remain within your Roth IRA. You can certainly do the math to figure out how to get your overall asset allocation to be where you want it to be across various different accounts, but the idea of this spreadsheet was to do the math for you and give you a (relatively) easy way to do the rebalancing.

Separate Sheets for Each Individual

If you have a partner, you share finances, and you want to look at your entire financial picture, then you want both of your investment portfolios included. But, again, you can’t sell part of an asset class in your Roth IRA and then use that money to buy a different asset class in your partner’s Roth IRA. (The one exception to this is your taxable accounts if they are held jointly.) So the (relatively) easy way to handle this is to look at your individual (tax-advantaged) accounts separately for rebalancing purposes. That means there are Roth sheets for both partners, Traditional sheets for both partners, HSA sheets for both partners, and taxable brokerage account sheets for both partners (although if they are joint taxable accounts you should add that data to just one of them).

If you are single you can just put zeros in the Person 2 sheets.

What About Tax Allocation?

Up to this point we’ve been talking about asset allocation, but another important consideration is tax allocation: how much you have allocated to your already taxed accounts (Roth, HSA), your still to be taxed accounts (Traditional), and your currently taxable accounts (Taxable). So I’ve included the ability to indicate which kind of tax treatment any individual holding has in order to also show the overall tax allocation.

This does make it more complicated, so you can certainly just ignore that part if you don’t want to consider your tax allocation.

Layout of the Spreadsheet

So let’s take a look at the spreadsheet. The first sheet (tab) in the spreadsheet is a “Table of Contents”. With 15 separate sheets it can be (very) annoying to have to scroll horizontally to find the one you want. So this first sheet contains links to each of the other sheets, and all of the other sheets contains a link back to the Table of Contents sheet.

You’ll notice that some of the sheets have an * after their name. This means these are sheets were you actually want to enter data (the other sheets simply pull information from the data sheets).

The exception to this is that for each ByAssetLocation and ByTaxLocation sheet, you will still need to enter your target allocation percentage and, if you use the threshold rebalancing method, your threshold percentage.

The next two sheets look at the asset allocation and tax allocation for both partners combined. Even though we typically do our rebalancing within individual accounts, if you share finances it’s still useful to look at your overall allocations between the two of you. These sheets pull all of their data from the other sheets (except for the target and threshold percentages which you’ll have to enter).

The next six sheets are all for Person 1’s accounts. The first two look at their asset and tax allocations which pull data from the next four sheets: Roth, HSA, Traditional and Taxable accounts.

Following that are six more sheets for Person 2’s accounts, with the same two for their allocations and the four for their data.

Layout of a Data Sheet

Let’s now look at the sheet for Person 1’s Roth accounts to get an idea of how the data sheets work.

In Column A you’ll notice two separate sections labeled Roth Account 1 and Roth Account 2. Some people won’t have any Roth accounts, some will have more than one, and some will have more than two, but two seemed like a good number for the template. In this sample, we see a Roth IRA for Account 1 and a Roth 401k for Account 2. Again, the reason for listing them separately is not just for ease of input, but because the (relatively) easy way to rebalance is within a given account.

If they were simply combined into one giant “Roth”, then you could see your asset allocation, but you’d still have to do a lot of math to figure out how to rebalance across accounts.

Column B is for the ticker symbol for each individual holding within that account. If you type a valid ticker symbol, it will then populate Column C with the correct name for the Ticker symbol.

If that doesn’t work, you can always just type in the name yourself in Column C.

Column D then offers you a drop-down to choose the Asset Class for this particular holding. This is currently populated with the following choices, but you can choose to modify these if you wish.

You need some spreadsheet skills in order to modify the drop-down, or simply ask me and I can help.

Column E is for tax location, with these choices in the drop-down.

Column G is where you type the current actual value of that holding (in dollars). You can find this by logging into that account (or, if you use an aggregator like Empower, you can login to that to get all of the balances in one spot).

Column H is your planned value (in dollars) for this holding which is calculated based on your target allocation (which you enter in Column K).

A limitation of the spreadsheet is that it assumes one holding per account per asset class in terms of setting a target allocation. If you have more than one holding in that asset class within that account, then you’ll need to divide your target asset allocation for that asset class however you’d like between those two holdings.

Column I shows the (calculated) rebalance amount (within this account) necessary to get back to your target asset allocation. This uses the difference between your target allocation (Column K) and your actual allocation (Column J). Column L is the percentage difference between your target and your actual allocation which you can use along with Column M if you want to use the threshold method for determining when to rebalance.

Columns N and O look at Expense Ratios, with N being the expense ratio for that individual holding and O being the weighted expense ratio for that holding (which factors in the expense ratio and what percentage of that portfolio you have in that holding).

Columns P through S are there if you want to manually add the returns for that holding over various time periods. This can be useful as you think about your individual holdings and whether you want to make any adjustments to your future allocations.

Below each account there is also a partially populated “Totals” row. This shows you the total dollar amount in the account, the total of your actual and target percentages so that you can check they are adding to 100%, and your overall expense ratio for that account (the sum of the weighted expense ratios).

Note that the spreadsheet is designed to try to accommodate you adding in additional rows of holdings within the account. It is somewhat adaptable and will try to adjust, but it’s not unbreakable. If you have more than two Roth accounts you can also copy and past an entire account section in the rows at the bottom, but you may have to modify a couple of formulas slightly. If you need to add additional holdings or additional accounts and can’t make it work, contact me and I can help you do that.

Each of the other data sheets for each person is laid out the same.

Note that for your taxable brokerage holdings, including your checking and savings accounts, you want to be sure not to list them twice if they are joint accounts. Simply list them once on either Person 1 or Person 2, but not both. Some people have separate taxable accounts instead of joint, in which case they would both list their own on their individual taxable account sheet.

Person 1 and Person 2 Summaries

While there are sheets for the asset allocation and tax allocation of the two partners combined, it’s also helpful to see those separately. So Person 1 has their own asset allocation sheet and tax allocation sheet, as does Person 2. Just like the combined allocation sheets, these sheets pull their information from the data sheets (except for the target and threshold percentages which you still need to enter).

So What’s the Process to Use this Beast?

Again, this may appear complicated (and may actually be complicated for many). But I think for those who are interested in can be useful once they get the hang of it. You can approach it however you’d like, but this is what makes sense to me.

• Start by adding the appropriate data to the four Person 1 data sheets. If you have a partner, add the data to the four Person 2 data sheets (if not, be sure to zero out the appropriate numbers for Person 2 on their data sheets).
• Add the appropriate target (and threshold if you’re using that method) percentages to each of the six allocation sheets.
• Take a few minutes and make sure everything looks reasonable and, especially, that all the percentages add up to 100% and all the totals match up across the relevant spreadsheets. (It’s easy to miss something.)
• At that point, if you have the hang of this and feel like it could be useful for you, decide whether you are going to use the check-it-once-a-year method or the threshold method. If it’s the check-it-once-a-year method, decide on what date each year you want to check it and set a calendar reminder. When that date comes around, you will then need to update the data sheets as of that date. If you use the threshold method, you will need to update the data much more frequently, maybe once a month or so (this is where an aggregator like Empower could really speed things up).

So, after all this I’m really not sure if this will be helpful to anyone or not, but at least it was fun figuring out the spreadsheet :-). If you do find this useful, or have suggestions, please contact me and let me know.

Edit: If you just want a very simple rebalancing spreadsheet, here you go.