You’ve got an RRSP with your employer, another with a discount brokerage, and your spouse has a couple of his or her own. You both have TFSAs, too, plus an online savings account where you park your cash. If all of these accounts are intended for the same purpose (such as to fund your retirement), you should think of them as one large portfolio. But how can you keep track of them all?
The ever-industrious Justin Bender, portfolio manager at PWL Capital in Toronto, has come to the rescue again. He’s created a custom spreadsheet to help Couch Potatoes keep track of their asset allocation across multiple accounts. Download the spreadsheet here, fire it up in Excel, and follow these instructions:
1. On the “Rebalancing Table” worksheet, enter your overall target asset allocations in the grey cells of Column C. The subcategories will automatically be totalled.
2. Now click the tab at the bottom left of the screen to switch to the “Input” worksheet. In the green cells at the top, you can enter the type and owner of each investment account: e.g. RRSP Homer, or TFSA Marge. You can enter up to 10 separate accounts.
3. Enter the names (Column A) and tickers (Column B) of the index funds or ETFs you use to get exposure to each of the asset classes. The spreadsheet has been preset with popular ETFs, but you can easily change these if you use different funds. (Important note: if you enter an actively managed fund, your computer will explode.)
4. Grab your most recent statement for each account and enter the current value of each fund in the appropriate cells.
5. Now toggle back to the “Rebalancing Table” worksheet by clicking the tab at the bottom left. You’ll see that this worksheet combines all of the fund values you just entered, treating all of your accounts as one large portfolio.
6. Compare your overall asset allocation with your target. Column E tells you the dollar amount you will need to add or subtract to each asset class in order to bring it back to its target.
By the way, cells that contain formulas are locked to ensure you don’t accidentally delete or modify something important.
@Steve: I did a little more poking around, and in the Google Spreadsheet help section, it explains that the “New” style Google Sheets launched in March 2014 were improved and different. This presumably is the reason that my old built spreadsheet doesn’t work with your fix. My fix is to copy my data to a new spreadsheet. But even if I elect to do nothing, the old spreadsheets will automatically be converted eventually to the new version. So this is what I’ll do, or rather not do.
Quick question – Should spouses hold unregistered accounts individually for tax reasons or is there any merit in having a joint unregistered investment account?
@Darby: There are often good reasons for non-registered accounts to have joint ownership, even if only one spouse is making the contributions and paying all the taxes. If one spouse were to die, the account immediately becomes the property of the joint owner: it does not have to go through the will and is not subject to probate.
how do I unlock the spreadsheet pls in order to change names?
@Tony: The spreadsheet is intentionally locked to prevent changes that could potentially corrupt the formulas . However, you can get a more up-to-date version of the spreadsheet from Justin Bender’s site. Scroll down to “Rebalancing Table”:
http://www.canadianportfoliomanagerblog.com/calculators/
Hi,
I’m new to investing and so far has been using the Tangerine funds and now I would like to start with ETFs. It seems like the spreadsheet doesn’t have Tangerine as one of the options. If I want to include my Tangerine funds as part of the portfolio, is it not possible?
Thanks!
@Helen: Thanks for the comment. The Tangerine funds are balanced funds, which means they hold all of the asset classes (bonds, Canadian and foreign stocks) so there is no need to rebalance them. That is is why they are not included in the spreadsheet.
I have a group RRSP that I contribute to through payroll deduction and a self directed RRSP. My spouse has a self directed RRSP as well.
What would be the best way to set up accounts with minimal overlap in etf’s?
Thanks
@Scott: With employer-sponsored RRSPs I usually recommend just setting these up so the asset mix mirrors your overall target. This may not be optimal in terms of asset location, but the practical reality is that you don’t have a lot of product choice with group plans, and it isn’t easy to incorporate the into a rebalancing strategy.
Hey guys,
I’m thinking about creating an app for this exact problem and wondering if you guys think its a good idea or not…
It would be similar to the spreadsheet in the post where you would add all your accounts, asset classes and desired allocations (only easier because it wouldn’t be a spreadsheet). It would then track the performance of the portfolio over time and show you how to rebalance things or make new contributions.
Would anyone use this??
I’m not sure an app would work in my particular case.
I finally got around to writing an Excel spreadsheet that allows me to rebalance within each account (joint taxable, wife and mine RRSP, wife and mine TFSA), and then also allows me simultaneously do a rebalance as if it was one large account, so that for example bonds would be tax efficiently distributed to RRSPs and TFSAs, to avoid US and international tax withholding at source US/International etfs are distributed to RRSPs, and Canadian dividend tax credited etfs are distributed to the joint taxable account. I only have to rebalance once per year, but I have my trading account open on a second screen at the same time so I can update my excel at the same time. I’m not sure how that would be possible on a small screen on a cell phone, but my old eyes certainly couldn’t handle it.
Is there a way to add lines? Thanks