Your Complete Guide to Index Investing with Dan Bortolotti

A Spreadsheet to Manage Multiple Accounts

2017-02-07T10:28:12+00:00March 15th, 2012|Categories: Indexing Basics|62 Comments

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.