Non-Registered Investment Tracking Spreadsheet

September 8th, 2015 by Potato

In a non-registered account you will at some point have to pay tax on any capital gains, and you have to report that amount yourself. You’ll need some method to properly* track your cost base so that when you sell you know how much of a gain (or loss) to claim on your taxes. [TL;DR — click here to download the spreadsheet]

I often get asked whether tracking ACB yourself is really necessary. Brokerages and mutual fund dealers will track your book value for you, and most of the time they’re accurate. So you could save yourself a fair bit of effort by just relying on their calculation for tax purposes, especially if all of your accounts are in one place. However, if you have holdings of the same ETF/stock/fund at different brokers (e.g., if I had an account at TD Waterhouse holding VDU and one at Questrade holding VDU) then they wouldn’t talk to each other and there would be no option other than to track it myself. They also don’t seem to catch superficial losses. The bigger issue though is that the brokers sometimes make mistakes in the book value calculations, and it is ultimately your responsibility to report correctly to the CRA.

So the only proper advice I can give is to carefully track it yourself to double-check your broker/mutual fund dealer because the onus on being correct will fall on your shoulders. But that said, I do personally know a few people who do not track it themselves and who come out fine (either because the broker/dealer gives them correct information, or because even when there are errors they don’t get audited) — in the real world it’s very tempting (and understandable) to take the free tracking offered. With mutual funds there’s one less step for information to go wrong (at least if you’re buying funds from the issuing company, like TD e-series at TD), so it’s even more likely to be correct, but you’ll never know if you’re the one unlucky customer with a bad book value calculation until you get audited. And of course, the errors they’re making could be costing you money. If you do let the company track for you, at least be sure to hold on to the statements you’ll need to re-do the calculation if it comes to it.

Tracking it is not all that hard a mathematical exercise, the tricky part comes in being careful to put everything on the right side of the ledger and to follow-through each year. There can be a lot of transactions to put in there, including annoying “phantom” distributions that change your cost base but didn’t actually show up in your brokerage statement. There are a number of tools out there to try to help you with your ACB, including a web-based calculator and spreadsheets from several sources, like this classic (if sparse) one from the old Canadian Capitalist blog.

I myself use a very barebones spreadsheet similar to CC’s, but it’s not a great model for others to use because it depends on me knowing what it is I’m doing with the ACB calculation to create each new row as needed.

So I’ve created a new template that I hope is better suited to the target audience for the Value of Simple (click here to get the sheet). This sheet assumes that you’ll have a fairly typical experience of buying/DRIPing many shares/units over time, and only occasionally selling. So rather than save space and intersperse all the transactions as they happen, it’s set up to go with your workflow. I assume that you’ll go through various modes. First you’re buying and holding, then it summarizes your ACB for a sale with all the adjustments from RoC and reinvested capital gain distributions, then you make a sale (or series of sales), and then get back into buying mode. Then each of your funds gets its own tab.

As with many ACB trackers, the superficial loss rule can throw you for a loop. It’s hard to catch everything that can trigger a superficial loss, so there is no automatic check for it so you won’t have a false sense of security — you’ll have to catch those situations yourself (though there is a sample of that case so you can see how to adjust your cost base for when it does happen).

* – I often stress the importance of tracking this yourself. That’s because it’s so tempting to not track it properly and independently and rely on the “book value” or “cost” listed on your brokerage statement. Most of the time this will be correct and you could have saved some record-keeping effort. However, sometimes it won’t be, and then you’ll be mis-reporting to the CRA. It’s analogous to tracking your TFSA contributions yourself: the CRA online tool or phone reps may be able to accurately tell you how much room you have left, but in the rare case where that’s wrong the penalties will still fall in your lap. So I’ll try my best to tell you how to track and report properly, and make it as easy as possible; if you choose not to and get audited, at least you’ll know what you did wrong!

2 Responses to “Non-Registered Investment Tracking Spreadsheet”

  1. Potato Says:

    Some extra tips: if you add a new block below the first block, be sure to shade out the first block so you don’t accidentally add new information in the wrong spot.

    There are some things you’ll still have to handle manually, like splits.

  2. Weekend Reading – Facelifts, investment tracking, $20 oil, stocks and more Says:

    […] Holy Potato wrote about non-registered investment tracking. […]