FIFO Ending Stock

[BhuSan]

Dear All,

I need an excel formula to determine the holding period of the share I sold in during Jan to Dec 2019 is long term (greater than 365 days) or short term (less than 365 days) using FIFO method and also I need the ending no of share after each sale happened.

You can change the format I need the results as given in column E and D.

See the attached excel file.
1. Column E needs to populated only for the sale rows that happen in Jan to Dec 2019
2. In Column D I need the ending no of share after all cumulative sales that happened. Please see the manual adjustment I made in cell D3 and D4 to arrive in the correct ending share balance.

Thanks for helping me in advance.

 

[BhuSan]

Hi All - Please can anyone help me with this.

 

[bosco_yip]

Try,

1] In D3, copied down :

=IF(OR(B3="",B3="Sell"),"",C3-MIN(C3,MAX(0,-SUMIF(B$3:B$9,"Sell",C$3:C$9)-SUMIF(B$2:B2,"Buy",C$2:C2))))

2] In D10, formula :

=SUM(D3:D9)

3] In E3, copied down :

=IF((YEAR(A3)=2019)*(B3="sell"),"Long term","")



Regards
Bosco

 

[BhuSan]

Try,

1] In D3, copied down :

=IF(OR(B3="",B3="Sell"),"",C3-MIN(C3,MAX(0,-SUMIF(B$3:B$9,"Sell",C$3:C$9)-SUMIF(B$2:B2,"Buy",C$2:C2))))

2] In D10, formula :

=SUM(D3:D9)

3] In E3, copied down :

=IF((YEAR(A3)=2019)*(B3="sell"),"Long term","")

View attachment 67230

Regards
Bosco

Hi Bosco,

Thank you very much I have no words to express my thanks. Column D is working well like a charm but column E needs to be calculated based on the difference between the purchase date and sale date if the date difference is more than 365 days then it is long term otherwise short term. Please see the revised workbook attached. Many thanks in advance. I don't column F I'm just giving that for reference.

Thanks
Santhosh