FIFO Stock (Security) Cost per share calculation - multiple buy and sell tansactions, multiple stocks

[agizbert]

I am looking to calculate the average cost value of a stock is where multiple transactions have occurred (Buy and Sell). I want to calculate the average cost based on balances being determined on a first in first out basis.

I have tried SUMIf and sumifs using tables and I am at my wits end...

Sample Data

Stock Action Date # Shares Cost/ Share

abc buy 1/1/2000 500 $25
def buy 1/2/2005 300 $15
xyz buy 2/3/2010 150 $40
abc buy 1/10/2005 250 $45
abc sell 10/10/2014 550 $50
abc buy 10/15/2014 200 $55

The abc security is the challenge. The sell transaction should decrement the first buy (1/1/2000) to zero, thus eliminating the inclusion of that cost per share from the overall cost per share calculation. The sell transaction would leave 200 shares left for the second transaction of 1/10/2005. THen the added buy on 10/15/2104 would require inclusion of that tranche in the Average Cost Calculation.

Thanks for any help. I can provide more details as required or send my sample workbook..

 

[saeedkk]

Hello,
well what you need is a bit tricky,i trust you are going for a periodic system , because perpetual
system would means that you would depend on dates and at each point you would need to get the price
so my solution is based on periodic where you will get the final result as the some of the quantity left of each item / the sum of the quantity left

so as much as i understood what you need please find the solution, in order to fix this you need helper columns

1st field reverse the sign of a sell to simplify things with ta new field of function =IF(B3="buy",D3,(-1*D3))
2nd field you would need to sum all the buys from each point up =IF(F3<0,0,SUMIFS($F$3:F3,$B$3:B3,"buy",$A$3:A3,A3))
3rd field this field will get the amount available at this point since it will take the sum of all the "sells"and subtract it from the reached " buy" amount till this point ( remember the sell is negative since using field 1st field added
4th field multiply the 3rd field result by the price =IF(H3*E3<0,0,H3*E3) and use if to remove the -ve where at a point the total sell is higher than the current buy
now end it with a legend of your security names where u get the sum of all >0 value of field 4 and the sum of the security name in field 3 of course all with the proper filters
be careful u may get div /0 if sell = buy ... i m sure you would know how to fix it ...

i have uploaded the worksheet to this link check it out and tell me if that is what you need or if you need more modifications ...

https://drive.google.com/file/d/0BzZL_L3ZnGTdd1BGS3BDVGJuMU0/view?usp=sharing


Hope this helps you
regards
saeed khodor

I am looking to calculate the average cost value of a stock is where multiple transactions have occurred (Buy and Sell). I want to calculate the average cost based on balances being determined on a first in first out basis.

I have tried SUMIf and sumifs using tables and I am at my wits end...

Sample Data

Stock Action Date # Shares Cost/ Share

abc buy 1/1/2000 500 $25
def buy 1/2/2005 300 $15
xyz buy 2/3/2010 150 $40
abc buy 1/10/2005 250 $45
abc sell 10/10/2014 550 $50
abc buy 10/15/2014 200 $55

The abc security is the challenge. The sell transaction should decrement the first buy (1/1/2000) to zero, thus eliminating the inclusion of that cost per share from the overall cost per share calculation. The sell transaction would leave 200 shares left for the second transaction of 1/10/2005. THen the added buy on 10/15/2104 would require inclusion of that tranche in the Average Cost Calculation.

Thanks for any help. I can provide more details as required or send my sample workbook..

 

[agizbert]

Saeed,

Thanks. I checked and it appears to work with a very fast try. I changed the data and formulas to create the content in a table. Then I used the tab key to add a row and added data. The solution calculated the average cost correctly in a fast view. I will do more thorough work later tonight or Monday morning. But looks very good so far. ...and yes, it's a bit tricky...

Hello,
well what you need is a bit tricky,i trust you are going for a periodic system , because perpetual
system would means that you would depend on dates and at each point you would need to get the price
so my solution is based on periodic where you will get the final result as the some of the quantity left of each item / the sum of the quantity left

so as much as i understood what you need please find the solution, in order to fix this you need helper columns

1st field reverse the sign of a sell to simplify things with ta new field of function =IF(B3="buy",D3,(-1*D3))
2nd field you would need to sum all the buys from each point up =IF(F3<0,0,SUMIFS($F$3:F3,$B$3:B3,"buy",$A$3:A3,A3))
3rd field this field will get the amount available at this point since it will take the sum of all the "sells"and subtract it from the reached " buy" amount till this point ( remember the sell is negative since using field 1st field added
4th field multiply the 3rd field result by the price =IF(H3*E3<0,0,H3*E3) and use if to remove the -ve where at a point the total sell is higher than the current buy
now end it with a legend of your security names where u get the sum of all >0 value of field 4 and the sum of the security name in field 3 of course all with the proper filters
be careful u may get div /0 if sell = buy ... i m sure you would know how to fix it ...

i have uploaded the worksheet to this link check it out and tell me if that is what you need or if you need more modifications ...

https://drive.google.com/file/d/0BzZL_L3ZnGTdd1BGS3BDVGJuMU0/view?usp=sharing


Hope this helps you
regards
saeed khodor