Virtual lots

[alensiljak]

As the Lots feature in GnuCash modifies the original records, and is therefore risky to use multiple times, the same concept could be recalculated on the fly. This would create virtual lots, used just for the purpose of calculating the capital gains on the sold stock, and average price for the remaining stock.

The calculation would be based on the current state in the account. Meaning that, if there are any changes in the original transactions, the numbers would be recalculated based on the new state. This is not the case with Lots in GnuCash, where transactions get duplicated when Scrubbing is used, effectively making it extremely difficult to track the changes in the account visually.

Instructions:

• use two columns - buy and sell.
• order records by date.
• sum of purchases - sum of sales = remains (open lot/s)
• capital gains are calculated on the matched blocks. These are not just closed lots but each block from sell that matches a block from buy. A block is a portion (or whole) of the buy/sell transaction.

Note: this calculation already exists in GnuCash Lots view. It is shown in Splits Free window.

The Virtual Lots model should be the base for any calculation on the remaining portion of the stock.

[alensiljak]

[alensiljak]

Solution (FIFO)

The easiest implementation of the first-in, first-out (FIFO) calculation using virtual lots only (no real lots needed in GnuCash book) is to calculate backwards:

• get the total of purchase transactions,
• get the total of sale transactions,
• sale - purchase = balance
• iterate from the newest purchase transaction backwards, and subtract the quantity until
  • purchase transaction is "consumed", in which case continue with the next one,
  • balance is "consumed", marking the end point.
• take all the lots until the end point and calculate the average price based on this information.
• Only the lot containing the end point will get split or virtualized. The newer lots will use their original information (price x quantity = paid) for the calculation of the avg.price.

In the chart above:

• purchased quantity = x, sold quantity = y; x-y = z (balance);
• we will start from the end of slot D, subtracting from Lot 2 until we get the balance to 0;
• this will leave us at the point between C and D slots, the end point;
• since this is the first and only lot and slot to be used in the calculation, we will split Lot 2 and use only the slot D. It will be the amount D * the price paid for Lot 2 = total money paid, which gives the average cost for the security in the graph. In this case the average cost is the price paid for the Lot 2.

Note that this calculation has to be done per each holding account. It also does not take into account any stock splits or repricings.

[alensiljak]

There are only two cases - the one above where we split the last lot, and this one, where we have full lots before reaching the one to be split.

In this, more complex example, we will take as many as full lots as we have (slot E, Lot 3 and/or more), before reaching Lot 2, which gets split. Then we calculate the value for the remaining part of Lot 2 and add to the other, previously calculated ones.

[alensiljak]

Test Cases

1. Same Amounts

Buy 100 @ 10 = €-1000 Sell 100 @ 15 = €1500 Calculate CG, find matching transactions, if any. €500

2. Different Amounts, 2 Tx

Buy 100 @ 10 = €-1000 Sell 50 @ 20 = €1000 Calculate realized CG = €1000 Calculate unrealized CG = €1000

3. Different Amounts, 3 Tx

Buy 100 @ 10 = €-1000 Sell 50 @ 20 = €1000 Calculate realized CG = €1000 Calculate unrealized CG = €1000 Sell 50 @ 15 = €750 Calculate realized CG = €250. Unrealized = €0.