Wondering if you could help in coming up with an Excel function to determine the FIFO (First in First Out) price of stocks based on consumption.
Stock ID (string) is uniquely identified for any transaction.
Stock Date Qty Selling Price Cost Price (Excel Custom Function to determine the Cost or VBA code to look for the cost?) Selling Price
Stock 1 20-Nov-16 70 $ 110 ?? ----Answer based on FIFO: $70 $40
Stock 1 21-Nov-16 40 $ 100 ??-----Answer based on FIFO: $70 $30
Stock PurchaseDate Qty Cost Price Remaining Stock
Stock 1 01-Nov-16 120 $ 70 ??
Stock 1 02-Nov-16 100 $ 80 ??
Greatly, appreciate your help.