Formula Help: Calculate Net Cost on Shares (excluding sell orders)
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Formula Help: Calculate Net Cost on Shares (excluding sell orders)

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Formula Help: Calculate Net Cost on Shares (excluding sell orders)

     
    Hi All,

    I have a spreadsheet with details of shares bought and sold, I have an add-in that calculates the current prices and everything is working fine but I would like to add a NET COST cell that calculates the cost of shares currently in my possession.

    The below example data is transactional and chronological from top to bottom, so a positive Qty is a buy and a negative Qty is a sell. From the below data I have been calculating a net cost using a sumif formula that excludes negative "Qty Bought/Sold" values (those are sell orders), giving me a net cost of $12,154. However, I now want to calculate the net cost of shares currently in my possession (the correct value is $4,170), which means that I need to subtract the Qty sold, but multiply it by the buy price (not the sell price which is in the adjacent cell). I am working under the assumption of "First in, first out" whereby I am assuming that I am selling the oldest buy order each time.

    Hope this makes sense, if anyone has any thoughts on how to do this please let me know! I can add a column to the right of the Price Bought/Sold column to calculate if that is easier, but would prefer it to calculate in one cell (assume that is impossible though). Prefer to use formulas rather than VBA if possible.

    Example data (rows are in chronological order):

    Net Cost (?)

    Qty Bought/Sold Price Bought/Sold
    20,600 $0.24
    8,000 $0.38
    -20,600 $0.40
    -8,000 $0.35
    8,000 $0.37
    2,000 $0.28
    2,500 $0.26

    Thanks all!

  2. #2
    Board Regular Flashbond's Avatar
    Join Date
    Mar 2010
    Location
    Turkey
    Posts
    596
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Help: Calculate Net Cost on Shares (excluding sell orders)

    This is what SUMPRODUCT function is built for

    =SUMPRODUCT(A2:A8,B2:B8)

    EDIT: SORRY got your question wrong. Never mind.
    Last edited by Flashbond; Dec 7th, 2017 at 12:32 AM.

  3. #3
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,243
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Formula Help: Calculate Net Cost on Shares (excluding sell orders)

    Maybe i'm oversimplifying the problem, but I think that in order to calculate the net cost it would be enough to subtract from the total amount spent on purchases the total received in sales.

    Something like (as suggested by Flashbond)
    =SUMPRODUCT(A2:A8,B2:B8)
    =1114

    Then, at the end, the current quantity of shares owned is
    =SUM(A2:A8)
    =12,500

    And the average price of each stock would be
    1114/12500 = 0.08912

    M.

  4. #4
    New Member
    Join Date
    Dec 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Help: Calculate Net Cost on Shares (excluding sell orders)

    Thanks Marcelo, this is certainly a possibility but what I would really like to do is take off the actual cost from the original purchase (eg. excel works down the list and takes off the first purchase, then the second and so on up to the quantity of what has been sold) ... not sure if this is possible though.

  5. #5
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,243
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Formula Help: Calculate Net Cost on Shares (excluding sell orders)

    Sorry, I'm not understanding what you want and how you got the value of 4170 mentioned in your original message - if you show us, step by step, the logic to get such value, maybe someone can help you.

    M.
    Last edited by Marcelo Branco; Dec 7th, 2017 at 02:02 AM.

  6. #6
    New Member
    Join Date
    Dec 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Help: Calculate Net Cost on Shares (excluding sell orders)

    I am not getting what your saying can you please explain briefly.


    ISO 9001 Certification in Kuwait

  7. #7
    Board Regular Flashbond's Avatar
    Join Date
    Mar 2010
    Location
    Turkey
    Posts
    596
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Help: Calculate Net Cost on Shares (excluding sell orders)

    Quote Originally Posted by Marcelo Branco View Post
    Sorry, I'm not understanding what you want and how you got the value of 4170 mentioned in your original message - if you show us, step by step, the logic to get such value, maybe someone can help you.

    M.
    I understand the problem but have no solution Let me clearify it for you:

    1. 20600 LOT is bought from the price $0,24. Which is totally $4944.
    2. Also he buys 8000 LOT from the price $0.38. Which is a total of $3040.
    3. Then he sells previous 20600 LOT. But he says $0,40 selling price is not important. So there is a -$4944 again calculated from previous buying price.
    4. The same is valid for -8000 LOT. -$3040 is calculated from previous buying price.
    5. Then a brand new 8000 LOT is bought with the price $0,37. Which is a total of $2960.
    6. Then also a new 2000 LOT is bought wşth the price $0,28. Total $560.
    7. And finally a new 2500 LOT is bought with the buying price $0,26. A totall $650.

    So in the end the grand total will be:
    4944+3040-4944-3040+2960+560+650=$4170

    Chhers
    Last edited by Flashbond; Dec 7th, 2017 at 08:09 AM.

  8. #8
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,243
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Formula Help: Calculate Net Cost on Shares (excluding sell orders)

    Great detective work!
    But the result does not seem to represent the net cost since it does not consider that there was a profit by selling 20600 shares for 0.40 that were acquired at 0.24 and the loss on selling 8000 shares for 0.35 that were acquired by 0.38.

    M.

  9. #9
    Board Regular Flashbond's Avatar
    Join Date
    Mar 2010
    Location
    Turkey
    Posts
    596
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Help: Calculate Net Cost on Shares (excluding sell orders)

    Quote Originally Posted by Marcelo Branco View Post
    Great detective work!
    But the result does not seem to represent the net cost since it does not consider that there was a profit by selling 20600 shares for 0.40 that were acquired at 0.24 and the loss on selling 8000 shares for 0.35 that were acquired by 0.38.

    M.
    Yeah, that's what I was thinking, too... That's why I suggested SUMPRODUCT function on the first hand.

    But what can you do, this is what @Tilly13 needs

  10. #10
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,243
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Formula Help: Calculate Net Cost on Shares (excluding sell orders)

      
    Quote Originally Posted by Flashbond View Post
    Yeah, that's what I was thinking, too... That's why I suggested SUMPRODUCT function on the first hand.

    But what can you do, this is what @Tilly13 needs
    I know, I know and unfortunately I also do not know how to provide the desired solution using Excel. If (big IF) the lots (Buy x Sale) are the same size *maybe* it's possible to create a formula (listen, i said maybe )

    M.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com