Calculation of Capital Gain based on holding time and FIFO logic

[Neeraj_Malik]

Dear Experts,

This is my transaction Table A that maintains all stocks sale purchase transactions

And this is another Table B, subset of Table A, that maintains only the purchase transaction that has stock balances more than 0 which is updated after every sale/purchase txn and data is always sorted by date.

Now, while initiating a sale transaction I need to calculate the captial gain based on the holding time.

For example, As per table B, Stock with Ticker Symbol INN.VX has total balance 5500 (Sum of column "Stock_Purchase_Balance" for all rows for Ticker Symbol INN.VX) and need to initiate a sale transaction for selling 4000 stocks of INN.VX

In the sale transaction of 4000 stocks, the stocks needs to be picked from Table B on FIFO basis. And, the capital gain for 2500 stocks from first three Purchase Txn would be at rate of 1% that are having more than 180 days holding time and for the balance 1500 stocks that would be picked from other two Purcashe Txn with holding time of less than 180 would be at rate of 4%

I'm struggling to get the right expression for Capital Gain Tax that will based on the formula for Capital Gain tax as

(Unit Price for Sale Txn - Unit Price for respective Purchase Txns) * 0.01 * (Qty of stocks picked from respective purchase lot with holding time of more than 180 i.e 2500 stocks in this example) + (Unit Price for Sale Txn - Unit Price for respective Purchase Txns) * 0.04 * (Qty of stocks picked from respective purchase lot with holding time of less than or equal to 180 i.e 1500 stocks in this example).

Request, if any of our friend can help me in resolving this struggle. @Steve @Suvrutt_Gurjar @WillowMobileSys @Marc_Dillon @LeventK @MultiTech