Capital Gain Calculation

[Surendra Laxman]

Greetings to all Gurus,

I would be grateful if some of you Excel gurus can give me a hand. The problem statement “I invest in stock market and at the year end; I am able to generate the report from the trade book. Due to voluminous transaction, I am not able to

• Segregate Long term capital gain and short term capital gain.(Stocks which are sold within a year of purchase are termed as shorter others are long term)

• For above calculation, First in first out method to be used (FIFO) by achieving this I need to calculate total gain or loss for the period.

• Can anyone help on this please?


Sample data given below

Date Stock Action Qty Price Trade Value Brokerage incl. taxes Exchange

17-Jun-09 ICIBAN Buy 25 730 18250 17.65 NSE

17-Jun-09 ICIBAN Sell 25 731 18275 22.22 NSE

01-Sep-09 INDIBA Buy 250 137.5 34375 66.75 NSE

01-Sep-09 INDIBA Buy 250 137.66 34415.5 66.8 NSE

11-Sep-09 INDIBA Sell 500 146.7 73350 708.69 NSE

24-Sep-09 INDIBA Buy 500 155.5 77750 47.5 NSE

24-Sep-09 INDIBA Sell 500 156.5 78250 67.36 NSE

25-Sep-09 INDIBA Buy 500 162.5 81250 157.71 NSE

25-Sep-09 INDIBA Sell 500 161.25 80625 69.37 NSE

25-Sep-09 INDIBA Buy 500 160.96 80478.35 49.15 NSE

05-Oct-09 INDIBA Buy 500 163 81500 157.83 NSE

05-Oct-09 INDIBA Sell 500 164.1 82050 70.3 NSE

05-Oct-09 INDIBA Buy 500 162 81000 49.09 NSE

07-Oct-09 INDIBA Sell 500 167.5 83750 808.89 NSE

08-Oct-09 INDIBA Sell 500 165.7 82850 70.96 NSE

08-Oct-09 INDIBA Buy 500 163.5 81750 49.6 NSE

09-Oct-09 INDIBA Sell 500 172.7 86350 73.99 NSE

09-Oct-09 INDIBA Buy 500 173.5 86750 59.6 NSE

09-Oct-09 INDIBA Sell 500 174.5 87250 74.76 NSE

09-Oct-09 INDIBA Buy 500 173.5 86750 52.65 NSE

09-Oct-09 INDIBA Buy 500 170.5 85250 158.31 NSE

12-Oct-09 INDIBA Sell 500 176.5 88250 852.36 NSE

14-Oct-09 INDIBA Sell 500 180.7 90350 77.39 NSE

14-Oct-09 INDIBA Buy 500 179.5 89750 54.45 NSE

14-Oct-09 INDIBA Sell 500 180.7 90350 77.39 NSE

14-Oct-09 INDIBA Buy 240 181 43440 26.33 NSE

14-Oct-09 INDIBA Sell 500 181.75 90875 77.87 NSE

14-Oct-09 INDIBA Buy 260 180.5 46930 28.47 NSE

14-Oct-09 INDIBA Buy 500 180 90000 54.6 NSE

15-Oct-09 INDIBA Buy 500 183.5 91750 177.74 NSE

16-Oct-09 INDIBA Buy 250 188.4 47100 28.57 NSE

16-Oct-09 INDIBA Buy 250 186.7 46675 28.3 NSE

16-Oct-09 INDIBA Sell 500 188 94000 88.05 NSE

16-Oct-09 INDIBA Sell 500 187.5 93750 897.94 NSE

17-Oct-09 INDIBA Buy 100 187.5 18750 181.1 NSE

20-Oct-09 INDIBA Sell 100 195 19500 188.32 NSE

20-Oct-09 INDIBA Sell 250 192.3 48075 41.18 NSE

20-Oct-09 INDIBA Buy 250 191.9 47975 29.11 NSE

20-Oct-09 INDIBA Sell 250 192.42 48105.05 41.2 NSE

20-Oct-09 INDIBA Buy 254 191.95 48755.3 29.57 NSE

20-Oct-09 INDIBA Sell 254 192 48768 41.76 NSE

20-Oct-09 INDIBA Buy 250 190 47500 28.83 NSE

21-Oct-09 INDIBA Buy 500 194 97000 187.85 NSE

 

[NARAYANK991]

Hi Laxman ,


I am not clear on your requirement ; the data you have given does not indicate any stock sale after one year.


I would request you to post just two or three rows of data , which capture both short-term gain and long-term gain ; the Sell date should be within / beyond the appropriate dates so that these are applicable.


A second question is how do you define a trade ? There are several BUY and SELL transactions in the same scrip ; suppose 200 shares of a scrip are purchased on a particular date ; thereafter there are several BUY and SELL transactions in the same scrip ; after a year 100 shares of this scrip are SOLD ; how do we assume that these 100 came from the 200 which were bought a year back ? Will the SELL quantity always match the BUY quantity ? Or do we match the BUY and the SELL because they are together , even if the quantities are not identical ?


Narayan