• 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

bosco_yip

Excel Ninja
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

anilbaheti

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

Status
Not open for further replies.
Top