I am working on an investment master application in qliksense where I have to calculate realized gain/loss by using the First in First out (FIFO) method.
Here is a sample of what I have to achieve:
Let us assume we have the following position:
ISIN
Purchase/ Redemption Date
Flag
No. of Units
NAV
Amount
1001
1/1/2022
Purchased
1577
₹ 633.76
₹ 999440
1001
2/1/2022
Purchased
800
₹ 625.5
₹ 500400
1001
24/3/2022
Redeemed
842
₹ 712.21
₹ 599680
1001
26/3/2022
Redeemed
1528
₹ 713.04
₹ 1089525
We have to calculate realized gain for REDEEMED UNITS. After applying FIFO result would be something like:
the final table should look like this:-
ISIN
Purchase Date
Redemption Date
NAV on purchase date
NAV on redemption date
No. of units
Realized Gain/ loss
1001
1/1/2022
24/3/2022
₹ 633.76
₹ 712.21
842
₹ 66054
1001
1/1/2022
26/3/2022
₹ 633.76
₹ 713.04
735
₹ 58270
1001
2/1/2022
26/3/2022
₹ 625.5
₹ 713.04
793
₹ 69420
Gain for first redemption (24/3/2022):
The first REDEMPTIONfor 1001took place on 24/3/2022 while the first PURCHASE happened on 1/1/2022.
No. of units for the first REDEMPTION is 842 units, while no. of units for the first PURCHASE is greater, being 1577 units.
So, the gain for the first REDEMPTION can be covered entirely by the first PURCHASEposition, and we get a gain of ₹ 66054 [(₹712.21 - ₹ 633.76) * 842].
1001
1/1/2022
24/3/2022
₹ 633.76
₹ 712.21
842
₹ 66054
By multiplying the no. of units sold 842 with the NAV difference between the time we REDEEMED and the time we bought (₹712.21 - ₹ 633.76) we get a gain of ₹ 66054.
Notice that we only sold 842 units out of 1557 units that were the original units of the FIRST 1001 PURCHASEposition. So, the first 1001 PURCHASE POSITION now has a balance of (1577- 842) = 735 units.
Calculating gain for second redemption position (26/3/2022):
The second REDEMPTION position for 1001 happened on 26/3/2022. And, since the first PURCHASE position that was executed on 1/1/2022 still has an unsold balance of (1577- 842) = 735 units, we can use that when calculating the gain of the second REDEMPTION position.
However, since the second 1001 REDEMPTION position has a quantity of 1528 units, it means the first position can be liquidated entirely:
1001
1/1/2022
26/3/2022
₹ 633.76
₹ 713.04
735
₹ 58270
So, the gain realized by selling the remaining 735 units from the first PURCHASE position generates a gain of 735 X (₹ 713.04 - ₹ 633.76) = ₹ 58270.
But, we are not done since we only sold 735 out of 1528 units from the second REDEMPTION position. So, the REDEMPTION position has a remaining balance of (1528 - 735) = 793 units.
Therefore, we proceed with the next consecutive PURCHASE position:
ISIN
Purchase/ Redemption Date
Flag
No. of Units
NAV
Amount
1001
2/1/2022
Purchased
800
₹ 625.5
₹ 500400
Since the remaining 793 units balance of the SECOND REDEMPTIONposition can be sold from the quantity of the SECOND PURCHASE position, we can liquidate the remaining balance of the secondREDEMPTION position entirely, and a new trading gain is being realized, 793 X (₹ 713.04 - ₹ 625.5) = ₹ 69420:
1001
2/1/2022
26/3/2022
₹ 625.5
₹ 713.04
793
₹ 69420
I am new to Qliksense so please help me to achieve this solution.
