How do I calculate average cost for Share/Stock transaction

[anilbaheti]

Hi

I am attaching a template, wherein I can calculate average cost in first block, which in reality does not matches with actual average due to FIFO concept applied when selling a share/stock. I am unable to figure out how to get to the actual average cost.

Appreciate if someone can help on this template.


PS: I did go through below link, it does not matches with average and the template requires buy and sell to be two different block.

How to get average cost of remaining (unsold) stock?

Here is my excel sheet having buying details on the left and the selling details on the right. A2 is the total buying average. I2 is the total selling average. E2 is the remaining Stock. E4 is the Cell where I need to get the desired result of average cost of remaining (unsold) stock. I have...

chandoo.org

Regards
Anil

 

[Excel Wizard]

Please try at F2

=SUMIFS(D$2:D2,B$2:B2,">0")-SUM(TEXT(-SUMIF(B$2:B2,"<0")-MMULT(--(ROW(B$2:B2)>TRANSPOSE(ROW(B$2:B2))),B$2:B2*(B$2:B2>0)),"0;\0")*(C$2:C2-N(+C$1:C1)))

Ctrl+Shift+Enter

 

[AlanSidman]

Here is a template that I use for tracking stock transactions.

 

[anilbaheti]

Please try at F2

=SUMIFS(D$2:D2,B$2:B2,">0")-SUM(TEXT(-SUMIF(B$2:B2,"<0")-MMULT(--(ROW(B$2:B2)>TRANSPOSE(ROW(B$2:B2))),B$2:B2*(B$2:B2>0)),"0;\0")*(C$2:C2-N(+C$1:C1)))

Ctrl+Shift+Enter

Fantastic! This worked like a charm. Thanks a ton.

 

[anilbaheti]

Here is a template that I use for tracking stock transactions.

Thank you Alan for sharing. I will use the best practices.

 

[bosco_yip]

Another formula option, without array entry, just using normal entry

In F2, formula copied down :

=SUMIF(D$2:D2,">0")-SUMPRODUCT((-SUMIF(B$2:B2,"<0")>SUMIF(OFFSET(B$1,,,ROW(B$1:B1)),">0"))*(-SUMIF(B$2:B2,"<0")-SUMIF(OFFSET(B$1,,,ROW(B$1:B1)),">0"))*(C$2:C2-N(+C$1:C1)))

 

[anilbaheti]

Another formula option, without array entry, just using normal entry

In F2, formula copied down :

=SUMIF(D$2:D2,">0")-SUMPRODUCT((-SUMIF(B$2:B2,"<0")>SUMIF(OFFSET(B$1,,,ROW(B$1:B1)),">0"))*(-SUMIF(B$2:B2,"<0")-SUMIF(OFFSET(B$1,,,ROW(B$1:B1)),">0"))*(C$2:C2-N(+C$1:C1)))

View attachment 79511

Thanks. this works well except when I add another symbol and its transaction. See below. I just copied same rows and changed Symbol to YYY and it shows twice the average.

 

[anilbaheti]

Can someone help me with above query?

 

[bosco_yip]

Can someone help me with above query?

This is the forum rule, one question one post.

Your additional question in level #7, appear a new question.

So,

Please open a new thread for your new question with all attachment.

Thank you

Regards