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