- Comments (12)
- Text Size: Down Up
moneysense.ca, 24/08/10
Free ACB & Capital Gains Tracker in Excel
In response to yesterday’s post, Reader Kevin asked me to share the Excel spreadsheet that I use for tracking adjusted cost base (ACB) and capital gains. Since I mostly make buy transactions and rarely do a sell transaction these days, my set up is rather simple. I simply input the date, type of transaction (buy, sell or ROC), no. of shares, price / share and commission and compute the ACB and share balance. Each stock goes into a separate sheet and US stocks have an additional column for the exchange rate.
If you haven’t calculated ACB before, it is quite simple. When you make a buy transaction, you should add the total value of the current transaction to the previous ACB. For instance, let’s say you own 300 shares of XYZ Corp. at an ACB of $3,000. If you buy another 100 shares of XYZ Corp. for a total of $1,500, you’ll own 400 shares of XYZ Corp. at an ACB of $4,500.
When you make a sell transaction, your ACB decreases by the number of shares sold times the average ACB per share. For instance, if you had instead sold 100 shares of XYZ Corp., you’ll now own 200 shares at an ACB of $2,000. You’ll also have a capital gains of $500.
Return of Capital simply reduces the ACB by the number of shares times ROC per share. For instance, if XYZ Corp. had a return of capital of $1 per share, your ACB would be $2,700 ($3,000 – 300 shares * $1). If you need more detailed explanation of ACB, check out Gummy Stuff’s Adjusted Cost Base for Canadians. DRIP Primer also has a handy explanation of ACBs for those who reinvest their dividends.
To make Kevin happy, I cleaned up my Excel spreadsheet and added some code to automatically handle buy, sell and Return Of Capital transactions. All you need to do to track your ACB, is copy the cells into a new sheet and enter the Date, Transaction, # Shares, Price / Share, Exchange rate (if applicable) and Commission columns. The spreadsheet should calculate ACB, Share Balance and Capital Gains columns. Just copy and paste the previous row to create a new transaction. You can download the spreadsheet here. Comments are welcome.
moneysense.ca, 24/08/10
There is no limit to how complicated these stock transaction spreadsheets can become. For example, I used to have to track stock options and deferred stock option gains. The critical thing is to learn enough to be able to create your own spreadsheet. Start with CC’s spreadsheet, but be prepared to know how to modify it to suit your own situation.
@Michael: I agree that this is just a starting point. It can become a lot fancier depending on how fancy one wants to make it. I’ve been meaning to clean up my spreadsheet for a long time because it is better to get it done right once instead of doing it manually and increasing the chances of errors.
@CC: I agree that automation is best, but it is hard to anticipate all possible events. There are stock splits, reverse splits, share distributions, returns of capital, spinoff of shares of other stocks (such as when BCE gave Nortel shares to its shareholders), and other even more exciting possible events. The latest update to stock option gain deferral rules forced me to add a new column to my spreadsheet. So, while I do my best to automate, I find it necessary to update my spreadsheet periodically.
The one I like is that the issuer doesn’t tell you how much of your distribution is Return of Capital (ROC) until tax time… So, you don’t actually know the full tax implications of your trading until April 30 or whatever.
Like you, I try to make my life simple by making sure that I never sell the investments (mostly REITs) whose distributions contain a lot of ROC, unless they’re inside my RSP or whatever.
DRIPers can use CC’s spreadsheet, by simply entering a reinvested dividend as a ‘buy’.
DRIPers still not getting Adjusted Cost Base after reading CC’s or the Gummy’s explanation, they can check out my attempt at explaining it here:
http://www.dripprimer.ca/calculate-acb
“When you make a sell transaction, your ACB decreases by the number of shares sold times the average ACB per share.”
Thats not strictly correct is it?
“you own 300 shares of XYZ Corp. at an ACB of $3,000. If you buy another 100 shares of XYZ Corp. for a total of $1,500, you’ll own 400 shares of XYZ Corp. at an ACB of $4,500.”
When I go to sell, you don’t claim capital gains on the average ACB, but base it on the original purchase price for what I’m selling. So if I decide a year later to sell 100 shares of XYZ, I can elect to dispose of the 100 at $1500, or 100 at $1000 (or even a different mix if you are like pain
. Depending on how much extra gain/loss you want to claim, the remaining 300 shares ACB will then be either $3000 or $3500.
So don’t you have to treat the sells exactly like buys, only with negative quantity with original cost of your *chosen* units, and base you gain/loss from that?
@highlander: That’s not correct. When you sell, CRA requires you to claim capital gains or losses based on the ACB. There is no leeway here. You can’t choose FIFO, LIFO or whichever you find convenient. If you think that’s not how CRA treats capital gains and losses, can you point me to the relevant source?
[...] to keep track of Adjusted cost base for investments in taxable accounts was difficult, check out this Excel spreadsheet from Canadian [...]
[...] Free ACB & Capital Gains Tracker in Excel @ Canadian Capitalist [...]
[...] know how much of a pain in the ass that can be. Well to your rescue came the Canadian Capitalist with his spreadsheet. Go get a copy if you need [...]
[...] Canadian Capitalist offers a free ACB and capital gains tracker. [...]
I was wondering if this tool could be enhanced to support journaling from an exchange to another and still keep tracking the cost back to the original currency the securities were purchased. Journaling complicates the tracking of the ACB and also the performance of a holding. Also, it is my understaing that if a security is interlisted, then driving currency is the one the headoffice is based on, e.g. TD/TSX fluctuates based on sell/buy activity; TD/US fluctuates based on sell/buy activity AND currency exchange; alternatively, lets say GM (when coming back to public), since the head office is in the US, will behave in reverse.