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]

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.

Oh, yes... I think this is the stiuation. Otherwise it makes no sense. But still can't figure it out.

Let say there is no contribution of negative numbers to the grand sum. Their total effect is zero. But when? Since you can exclude their possitive effect, too...

What I mean is there should be a --() condition which will return zero on a nagative row but also look back and find the last positive accurance of the same value (there should be one at least! But which one?!?! Probably the last one :S) and provide an array like {0,0,0,0,1,1,1} to the SUMPRODUCT function.

I don't think such condition can be written.

 

[Marcelo Branco]

Maybe with a helper column to get the correspondent last price


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Qty Bought/Sold​

[/td][td]

Price Bought/Sold​

[/td][td]

Price​

[/td][td][/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

20600​

[/td][td]

0,24​

[/td][td]

0,24​

[/td][td][/td][td]

1210​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

8000​

[/td][td]

0,38​

[/td][td]

0,38​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

-20600​

[/td][td]

0,4​

[/td][td]

0,24​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

-8000​

[/td][td]

0,35​

[/td][td]

0,38​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

8000​

[/td][td]

0,37​

[/td][td]

0,37​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

2000​

[/td][td]

0,28​

[/td][td]

0,28​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

2500​

[/td][td]

0,26​

[/td][td]

0,26​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

-8000​

[/td][td]

0,4​

[/td][td]

0,37​

[/td][td][/td][td][/td][/tr]
[/table]

Formula in C2 copied down
=IF(A2>0,B2,LOOKUP(2,1/(A$1:A1=-A2),B$1:B1))

Formula in E2
=SUMPRODUCT(A2:A9,C2:C9)

Note that changing the ranges to the original data setup (ignoring the added last row of data)
=SUMPRODUCT(A2:A8,C2:C8)
we get 4170 as desired

M.

 

[Legacy 143009]

A tricky question: what happens if the data looks like below?

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

Should -8,000 on the 5th row be calculated with the 2nd row buying price or the 3rd row price?

 

[Marcelo Branco]

With such scenario my suggestion above fails.
Another helper column? Argh!! It seems it's not possible to create a formula to solve this problem...

M.

 

[StephenCrump]

With one helper column:

Qty: A2:A8
Price: B2:B8
CumulativeBuys: C2:C8
C8: =MAX(0,A8)
C2: =C3+IF(A2>0,A2) Copy down to C7

C10: =SUMPRODUCT(--(ROW(Qty)>=MAX(IF(CumulativeBuys>=SUM(Qty),ROW(Qty)))),--(Qty>0),Qty-IF(CumulativeBuys>SUM(Qty),CumulativeBuys-SUM(Qty)),Price)

To do it without a helper column, you could generate CumulativeBuys using a formula, but it would be long and messy.

Excel 2010
	A	B	C
1	Qty Bought/Sold	Price Bought/Sold	Cumulative Buys
2	20,600	$0.24	41,100
3	8,000	$0.38	20,500
4	-20,600	$0.40	12,500
5	-8,000	$0.35	12,500
6	8,000	$0.37	12,500
7	2,000	$0.28	4,500
8	2,500	$0.26	2,500
9
10		Result	$4,170.00
Sheet1

 

[Tilly13]

I think the conclusion is it really does need to be done manually ... I'll continue having a think about how to phrase this in formula-speak, but I believe it's close to impossible. Thanks all for trying to understand my nonsense and help out

 

[Tilly13]

Thanks Stephen, I will give this a try Definitely seems like what I'm looking for, despite my particularly poor description of the problem

 

[Tilly13]

Mate, this works perfectly. Thanks so much for understanding my nonsensical jumble and providing a solution that works! Cheers.