Currency cost base tracking, borrowing and taxes

Asset allocation, risk, diversification and rebalancing. Pros/cons of hiring a financial advisor. Seeking advice on your portfolio?
User avatar
newguy
Veteran Contributor
Veteran Contributor
Posts: 8088
Joined: 10 May 2009 18:24
Location: Montreal

Re: Currency cost base tracking, borrowing and taxes

Post by newguy »

peter wrote:They do report interest paid on the annual activity statement (not a T slip), e.g. 'Broker Interest Paid' and 'Interest Accruals'. They also appear to report forex trades, under 'Trades'
You're right, I meant to say they don't report it on t-slips. If you're not going to check for mistakes or report foreign currency profits/losses or keep track of your cost base, then I agree, you can just use the annual statement.

newguy
travesty
Contributor
Contributor
Posts: 690
Joined: 07 Feb 2007 06:13
Location: Vancouver

Re: Currency cost base tracking, borrowing and taxes

Post by travesty »

newguy wrote:
Norbert Schlenker wrote: As for newguy's "thousands of currency transactions", he has my sympathies. ;)
The good thing is IB has a file called executions.txt that I wrote an excel macro to import. Most of the transactions are futures profs/losses and not exactly currency trades, but they still have to be calculated.

newguy
Is this executions.txt file generated by the "Trade Confirms Flex"? If so, how did you configure your query?
travesty
Contributor
Contributor
Posts: 690
Joined: 07 Feb 2007 06:13
Location: Vancouver

Re: Currency cost base tracking, borrowing and taxes

Post by travesty »

Hi Newguy,

Thanks for the spreadsheet. I am just starting to take a look at it now.

I'm still not sure I understand your EOY "valuation" entry. As I understand it, this is a fake entry, which essentially marks to market all your foreign currency holdings. So in a very simple case where you bought 100 USD for 100 CAD (1.00 exchange rate) at some point in the year, and then 1 CAD is worth 0.80 USD on Dec 31st, you'd have a mark to market entry for 125 CAD (reflecting the new value of 100 USD) and declare a $25 gain.

As I understand it, however, the CRA does not allow this mark-to-market technique - gains and losses are only realized when actual disposal takes place. So either I'm misunderstanding what you are doing here, misunderstanding what the CRA wants, or you believe this technique will be accepted in practice even if technically not compliant.
User avatar
newguy
Veteran Contributor
Veteran Contributor
Posts: 8088
Joined: 10 May 2009 18:24
Location: Montreal

Re: Currency cost base tracking, borrowing and taxes

Post by newguy »

Travesty, everything you said is right. I'm stupid, but that's what I do. I have so many transactions that I make a file for each year. I bring forward my stock positions each year into the new file with the cost bases and marked with a v (usually 2-3 positions). Since I don't track the cost bases of currencies due to so many transactions, I just pretend buy or sell at the end of the year. It's wrong but I doubt CRA cares and this year I actually closed out all currency positions (mainly USD).

There is an easy way to bring forward the cost base properly. Right now if I select USD in the filter for security and highlight the 'book cdn' row, it totals in the status bar to $8755. I highlight the 'amount' column and I get $8259 (or look on my statement at USD cash). I could then carry these numbers forward on to next years sheet.

To do a p/l calculation without closing the posn -> 8259/.99 is about 8350 CAD which cost me 8755, so I've lost ~400 so far this year being short CAD (which is why I have the very small posn. right now).

I doubt this sheet would suit your purposes because it's made for my unique circumstances. I was just thinking you could read the macro to see how you could automate things for you.

newguy

add: executions.txt is made automatically by IB's software AFAIK.
travesty
Contributor
Contributor
Posts: 690
Joined: 07 Feb 2007 06:13
Location: Vancouver

Re: Currency cost base tracking, borrowing and taxes

Post by travesty »

Thanks for the clarification, newguy. I did indeed find the macro very useful for parsing the file and agree that your strategy is reasonable and unlikely to raise the ire of the CRA, given that you've probably already gone well above what the average person would do in terms of accuracy. In any case, the EOY mark to market is probably a disadvantage overall for the tax payer, since it removes the ability to defer gains (while losses can already be crystallized on demand by selling anyway), which is another reason they are unlikely to care.

Where do you actually get the executions.txt file though? There are many options in IB account management, but I can't find one that gives exactly a file called executions.txt, without setting it up myself in either the "trade confirms flex" or "activity flex".
User avatar
newguy
Veteran Contributor
Veteran Contributor
Posts: 8088
Joined: 10 May 2009 18:24
Location: Montreal

Re: Currency cost base tracking, borrowing and taxes

Post by newguy »

travesty wrote:Where do you actually get the executions.txt file though?
Do you use the stand alone software? IOW, did you download and install TWS, or do you connect via web. I just found this file in c:\jts\dzwojkrn\executions.txt, not sure about that last folder, it could be my scrambled password for all I know :shock: .

