How to find the Price under the FIFO method in excel

[SONJOE JOSEPH]

Dear All,

I have attached an excel sheet were in the data i have entered the buy and sell transactions of a particular stock named PPL. I just need to find the price under the FIFO method of calculation. For this stock the FIFO method will be as follows:-

20 qtys @ 394.38 ( from 25 qtys 5 qtys has been sold on 5th Jun ) Amount 7887.60
20 qtys @ 496.60 Amount 9932.00
20 qtys @ 472.90 Amount 9458.00

So now total qtys 60 and you add the Amount u will get Rs. 27277.60. When 27277.60 / 60 = 454.63 will be the price under the FIFO method

Suppose if i purchase some other stocks and sell it this calculation has to go on and I should be able to get the price of each particular stock until the each particular stock holdings is zero.

Do help me with the calculation and working file is attached along with this mail.

Thanks & Regards,

Sonjoe Joseph

 

[vletm]

SONJOE JOSEPH​

Can You use other than formula-solution ... ?

 

[Peter Bartholomew]

This is a bit rushed but I downloaded a gist I had previously written from GitHub and made a first stab at applying the function.

FIFO Lambda in Excel

FIFO Lambda in Excel. GitHub Gist: instantly share code, notes, and snippets.

gist.github.com

 

[Peter Bartholomew]

Dear All,

I have attached an excel sheet were in the data i have entered the buy and sell transactions of a particular stock named PPL. I just need to find the price under the FIFO method of calculation. For this stock the FIFO method will be as follows:-

20 qtys @ 394.38 ( from 25 qtys 5 qtys has been sold on 5th Jun ) Amount 7887.60
20 qtys @ 496.60 Amount 9932.00
20 qtys @ 472.90 Amount 9458.00

So now total qtys 60 and you add the Amount u will get Rs. 27277.60. When 27277.60 / 60 = 454.63 will be the price under the FIFO method

Suppose if i purchase some other stocks and sell it this calculation has to go on and I should be able to get the price of each particular stock until the each particular stock holdings is zero.

Do help me with the calculation and working file is attached along with this mail.

Thanks & Regards,

Sonjoe Joseph

Are you using 365 (ideally insider beta)? If not count me out.
I notice that I had used the 'Price' column rather than 'CP' (you quote two of the second followed by one of the first).

Working the formulas for a range of stocks would be something of an organisational challenge and would your guidance would be required.