Average Cost of stock from buy/sell transactions

[Toadstool]

I have stock buy/sell transactions and want to calculate my position.
Here is some sample data:

I can calculate most things I need but I'm struggling with Average Cost.

So XXX bought 100 at $1 then sold 50 at $2, so my average cost is $1.
Then bought 100 at $2 and sold 100 at $3 so I have a Holding of 50.
I'm OK with assuming the oldest was sold first so the Average Cost of my 50 would be $2 (50 of the 7 Jan transaction).
I'm OK with assuming the newest would be sold first so the Average Cost would be $1 being 50 of the 100 bought 1 Jan.

It gets trickier with ZZZ as the 19 Jan transaction takes my holding to 0 so I really need an Average Cost of $1 being either 22 or 25 Jan buys.

It may be that I just haven't had enough coffee yet but this is hurting my head.

Any ideas for a formula/function solution? (avoiding macros or VBA).

Thanks!

 

[Toadstool]

Surely this type of calculation is needed all the time?

 

[iggy_]

Do you want to have the average cost as an additional row? Why not just have a couple helper cells with total position and average cost, you can have a more accurate average aswell.

 

[Marcelo Branco]

To calculate the real AVG price maybe something like this...

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Symbol​	Buy/Sell QTY​	Price​	Date​	Transaction Value​	Current QTY​	Current Cost​	Current AVG Price​
2​	XXX​	100​	1,00​	01-jan-18​	100,00​	100​	100​	1,00​
3​	XXX​	-50​	2,00​	04-jan-18​	-100,00​	50​	0​	0,00​
4​	XXX​	100​	2,00​	07-jan-18​	200,00​	150​	200​	1,33​
5​	XXX​	-100​	3,00​	10-jan-18​	-300,00​	50​	-100​	0,00​
6​	ZZZ​	200​	2,00​	13-jan-18​	400,00​	200​	400​	2,00​
7​	ZZZ​	-100​	1,00​	16-jan-18​	-100,00​	100​	300​	3,00​
8​	ZZZ​	-100​	1,00​	19-jan-18​	-100,00​	0​	200​	0,00​
9​	ZZZ​	100​	1,00​	22-jan-18​	100,00​	100​	300​	3,00​
10​	ZZZ​	200​	1,00​	25-jan-18​	200,00​	300​	500​	1,67​
11​	ZZZ​	-100​	1,00​	28-jan-18​	-100,00​	200​	400​	2,00​

<tbody>
</tbody>

Formula in F2 copied down
=SUMIF(A$2:A2,A2,B$2:B2)

Formula in G2 copied down
=SUMIF(A$2:A2,A2,E$2:E2)

Formula in H2 copied down
=IF(F2>0,MAX(0,G2/F2),0)

M.

 

[Toadstool]

To calculate the real AVG price maybe something like this...

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Symbol​	Buy/Sell QTY​	Price​	Date​	Transaction Value​	Current QTY​	Current Cost​	Current AVG Price​
2​	XXX​	100​	1,00​	01-jan-18​	100,00​	100​	100​	1,00​
3​	XXX​	-50​	2,00​	04-jan-18​	-100,00​	50​	0​	0,00​
4​	XXX​	100​	2,00​	07-jan-18​	200,00​	150​	200​	1,33​
5​	XXX​	-100​	3,00​	10-jan-18​	-300,00​	50​	-100​	0,00​
6​	ZZZ​	200​	2,00​	13-jan-18​	400,00​	200​	400​	2,00​
7​	ZZZ​	-100​	1,00​	16-jan-18​	-100,00​	100​	300​	3,00​
8​	ZZZ​	-100​	1,00​	19-jan-18​	-100,00​	0​	200​	0,00​
9​	ZZZ​	100​	1,00​	22-jan-18​	100,00​	100​	300​	3,00​
10​	ZZZ​	200​	1,00​	25-jan-18​	200,00​	300​	500​	1,67​
11​	ZZZ​	-100​	1,00​	28-jan-18​	-100,00​	200​	400​	2,00​

<tbody>
</tbody>

Formula in F2 copied down
=SUMIF(A$2:A2,A2,B$2:B2)

