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.
Thank you for this great and helpful tool !
Thank you!
Hi,
Do you think a portfolio with VGRO + additional ETFs stays in balance better than one without VGRO?
For example:
If we ignore bonds (but assume 20%), which portfolio do you think would stay in balance better?
– A 5 ETF portfolio of 45/20/10/5 (US/CDN/Int/Emerg) that includes 50% VGRO + 4 additional ETFs, or
– a 4 ETF portfolio of with the same 45/20/10/5 allocation without VGRO.
It seems to me like the self balancing of VGRO would cause the first portfolio to hold to its target allocations better. Has anyone done the math on this?
Very useful. Thank you !
Ugh, I just did my annual rebalancing last month with my own spreadsheet, but this one looks much better. I’ll definitely try it next time, thank you!
This is an incredible resource and came at just the right time for me
Oh, thank goodness! No, wait… Thank YOU!
Thanks Dan, looks great! Might like to see ZDB, but should be easy enough to add on my own. And of course USD equities, but then that gets into currency conversion and it is easy to understand why you chose to leave those out.
@Mark: Adding a different ETF (such as ZDB) is ridiculously easy. As for US-listed ETFs, you can add these too and just enter the value of the holdings in CAD. You would need to do this anyway when rebalancing:
https://canadiancouchpotato.com/2015/02/09/rebalancing-with-foreign-currencies/
@Dylan: Your intuition is correct: the greater the allocation to VGRO (or a similar ETF) in the overall portfolio, the less frequently you would have to rebalance.
Really a great tool and one I needed! Thanks!
Wonderful – can’t wait to set it up!
Terrific, thanks very much! This is way better than my own spreadsheet, which doesn’t handle the mixed-asset-class ETFs very well.
Great ideas and work Dan. Thank you very much.
Not sure how many people have hybrid portfolios but it would be nice to see a Sector spreadsheet page as well. For example I have x$ in each of the 10/11 GICS sectors I add $200,000 of VCNS or one of the other ETFs how does my sector allocation now look. VCNS VBAL & VGRO are pretty easy because their sector allocation is the same for each. I tried it for my portfolio and it looks like a few $100,000 of any won’t skew things to bad.
Hello Dan,
Great information as usual.
I have the following question.
I maxed out my TSFA and RRSP and I want to start adding new money in my taxable account.
Which combination you thing is better:
Combination 1: VGRO and ZDB ( to get a 60% stock 40% bond combination)
Combination 2: VUN (30%) VIU (25%) VEE (5%) ZDB (40%)
Thanks
@John: If your goal is 60/40, why not hold VBAL or XBAL in all three accounts?
New and improved!!! Great tool, thanks Dan!!!!
Hi Dan,
Thanks for the quick reply.
Wouldn’t option 2 be more tax efficient and more cost efficient (lower MER) in a taxable account ?
@John: Depending on the dollar amounts (you mentioned that you are just now starting to add to a non-registered account) I’d expect the differences to be very small and the convenience to be much greater. Moreover, if you are juggling an all-in-one ETF in the registered accounts and ZDB in the taxable account you will not always be able to keep your overall asset mix at 60/40.
For example, assuming you had $60,000 in the TFSA and $100,000 in the RRSP (all in VGRO), then you would need to about $53,000 in ZDB to make the overall mix 60% equities and 40% bonds. What do you do if you have more or less cash than that available?
Really interesting, thanks!
I’m still debating what is the best platform and strategy to buy ETFs. I’m currently buying TD e-series funds, which is free but if I go with ETFs, there’s a 9.99$ fee each time. I was thinking to do less transaction and buy more each time. Any advice?
Thanks!
@ccp,
If I take each account separately and not as a whole.
TSFA: will not touch until I am 72. I am using presently VGRO
RRSP: Same As TSFA
Taxable account: VBAL ( as you mentioned) OR VUN (30%), VIU (25%) VEE (5%) and ZDB (40%) ? (which option you think is better) I have a 5 figure amount to invest in the taxable account…
Thank You !!!
This is so helpful. Thank you!
Thanks for all that you do
Cool sheet, but without currency conversion I can’t use it. Live currency conversion by whatever API would be fine and is what I’m using in my own rebalancer sheet.
And of course Vanguard introduced 2 new all-in-one ETFs just a day after your post :)
@Anton: I would have been surprised if they didn’t. :) Of course, once these new funds have published their asset class breakdowns it will be easy to update the spreadsheet.
Thanks Dan, looks great. For someone who sticks to your 3-ETF model portfolio, I’m not sure this is the best tool to rebalance a single account though- having XAW broken down into it’s constituent parts in the summary table is not useful in rebalancing your 3-ETF portfolio. Are there spreadsheets that would help with this? (ie. showing how much more of XAW/VCN/ZAG needs to be bought or sold in order to rebalance)
Thanks for this great tool. I was wondering if this would be even easier if we set our (for example) TFSA balance and the sheet auto populates the 3 ETF model following our target allocation?
What a useful and attractive gem. As we have multiple RRIF accounts, doing the rebalance inside them to preserve fixed-income asset mix on the overall portfolio was a real headache in or first year of RRIF withdrawals. I took our comprehensive (10 years in development) which integrates our nine accounts and entered appropriate holdings values for yesterday.
Today I took my rough spreadsheet calculations with 2019 plans to modify the assets, keeping to our desired global neutral profile, and verified the before and after results, confirming my estimations were accurate.
Thank you for this tool, which I tinkered with extensively, substituting a number of the multi-asset ones to match our Mawer Balanced MF and Vanguard World holdings, using Morningstar for the specific regional holdings. It was a breeze, changing ETF names to suit, hiding unused entry lines — even copying and pasting in an extra entry on the US category (making sure the totals were accurate — and generating a beautiful legal-page size report of the whole works!
Even picked up a clue as to why VXC has been outperforming XAW, despite the lower MER of the latter, which had been puzzling me for the last six months.
@Davie215: Many thanks for the comment and for confirming that spreadsheet held up to your custom changes. Glad it worked for you.
@Jessy and Jan P: For a simple spreadsheet for one account try this:
http://www.squawkfox.com/wp-content/uploads/2012/02/Canadian-Global-Couch-Potato-ETF-Rebalance.xls
Hello Dan,
Thank you so much for this spreadsheet it came at a perfect time for a lot of DIY investors .
Dan i know this is to early but have you had a chance to look at the new Vanguard all equity etf VEQT ?
@Gus: Many thanks. I will be writing about VEQT, but it’s a bit early. Obviously an excellent choice for anyone who wants an all-equity portfolio, or it can be used with a bond ETF to get whatever asset allocation you want.
Thank you for the helpful info, CCP. My question: I hold some TD Mutual Fund (TDB398) in addition to ETFs/equities.
The asset allocation of TD398 (from Morningstar):
Cash 0.97 %
Canadian Equity 57.10 %
U.S. Equity 1.04 %
International Equity 1.12 %
Fixed Income 29.08 %
Other 10.67%
I can calculate the $ value of each asset allocation of the total invested in the Fund, and input that into the respective sections of spreadsheet – but I’m unsure what to do with that “Other’ 10.67%” – how to capture that so it is reflected in the overall asset class.
Thank you for your help.
@Diana: There’s no perfect way to handle this, so I would just split the amount proportionally between Canadian equities and fixed income, which are the main asset classes in the fund. Remember this does not need to be precise: 66% Canadian equity and 34% fixed income is close enough.
I tried this one and found the CPM rebalance much easier to use as I only have the three holdings in one self directed RRSP account, so I am only concerned with the relative weights of ZAG, VCN and XAW compared to their target allocation and not the particular sector allocation of XAW.
Bonus: If you use google sheets instead, you can use the ‘googlefinance’ function to get live share prices. So just put how many shares you own of each ETF and it will stay up to date on money held in each stock.
Hi,
My mom just recently turned 70 and has in her possession a sum of 230K cash. I would like for her to put it in the market, but I am not sure what is the best way forward.
I thought that conservative asset allocation would be a good thing for her. But I was wondering what would be the best way to go, also allow this sum to generate a certain amount of annually in dividend.
Would it be best to put her money on REIT ETF? or MLP ETF? with a little higher yield, would be a better thing?
Your help would be great.
Should an REIT ETF count as Fixed Income or Canadian equities?
I’m new to investing and I’m maxing out my RRSP and tax free with just the VGRO. Is this a good idea, or should I diversify a bit more in these accounts?
@Rachel: You are unlikely to be able to build a more diversified portfolio than VGRO. It’s one ETF, but it includes a he number of individual holdings in multiple asset classes, so don’t be deceived by its simplicity.
@Sylvain: REITs are equities, not fixed income.
Hi Dan – do you plan on doing one for the e-series mutual funds – that would be amazing!
Thanks!
@Maggie: You can adapt this spreadsheet for use with any funds, including the e-Series. Just add the individual e-Series funds in place of ETFs in the same asset class. (For example, overwrite VCN with TDB902 under the Canadian Equities heading.)
Dan: I’ve been following the Couch Potato method for a couple of years now to some success (XAW 60% / XIU 30% / ZAG 10%). Would you suggest just one of these new ETFs to replace the three I have?
-Firefly Mike
Thanks!!
Hi, thank you for the spreadsheet. I am wondering for the purposes of rebalancing a portfolio, do I include cash that I want to keep in a HISA say for school tuition or a downpayment in a few year in that spreadsheet? When I include a % cash in the spreadsheet, I need to take away a % in another category, say for example in bonds. I’m not sure how to reconcile this. For example, say if I want to keep $50,000 in a HISA for the above purposes, but I want a portfolio that is 25% fixed income (bonds, GIC), 25% VCN and 50% XAW. Do I exclude this $50,000 from the spreadsheet or should this be a part of my “fixed income” kept in cash and thus my overall portfolio risk is more balanced (eg. 40% fixed income/cash, 20% VCN, 40% XAW). Thanks!
@Donna: In general, portfolios with different goals should be treated separately. So the cash you have earmarked for short- or medium-term goas like tuition or a down payment should not be mingled with your long-term TFSA or RRSP. Just exclude the HISA from the spreadsheet.
Think of it like this: if stocks fall 20% or 25%, are you going to dig into your cash savings to rebalance. I hope not!
Thank you so much! That makes sense :)