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

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.

## Comments

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 %.

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!

Thanks again!

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.

Thanks yet again!

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.

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

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.

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!

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.

it is clear now

thx for your feedback

Roland

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