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.
This looks great. However when I downloaded it and tried to use it, I got a message that it was read-only. I’m using Open Office. ???
@Daryl: The spreadsheet is not locked in Excel. But it might not be compatible with other software.
Thanks for that quick reply
I found out I need to get it in xls. Is that possible?
@Daryl: .xls is a format used by old versions of Excel. If I convert the spreadsheet to that older format, it will not work properly.
Hi Dan,
I recently had a thought: if swap-based ETFs were outlawed by the CRA, wouldn’t everyone have to sell it, thus making the price of each share go down, resulting in huge losses? Or in another similar scenario, let’s say a large number of people decided to switch from VCN to ZCN, would this result in slower growth/losses for those who keep VCN?
@beginner: Index-tracking ETFs are priced according to the value of the underlying holdings: the supply and demand for the ETF shares themselves has an insignificant effect. This makes them fundamentally different small-cap stocks, for example.
So in the situations you describe, the effect of widespread selling of an ETF would be negligible. The VCN/ZCN example is a good one, because these two ETFs hold essentially the same stocks. People selling one and buying the other cannot drive up the price of one and drive down the price of the other.
What an excellent tool, thank you for sharing it with us!
In the case of multi-asset-class ETFs like XAW, how do we interpret the Buy-or-Sell column? For instance, the column is telling me to SELL $135 worth of shares in Emerging Markets, but the only way I have exposure to emerging markets is through XAW. Meanwhile, the Buy-or-Sell column tells me to buy $38 in US Equities and $508 in International Equities. How should I interpret this considering I’m not buying or selling these individually but through XAW?
My first guess was to raise the amount of cash I’m adding. That works to green-light the entire Buy-or-Sell column but I had to raise it to $5000 to accomplish that, I and that’s much higher than I was aiming for.
@Jarf: Glad you like the spreadsheet. Your asset allocation does not need to be exact (and never will be), so you can ignore small amounts in the buy/sell column:
https://canadiancouchpotato.com/2011/02/24/how-often-should-you-rebalance/
Remember, too, that you cannot control the mix of US, international and emerging markets stocks if you are using only XAW for foreign equities. If you want 40% in non-Canadian equities and you are using XAW, you should just change your target to 22% US, 12.8% international and 5.2% emerging markets, because that is the breakdown in XAW.
Hi,
I have opened the spreadsheet in excel however, when I try to unlock it by right-clicking on CCP Rebalancing Table the only option I’m given are insert, delete, rename, move and select all sheets. Not Unprotect Sheet. Do you know the reason for this?
Thanks
Thanks for this tool Dan. It is very handy.
Hi Dan,
You’ve written about avoiding buying ETF’s in December due to capital gains distributions around that time of year. Does this apply to buying *new* ETF’s only or also ETF’s that you already hold? Last year in late December 2018, the market saw a drop in prices which would have been a great time to buy more! But I waited till January to avoid the capital gains distribution and by then, the market had started recovering, so I feel I kind of missed out.
@Sd: The principle applies whether you already own shares in an ETF and plan to be more, or if you are considering adding a new holding. But it’s important to understand that it only applies to ETFs that have announced they will pay significant capital gains distributions at year-end, and many do not.
ETF providers typically announce which funds are expected to pay cap gains distributions in November or early December, so by late in the year you would have known whether your ETFs were on this list and could have made your decision accordingly. For example, here is BlackRock’s announcement:
https://www.blackrock.com/ca/individual/en/literature/press-release/pr-2018-11-19-en.pdf
Hey Dan,
I was wondering if there was an easy way to account for portfolios with mixed currency. As an example I use ITOT to represent 50% of my US asset mix. (I do this because I get a benefit from work that is payed out in USD)
Because I do 50/50 in ITOT AND XUU would I just apply the current exchange rate from a google search to this as an effective measure to keep ITOT equivalent to CAD?
I feel like this is the case but want to ensure I’m not overlooking something.
@Kosta: You have the right idea, yes. You could even adapt the spreadsheet by adding a cell somewhere that contains the current USD/CAD exchange rate. Then you can enter the USD amount of your holding in ITOT and multiply it by the value in that cell so the spreadsheet displays the CAD equivalent.
https://canadiancouchpotato.com/2015/02/09/rebalancing-with-foreign-currencies/
Hello,
I recently modified the excel sheet kindly provided by Dan to automatically track the value of my ETF shares by using google sheet and online fetching of market values. I made it so that one can enter the amount of shares instead of the monetary value of each ETF and the sums in each allocation category automatically update with the current market value (I believe there’s a 20 mins delay). Since it’s stored online, you can also access it from anywhere as long as you have internet access.
If it can be useful to any reader here, I’m happy to share it with this link:
https://docs.google.com/spreadsheets/d/1JWqyYo5o4OMtcrzIVKVdDC1_tq_P-is7VMCTxGLeLZE/edit?usp=sharing
Have a good day!
JF
Hello eveyone!
Concerning the google sheet I shared recently in a previous comment ( https://docs.google.com/spreadsheets/d/1JWqyYo5o4OMtcrzIVKVdDC1_tq_P-is7VMCTxGLeLZE/edit?usp=sharing ), I received questions by email as why users can’t “use” it. The intended way was to allow you to log on your google account and save a copy of the sheet in your own google drive. The original sheet can’t be edited or everyone would see the modifications you make with your own portfolio data. The blank sheet contains the same empty portfolio in the original Excel version by Dan.
Best regards!
JF
Hi Dan,
Thanks so much for this spreadsheet, it’s very helpful.
Are you able to kindly construct something similar for your CCP portfolio that also takes into account the most tax efficient allocation of funds into TFSA, RRSP and then non-registered accounts.
@RP: I don’t believe it is possible to create a useful spreadsheet that can do that: there are too many variables to consider.
Thanks so much for creating this. I was having trouble keeping track of my overall allocation across different accounts using different ‘all in one’ funds, and the spreadsheet was exactly what I needed. Also loving the Podcast. Keep up the good work.
Thank you so much Dan. Brilliant tool.
Great tool! Been doing couch potato since I was 18. Currently 20 now and hope it will all go well in the years to come!
Sometimes banks will have promotions where they’ll give you bonus money for putting a minimum amount in an RRSP or TFSA. For example, TD is currently offering a $100 bonus for investing $1000 into a GIC of one year or longer. Unfortunately TD’s GIC rates are low compared to other banks, but with the bonus, this would be a guaranteed 10% return. For someone that already has enough in fixed income, would you recommend taking advantage of these types of others anyway and then investing the money in index funds after it matures? Or would you recommend just investing the $1000 in index funds straight away?
@SL: It’s hard to argue with free money, but I think you need to keep in mind your overall investment goal. If you were going to use your contribution to buy fixed income anyway, then buying a GIC and getting the $100 bonus. But if you were planning to buy equities and you buy a five-year GIC instead, just because of the $100 bonus, that makes less sense.
Hi Dan!
Since I’m rebalancing regularly with small amounts of money, and don’t want to pay fees for selling ETFs, I am doing my best to rebalance purely by making purchases. Assuming this works over extended periods of time without selling anything, am I losing out on the power of dollar-cost-averaging, since I’m buying low, but not selling high (not selling at all)?
Would I be better off accumulating larger savings over the course of a year so that it’s (maybe) worth paying the fees for selling ETFs when I rebalance annually? I don’t love the idea of holding the money as cash in my Questrade account since I’d miss out on a lot of interest, so I suppose I’d be holding it in an HISA and then having to deal with transferring funds once a year.
Any thoughts or advice would be great!
Thanks!
Jeff
@Jeff: You’re definitely not missing out on anything by doing what you’re doing. Rebalancing is primarily about controlling risk by keeping your asset mix more or less consistent. It’s not about trying to boost your returns. So just keep investing your new contributions in whatever asset classes are below their targets and you’re doing great.
I would also note that this strategy will only work for so long: at some point your regular contributions will not be enough to allow you to rebalance without selling anything. For example, if you contribute $200 a month into a $50,000 portfolio you can probably keep your portfolio close to its targets with new cash flows only. But if the portfolio is $200,000 this will be much more difficult.
https://canadiancouchpotato.com/2014/06/23/rebalancing-with-cash-flows/
Hi Dan,
Great articles as usual! Don’t mean to hijack you post but I am having tough time finding answers online. I have a question regarding managing asset allocation for married couple. How do I adopt your CPP strategies to my exiting portfolio that made o multiple RRSP, TFSA, RESP and None-Register accounts (Me and my wife)? Do I treat them as a “whole” or I should treat them as separate portfolios? We are thinking about moving away from our current full service wealth management to DIY but not entire sure how to allocate our assets efficiently.
Warmest Regards,
Adam
@Adam: This can be a complicated question, but it in general it makes sense to treat all of your household accounts as a single large portfolio if you and your spouse plan to use the money for shared retirement expenses. (An RESP, with a shorter time horizon and different goal, should be treated separately.) This may help:
https://canadiancouchpotato.com/2012/03/12/ask-the-spud-investing-with-multiple-accounts/
That said, if you want to make your life easier without sacrificing too much in terms of tax-efficiency, you can make a strong argument for holding the same asset mix in each account, especially if you are using a “one-ETF portfolio.”
Thanks so much for the advice Dan.
Thanks for you response and advice, Dan! What you said about rebalancing a larger portfolio requiring more selling does sense. I look forward to having that problem one day :)
I’m curious how to allocate ETF holdings in commodities, such as gold or energy, in this spreadsheet. Any suggestions?
@Travis: If you’re good with Excel you can add rows for another asset class (commodities). Energy or other specific sectors are not different asset classes: they should just be included with equities.
I’m new to investing and have a question about rebalancing. I know that rebalancing once a year is enough but could it be a good idea to rebalance each time we’re buying shares?
Instead of selling shares, we buy a little bit more of the etf that bas the lowest proportion? Hope to make myself clear because my english is basic. Thanks!
@Christian: Yes, you can definitely rebalance simply by adding your new contributions to whichever asset class is furthest below its target:
https://canadiancouchpotato.com/2014/06/23/rebalancing-with-cash-flows/
Here’s a silly (read Newbie) question:
If instead of buying many ETFs to get a balance portfolio, then why not simply buy VBAL (or VCNS) for all portoflios (taxable, RRSP, TFSA and LIF)?
That way you avoid the rebalancing process (and incuring fees). Why not let the fund re-balance itself with something like VBAL?
The underlying question is: What’s the advantage of a multi-ETF vs. a single ETF portfolio?
@Jackie: Not a silly question at all. The short answer is that using an all-in-one ETF across all accounts is actually an excellent idea for a DIY investor who understands the trade-offs (slightly higher MERs and less-than-optimal tax-efficiency). For a longer answer, you may want to listen to the second part of my podcast #25:
https://canadiancouchpotato.com/2019/07/09/podcast-25-dr-steve-wendel-on-investor-success/
Just wondering is there a way to add VEQT to the Multi-Asset Class ETFs on the spreadsheet?
@Darby: Yes, see step 8 above. You can unlock the spreadsheet and VEQT on the second worksheet, called “Multi-Asset Class ETFs.” You can find the asset class breakdown for this fund on the Vanguard website.
Hi Dan, where (if at all) does one’s principal residence factor into asset allocation/rebalancing? Thanks!
@Frank: Your principal residence is, of course, an important consideration on your overall financial plan. But it should not have a significant influence on the asset allocation of your investment portfolio.
Hi Dan, just getting into the Couch Potato Strategy using your 2020 Model Portfolio with one TFSA and one RRSP. Would re-balancing be required if half of the TFSA is invested in VCNS and half is invested in XCNS and then the RRSP is invested in the TD e-Series Funds at a 40, 20, 20, 20 split?
Thank you in advance.
@Conner: VCNS and XCNS are 60% bonds, 40% stocks. So if that is your target asset allocation, then your TD funds should be 60% bonds and 13/13/14% stocks.
In any case, rebalancing will always be necessary if you use TD e-Series funds. It’s only the one-ETF portfolios that rebalance themselves.
It also doesn’t make sense to hold both VCNS and XCNS: just pick one or the other. They are not identical, but they are so similar that there is no benefit to holding both. It just doubles your transaction costs.
Curious how often you update this to include new products (ie. XEQT, VEQT). I suppose I should get more EXCEL savy, but I keep messing it up when I try to add/customize it….
Hi Dan,
Would it be possible to add VEQT and XEQT to this rebalancing sheet? Having issues achieving the proper asset mix when trying to add these to the sheet manually. Thanks!
Hi Dan,
When the equity markets fell sharply in March, I held the course. If I, instead, rebalanced some of my portfolio from bonds to equities back to my 60% equity / 40% bond target , this would obviously have made some difference in performance since then. I know hindsight is 20/20 and you have said in the past that rebalancing is for managing risk not performance, but does this strategy make sense to be aware of during the next sharp downturn? …or this simply trying to time the market?
Today, my portfolio went back to 60/40 without me doing anything. Just wondering if I would have been better off doing some
rebalancing in march to improve my long term performance.
@Frank: I think everyone would agree the market recovery in April and May was faster than expected, so don’t kick yourself too much if you didn’t rebalance. Staying the course was certainly the next best thing.
However, it’s worth asking whether you have a rebalancing threshold as part of your plan. For example, a common practice is to rebalance whenever an asset class is 5% off its target. During March, a typical balanced portfolio would have easily crossed that threshold, so rebalancing at some point would have been called for.
It’s quite possible that when you rebalance the market will fall further and you’ll feel like you made a mistake. Or you might wait too long, until after the market has already recovered much of its loss. But that’s OK. You will never be perfect with the timing and you don’t need to be. Just have some rules and a process for following them and you’ll get close enough.
I would also add that this is yet another powerful argument for using asset allocation ETFs that rebalance themselves. These held up extremely well during the volatility of the last several months and removed all emotional decision making from the investor.
I did seriously consider rebalancing during the worst part of the March correction and I always have the 5% rule in my mind, but during the fear and uncertainty of the events at that time (especially of watching the value of my bond ETF (ZAG) fall significantly along with everything else ,didn’t make sense to me)… I became paralyzed to make any changes at all!
Also, feeling like I had to recalculate my allocation percentage on a daily basis during that time seemed like I was being too obsessive and not worth the stress of the decision-making..
I now, REALLY, see the value of asset allocation ETFs even though their MERs are slightly higher than the average MER of my ETFs combined.
Time to consider switching. Thanks for all that you do for us, Dan!
Yesterday, there was an approx. 4% correction in the markets. So would the multi asset ETFs have automatically rebalanced yesterday?
@Frank: Asset allocation funds would not be rebalancing on a daily basis. They will have a constant stream of dividend and interest payments, as well as cash from new investors, and they will be making gradual adjustments all the time. It would take a prolonged trend to force them to make significant trades to rebalance.
Very handy tool, thanks!
One question I have is whether or not it makes sense to have a similar spreadsheet to assess your industry/sector allocation. I’m still fairly new to this whole world, but wouldn’t it be helpful to have a tool that points out when you’re too heavily invested in one sector?
I am really a brand new investor–I set up an RESP and an RDSP for my son following CCP guidelines to the letter and then have pretty much ignored them completely except to invest govt. contributions when they came in. I just checked for the first time in months and was delighted to see that both accounts have made much more money than I expected in this horrible year.
Anyway, I am just getting ready to make this year’s contributions to both and was wondering how on earth to figure out rebalancing. As with everything, you’ve done it all for me. This spreadsheet is magic for someone like me who’s not terribly mathematically minded. Thank you for this, as for everything.