investing

How to Use The Capital Gains Worksheet

March 16th, 2015

Save time and make investing easy

Investing can be so rewarding, but also time consuming and stressful. Passiv is here to save you time and make investing easy.

Get Started

This blog post was originally published on the MoneyGeek.ca blog by Jin Choi. The website no longer exists, but Jin has graciously allowed us to re-publish his research for the benefit of future investors forever.

I found that one of the most frustrating aspects of preparing my tax returns involves calculating capital gains. For those of you who dont know, capital gains refers to the profit you make from buying an investment low and selling it high. You can find more information about it in my free book.

Introducing The Capital Gains Worksheet

Calculating capital gains is painful because unlike dividends and interest incomes, brokers dont supply us with tax slips that summarize our gains and losses. Instead, they provide us with a history of past trades and we are expected to calculate the gains on our own.

If you buy an investment only once and sell it only once, and if the investment happens to be denominated in Canadian dollars, calculating the capital gains is simple. In this case, to arrive at your capital gains, you just need to take the dollar amount you received from selling the investment and subtract the amount that you paid to acquire it.

However, the calculation becomes more complicated when you buy the same investment more than once, and sell only a part of it. For example, lets say you initially bought 100 shares of XYZ at $5 a share, then bought 100 additional shares at $7 a share, and then sold 50 shares at $9 a share. In this case, you have to calculate capital gains on those 50 shares, but heres the question: How much did you pay for those shares? Its not immediately clear.

The amount you paid for each share is called the Adjusted Cost Base (ACB). Although the CRA is clear about how to calculate ACB in situations like these, I still find it tedious to have to set up spreadsheets in order to calculate these figures.

Calculating the ACB gets more tedious if the investment happens to be denominated in a foreign currency, such as the U.S. dollar. If you bought and sold a U.S. dollar denominated investment, you have to convert prices into Canadian dollars before calculating the ACB. This means you have to track down the foreign exchange rate on the date that you bought and sold the investments.

I have looked for a simple tool that would help me calculate capital gains easier, but I couldnt find a good one. So, naturally, I made one and I named it the Capital Gains Worksheet. The worksheet is available through this link or by going to the Resources menu.

The worksheet is available to everyone for free. However, only paid members have the ability to save transactions.

Ive specifically designed the worksheet to help you calculate the ACB for U.S. and Canadian stocks, bonds and ETFs. That means, as of right now, the worksheet doesnt have the capability to calculate the ACB for properties (because it cant handle capital gains reserves), nor does it have the capability to calculate the ACB of foreign currency denominated stocks other than those in U.S. dollars.

Furthermore, the worksheet currently doesnt have the ability to handle certain stock events, most notably mergers. For example, if you invested in XYZ shares and if XYZ got acquired by ABC, thats a merger event. Ive intentionally left out mergers because there is no one tax rule that applies to all mergers. In other words, each merger is treated differently by the CRA. However, mergers are relatively rare events and I believe most people will be able to use the worksheet to calculate their capital gains in full.

Lastly, please keep in mind that you if youre a day trader, you should report any gains or losses as trading income, rather than capital gains. Day traders are those who trade in and out of investments very frequently, often within the same day. With that in mind, let me explain how to use the worksheet.

How To Use The Worksheet

You can record five different types of actions in the Capital Gains Worksheet - buy, sell, split, name change, and return of capital. Let me go through each one.

Buy and sell actions should be self-explanatory for the most part. Security refers to the stock/ETF/bond that you bought or sold. The name of the security is case sensitive, so if you want to specify the same security in a different row, you should make sure that the names match exactly.

Note that the majority of the time, the number in the total amount column wont match the number of shares multiplied by the price per share. For example, if the number of shares bought is 100, and if the price per share is $5, you might expect the total amount to be $500. However, the actual total amount might be something like $505. This is because the actual total amount includes the commission, which in this case is $5. Ive deliberately designed the worksheet this way as this matches the format of the trading summary provided by Questrade.

Split/Consolidation action refers to an event where a company decides to either divide their current number of shares into a greater number of shares, or consolidate multiple shares into one share. For example, if you own 100 shares of XYZ and if XYZ decides to do a 2:1 split, youll own 200 shares after the split. Alternatively, if XYZ decides to do a 1:4 consolidation, youll own 25 shares after the consolidation. The worksheet will adjust the ACB of the stock according to the split or consolidation.

Note that only the ratio of new shares to old shares count. For example, it doesnt matter whether you specify 200 in the new shares column and 100 in the old shares column, or whether you specify 2 in the new shares column and 1 in the old shares column. In either case, the stock will be split on the ratio of 2 to 1.

Name Change action should be pretty self explanatory. For example, if XYZ changes its name to ABC, you can specify XYZ as the old name and ABC as the new name.

Return of Capital action usually only applies when you own Real Estate Investment Trusts (REITs). If you specify the total amount you received in the form of return of capital, the worksheet will prorate the amount according to the number of stocks you own.

Once you fill in the rows, you can click on the Summarize button to get a pdf summary of your capital gains. Clicking on the button will open the pdf in a new tab, so non-members dont have to worry about losing their work when they press the button.

Thats all there is to the worksheet. If you have any feedback, please let me know using the blog comments below. Ill try to accommodate the suggestions as best as I can.

This blog post was originally published on the MoneyGeek.ca blog by Jin Choi. The website no longer exists, but Jin has graciously allowed us to re-publish his research for the benefit of future investors forever.

Get all the insider financial info