Formula in G2 copied down
=SUMIF(A$2:A2,A2,E$2:E2)

Formula in H2 copied down
=IF(F2>0,MAX(0,G2/F2),0)

M.

MArcelo,
Thanks for the response!

The data is from a CSV file but I can add helper columns in another tab. I think you're saying my average costs for the current holding of 50 XXX and 200 ZZZ is 1.33 and 2 respectively.

So I'd state XXX Average Cost of holding is 1.33*50=$66.5 and for ZZZ is 2*200=$400 being the latest date for a stock with a positive purchase qty?

Thanks!

 

[Marcelo Branco]

I was not sure what exactly you wanted.

My formulas try to indicate the current cost of each stock. I'll try to explain my logic.

For example, in the case of XXX: after some buying operations that cost $300 (100 + 200) and some sales operations that yielded $400, 50 shares were left with a profit of $100, so the actual cost of these shares is zero because there was a profit on these trades.

M.

 

[Toadstool]

I was not sure what exactly you wanted.

My formulas try to indicate the current cost of each stock. I'll try to explain my logic.

For example, in the case of XXX: after some buying operations that cost $300 (100 + 200) and some sales operations that yielded $400, 50 shares were left with a profit of $100, so the actual cost of these shares is zero because there was a profit on these trades.

M.

Ah! No, that's not what I was looking for. I have a stock holding, say 50, and over time there were buys and sells but I want to know what the average cost was for those 50.

Thanks anyway.

 

[Marcelo Branco]

Ah! No, that's not what I was looking for. I have a stock holding, say 50, and over time there were buys and sells but I want to know what the average cost was for those 50.

Thanks anyway.

Not sure how do you want to calculate the AVG price.
Let's consider XXX:
100 shares were bought by $1.00
and
100 shares were bought by $2.00

Do you want a weighted average?
=(100*1 + 100*2)/(100+100)
= 300/200
= $1,50

M.

 

[Toadstool]

Not sure how do you want to calculate the AVG price.
Let's consider XXX:
100 shares were bought by $1.00
and
100 shares were bought by $2.00

Do you want a weighted average?
=(100*1 + 100*2)/(100+100)
= 300/200
= $1,50

M.

It's the selling as well as the buying I want to account for. Let's look at ZZZ

6​	ZZZ	200​	2,00​	13-jan-18​	400,00​	200​	400​	2,00​
7​	ZZZ	-100​	1,00​	16-jan-18​	-100,00​	100​	300​	3,00​
8​	ZZZ	-100​	1,00​	19-jan-18​	-100,00​	0​	200​	0,00​
9​	ZZZ	100​	1,00​	22-jan-18​	100,00​	100​	300​	3,00​
10​	ZZZ	200​	1,00​	25-jan-18​	200,00​	300​	500​	1,67​
11​	ZZZ	-100​	1,00​	28-jan-18​	-100,00​	200​	400​	2,00​

<tbody>
</tbody>

6. My first purchase is 200 at $2 so my holding cost is $400. Easy so far.
7. Then I sell -100 at $1 so I have 100 left but they still cost me $2 each so my holding cost is $200.
8. Now I sell -100 at $1 so I have 0 left, so no holding cost. Any profit/loss doesn't matter as that's a different calculation.
9. Now I buy 100 at $1...
10 ... another 200 at $1 so holding is 300 with cost $300.
Then I sell -100 at $1 so holding is 200 and average cost should be $200. It can't take account of any transactions before 19 Jan as that's when holding went to 0 so no average should be carried forward.

Does that make sense?

 

[xenou]

Just to clarify you want a LIFO method here. I.e., when you sell a stock you want to sell the "oldest" stock.
I'm not sure how that's how it would be done in practice (say at Ameritrade or ETrade), but that's what you are describing. What would make it interesting was if you bought 100 for 1, then 100 more for 1.20, then sold 75, then sold 10, then sold another 30, then sold 85. These would dip into your "LIFO" layers but wouldn't so conveniently remove entire layers in a single transaction, and could remove from two layers at once. However, you can also count backwards - if the current inventory exceeds the last layer, then you have stock from two layers, and so on until the current stock is fully accounted for. Probably software that does this would re-calculate the layers on each buy or sell transaction.