How to Make a Portfolio Rebalancing Spreadsheet
Q. Do you have a recommendation for a portfolio tracking spreadsheet that will also calculate when rebalancing is necessary and the amounts needed to rebalance? I am not versed in Excel and am not in a position to develop my own spread sheets. I tried using Sig-Fig about 7 years ago but it was really more tailored to individual stock portfolios rather than to index fund portfolios.
My initial response to this comment left on a blog post was that if you cannot make a spreadsheet that will calculate how much is needed to rebalance, you probably have no business managing your own money and should hire a financial advisor who offers good advice at a fair price. But then I got to thinking about it—if one person says he has this problem, it is likely that hundreds or even thousands of others have the same issue. So let’s address it.
What people really want, including me, is to do no work whatsoever and to pay no fees whatsoever but yet have someone else do all their work for them. So I will begin by pointing out the obvious—that our desired outcome, whether actually vocalized or not, is not realistic. You must either do some work, you must pay someone else to do some work, or you must suffer the consequences of the work not being done.
Portfolio rebalancing is one of many “investing chores” you will need to master if you wish to be a Do It Yourself Investor. Other chores include:
- Setting goals and running projections
- Designing your portfolio
- Mastering your available retirement accounts
- Investing your money each month
- Calculating your returns each year
- Taking out Required Minimum Distributions once you turn 70 1/2
- Doing IRA rollovers/transfers as needed
Lots of chores there. None are particularly hard. In my opinion, none are worth paying thousands of dollars a year to someone else to do for you. But l apparently have no problem paying someone $600+ to winterize my boat, change the oil and filter, and swap out the impeller. So if you view portfolio management like I view boat maintenance and have a few thousand dollars a year you can blow on making your life more convenient, then go for it. Otherwise, you’ll need to do these chores yourself.
Simple Portfolio Rebalancing Spreadsheet
Rather than just giving you a fish, I prefer to teach you how to fish. But if you’re just looking for free fish, here’s your first one:
Here’s what it looks like:
Yup. That’s it. If you cannot master this, better go get yourself an advisor. Let’s go through this DIY portfolio rebalancing tool step by step.
First, type the six categories for the columns into row 1. If you like, you can enlarge that row and “wrap text” for each of the first rows of each column to make it a little prettier. You’ll notice I also changed the width of the columns (at the top of the spreadsheet, just above the first row) to make it pretty.
Next, type each of your asset classes into the first column. Everyone’s asset allocation will look a little different. If you only have a single asset class (or use a single balanced, target retirement, or lifestrategy fund) then you don’t need this spreadsheet at all. If you have two asset classes in your portfolio, you’ll have two rows here. If you have ten asset classes in your portfolio, you’ll have ten rows. If you have fifteen asset classes in your portfolio, get rid of a third of them, they’re not doing you any good. 🙂 In this case, we’re doing a portfolio that has five asset classes as you can see above.
Next, refer to your written investing plan for the desired percentages of your assets and put them down for each asset class in the portfolio. What? No written investing plan? Well, go get that first. If you need help getting an investment strategy in place, consider taking my online course (cheaper but more work required) or hiring a good financial planner (more expensive but less work required.)
The next column requires some work on your part. There are functions that can pull the share values of your investments into the spreadsheet automatically, but we’re just going to update manually to start with. So, for your US stock allocation, perhaps you’re using the Vanguard Total Stock Market Index Fund, my favorite mutual fund, like many of us. If it is in just one account, go to the account website (you do have logins to your investing accounts, right?) look at the total and type it into that box. But perhaps you hold it in more than one account- your 401(k) and your taxable brokerage account. No problem. Just get the totals from both accounts and add them together. In a spreadsheet, that looks like this:
and the spreadsheet will do the arithmetic for you. If you want it to look pretty like mine, you’ll need to change it to dollars and maybe even move the decimal point over a couple of places so you don’t have to look at the pennies unless you want to. In Excel, those buttons look like this:
but they’ll look similar in other spreadsheets like the free “Google Sheets”, where it looks like this:
The next column is simply your current percentage. In order to get this, you’ll need some totals. So let’s work on that bottom row for a minute. Write “total” in column one, then in column two, add up the entire column. In spreadsheet speak, that looks like this:
=sum(B2:B6) where it adds up all of cells B2, B3, B4, B5, and B6 and spits out the total in B7. Cool trick eh? Can you believe people used to make spreadsheets by hand? NPR did a fascinating podcast on the history of the spreadsheet. Life-changing for business in this country.
At any rate, Now you can simply copy and paste (control + C on a PC, command + C on a Mac) that box into all of the other boxes in that same row. Now, back to that fourth column.
If you start in the 2nd row of that 4th column, what you’re trying to do is to find out what percentage of your portfolio that asset class currently makes up. So you need to divide what is in the asset class (B2) by the portfolio total (B7). If you want it to be a pretty percentage, you just need to highlight the box and hit the % button by that $ button. At any rate, this will make the box look like this:
You can type similar things (=C3/C7) into each row, or you can use a little shortcut. If you write =C2/$C$7 in D2, then you can just copy that box and paste it into rows C3-7. It will change the C2 to C3 etc, while keeping C7 the same.
The fifth row is your desired assets in that given asset class. This is simply the percentages from your written investing plan (2nd column) multiplied by the portfolio total (still in C7). The first box will look like this:
and you can just copy and paste it to the other boxes in the row.
The sixth column gives you the numbers you are looking for- how much needs to be moved from one asset class to another in order to rebalance. It is simply the fifth column (E2) minus the third column (C2):
You can copy and paste it to the rest of the column as you have previously done.
Now, you can see that you need to sell $6,702 of US stocks, buy $6,236 of International Stocks, buy $8,214 of Bonds, sell $5,727 of Real Estate, and sell $2,020 of Gold. Super easy if you only have one investment account, but obviously increasingly complicated if you have multiple investing accounts. I’m sure you can figure it out, especially if you start doing this as a resident with a single Roth IRA and only add one account at a time.
Don’t like reading words about this stuff? Try this YouTube video:
When to Rebalance Your Investments
Let’s pause for a minute and just talk about rebalancing for a second. Perhaps the simplest way to rebalance is to just do so based on time. So every year, maybe on the 1st of the year or on your birthday or whatever, you rebalance your portfolio no matter how much or how little it is out of whack.
The primary benefit of this method is that it requires very little work or portfolio monitoring. In fact, there is some data to suggest that the best rebalancing interval is not even every year, it’s more like every 2-3 years. In the past, this interval has allowed for momentum to provide a little boost to the portfolio. Naturally, no one has any idea whether that will also be the best interval in the future.
Some people prefer to rebalance only when the portfolio is really out of whack. One of the most popular rebalancing rules is the “5/25” rule. This says that for large asset classes, such as one that makes up 30% of your portfolio, you rebalance it when it is more than 5% out of whack, meaning if the % of the portfolio in that asset classes either drops below 25% or rises above 35% of the portfolio, then you rebalance the entire portfolio.
For small asset classes, such as one that makes up 5% of your portfolio, you rebalance it when it is more than 25% of its allocation out of whack. So for a 5% asset class, that means you rebalance everything when it hits 3.75% or 6.25% (5% * 25% = 1.25% and 5% +/- 1.25% = 6.25% and 3.75%.
The upside of this method is that you’re more likely to buy low after a market dip and sell high after a market rise, but it does require you to monitor your portfolio a little more closely, especially during volatile times when perhaps you shouldn’t be looking at your portfolio as much.
Rebalancing With New Money
The truth, however, is that for most investors in the first half of their accumulation phase, it is very rare that one might need to actually sell an asset class in order to rebalance. They can usually simply rebalance by directing their new investments each month toward whatever asset class is lagging. Whether this is better than simply setting up automatic investments and then rebalancing once a year or not is anyone’s guess, but it certainly means more work. Rebalancing doesn’t matter THAT much, so maybe it’s better to just keep things simple.
Don’t Rebalance in Taxable
Since rebalancing doesn’t matter all that much, you certainly want to limit (or preferably eliminate) all costs of doing so. Avoid an account where you’re paying commissions and maybe even bid-ask spreads. Certainly try to avoid doing it in a taxable account where you may end up realizing capital gains, especially short term capital gains. Since you should be looking at your entire portfolio as one big account spread across all of your (and your spouse’s) accounts, most of the time if you have to do any selling of appreciated shares you can do it in a tax-protected account.
Jazzing Up Your Rebalancing Spreadsheet
Ready to move beyond the basic portfolio rebalancing spreadsheet? Let’s talk about a couple of ways to make your portfolio a little fancier and easier to use. The OP above wanted his spreadsheet to tell him when he needed to rebalance, and that’s pretty easy to do. First, go to the fourth column and turn the text in D2-D6 red. It should look like this:
Now, go to box D2 and we will use “rules” to determine whether the cell is red (needs rebalancing) or green (doesn’t need rebalancing) according to the 5/25 rule. Obviously, if you’re rebalancing once a year you don’t need to do this. So go to “Conditional Formatting” and then “Highlight Cells Rules” and then “Between” as shown
Fill out that form as shown
Note that you’ll probably want to use the “custom format” where you can choose the text color and even a fill color if you want. I just changed it to green text with no fill. This will ensure that the text in cell D2 turns green if it is between 25% and 35% and otherwise will be red, indicating a need to rebalance.
In D3 and D4 you would use 20% and 30%, in D5 you would use 11.25% and 18.75%, and in D6 you would use 3.75% and 6.25%. When you’re all done, it would look like this:
showing you that you do not need to rebalance. However, if you were just rebalancing with new money and had $10K to invest this month, you would make sure to direct it to US Stocks, Real Estate, and/or Gold.
Let me show you one more cool feature for a rebalancing spreadsheet. What if you didn’t have to manually input your level of assets but could pull in that value automatically each time you open up the spreadsheet? It is possible to get your spreadsheet to get into your password-protected accounts, but I probably wouldn’t take it that far. It is easier and more secure to just have the spreadsheet pull in the NAV for an investment and manually change the number of shares in the spreadsheet any time you buy, sell, or reinvest dividends in that investment.
While you can use this feature in Excel for PC, it isn’t really supported in Excel for Mac, which is what I use. So let’s copy and paste our spreadsheet into Google Sheets to show you this one. Add Column G and Column H and label them as “Share Price” and “# Shares”. Fill in Column G with the # of shares, let’s say 1203 shares of Total Stock Market Index Fund in G2 and 3703 shares of Total International Stock Market Index Fund in G3. Now, in Column C, you can simply put =G2*H2 into C2 and copy and paste that into the rest of the column.
Now for the fun part. Pick a cell down below your chart, let’s say A10 and put in the following:
=IMPORTHTML(“https://finance.yahoo.com/quote/VTSAX/”, “table”, 1)
It will pull all kinds of data from the Yahoo Finance page on the Vanguard Total Stock Market Index Fund Admiral Shares and look like this:
Now go to H2 and put in the following:
Now the spreadsheet is updating your level of assets automatically each day.
You can follow a similar process for each asset class in the portfolio. Here’s what it looks like after doing two asset classes.
Cool trick eh? There are so many fun things you can do with spreadsheets that I bet learning to use one, as limited as my knowledge is compared to the real whizzes, is the most valuable skill, at least dollar-wise, that I’ve ever learned.
What do you think? Do you have a portfolio rebalancing spreadsheet? What does it look like? Any other great spreadsheet tips for readers? How do you do your rebalancing? Comment below!