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

[Tilly13]

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 (?)

[TABLE="width: 250"]
<tbody>[TR]
[TD]Qty Bought/Sold
[/TD]
[TD]Price Bought/Sold
[/TD]
[/TR]
[TR]
[TD]20,600
[/TD]
[TD]$0.24
[/TD]
[/TR]
[TR]
[TD]8,000
[/TD]
[TD]$0.38
[/TD]
[/TR]
[TR]
[TD]-20,600
[/TD]
[TD]$0.40
[/TD]
[/TR]
[TR]
[TD]-8,000
[/TD]
[TD]$0.35
[/TD]
[/TR]
[TR]
[TD]8,000
[/TD]
[TD]$0.37
[/TD]
[/TR]
[TR]
[TD]2,000
[/TD]
[TD]$0.28
[/TD]
[/TR]
[TR]
[TD]2,500
[/TD]
[TD]$0.26
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks all!

 

[Legacy 143009]

This is what SUMPRODUCT function is built for

=SUMPRODUCT(A2:A8,B2:B8)

EDIT: SORRY got your question wrong. Never mind.

 

[Marcelo Branco]

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.

 

[Tilly13]

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.

 

[Marcelo Branco]

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.

 

[Shilpa123]

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


ISO 9001 Certification in Kuwait

 

[Legacy 143009]

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

 

[Marcelo Branco]

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.

 

[Legacy 143009]

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

 

[Marcelo Branco]

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.