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.