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.

This article has 28 comments

  1. 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.

  2. @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.

  3. @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.

  4. 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.

  5. 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

  6. “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?

  7. Pingback: Our first Friday wrap up! | A Leveraged Life

  8. Pingback: Flipping Houses, Electric Cars, Market Roll Over and More | Million Dollar Journey

  9. Pingback: Wander Reading #27 | Canadian Dream: Free at 45

  10. Pingback: » Weekly blog roundup Canadian Business Blogs | Advice on Investment in Canada, Stock Market, Small Businesses Opportunities

  11. 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.

  12. @CC: Thanks for the spreadsheet!
    I looked at the US Stock tab, it seems it makes an assumption that the Comission is charged in CAD. If I recall correctly some brokers charge the Comission in USD in case of US stock buys/sells. I suppose I should adjust the calculation in my case?

  13. OOps. my comment is only regarding line #2. Starting from line #3 the formula does account for Comission charged in USD.

  14. @G: Good catch. Thanks for pointing out the error. I’ve fixed it now and uploaded the edited version.

  15. How would you best show a stock split, with this format of your spreadsheet, and thanks for this workmanship, it has sure helped me out good job.

  16. Potential enhancement for the ACB spreadsheet…adding an option for “Re-invested Distribution” that would increase your ACB as discussed here:

    http://www.financialwebring.org/forum/viewtopic.php?f=33&t=112877&start=25#p418107

  17. @Canadian Capitalist: Ericj is right, if you are investing with ETFs, you need to include the re-invested distributions in the calculation of ACB.

    See this post:
    http://canadianfinancialdiy.blogspot.ca/2007/03/adjusted-cost-base-for-etfs-and-mutual.html

  18. Investing in ETF’s and Mutual funds require more complexity that I would love to see added to the spreadsheet.

    The spreadsheet calculates Capital Gains only when the stock is sold, however with ETFs or Mutual Funds these Capital Gains occur often within the fund as the fund manager buys and sells stocks. An investor will receive T3/T5s indicating gains while he is holding the fund, and he must track this to determine his ACB. When this occurs, the taxes have to be paid in the year the gains occur but they also result in an increase in the ACB by that amount, unless there was also some Return of Capital in which case the amount of ROC decreases the ACB. Similarly, if the stock/ETF/fund declares a dividend that is re-invested, then that amount also increases the ACB — but again, you pay tax in the year it occurs even though you didn’t receive it. Even interest gained within the fund (say for money the fund manager has sitting while he waits to purchase stocks) must be taxed in the year they occur but will result in an increase in the ACB as long as it is not.

    It’s really important to track these gains as they occur within a fund, and updating your ACB, or else an investor will end up understating the value of the funds and will end up paying unnecessary capital gains when he sells (i.e. paying twice for the capital gains that occurred within the fund).

  19. hi,
    For US stocks, the CRA web site said the 2012 USD rate is 0.99958008 The Exchange Rate column only takes 2 digit after the decimal. How can I enter 0.99958008

    Thanks..Willy

  20. I found the answer. I edit the cell to increase the decimal places.

    Willy…

  21. I trade actively (more than a thousand transactions per year). Rather than track the ACB on each position (often held a week or two, not longer), can I not measure the gain on the overall portfolio during the year, and treat the gain as a capital gain (as if the portfolio were one stock)?

    It is clear how to calculate the ACB on the portfolio. If the portfolio ACB is $100,000, gains $5000, and you pay tax on that gain, the ACB increases by $5000 (to $105,000)

    If you spend $4000 and re-invest $1000, the ACB increases by $1000 to $101,000. Am I right so far?

    But what if you spend $10,000? The portfolio made $5000, you paid tax and spent the entire $5,000. No change to the ACB.

    But you also spent another $5,000. This is all return of capital; none of this money is taxable, right?

    Is the ACB also reduced by $5000 (to $95,000)?
    .

    • Canadian Capitalist

      @Toby: I’m afraid you have to track the ACB for each position. Also, you pay tax on gains only when you realize the gains by selling your positions. Here’s an example:

      You buy 100,000 shares of XYZ at $1.00 each. Your ACB on 100,000 shares is $1.
      XYZ goes up to $1.05. You sell 100,000 shares at $1.05 each. Your proceeds are $105,000.

      You spend $4,000 of your proceeds. You buy 1,000 shares of RST at $101.
      Now, you sell 100 shares of RST at $100. You have a loss of $100. ACB of RST remains at $101. You have $10,000 that you spend. You have 900 shares of RST left.

  22. After many years of buying mutual funds on a ongoing auto-purchase basis, I redeemed part of them last year. Can I use quarterly statements book value calculations issued just prior to the beginning of my redemption activity as a starting point for calculating/determing the ACB? I don’t have all of my purchase and re-investment details over the past many years (8 or more) but shouldn’t the Book Value also work? Thank you

  23. I started a TRP drip in my name. After a year I also started a drip joined account (with my husband) using one share from the drip in my name. Now I have 2 TRP drips (one in my name and one with my husband) and I am wondering how to calculate ACB. Your help is much appreciated.

  24. Hello,

    Thanks for all the great information and comments.

    My question is, if you have bought and sold a stock multiple times in the year and have kept track of the ACB using the spreadsheet, do you aggregate all sales for that stock into one disposition and ACB when you are filling out your taxes using software such as SimpleTax or do you need to do a separate disposition for each sale of that stock individually?

  25. Now how would you calculate this scenario in this sequence?

    BOT 1000 Shares of XYZ for $10
    BOT 1000 Shares of XYZ for $9
    SEL 1000 Shares of XYZ for $11
    BOT 1000 Shares of XYZ for $9

  26. Thanks you so very much! This little spreadsheet app was just the ticket for my needs!

Leave a comment

Your email address will not be published. Required fields are marked *

*