A Spreadsheet to Manage Multiple Accounts

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 “Allocation” worksheet, enter your overall target asset allocations in the white cells of Column C. The subcategories will automatically be totalled in the black cells.

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, 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 the funds in my Model Portfolios, 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 “Allocation” 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.

Justin and I used Excel’s “Protect” function to lock all cells that contain formulas to ensure that you don’t accidentally delete or modify something important. However, if you’re an experienced Excel user and you want to customize the spreadsheet, right-click on the worksheet tab, select “Unprotect sheet…” and type the password: potato. Be advised that this voids the warranty.

Experiment with the spreadsheet, and let us know what you think.

By the way, if you prefer a simpler rebalancing spreadsheet for a single account, you can download one here.

59 Responses to A Spreadsheet to Manage Multiple Accounts

  1. oldie March 3, 2015 at 8:08 pm #

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

  2. Darby April 13, 2015 at 5:06 pm #

    Quick question – Should spouses hold unregistered accounts individually for tax reasons or is there any merit in having a joint unregistered investment account?

  3. Canadian Couch Potato April 14, 2015 at 8:28 am #

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

  4. Tony August 16, 2015 at 5:12 pm #

    how do I unlock the spreadsheet pls in order to change names?

  5. Canadian Couch Potato August 16, 2015 at 5:17 pm #

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

  6. Helen July 22, 2016 at 1:30 pm #

    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!

  7. Canadian Couch Potato July 23, 2016 at 10:30 pm #

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

  8. Scott September 22, 2016 at 3:49 am #

    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

  9. Canadian Couch Potato September 22, 2016 at 9:33 am #

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

Leave a Reply