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.