Go here and search for "auto export" it has a few suggestions. I never did anything except look through my directories just for fun, but I do that with everything. I'll check monday when I can login :evil: .
http://www.interactivebrokers.com/en/so ... sguide.htm

newguy
travesty
Contributor
Contributor
Posts: 690
Joined: 07 Feb 2007 06:13
Location: Vancouver

Re: Currency cost base tracking, borrowing and taxes

Post by travesty »

newguy wrote:
travesty wrote:Where do you actually get the executions.txt file though?
Do you use the stand alone software? IOW, did you download and install TWS, or do you connect via web. I just found this file in c:\jts\dzwojkrn\executions.txt, not sure about that last folder, it could be my scrambled password for all I know :shock: .

Go here and search for "auto export" it has a few suggestions. I never did anything except look through my directories just for fun, but I do that with everything. I'll check monday when I can login :evil: .
http://www.interactivebrokers.com/en/so ... sguide.htm

newguy
Huh - yeah, I use TWS. I didn't realize that it was actually generating this file - although I had seen that path hard-coded into your macro, but it never occurred to me that it was created by TWS. I found it on my machine and it contains a bunch of stuff. Still, I think its safer to use the generated stuff from "Account Management" - such as trade flex, which offers the same kind of file, and covers all the machines you may have used (e.g, you might use TWS on more than one machine, or use the web interface, or the iPhone app or whatever), and offers more than just executions (e.g., interest payments, dividends).
User avatar
newguy
Veteran Contributor
Veteran Contributor
Posts: 8088
Joined: 10 May 2009 18:24
Location: Montreal

Re: Currency cost base tracking, borrowing and taxes

Post by newguy »

travesty wrote:Still, I think its safer to use the generated stuff from "Account Management" - such as trade flex, which offers the same kind of file, and covers all the machines you may have used (e.g, you might use TWS on more than one machine, or use the web interface, or the iPhone app or whatever), and offers more than just executions (e.g., interest payments, dividends).
Sounds good, but you're in for lots of programming.

newguy
travesty
Contributor
Contributor
Posts: 690
Joined: 07 Feb 2007 06:13
Location: Vancouver

Re: Currency cost base tracking, borrowing and taxes

Post by travesty »

No programming required - you just configure your flex report in terms of what columns you want, what format it should be in (text or XML), what the separator characters should be, etc then run it. You can get output pretty much identical to executuions.txt.
pmj
Veteran Contributor
Veteran Contributor
Posts: 3412
Joined: 27 Feb 2005 18:15
Location: Ottawa

Re: Currency cost base tracking, borrowing and taxes

Post by pmj »

I have a foreign currency bank account that has experienced a capital loss when viewed in C$ :( .

I'm reasonably clear on the concepts of acquisition costs and capital losses on dispositions / purchases from that account - convert transactions into C$ at relevant dates, etc.

What I'm not sure about is whether a transfer from this account to another same-currency account is a disposition - which would trigger a loss, followed by a purchase at a lower cost-base? Or is this a transaction to which superficial-loss rules apply - which would imply that it couldn't be reported as a capital loss at line 174? I see that question has already been raised - but no answer....

CRA's IT387 Meaning of Identical Properties doesn't specifically address either cash or cash in a bank account - but I'd think that all cash of a given currency would be identical property?

Which then leads to the purchase of same-currency shares (which would also require a transfer from the chequing account to a brokerage cash account)? Are shares sufficiently different from cash that they are not identical property? Here my guess would be that shares are not identical property - and thus the loss in the cash account should be recorded at the purchase date of the shares?
Peter

Patrick Hutber: Improvement means deterioration
User avatar
DavidR
Veteran Contributor
Veteran Contributor
Posts: 1937
Joined: 30 Oct 2005 08:33
Location: Toronto

Re: Currency cost base tracking, borrowing and taxes

Post by DavidR »

I agree with you that US cash on deposit at a bank is identical to US cash on deposit at a brokerage firm. So transfers would not trigger a disposition of the US cash.

I also agree with you that when the US cash is used to purchase something (including US securities), then there is a disposition to report.
pmj
Veteran Contributor
Veteran Contributor
Posts: 3412
Joined: 27 Feb 2005 18:15
Location: Ottawa

Re: Currency cost base tracking, borrowing and taxes

Post by pmj »

Thank you, DavidR. I've been thinking about this problem for some time - and I only just realized that the stock purchase would be a disposition - hence the two stages of my question.

It happens that the cash is in the UK - and the challenges of investing in the UK as a non-resident are significant - very few brokers will take me on :(. Now that I understand the application of the capital loss rules, it's pretty well a wash between leaving the cash in the UK, and triggering a loss when buying shares on the UK market - or bringing it here and triggering a loss on conversion. More thought required.....
Peter

Patrick Hutber: Improvement means deterioration
Post Reply