Calculate stock returns considering the "first in - first out" principle

[Jana He]

Hello,

I have a dataset of individual trading data representing investors buying and selling behavior. My dataset consists of investor identifier, stock identifier (cusip), date of purchase or sale of stocks, amount of stocks purchased or sold (negative sign indicates sale of stock) and the price of the stock. Now I want to calculate the return an investor generates with each stock. I also want to calculate unrealized returns, therefore each time a stock is sold other stocks of the investor that are currently in his portfolio should be evaluated on the date of sale using closing prices from CRSP. These "unrealized stocks" are the stocks where the variable "stock" == . .

* Example generated by -dataex-. For more info, type help dataex
clear
input float(investor cusip) str8 dates float(stock price date)
1 1234 "19910108"  100  10 11330
1 4321 "19910203"   50  25 11356
1 1234 "19910505" -100  12 11447
1 4321 "19910505"    .  30 11447
2 5678 "19920407"  200  75 11785
2 5678 "19920409"   50  72 11787
2 5678 "19920802" -250  50 11902
3 1234 "19921105"  100 100 11997
3 1234 "19921111"  100  85 12003
3 1234 "19921212" -150 110 12034
3 1234 "19921220"  -50 150 12042
4 8910 "19930218"  100 500 12102
4 7891 "19930509"   25  10 12182
4 7891 "19930606"   50   8 12210
4 8910 "19931006"  -75 510 12332
4 7891 "19931006"    .  15 12332
4 7891 "19931020"   30  11 12346
4 8910 "19931020"    . 525 12346
4 8910 "19931130"  -25 530 12387
4 7891 "19931130"    .  16 12387
5 4657 "19940506"   40 260 12544
5 8585 "19940512"   50  25 12550
5 4657 "19940513"   20 250 12551
5 8585 "19940601"  -20  15 12570
5 4657 "19940601"    . 251 12570
5 8585 "19940605"   30  20 12574
5 4657 "19940707"  -50 300 12606
5 8585 "19940707"    .  26 12606
5 8585 "19940725"   50  30 12624
5 4567 "19940725"    . 330 12624
end
format %td date

To calculate stock returns I want to apply the FIFO method. For example we can take a look at investor 2. Investor 2 made two purchases of the same stock before he sells his whole position in the stock. The selling price should now be compared to the weighted average of the two purchases, so (200/250)*75 + (50/250)*72 and with this average purchase price the return should be calculated, i.e. (50/74.4)-1 = -0.33. The same method should apply for unrealized returns.


Thanks in advance!

[Ken Chui]

Not sure what is meant by "unrealized returns" (not in finance field), but based on the example I hope this should get you started.

bysort investor cusip: egen buy  = sum(stock) if stock > 0 & !missing(stock)
bysort investor cusip: egen sell = sum(stock) if stock < 0
gen wt_buy  = (stock/buy)*price
gen wt_sell = (stock/sell)*price

collapse (sum) wt_buy wt_sell, by(cusip investor)
gen return = round((wt_sell/wt_buy)-1, 0.01)

Results:

. gsort investor cusip

. list, sepby(investor)

     +------------------------------------------------+
     | investor   cusip     wt_buy   wt_sell   return |
     |------------------------------------------------|
  1. |        1    1234         10        12       .2 |
  2. |        1    4321         25         0       -1 |
     |------------------------------------------------|
  3. |        2    5678       74.4        50     -.33 |
     |------------------------------------------------|
  4. |        3    1234       92.5       120       .3 |
     |------------------------------------------------|
  5. |        4    7891   9.333333         0       -1 |
  6. |        4    8910        500       515      .03 |
     |------------------------------------------------|
  7. |        5    4567          0         0        . |
  8. |        5    4657   256.6667       300      .17 |
  9. |        5    8585   25.76923        15     -.42 |
     +------------------------------------------------+