›

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

Quick Links

Categories

Incorrect Profit %???

nowaynoway
in Excel Workbook
How is Profit % column in Holdings tab calculated?
Unrlzd Cap Gain % calculation is correct at 49.74%, when I add in dividends this goes to ~52%. However, Profit % is liste as 147.24%???? Where does this number come from?

Thanks for helping me to fix my error.
«1

Comments

  • VidasMatelisVidasMatelis
    Profit % is calculated linking MONTHLY profit %. When you see unreasonable number, it is likely because end month holdings and profit amount does not make sense. Usualy this could happen when one month you sold shares and next month, when your holdings amount was 0, you received dividends. Please check for that. To fix, change last dividend date to symbol sale date.
    To investigate, I usually create report, that filters by symbol and then shows monthly profit %. Then I review transactions for the month with unreasonable profit %.
  • nowaynoway
    1) Why does profit % calculate based on monthly data?
    2) This position has never had any shares sold
    3) Looking into 2016 monthly data it is indeed incorrect. I confirmed the quote information in the quote file _Ticker_.txt is correct
    4) What could be causing the monthly capital gain to be incorrect? It is approximately 2x more than actual gain, but not exactly. Example 2040 vs 4409 for 1 month.

    Thanks again for your help!

  • nowaynoway
    Also, FYI I did not manually record the dividend receipt. This is not DRIP and dividends are sitting as cash. I used STOCK,,,,Y in psConfig file, perhaps this is my problem??
    Thanks again!
  • VidasMatelisVidasMatelis
    If you see incorrect cao gain, then issue us either with quotes data or maybe stock dplit, but you have not recorded split transaction? Check symbol qyotes file first and confirm it is ok for the month you have issues. If quotes ok, please confirm there was no split
  • nowaynoway
    Doh! I did not refresh the data. The 2016 monthly data looks correct now. However, my total profit is still very high at 147%.

    I am a newb with pivot tables, can you advise how to check other years for monthly report. How can I? create a new filter to obtain past 12 months but, for years before 2016?

    FYI the capital gain sum in the yearly tab for past 5 years is correct and matching the overall / sum capital in the holdings tab.

  • nowaynoway
    Also the profit % between yearly tab and holdings tab is different. Example, 2015 yearly profit does not equal holdings tab profit with 2015 filter displayed, any ideas on how this could be?

    Thanks yet again!
  • VidasMatelisVidasMatelis
    In yearly tab you see each years profit, in the holding tab you see total profit since you bought symbol UNTIL selected date - so different measures.
    Google how to create copy existing pivot table, then copy yearly pivot table to new worksheet, remove year, add month instead and you will see what month is causing problem.
  • nowaynoway
    Ok I will do that. To be clear, the only way the monthly data can be wrong is if the quote data is wrong, is that correct, or is there some other input into monthly data that I need to check?
  • VidasMatelisVidasMatelis
    There could be other issues that affect calculations. Please check monthly data first. I am on vacation till Thursday, if you still have any issues then, you can send me workbook and I will look into this in more depth.
  • rolandroland
    edited November 2017
    hello,
    I see also some strange figures in profit %. While profit itself seems fine, the % value ytd is totally wrong while monthly figures are ok. Here attached a sample file.
    otherwise the tool is really great
    thx for your support
    Roland
  • VidasMatelisVidasMatelis
    Roland,

    Thank you for sending this workbook. I am very happy when users create test pivot like you did to investigate issues. Every time I investigate issue, I do exactly like you did - I create separate pivot with data usually monthly and then I look into monthly results and then total results.
    As you said, in your report the results for each month are OK, but total values are "totally wrong".
    This is how Portfolio Slicer (PS) calculates Profit %:
    Profit % is calculated for each month and then these monthly results are geometrically linked. This way I am getting "Time-weighted profit".
    Here is where this method is described: https://russellinvestments.com/ca/funds/personal-rates-of-return
    For each month profit % is equal to: m(x) = (Dividends + Cap Gain) / Start Value.
    Then I calculate total Profit %: (1+m(1))(1+m(2))...(1+m(last))-1
    Note: in PowerPivot DAX there is no function that does multiplication of multiple values. This function exists in Excel (function name is Product), but it is not in DAX. So I have to use combination of EXP and LN to get the same behavior. Product=exp(sum(ln(value)))

    So if you accept that PS calculates monthly values correctly and then when you geometrically link these values, you will get Total Profit % exactly the same as PS calculated - in your case 9.87%. You can test this easily on your data by adding calculated column that is equal ProfitColumn + 1 and then doing PRODUCT() -1.

    So I believe that the issue is - you are expecting Profit % to be "simple Profit %", but PS shows to "Time-weighted profit%". I believe you see such a large difference between these 2 calculations because you have very large dividend amount on 2016-03: amount is 266 for stock that is valued in that month as 993.

    That is my explanation for this discrepency. Does that make sense? Should I introduce another measure, like Profit % simple, that would try to calculate simple profit %. But then how would I calculate value when lets say, in your example, in 2016-12 I buy 1000 additional shares of the same symbol.
  • nowaynoway
    Vidas,

    I am still having issues with profit %. I did clear out all quotes and re-download them just in case there was an issue with the quote data. Please find the file attached for your review. Appreciate your insight to advise where the 'error / incorrect data' lies. I do see this issue with multiple stocks. (TXN, BHI etc) Thanks very much!
  • VidasMatelisVidasMatelis
    edited November 2017
    Hi Noway,

    Lets focus on symbol TXN. In the Holdings worksheet Profit % for this symbol is 185.19%.
    I created new report in OneSymbolProfit% worksheet that shows monthly profit and then total profit.
    I attached image of that report - please first check monthly results and then in the next column I added Excel formula that manually calculates total Profit % (based on rules in my previous post) and results match exactly.
    Keep in mind that this symbol was bought over 10 transactions and some quantity was sold over 2 transactions. So profit % includes all buy/sales and dividends over the all months.

    I also attached workbook with your data and that new report.

    Now you said that you believe that Profit % for TXN is not right. Could you please clarify what value would you expect it to be? How did you come up with that number. Does monthly profit % value looks right? It is possible that I have bug in my calculations, but I need your help to figure out where.

  • rolandroland
    hi Vidas,
    it is clear now
    thx for your feedback
    Roland
  • rayhanrayhan
    edited November 2017
    Hi Vidas,

    Really appreciate the effort to build the workbook. I've just finished keying in all my transactions and I have a few problems that would require some help:

    1. Cash deposits are not reflected on dashboard tab
    2. Holdings tab capital gains does'nt seem to reflect correctly (or is it because the position was open until the next year)
    3. Yearly returns seem to have major errors

    I have attached the excel for reference. Thank you!

    Best regards,
    Ray
Sign In or Register to comment.