VBA function to Calculate FIFO price

[apradeepk]

Hello Experts,
Wondering if you could help in coming up with a Excel function to determine the FIFO price of stocks based on consumption.

For example:
Sales:
Stock Date Qty Selling Price Cost Price (Excel Custom Function to determine the Cost or VBA code to look for the cost?)
Stock 1 20-Nov-16 70 $ 100 ?? ----Answer based on FIFO: $70
Stock 1 21-Nov-16 40 $ 100 ??-----Answer based on FIFO: $70

Purchases:
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.

Thanks,
Pradeep