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.
“if you enter an actively managed fund, your computer will explode” — LOL. A lot of the cells say “#NAME?”. Don’t know what that means.
@Beardie: It wasn’t a joke—it’s true. :) Not sure what’s causing the #NAME errors. There may be some compatibility issues with the spreadsheet if you’re not running a recent version of Excel.
I laughed at first when I saw room for 10 accounts, but I quickly remembered that my wife and I have 9 accounts between us. It’s amazing how quickly the number of accounts can grow.
Ha – I was updating my own version of this last night. It’s so hard to read but at least fits on one spreadsheet. I like how this was setup and I now have a logo of a potato and bags of money chilling on a couch. Thanks guys.
I do something similar on google docs spreadsheet.
I even combined it with my budget, and flag when REBALANCE when something needs to be rebalanced, etc.
Rebalancing threshold is computed dynamically base on my portfolio size, and my savings rate.
I have OCD….
I’m getting a window into the lives of my readers now. Glad to know I’m not the only geek who likes spreadsheets like this.
I’m using Open Office on my mac and ran into the #NAME issue. The IFERROR function does not work in OO so I changed to use IF(ISERROR….
Eg.”= iferror(B8/$B$38;0)” was changed to
“=IF(ISERROR(B8/$B$38);0;B8/$B$38)”.
Probably a better way to do it but that’s what I came up for now.
My wife and I have seven accounts, but we tend to concentrate most ETFs in different accounts (eg. VTI and VWO are only in one account each). I had already modified you original spreadsheet to include a row for each account/ETF. I then added columns to calculate the after tax value of the account such that our tax deferred RRSP holdings are multiplied by (1- our marginal tax rate) and the TFSA and RESPs are counted as their full value. This allows me to maintain my target asset allocation on a tax adjusted basis.
@rj: holy cow, I should do that !!
In the future, please give us all spreadsheets in OpenOffice.org format. It’s free, works, and despite my owning a few shares in Microsoft due to my existence as a couch potato…I can’t wait until their empire crumbles and we all wind up using free software ^-^
@Slacker as I am sure you can tell I have OCD when it comes to personal finance as well (:
It would be nice if the spreadsheet included VT….I added it in and its really nice to see where the money is allocated. Good job and thanks alot!!
I’ve built a different kind of spreadsheet, built around calculating my asset allocation while taking actual value into account. ie: My RRSPs are calculated according to their current value if I were to sell them now (I have a defined benefit pension so I’ve got a ballpark idea of what my income tax bracket will look like in 25 years), my non-registered account calculates current value after capital gains taxes, and my TFSA is fully valued. I then fiddle with scenarios… “What happens if I add my ZRE allocation earlier this year, will I really be out of whack elsewhere”, and decide what I want to do NOW, as well as medium-term planning (like: what’s my TFSA investment for 2014 going to be ?)
Thanks for this spreadsheet. Its very similar to one I have been using for years but better structured. I have one thing you could add as a separate sheet as per Paul G.’s comments: if the user could add their income from various sources, both from the portfolio and from wages, with the tax implications of the asset allocation calculated. This would allow balancing account asset allocations to optimize for taxes.
Alternately just have the marginal tax rate for the province the user resides as a simpler way to add some tax functionality.
Thanks again! This site is the most useful investment site I use!
Thanks for the spreadsheet update!! I’ve used the “simpler” one quite, adding a lot of my own enhancements, but hadn’t got around to doing the multiple accounts yet. Great way to store information about all your accounts for your spouse or significant other, who might need it if you are out of commission in any way.
It does bring up an issue, though, and that is security of information. I’ve added a password to my spreadsheet, just to make it a little harder to access. But there are “password recovery” programs out there that could hack into one of my Excel spreadsheets with ease. Any thoughts on other ways to make spreadsheets more secure, or otherwise protect our valuable financial information??
@GCB: VT is a tricky fund when considering your asset allocation, as it includes US, EAFE and emerging markets stocks. In a small portfolio you can lump it in the global equity category.
@Andrew: I don’t think we’ll be adding tax functionality to the spreadsheet, which would make it awfully complicated. Readers are more than welcome to customize it however they please, of course. :)
@John: A firewall should protect you from being hacked. In any case, there’s not much that a thief could do with a spreadsheet full of account information. If this was a danger, then people would steal account statements from the mail.
@John- a couple of ideas for you. You are correct – spreadsheet passwords are virtually useless, even if you make them compicated.
A hardware firewall (Router) will help. Stay up to date with anti-virus (MS Security Essentials is free and works well). Scan your system regularly.
For important files like my finances, I use a program called TrueCrypt to create a secure “drive” on my PC. It looks like a separate hard drive, but it is really just an encrypted file. But any program (including Excel) can read and write to it. If your PC is hacked, the hackers need the TC password to open the “drive” and see your info. With military grade encryption, that is not likely. As a bonus, you can backup the “drive/file” and all the data within it is backed up as well – and is encrypted.
Also, divide your passwords into 3 or 4 groups. Unimportant stuff you can use a basic password and share across sites (e.g. news sites, etc.). More personal stuff have a more complicated password (Facebook, etc.). Banking and ID Theft risks use the most complicated password you can.
Mix letters and numbers. Use mnemonics (e.g. This is my Password) becomes TimP (don’t use that example, of course). But you can repeat them in reverse TimPPmiT to make them more complex, adding in some numbers important to you so you have TimP72PmiT. etc.
My banking ones are pretty complex so I use a program called KeePass to securely store them. These apps are all open-source – I have no interest in them! they are just useful.
Finally, use a better browser – Firefox. IE is notorious for security flaws.
Should one consider a defined benefits pension as a part of your asset allocation, assuming it must be supplemented by other investments? I am wondering if it should replace the bonds/fixed income portion of my retirement savings. Then all my actual investments would be in equities. Is this logical?
@Charlotte: You certainly need to consider a DB pension plan when setting the asset allocation of for your other retirement accounts, and you’re right that, in general, this means you can dial down the fixed income. Unfortunately, there’s no easy formula for doing this. A financial planner would be helpful here.
Dan and Justin – thanks for the spreadsheet. It is working well for me with Excel 2010 Any suggestions on where in the spreadsheet I should put Sunoco shares and iShares XEG and XFN. Thanks!
@Bill – Thanks for the comments. There is a good discussion of web browser security at:
http://blog.zonealarm.com/2012/02/which-web-browser-is-the-most-secure.html
I do most of the things you mentioned, but not encrypting a drive. I’ll look into that!
@Linda: Glad it’s working for you. Just include Sunoco, XEG and XFN in the Canadian equity section.
Great spreadsheet, thanks! What I find confusing though is that you don’t seem to track the currency of each investment. How do you rebalance each year if your portfolio includes ETFs in US$ and in CAD? I assume that you would need to indicate in the spreadsheet the currency of each ETF, and convert it to CAD using the current exchange rate. Otherwise at the time of rebalancing, how would you know how much money to add to the US-listed ETFs?
Nice spreadsheet. Just curious on your take on USD side of things. Right now it is simplified since the dollars are on par. Even so I assume the intention would be to calculate everything back to CAD on the input sheet. Is that correct? Can we infer from that: 1) that target allocations in various model portfolios should be in CAD? and 2) rebalancing might be necessary if there is a big shift in currency exchange rates?
Of course if some of the above is correct then one would have to reconvert calculated $ differences in the spreadsheet back to USD when considering rebalancing such ETFs as PRF, VEA, PDN, etc.
@Jean and Jack: With the rate so close to par these days, it almost doesn’t matter. But in general, yes, you should consider each asset class in Canadian dollars, and if there is a big move one way or the other, that should trigger a rebalancing.
If you do want to add this capability to the spreadsheet, the easiest way would be to “unprotect” it (password: potato) and then pick a cell to enter the current exchange rate. Let’s say that $1 CAD = $1.05 USD. You would enter “1.05” in the cell.
Then when you enter your dollar amount for a US-denominated fund, multiply it by the value in that cell. If you have $10,000 USD in the fund, and your exchange rate is entered in cell B7, you would enter: “=10000*B7”
That would produce the value in Canadian dollars.
Hope that helps.
While I’m a little ‘late to the party’ here, I’m wondering about the comment with respect to the ‘tax adjusted basis’ calculations. Say I have a 50-50 portfolio split between registered and non-registered accounts, and I want a perfect 50-50 allocation split between stocks and bonds. If I had $100k to invest and held a bond ETF in my registered account and a broad market stock ETF in my non-registered account, I assume that it’s not as simple as saying I’d allocate (and rebalance) based on putting $50k into each.
If I was to assume I will have a marginal tax rate of 40% in retirement, then would the target amounts I should shoot for with 100k to invest (to balance it 50-50) become: Registered – 62500 and non-registered 37500? [62500 – 40% tax = 37500]
Perhaps this has been covered already and I just missed it. Those of us with defined benefit pensions don’t generally have a whole ot of room in our RRSPs to begin with and I’m curious to how one would go about accounting for the difference between the after-tax values of the registered and non-registered accounts.
Thanks in advance.
P.S. For my previous post you can assume registered account means RRSP, and we’ll pretend TFSA’s don’t exist :)
@Chad: You’ve got the right idea: a $50,000 investment in an RRSP is worth a lot less than $50,000 in a non-registered account, because the latter has a much smaller tax liability. Unfortunately, there’s no simple formula you can use to calculate the optimal balance, because your future tax liability is only an estimate. There is some advanced financial planning software that can help, but I’m not aware of anything easily available to the public.
@CCP: Thanks for the confirmation – happy holidays!
@CCP: I’m down to the “almost done” in allocating my portfolio, and in anticipation of future rebalancing tasks, tried to download the rebalancing spreadsheet only to find that, as the user of a Mac since mid 2011 (I could hardly understand how to keep safe and use my PC without crashing) I can’t make the spreadsheet work. Any likely help here? (I don’t really expect that you’d be knowledgable in Passive Index Investing AND cross platform Excel usage, but I hoped someone might know!)
@CCP: The more I read-up, the more comfortable I feel about the CCP approach, and more questions I have, such as how to address multiple accounts, their asset allocation and asset location. And, to your credit, you keep answering these questions and providing helpful tools – such as this spreadsheet – to enable us beginners to become intermediate/more advanced!
You are to be commended good sir! :)
Just discovered your site and the spreadsheet looks great. I am assuming that the user will have to update all the individual investment values accounts whenever he wants to see the asset allocation. In the philosophy of the couch potato, what are the chances of connecting this spreadsheet with a site like Google finance or some other source to have the stock values automatically updated? At least the ones where it is available?
Of course the next level in couch potato ecstasy is to have the spreadsheet wake up and alarm the couch potato when the asset allocation deviates from the set point by more than a predetermined amount?
Great work!
@Oliver: Glad you like the spreadsheet. I’ve tried linking spreadsheets to stock data using both Excel and Google Docs, but I’ve found that Canadian stock data is hard to come by this way.
Hi, I am getting a lot of delayed quotes from Google Finance site where I can construct my own portfolio of stocks and ETFs (in US and Canada) and then export it to Excel spreadsheet with a CSV file. I am thinking of linking that to your spreadsheet so that once a month or quarter or whenever I feel like I can do a download from Google Finance to update stock and ETF prices to the delayed quotes and get an up to date asset allocation. This should be easier than going through all the statements to get values for each and every holding.
It is doable but it is a bit involved every time a change in position happens as I will have to update the quantity and add or delete items in the look up and linking tables. Kind of counter intuitive for a couch potato.
Oh well, even couch potatoes have to move, so maybe until the next thing comes along…..
Back to the days before the TV remote…..
Still, you have gone a long way to keep us on the couch!
Thanks for making this spreadsheet. I’ve used it for the past year and it’s worked well. Being quite familiar with excel I made 2 small changes that perhaps you may want to consider for the future (I can even email you mine if you’d like).
1. Box for current exchange rate and conversion of ETF’s in USD to CDN (for apples to apples comparisons)
2. A rebalancing range of +/- 1% of ideal for each fund
This spreadsheet is an excellent way to stay organized, thanks again for putting this together.
Hi, and thank you for creating the spreadsheets, I’ve downloaded them both and use them regularly. I have one question though, I’ve tied them together and use them both together to rebalance within each, (TFSA, RRSP, Non-registered accounts) by using the individual spreadsheet for each account and then have them update on the multiple account spreadsheet so I can see my overall balance. Is there a way I can make my target mix ratio on my multiple account spreadsheet update automatically when I make a change within my individual account spreadsheets? Right now, for example, if I have a bond ratio of say 20% in my RRSP spreadsheet and I add cash to the RRSP, when I move the cash into a bond purchase it throws off my overall spreadsheet mix because new money has been added, but the bond target ratio was good before, and now shows as too high on the overall accounts spreadsheet.
@Keith: I’m a bit confused about why you would need both spreadsheets. If you have multiple accounts I don;t think it makes sense to have target weights for each individual account: the targets should only apply to the overall portfolio.
CCP;
Thanks for responding. I try and keep my bonds and interest bearing ETFs in RRSPs and TFSAs and I have a larger portion of my portfolio in a taxable account. My US and Foreign ETFs are also currently kept exclusively in our RRSPs to minimize the withholding tax. Recently I sold one of my a rental houses and had a large cash input, but my TFSAs and RRSPs had already reached their limits, so I had to both treat the accounts as one large investment, while at the same time rebalancing within each account.
As rental income from the remaining houses comes in, I try and rebalance once per year, but I still maximize my RRSPs and TFSA room (I have a small T4 income so my RRSP room is relatively tiny), and I end up having to put more into the taxable account.
I guess the simple solution would be to start to include bonds, interest bearing investments, and US ETFs in my taxable account, but I was just hoping to minimize tax as much as possible by having a spreadsheet that allowed rebalancing of each account (taxable, TFSA, and RRSP), individually while at the same time keeping an eye on the bigger picture.
Right now when I add a substantial cash position to the taxable account, it throws my individual account rebalancing spreadsheet for a loop, whereas it is a much smaller deviation on my multi-account spreadsheet.
Maybe I’m over thinking it, but I just wondered if there was a formula I could input on the target percentage column on the multi-account spreadsheet that would then correlate with the target percentage of the individual accounts spreadsheets.
@Keith: This is actually a pretty common problem, but there is no simple solution for managing it. At the most basic level, the strategy typically involves gradually selling equities in the registered accounts and repurchasing them in the non-registered account. If at some point you run out of RRSP/TFSA room and you need to hold fixed income in the non-registered account, then you need to go the next step and use GICs or another tax-efficient product. You can;t automate this, because a lot depends on knowing what future contributions will be, and that’s something that’s hard to build into a spreadsheet. Might be a good idea for a future blog post!
CCP;
Thanks for the response. Actually, I’m quite close to a solution. I’ve taken the basic spreadsheets you kindly provided, and have combined them as sheets in a workbook. I’ve also added a ‘drawdown’ spreadsheet which allows a person to input their current monthly expense requirements, the inflation rate, as well as the current yield of their ETFs to provide a guideline of the increasing monthly budget requirement over time as well as the remaining capital nest egg. Additionally, I have added and linked in a page that allows a user to input their various ETF holdings, the current yields, MER expense ratios, and number of units. On a separate page, I’ve listed any rental income/expenses a user may have. I’ve linked all of the pages in the workbook to a current networth statement that shows the individual’s diversification across all asset classes, the overall yield from each asset class and the cashflow generated. I would gladly share the workbook if you’d like to include it in an upcoming blog post. The only formula missing is that pesky and elusive one that will allow users to rebalance within each account and tie their target percentages with each account to the target percentages in their mulit-account spreadsheet.
Hi Dan;
Just curious if you have any plans on updating the spreadsheet to align more with the revised model portfolios….capturing VUN, XEC, XEF etc?
Thanks
@Darrell: The fund names are just there as placeholders and any investor can add, delete or change them to better reflect their personal portfolios.
Thanks so much for the spreadsheet.
My wife and I have ~$200k each in our portfolio and are using the Complete Couch Potato model. If we manage our portfolio as one combined portfolio, there is that possibility that we might end up with fairly uneven portfolio sizes in the end (for example if one portfolio holds more equities than the other). I realize there are income splitting opportunities (with some limitations) for retirees, but wouldn’t we better off managing each of our portfolios “independently” so that we are more likely to end up with portfolios that are roughly the same size?
Thanks in advance.
@The V: There may be situations where it makes sense for a couple to build portfolios that are roughly the same size, but it’s not that common. As you’ve acknowledged, with the ability to split RRIF income after age 65 there is little need to keep RRSPs roughly equal. However, it may still make sense to use spousal RRSPs to even things out if one or both of you plans to retire young, since RRIF income splitting is not permitted before age 65.
That said, if all or almost all of your investments are tax-sheltered, there’s less need to treat them all as a single portfolio. Usually the goal with that approach is to maximize tax-efficiency with careful asset location. If a couple is using only RRSPs and TFSAs they can certainly use the asset allocation in each account if that is easier for them to manage.
@Dan: Any plans on incorporating a macro in your Re-balance Spread Sheet to automatically pull the current Stock Price. There by just using the amount of holdings you have, and then multiplying it by the automatic updated stock price. It seems very handy, and I would love an end product from you. I did find this one that looked good:
http://www.excelclout.com/stock-quotes-in-excel/
Thanks, Que
@Que: I have done this using Googledocs cloud based spreadsheets, and the formula is
=GoogleFinance(A11,”price”)
where A11 is the cell holding the ticker symbol e.g. VCE, VAB, ZEA etc. It seems to work for US listed stocks, e.g. VBR, too. Exactly how it know where to look, I can’t figure out, but it returns the most current listed stock price, usually with a 15 or 20 minute delay.
Where it breaks down is when the TSE stock ticker symbol is the same as another stock on the NYSE or other exchange — for instance if you have FXM or XEC on the TSE, the above formula would return the price of the US listed stock of the same ticker symbol.
I have done an ugly workaround, which I admit I hardly understand, but it works like this:
instead of FXM, OR XEC, put the symbol as FXM.TO and XEC.TO
The formula that will pull up the current price of the TSE stock is
=index (importhtml(“http://finance.yahoo.com/q?s=”&A7&”&ql=1”, “table”, 2), 4, 2)
However, check for reasonableness. There are times when the formula returns an ERROR message, and there are other times when the price that shows does not make sense. I don’t understand exactly why this happens, but my guess is that the formula involves looking up values in a table, and I actually have the wrong cell in the table that sometimes gives a number that is not the current price, not even close, but at other times gives the current price.
The hazard of the above glitch is that if the number is really crazy wrong, and you have plugged in a formula into your spreadsheet giving the current asset allocations (to reference against your target allocations), then it will affect the calculated value of the other stock percentage value allocations, giving a wrong percentage, as well as giving a wrong value for the current total value of the portfolio, even though the individual values of the other stocks are still calculating correctly.
@oldie: If you are using the GoogleFinance function then prefix the Canadian ticker symbol with TSE: (example TSE:IMO) and it will find the Canadian one.
I have a Google Sheet to rebalance a portfolio, similar to many I have seen, with the added feature that it will balance across multiple accounts and let you put a priority of funds by accounts. I will be publishing a post on it in a week or two, but here’s a link to it in case you want to take a look.
https://drive.google.com/open?id=1_Llg-aA_GFXRoImd3eX_Tcffv27vFa8S67l7dTf31oY&authuser=0
@Steve: Thanks for hint. However, it may well work for other identically labelled stocks on other exchanges, but when I plugged in TSE:FXM and TSE:XEC I got #N/A messages, and the error note was that the TSE exchange was not supported (??). Don’t know why exactly there is a discrepancy, but there it is.
@oldie: I checked and TSE:XEC does work for me. Here’s a link to a google sheet that I use to just pull prices.
https://drive.google.com/open?id=1Cp0XWyc7amhFBR7tdxl-NbZsoOeFb8HwobM-Y3T5t0A&authuser=0
@Steve:
This is weird! I went to your link and indeed your TSE:XEC works, as does TSE:FXM. But no matter how I do my old spreadsheet which I built early last year, I think, and modified many times since, I can’t get it to recognise the same labels as your spreadsheet does. I even checked my formula which was a minor variant from yours (mine didn’t have the “$” modifier on the column indicator; so I changed it to exactly yours — no luck.
So I built a completely new test spreadsheet with 5 cells in it holding VCE, XEC, TSE:XEC, FXM and TSE:FXM. and the formulas in cells in an adjacent column, similar to yours. It worked perfectly!
I don’t quite understand how on-line spreadsheets work. Whenever you build a new spreadsheet from scratch, you obviously must be using the latest version. Maybe a spreadsheet built 1 or 2 years ago using an old version doesn’t get updated over the years — and maybe the old version didn’t have the capability to support the TSE.
If this is the case, I don’t know how to update it except by building a new one identically from scratch, and repopulating it with data from the old one.
In any case, thanks again for the tip — it certainly fixes the annoying problem that I have been having!