It’s easy enough to build an index fund portfolio with your desired mix of stocks and bonds. But as any experienced investor knows, your asset allocation changes over time as markets move in different directions. When your portfolio drifts too far from its targets, then it’s time to rebalance.
Rebalancing your portfolio is much easier with a spreadsheet, so I have created one you can download here.
Rebalancing spreadsheets are not new, but this one has an additional feature I hope you will find useful: it allows you to incorporate ETFs that hold more than one asset class.
My model ETF portfolio, for example, includes the iShares Core MSCI All Country World ex Canada Index ETF (XAW), which is roughly 55% US equities, 32% international equities, and 13% emerging markets. Let’s say you hold $20,000 of the ETF in your TFSA as well as $10,000 of a stand-alone emerging markets ETF in your RRSP. What is your overall allocation to emerging markets? That’s not easy to answer, and it will make it harder for you to keep your portfolio on target.
This question gets even thornier with the new “all in one” ETFs from Vanguard and iShares. These popular ETFs include a diversified mix of both stocks and bonds in a single fund. If your whole portfolio consists of nothing but one of these ETFs, then you don’t even need to think about rebalancing because it’s done for you. But if you also hold other funds, then it helps to understand how all the pieces fit together. This new spreadsheet should help.
1. Enter your target asset allocation
Your target asset mix is one of the most important parts of an investment plan. Near the bottom of the spreadsheet is a table where you can enter the targets for your portfolio. This table is pre-filled with 40% fixed income and 60% equities, but you can change these targets to suit your own objectives:
2. Enter your account names along the top row
Rebalancing is straightforward if you have only one or two accounts, but not if you’re managing a household portfolio with two RRSPs, two TFSAs, a non-registered account, and maybe a LIRA you’ve been dragging around since your last job change. With that in mind, the spreadsheet is set up for up to 10 individual accounts. You can change these headings (Rows 2 and 3) to include the account type and the name of the owner.
3. Enter the names of each fund in your portfolio
I have pre-filled the spreadsheet with common ETFs in each asset class, but you can change these names and tickers if you use different funds. Make sure you put each new ETF in the correct category (Fixed Income, U.S. equities, etc.).
The last category is for “Multi-Asset-Class ETFs.” If you want to add a new fund here, you’ll need to overwrite one that is already in the list: you cannot insert a new row in this section. Your new fund in this category it will be automatically linked to a second worksheet, where you’ll need to enter the asset mix (see next step).
4. Enter the breakdown of any multi-asset-class ETFs
If you are using a fund that includes more than one asset class, click the tab at the bottom of the screen to open the second worksheet:
This is where you need to enter the percentage allocated to each asset class in the ETF. I’ve already included the most common funds of this type, as well as their current breakdowns. These may change a little over time, so you should check the ETF’s website and update them before you do a major rebalance. They don’t have to be precise: just round them off to the nearest whole number, or use one decimal place at most.
5. Enter the current value of each of your holdings
Now grab your statements or log in to your accounts and enter the current market value of all of your holdings in the appropriate cells on the rebalancing table. For example, if John’s RRSP holds $25,000 in VAB, enter that amount:
If you hold any multi-asset-class ETFs, just enter the total value in the appropriate cell. The spreadsheet will do the rest. Don’t forget to enter any cash balances, too.
6. Assess your overall asset mix
Once you have entered all your holdings, scroll to the bottom of the spreadsheet to see your portfolio’s asset mix across all of the accounts. Any holdings in multi-asset-class ETFs will be broken down according to the percentages you entered on the second worksheet (see step 4).
For example, a $25,000 holding in VAB (all bonds) and a $50,000 holding in VBAL (mix of 40% bonds and 60% stocks) works out to $75,000 broken down as shown below. Note that the total amount in bonds is $45,000, which is the entire value of VAB ($25,000) plus 40% of the value of VBAL ($50,000 x 40% = $20,000):
7. Rebalance if necessary
The right-hand column in the table above tells you how much you would need to buy or sell of each asset class to get you back to your target. Now you need to decide whether it is worth it to make the necessary transactions. Rebalancing is always a trade-off between risk and costs. It’s not worth incurring trading commissions, bid-ask spreads, and potentially capital gains taxes if your portfolio is out of balance by a percentage point or three.
8. Customize the spreadsheet—at your own risk
You can make any changes you want to the spreadsheet. I’ve locked it to prevent accidental changes to the formulas, but it’s not password-protected, so you can unlock both worksheets by right-clicking the tab at the bottom and selecting “Unprotect Sheet…”
One easy way to tidy up the spreadsheet is to unprotect it and then hide (not delete!) the rows containing funds you do not use.
Please be aware that any changes you make can result in formulas being deleted or cause other errors. I’m not able to offer any support to readers who want to edit the spreadsheet, either in the comment section or by email.