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

Tilly13

New Member
Joined
Dec 6, 2017
Messages
5
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

<tbody>
</tbody>

Thanks all!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
This is what SUMPRODUCT function is built for :)

=SUMPRODUCT(A2:A8,B2:B8)

EDIT: SORRY got your question wrong. Never mind.
 
Last edited by a moderator:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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 a moderator:
Upvote 0
Great detective work! (y)
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.
 
Upvote 0
Great detective work! (y)
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 ;)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top