Your Complete Guide to Index Investing with Dan Bortolotti

A New Rebalancing Spreadsheet for ETFs

2019-02-02T14:55:26+00:00February 4th, 2019|Categories: Portfolio Management|Tags: |42 Comments

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.

42 Comments

  1. Adam February 4, 2019 at 7:19 am

    Thank you for this great and helpful tool !

  2. David February 4, 2019 at 11:50 am

    Thank you!

  3. Dylan February 4, 2019 at 12:26 pm

    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?

  4. Manpreet February 4, 2019 at 12:27 pm

    Very useful. Thank you !

  5. @peterforint February 4, 2019 at 12:31 pm

    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!

  6. Ben February 4, 2019 at 12:31 pm

    This is an incredible resource and came at just the right time for me

  7. Joon February 4, 2019 at 12:32 pm

    Oh, thank goodness! No, wait… Thank YOU!

  8. Mark February 4, 2019 at 12:56 pm

    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.

  9. Canadian Couch Potato February 4, 2019 at 1:31 pm

    @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/

  10. Canadian Couch Potato February 4, 2019 at 1:48 pm

    @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.

  11. Chris February 4, 2019 at 2:43 pm

    Really a great tool and one I needed! Thanks!

  12. PatW February 4, 2019 at 4:34 pm

    Wonderful – can’t wait to set it up!

  13. neil February 4, 2019 at 4:36 pm

    Terrific, thanks very much! This is way better than my own spreadsheet, which doesn’t handle the mixed-asset-class ETFs very well.

  14. Richard Parent February 4, 2019 at 5:11 pm

    Great ideas and work Dan. Thank you very much.

  15. Brian February 4, 2019 at 6:09 pm

    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.

  16. John February 4, 2019 at 6:10 pm

    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

  17. Canadian Couch Potato February 4, 2019 at 7:38 pm

    @John: If your goal is 60/40, why not hold VBAL or XBAL in all three accounts?

  18. Joe February 4, 2019 at 7:53 pm

    New and improved!!! Great tool, thanks Dan!!!!

  19. john February 4, 2019 at 7:54 pm

    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 ?

  20. Canadian Couch Potato February 4, 2019 at 8:03 pm

    @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?

  21. Guillaume February 4, 2019 at 8:08 pm

    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!

  22. john February 4, 2019 at 8:15 pm

    @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…

  23. Desrochers February 4, 2019 at 8:38 pm

    Thank You !!!

  24. Nick February 4, 2019 at 10:10 pm

    This is so helpful. Thank you!

  25. George Cossenas February 5, 2019 at 9:05 am

    Thanks for all that you do

  26. James February 5, 2019 at 11:42 am

    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.

  27. Anton February 5, 2019 at 1:29 pm

    And of course Vanguard introduced 2 new all-in-one ETFs just a day after your post :)

  28. Canadian Couch Potato February 5, 2019 at 1:40 pm

    @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.

  29. Jan P February 5, 2019 at 2:32 pm

    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)

  30. Jessy February 5, 2019 at 5:24 pm

    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?

  31. Davie215 February 5, 2019 at 7:56 pm

    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.

  32. Canadian Couch Potato February 5, 2019 at 8:25 pm

    @Davie215: Many thanks for the comment and for confirming that spreadsheet held up to your custom changes. Glad it worked for you.

  33. Canadian Couch Potato February 5, 2019 at 8:30 pm

    @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

  34. Gus February 5, 2019 at 8:56 pm

    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 ?

  35. Canadian Couch Potato February 5, 2019 at 9:50 pm

    @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.

  36. Diana February 6, 2019 at 1:02 am

    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.

  37. Canadian Couch Potato February 6, 2019 at 7:40 am

    @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.

  38. Sheldon Petrie February 6, 2019 at 12:58 pm

    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.

  39. Brad February 14, 2019 at 2:59 pm

    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.

  40. Duy February 14, 2019 at 4:07 pm

    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.

  41. Sylvain February 14, 2019 at 7:28 pm

    Should an REIT ETF count as Fixed Income or Canadian equities?

  42. Rachel February 14, 2019 at 9:33 pm

    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?

Leave A Comment