The portfolio spreadsheet includes:
- all the Canadian and US holdings together, with the US holdings converted into Canadian dollars;
- live pricing of all the holdings, courtesy of Google Finance, at least I hope that it updates live as posted; it did while I was editing it on the web; we'll see tomorrow;
- that includes live conversion of US dollars into Canadian dollars (during the North American trading day at least); this part required a bit of finagling using the FXC Canadian vs US dollar currency ETF as an approximation but there seems to be NO direct way of getting live currency rates into a portfolio calculation on the web at the moment;
- gains or losses by holding and for the overall portfolio, which can be used to see potential capital gains and perhaps later in the year facilitate some tax loss selling;
- gains or losses and movements away from the target percentages by asset class, which will enable quick and simple portfolio rebalancing come next May;
- a separate tab/sheet for the asset allocation model I used, including some notes on how and why I picked the ETFs I did, as well as credible alternatives; note that the allocation percentages get pulled from the this sheet into the main portfolio sheet so if I decide next year to change my percentages among classes, it will be quicker to see what buys/sells are required;
- another separate tab for tracking the Adjusted Cost Base for Canadian tax purposes - the one on the spreadsheet below is actually an edited one of my own real one, since I am too shy to reveal the details of my financial affairs, but I hope it is of use to others who may be looking for a simple model; here again I've pulled some of the ACB data per share into the main spreadsheet in the "Cost May 23" area; if one is buying holdings at different times, the only way to keep track of the ACB of US holdings for Canadian tax purposes is to do some sort of journal with US exchange rates applied at the time of purchase (not those in effect at the time of eventual sale, unless you either want to make a gift to Revenue Canada, if the C$ continues its upward path, or get in trouble with Revenue Canada, if the C$ starts heading back down;
Stumble It!
11 comments:
Wow, that spreadsheet is really neat.
I like your new look. I'd like to make changes to my presentation but I'm afraid to touch anything for fear the whole blog will get messed up.
Mike
The new layout of your blog is much improved! Great job!
FT
That is a very nice spreadsheet... great example of how to use the googleFinance() functions...
You may want to share the faster published version URL (get that from the PUBLISH tab within the spreadsheet editor) as a perpetual link on your blog... like on the right side pane... so people can always get to it... you may even want to try embedding a small range (summary of returns?) so people see that without clicking any links...
have fun!
Thanks for suggestions. This stuff is always a work in progress, huh? Some sort of chart display of the data would likely be more visually effective. ... which reminds me of another favorite book by Edward Tufte called the Visual Display of Quantitative Information, a clasic definitive book if ever there was one.
Got to admit I switched to another blog template - that's all that is needed to be done, Mike, no data is lost in the process - to accommodate the wider view of the spreadsheet. Have to admit also that the new look is less "lugubrious" than what I had before. The sun has come up, so to speak.
amazing spreadsheet...this is exactly what I've been after. I've downloaded to my computer (thank you). Unfortunately, I know very little about excel...my cells are blank for getting the quotes...could you tell my the formula I need to enter to have the stock prices downloaded.
Thank you so much.
The formula is as follows.
Example for XMD iShares Mid-cap on line 6
in cell H6 Market Value put
= GoogleFinance (B6, $H$25)
the B6 takes the XMD ticker symbol and the $H$25 is an absolute/invariable reference to cell H25 where the word price is found; that tells the GoogleFinance function to fetch the price, as opposed to a whole bunch of other possible values for XMD.
For a US Holding like AGG in line 8
cell H8 contains
= GoogleFinance (B8, $H$25)
i.e. fetch ticker AGG, its price
cell I8 has the US$ value of the holding
= H8*C8
and cell J8 multiplies by the approx. C$/US$ rate in cell I26 with this formula
= I8*$I$26
Cell I26 contains another GoogleFinance function as follows
= 1/GoogleFinance ("FXC"; "price")*100
i.e. fetch the price of FXC, the foreign currency ETF that holds C$ and turn it into the format to multiply by the US$ value of the holding.
Good thing you have enquired about this, I just noticed that the description in cell H26 is backwards - it's C$ per US$ not US$ per C$ - and I've now fixed it.
With one of each of the Canadian and US holdings shown you can simply copy and paste the formula down each column in the appropriate US or Canadian cells to recreate what Google unfortunately strips out when the spreadsheet is downloaded.
You can substitute your own ticker symbols in column B, though you'd have to manually change the long name of the holding.
Check out Google's help page that explains this at
http://google.com/support/a/users/bin/answer.py?answer=54198&topic=11264
Good luck!
brilliant...thanks very much. very helpful
Outroupistache,
What great info you have here. I find your post extremely informative. I'm 29 and from Alberta...started investing about 1.5 yrs ago and have enjoyed nice gains with our market. Anyway, I have limited financial knowledge and realized my luck would soon run out so wanted to come up with an long term investment plan. Decided ETFs would be best for me and came up with a very simplified plan. I'd be very interested in your thoughts/comments if you have time.
I use interactive brokers ($1-2/trade) for non-RRSP and a large online broker for RRSP. Breakdown...
XIC 25%
VTI 30%
VGK 10%
VPL 7.5%
VPO 7.5%
? BONDS 10%
DISCRETIONARY 10%
I plan on keeping VTI in RRSP and purchase to reblance twice per year. The others will add every 2 months.
Be very interested in your thoughts on...
1)reasonable approach? percentages?
2)just starting to learn about bonds and presently have no idea would this be best in one of the ishares bond funds, GIC, mix other
3)planning on VTI and bonds in RRSP account - hoping I can purchase US dollars from interactive brokers account (much cheaper) and transfer to my RRSP acct. Reasonable approach? If I had room for any others in RRSP, which would be best.
4)any other thought comments greatly appreciated.
Thanks so much. Was able to modify you spreadsheet for me and find it is great.
dre222000,
1) overall approach and balance looks ok, mind you, it's about what I have picked for myself so that's my bias. (I assume you mean VWO where you have VPO.) If the "discretionary" means money to play with and doesn't count in your allocation at all, or it is cash reserve funds for unexpected day-to-day spending, then only 10% in bonds is a bit light - I'd go for 20%, which is what from various readings seems to lie on the efficient frontier.
2) easiest way to get into bonds is to buy something like XBB, the Canadian iShares bond fund, which is a mix of all maturities, and all issuers, government and corporate. That enables easy rebalancing with just the one holding. GICs are awkward for rebalancing since they are fixed locked-in amounts and though their return is guaranteed, in the long run they won't return as much as bonds.
3) the messy, complicated part to which there is no perfect solution and which has chewed up a lot of my time and effort over the past years. I refer to the management of a portfolio that includes both non-registered and registered accounts. One cannot move money back and forth. You don't want to lose the benefits of the RRSP by moving money out to the non-reg account to rebalance. Having VTI and the bond fund in the RRSP might work reasonably well since those holdings tend to work in opposite directions and what you have to sell in one, you might be able to buy in the other at rebalancing time. Expect though that at some point you might end up with a small holding of VTI or XBB in the non-registered account in order to achieve the rebalancing, or you might need to buy one of XIC, VPL, VWO in the RRSP.
To alleviate this hassle you might consider rebalancing only once a year and you might allow some leeway from the perfect/exact target allocation. When the trading commissions cost more than 1% of the rebalancing amount I don't bother. Your plan is good in that you have few holdings overall, which should make the rebalancing exercise easier.
4) you might be interested to look at Million Dollar Journey's blog at
http://www.milliondollarjourney.com/interactive-brokers-review.htm for his review of Interactive brokers. It seems that they charge you 10US$ per month whether you trade or not so if you adopt a passive, index investing strategy it might be better to look at other brokers, which he also conveniently reviews in another posting at http://www.milliondollarjourney.com/review-canadian-discount-brokerages.htm
The worst case is that you would trade every holding once per rebalancing so you could compare your annual costs for different brokers that way.
Another element to consider is the foreign currency risk, especially that of the US$. For 30 years the C$ went lower and lower against the US$ and that boosted returns for investments like VTI - if the US market went up 8% and the US$ went up 2% then in Canadian dollars the net was plus 10%. This force is working the other way right now as the C$ is rising vs the US$. How long will it continue? No one knows. There are research studies that maintain that a Canadian investor benefits from exposure to the swings of foreign currency, acting like another asset class which reduces volatility and increases returns. Given your presumed long investment time horizon at your age, this is less of a factor than for me - I've decided to buy protection from the currency shifts by placing a good chunk of my US holdings into the XSP (S&P500) and XSU (Russell 2000) funds which are hedged to Canadian dollars (i.e. the fund managers wash out future C$ vs US$ changes by buying C$ futures, the cost of which is added to fund expenses and reduces overall returns of course). In your situation I'd likely take the chance with VTI alone, but I don't think there is a hard best answer.
Good luck
Outroupistache,
Thanks very much for your detailed reply. I will take a closer looks at bonds and increasing my percentage (don't think I've been around long enought to appreciate there value in a portfolio) as well as look at the Million Dollar Journey's broker's blog. Really appreciate your valuable input.
C
Hi - You must be pretty cool.You have got to be the only other person I know of who has read "the Visual Display of Quantitative Information". Great book - a classic on visualizing data.
Seriously, though, your spreadsheet is great. When I exported it in .xls, I got a message about circular references. I'll check and let you know.
I can't find enough information about ETFs. I have a complete ETF portfolio but find little info to help me choose. Biggest problems, I think, are correlation among ETFs and the overlapping of individual holdings.
Thanks
Al Brockman
PS - Any ideas about how to update prices directly in Excel?
Post a Comment