• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do I calculate average cost for Share/Stock transaction

Status
Not open for further replies.

anilbaheti

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


Regards
Anil
 

Attachments

  • Calculate Stock Average Price.xlsx
    12.1 KB · Views: 9
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
 

Attachments

  • Calculate Stock Average Price.xlsx
    13.4 KB · Views: 7
Here is a template that I use for tracking stock transactions.
 

Attachments

  • Average Stock.xlsx
    13.5 KB · Views: 12
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)))

79511
 

Attachments

  • Calculate Stock Average Price (BY).xlsx
    12.4 KB · Views: 3
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.

79527
 

Attachments

  • Calculate Stock Average Price (BY).xlsx
    11.4 KB · Views: 2
Status
Not open for further replies.
Back
Top