For any account with a nonzero share balance, it appears the Realized Gain column of the Advanced Portfolio report is always based on money in and money out, or in effect the Average Cost Basis. My concern is that this happens even if I’ve entered the capital gains splits as described in the GnuCash manual. So if I specify FIFO orders with my broker and calculate the correct gain to put in the sale splits, the Realized Gain with the broker/register does not match the report, and therefore the Unrealized Gain is no longer useful or relevant in any way. While this report does some things very well, I think the Realized Gain column needs to be refactored or relabeled to be less confusing.
Hi Robert The Advanced Portfolio Report was written by someone who left a long time ago and did not completely document the report assumptions. It would really help us to bugfix if we can have a minimal-case test file, along with some documentation about the expected value. We will also take the opportunity to clean up the report, and understand the report assumptions so that documentation can be improved. Your bug report and contributions very appreciated!
Created attachment 373172 [details] Capital Gain Test Case Testcase Description 1 share purchased at $100, followed by 1 more share purchased at $200. Stock appreciates to $300 per share and the owner sells 1 share on a FIFO basis. The owner has a $100 cost basis in the sale, and a $200 cost basis in the remaining stock. The sale realized a $200 capital gain ($300 - $100) which was entered manually according to Table 9.2 on this page... https://www.gnucash.org/docs/v3/C/gnucash-guide/invest-sell1.html Actual Results In the Reports Menu, click on Assets & Liabilities and then Advanced Portfolio. This report calculates the Basis in the remaining 1 share as: 1.00 Shares Remaining * $300.00 Money In / 2 Shares Purchased = $150.00. Realized Gain seems to be: $300.00 Price - (1 shares sold * $300.00 Money In / 2 Shares Purchased) = $150.00. Unrealized Gain seems to be: $300.00 Value - $150.00 Realized Gain = $150.00. I observed these results both with and without the capital gain split being added to the sale transaction. Expected Results Basis: $300.00 Money In - $300.00 Money Out + $200.00 Capital Gain = $200.00. Realized Gain: $200.00 Capital Gain (total of sales splits). Unrealized Gain: $300.00 Value - $200.00 Realized Gain = $100.00.
I suggest you go to 'Edit > Report Options' and change the 'Basis Calculation Method' to 'FIFO'...
The problem is I'm not actually using FIFO in my real accounts. You wanted a "minimal-case test" 😂
Well we want minimal test case that reproduces your issue...
Created attachment 373176 [details] Capital Gain Test Case 2 Testcase Description 1 share purchased at $100, 1 share purchased at $200, and 1 share purchased at $400. Stock price changes to $300 per share and the owner sells the 1 share that originally cost $200. The owner has a $200 cost basis in the sale, and a $500 cost basis in the remaining stock. The sale realized a $100 capital gain ($300 - $200) which was entered manually according to Table 9.2 in the manual. Actual Results In the Reports Menu, click on Assets & Liabilities and then Advanced Portfolio. This report calculates the Basis in the remaining 2 shares as: 2.00 Shares Remaining * $700.00 Money In / 3 Shares Purchased = $466.67. Realized Gain seems to be: $300.00 Money Out - (1 shares sold * $700.00 Money In / 3 Shares Purchased) = $66.67. Unrealized Gain seems to be: $600.00 Value - $466.67 Basis = $133.33. I observed these results both with and without the capital gain split being added to the sale transaction. Expected Results Basis: $700.00 Money In - $300.00 Money Out + $100.00 Capital Gain = $500.00. Realized Gain: $100.00 Capital Gain (total of sales splits). Unrealized Gain: $600.00 Value - $500.00 Basis = $100.00.
Found this in the guide https://www.gnucash.org/docs/v3/C/gnucash-guide/rpt_standardrpts.html#rpt_advport disclaimer - I can try decode but i don't 100% understand the report myself.
I think I was the last person to do much work on the advanced portfolio report. It wasn't all that long ago (my last commit was about 4 years ago) and I'm still here although not so active. I didn't write the report. Mostly I tried to clean it up and make it do what people wanted. I would strongly urge you to read the other bug reports and mailing list conversations about this report before doing much with it. There were many requests for changes to the report back then and some of them were fairly subtle. It deals with stock splits, return of capital, dividends and dividend reinvestment (including residual share accounts), etc. It would be easy to break some of that. You're right that it ignores capital gain splits. It calculates the basis entirely from the other splits. It has always been that way and I didn't change it. People don't always enter capital gain splits and they want the report to compute the gains without them. It would be possible to add an option to use them, but it should still work without them. I think your observed action for this test case is correct for average cost basis. It also works correctly for LIFO. You seem to want "MIFO" (middle in first out). There is no provision for specifically designating which lot is being sold in a sell transaction. It wouldn't be hard to implement this except for coming up with a reasonable user interface to specify the lot being sold. It might be possible to deduce which lot is being sold from the capital gain splits (if they exist), but it would be hard and there are probably some cases that are ambiguous. Internally the report keeps the basis for each lot separately (except for average price basis) and associates each sale with as many lots as necessary to cover the sale. If someone wants to work on this report, I'd be glad to help them figure out the current code.
One possibility if someone wants to get into this is use the Gnucash lots scrubber to associate stock sales with the correct stock purchase. If you use the "Scrub account" button in the lots viewer to create the capital gain splits (instead of creating them manually) it creates Gnucash lots to reflect FIFO strategy. You can then manually remove the purchase split from the lot Gnucash created and add the one you want to use. If you scrub the account again, it recalculates the gain using the purchase you specified. When the advanced portfolio report sees a stock sale, it could check if the sale split is in a Gnucash lot and use the purchase splits also in the lot to calculate the basis for the sale. This is conceptually easy, but might be tricky to code. It would be easier if you reject any Gnucash lot with more than one sale split in it. This simplifies things and Gnucash won't create such lots automatically.
(aside) @mta thank you for describing the APR - I have it in my target sight to refactor it completely. It would be nice to get some assistance to ensure 100% options coverage for the integration tests...
> When the advanced portfolio report sees a stock sale, it could check if the sale split is in a Gnucash lot and use the purchase splits also in the lot to calculate the basis for the sale. While this might be a worthwhile feature, it does not address my test case. > People don't always enter capital gain splits and they want the report to compute the gains without them. I suggest that either the splits should be used when found, falling back on averages if not, or there should be an extra Basis Calculation Method called Capital Gains, where the basis is simply calculated by Money In - Money Out + Capital Gains splits.
I don't understand why it wouldn't address your problem. If you created a lot containing the sell split and corresponding buy split and if the report used that lot to tell it which buy to use for the basis calculation, it seems to me that the report would produce the output you want. If that is not true, explain why it is different from what you want.
Both of the previous comments describe data that does not exist in the test case.
I must be dense. What data? The lot itself? It's true that this doesn't exist in your test file, but it could be added with a trivial amount of effort. I did it two or three times in slightly different ways in a few minutes when I was testing this approach. If not that, then what?
Hi Mike, Just wanted to step in the shoes of Robert here for a moment. I'm experiencing the exact same issue that he outlined in comment 6. However, I think Robert may have misunderstood your comment 8. It appears that the cost basis options available in Advanced Portfolio do not operate as desired when a partial share sale occurs when there are multiple lots. Your suggestion of a MIFO option would solve the issue in the test case. However, in practice, the costs basis for the lots sold could be any order. For example, purchasing 4 lots of shares in the quantity 30, 50, 50, 110. If I sold 105 shares (being first and last lots), the LIFO, FIFO, average or even MIFO options would adequately reflect the capital gain. Of course, if the Advanced Portfolio could work off the capital gain splits, that would address the issue. I note that a possible work-around could be using separate accounts for each lot purchase. However, this would reduce the utility of the Advanced Portfolio somewhat. Cheers, Tim
My "MIFO option was meant as a joke. You can't do that since it isn't well-defined in the general case (as your example shows). I agree that there are ways this could be improved. I outlined some of them in my previous comments. If you want to implement any of these, or some other ideas, be my guest. I probably won't do it myself.
