Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Quick Links
Capital Gain - FX issue
Hi,
I believe I may have found a potential calculation error.
The issue arises if you buy a USD denominated stock in a CAD account. When calculating capital gain in USD, PS takes the initial value of the position and divides by the foreign exchange rate on the day of purchase - but this may not be the FX rate you got when you bought the stock. Therefore, the capital gain will be different and the difference will be determined by the variance of the exchange rate from BoC site and the exchange rate your brokerage offered you.
I bought VOO in my RRSP and have an incorrect capital gain when viewing in USD or Original Currency. In fact, when viewed in *Original currency, the capital gain is way off and not even the right sign.
Has anybody noticed this?
I believe I may have found a potential calculation error.
The issue arises if you buy a USD denominated stock in a CAD account. When calculating capital gain in USD, PS takes the initial value of the position and divides by the foreign exchange rate on the day of purchase - but this may not be the FX rate you got when you bought the stock. Therefore, the capital gain will be different and the difference will be determined by the variance of the exchange rate from BoC site and the exchange rate your brokerage offered you.
I bought VOO in my RRSP and have an incorrect capital gain when viewing in USD or Original Currency. In fact, when viewed in *Original currency, the capital gain is way off and not even the right sign.
Has anybody noticed this?
Comments
Every field value that you enter for "Buy" transaction where this problem is observed.
I have sent you an email with the workbook attached. I tried to explain the issue as I understand it as best I can.
Thanks,
You entered the transaction for CAD account for USD symbol. You specified Price in USD and then specified ExchRate as 1.40339901.
At the time Exch rate from USD to CAD was 1.38044 - that is the number is your PowerPivot model for 2020-03-13 date. If you do not want to use this number for your calculations, you should specify a value in ExchRateRpt2Override column and the value should be 0.712555726 (=1/1.40339901). There is a big discrepancy for your used exchange rate: 1.40339901 vs bank posted exchange rate of 1.38044, so that is why it is a good idea to override default rate.