Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Quick Links
Cost Basis Not Calculating Correctly
After migrating to v2 of the program, the Cost Basis of some Sell transactions is calculating as +$10,000,000 instead of the much much much smaller amount that it should be. I have tried downloading the newest version of the workbook from the website just now and had the same result.
Comments
What happened - there is technical limitation in PowerPivot - it does not support recursive calculation. To go around this in v1.0 I moved CB calculation into Excel - that worked but at the cost of enlarged Excel data file (double size) and model complexity increase. In version 2.0 I moved calculation into PowerPivot, but because of limitations I was able to put CB calculation just up to 5 "partial sales".
In your case you will have to manually specify Cost Basis for these transactions.
How badly this change is affecting you? Can you live with current limitations? I was expecting that very few (if any at all) users would be affected by this limitation, but it is possible that I was wrong.
Currently code to calculate 5 "partial sales" has 70 lines of code. To add 6th "partial sales" code would become 140 lines, to add 7th, code would become 280lines, etc.
What is maximum Trans[Sell No] value with your data? You can see it by adding this this field into TransInfo reports "Report Filter" area and then checking for list of values there.
To workaround the issue, I just added the calculation back into the srcTrans table and whenever dealing with the fund in question, set CostBasisOverride to the Calculated Column.
The issue arises because in one account (housing employer funded RRSP), some funds are redeemed every month to pay for the account fees. In all self managed accounts, I do not have this issue.
I am glad you figured out how to go around this limitation!
I have sent you for some calculation problem in my excel regarding cost basis regarding PFC.NS.
You have advised for alternate way to overcome issue. However, in older version there was no problem of 10,000,000.
Sir, if you could focused in this version, it would be more beneficial to all.
Could you be more specific about the workaround you discovered?
I am a swing trader who does partial sales of my holdings quite frequently. I have 1 holding which has already reached this limit and a few other holdings which will reach the 5 partial sale threshold within the next few weeks.
Thank You!
This workaround is already implemented in v2.3. Basically limitation was removed if your reporting currency is matching account currency. Does that help you, or you have different currencies?
Thank you for letting me know. I am still using version 2.0.0 because I have made some modifications to the document. I will update it now.