What are some necessary columns for spreadsheets for investments taking particularly for the purposes of tax reporting?

I will be hiring a cpa next year (any suggestions for a specific firm or person to hire? I'm in Ontario) for my taxes so I want to be prepared with an entire spreadsheet. So I know I'll need date, ticker, type of security (shares, call, put), quantity, average cost base, us to cad currency (all my investments are us tickers so far) , what else will I need?

Any useful links, videos, articles and even entire courses and books will be helpful for this if you have any suggestions at all. Googling this info doesn't give good or specific enough results

Posted Jul 28, 2021, 4:50 AM UTC

34
gamefixated•

I found my CPA on kijiji. Smaller firms (I.e. less expensive) advertise there. Stocks and options aren't complicated from a tax perspective (unlike flow through shares for example).

Some brokers give you most of the tax information in the form of T5008s. TD at least does the conversion to CDN for you. With IBKR you can download all transactions in a spreadsheet, then simply use Bank of Canada exchange rates (download the entire years daily rate and use a vlookup function). Just do the conversion at year end to save time.

What is missing from the T5008 is options (bought call or put) that expired worthless since there are no proceeds, so track these for sure. Some people claim T5008 aren't accurate, but I have not seen any mistakes that require manual tracking. But I still keep a spreadsheet.

Don't forget about commissions.

Positions open across year end require special attention. Sold calls and puts for example will be declared as a capital gain in the tax year, and adjusted in the next year if that is when they expire.

2
ItsNotMeFromBeforeOP•

What is flow through shares?

How would the vlookup function help with the exchange rate? Wouldn't a simple Ctrl f be more suitable for this to just find the specific date? You said to do conversion at years end but I was under the impression that you have to calculate EoD rate for each day that you've purchased or bought American securities/options?

As for commissions and the expired purchased options, how do I track them? Will they count as capital loss or expenses or what exactly? I feel like purchased options should certainly be separated from commissions though, right?

1
gamefixated•

Flow through shares are a tax shelter. I did a write up on them a while ago https://redd.it/m3m4t4

If all your purchases/sales are in USD, you can use the vlookup as follows. On a separate sheet, download the entire years worth of daily exchange rates from BoC website on December 31. This can be done in excel format. Then on your main sheet, have a column for the CDN purchase price, sell price, and commissions. Use the vlookup function with the purchase date as a parameter (search on column on the separate sheet) to return the exchange rate on that date. Repeat for sales date.

Commissions paid to open a position can be subtracted off the cost base. Commissions paid to close a position can be subtracted off the proceeds.

Yes, expired worthless options are capital losses and definitely should be reported.

1
Meneenz•

https://www.adjustedcostbase.ca/

All of the tools and information you'll need, for free.

2
[deleted]•

[deleted]

1