[GNC] Lot Calculator Off?

[john]

Run a transaction report on the register, paste it into a spreadsheet, and calculate the sum of the gains. Run a trial balance report for the day after the transaction and make sure there aren't any unrealized gains from the sale, then run an Income Statement for the day and check that the total reported capital gain matches the statement amount.

It won't match if your broker doesn't use FIFO for calculating the gains. There's a good chance they use average cost and that will produce a different gain.

Regards,
John Ralls


> On Dec 5, 2022, at 11:19 AM, David T. <sunfish62 at yahoo.com> wrote:
> 
> Further to the issue of the lot manager splitting sales by lots: I attach the screenshot of GnuCash's management of the capital gains for one mutual fund sale. As you can see, there is a more than a full screen of separate gains transactions generated by the Lots Manager for this one sale (there are several entries offscreen). The broker lists ONE gain transaction and states that it's from multiple lots.
> 
> Honestly: how would anyone EVER evaluate whether the two accounting records match? And, if the point of GnuCash's approach is to allow the user to validate the broker's number, how would the user ever determine this? There are so many entries that it would be nearly impossible to determine any errors--and if a discrepancy were found, how would a user ever contest the results?
> 
> My point is: at this level of detail, are GnuCash users being served by this approach? 
> 
> Best,
> 
> David T.
> 
> On 12/3/2022 10:34 PM, David T. wrote:
>> So, the statements that I receive from brokers, which aggregate lots into one sale/gain entry, are incomplete and don't give a full audit trail. That's frustrating. 
>> 
>> I use the scrub feature, but compare my results to the financial institution and pull things apart when the results are off. That's actually how I got into this mess in the first place. I've gotten rather adept at assigning sales to specific lots, since the financial institution I use makes aggressive use of specific lots to manage gains throughout the year. 
>> 
>> David T.
>> On Dec 3, 2022, at 9:12 PM, john <jralls at ceridwen.us <mailto:jralls at ceridwen.us>> wrote:
>>> 
>>> It's not a programming necessity, it's an auditing necessity. Each purchase split represents a lot and in order to track the share balance of each lot a sale that touches multiple lots has to have a separate split for each lot so that you can work out what it did by examining the register.
>>> 
>>> It's possible that the lot scrubber screwed up--the code is old and torturously complex--but you didn't provide enough information for me to analyze that.
>>> 
>>> Note also that the lot scrubber is strictly FIFO.
>>> 
>>> Regards,
>>> John Ralls
>>> 
>>>>  On Dec 3, 2022, at 1:34 AM, David T. <sunfish62 at yahoo.com> <mailto:sunfish62 at yahoo.com> wrote:
>>>>  
>>>>  John,
>>>>  
>>>>  Thanks for the reply. I went back and re-entered all of the transactions again and this time it (mostly) came out as you have indicated. Not sure how I got into the rabbit hole I was in...
>>>>  
>>>>  For what it's worth, the "separate" sales were the result of the lot manager's splitting of a single sale into separate splits. While I understand the programming necessities behind this approach, it renders some accounts nearly incomprehensible when comparing to a financial institution's records, as the GnuCash user is forced to examine (and tally) multiple GnuCash sales and gains entries to determine whether the GnuCash books match the bank's. When a given transaction is split into two or three separate sales/gains entries, the burden is (mostly) manageable; when a single sale gets split into 6 or more such entries (as can happen with mutual funds with regularly-reinvested dividend entries), it is significantly more difficult to manage. Since most institutions simply report the aggregate sale and gain, it would be nice if there were a way to mimic this in GnuCash.
>>>>  
>>>>  David T.
>>>>  
>>>>  On 12/2/2022 9:31 PM, john wrote:
>>>>>  
>>>>>>  On Dec 2, 2022, at 1:06 AM, David T. via gnucash-user <gnucash-user at gnucash.org> <mailto:gnucash-user at gnucash.org> wrote:
>>>>>>  
>>>>>>  Hello,
>>>>>>  
>>>>>>  GnuCash 4.11 on Windows 10
>>>>>>  
>>>>>>  As the end of the year approaches, I am working to get all my accounts up to date, and I've stumbled across an apparent error in capital gains calculations in the Lot manager. I am attaching a screenshot of the problem I am seeing. If you add up the sales prices and the gains, they are off from the original purchase price by $4.20. The second sales loss has seemingly failed to account for the first calculation for some reason, and the resulting amounts differ from the financial institution by the same $4.20.
>>>>>>  
>>>>>>  Since I used the lot manager to handle all of the gains calculations, I'm at a loss for determining why or how I've achieved this erroneous result. Not sure if the table below will display correctly, but the numbers are there for reference.
>>>>>>  
>>>>>>  Best,
>>>>>>  
>>>>>>  David T.
>>>>>>  
>>>>>>   Amount  Gain(Loss)  Sale with Gain/Loss  
>>>>>>  Purchase cost  $ 230.96    
>>>>>>  Sale 1  $ 140.15  $(4.20)  $ 144.35  
>>>>>>  Sale 2  $78.44  $(12.37)  $90.81  
>>>>>>     $ 235.16  Total
>>>>>>     $(4.20)  Difference
>>>>>  Combining the info in the image with your table, I think you have three transactions:
>>>>>  
>>>>>          Shares Price  Debit Credit
>>>>>  Purchase:               8      28.87 230.96
>>>>>  Sale 1        5 28.03   140.15
>>>>>  Sale 2        3 26.1467     78.44
>>>>>  
>>>>>  The loss on the first is 5 * 0.84 = 4.20 and on the second 3 * 2.7333 = 8.17.  12.37 - 8.17 = 4.20.
>>>>>  12.37 / 3 = 4.1233, implying a sale price of 24.7467 and so proceeds of $74.24.
>>>>>  
>>>>>  Check your statement or sale ticket for the second sale to see which number you entered wrong.
>>>>>  
>>>>>  Regards,
>>>>>  John Ralls
>>>>>  
>>>>>  
>>> 
> <Capture.PNG>