I am at my wits end but getting stuck. Pl. refer to the attached excel file. I want a formula in Col H that will calculate the cost value of the respective stocks on FIFO basis automatically. In FIFO basis, the stock purchased first will be sold first and according to that the cost value of the respective stock has to be calculated after multiple buy and sell transactions for different stocks.
For eg. in the attached excel sheet, the sale value of 225 shares of CCC on 13-09-2017 is 39375 and the cost value on FIFO basis, as calculated manually is 33125 (200 shares purchased on 04-06-2017 @ 150 per share + 25 shares out of 50 shares purchased on 08-08-2017 @ 125).
I need the formula to find the cost value in Col H automatically. The formula should be scalable so that I can add more stocks and transactions and just copy paste the formula.
In Col J & K, I have shown the calculation and the cost value as obtained through manual calculation so that the Cost Value as obtained through a suggested formula in Col H can be checked.
I am happy to clarify further and look forward to the help.
Thanks and regards,
P.S: The earlier attached excel file had some mistakes, so uploading the updated file