Weighted average cost formula required

[JackDanIce]

Hi,

I have a series of transactions and need to calculate the average cost which includes both buys and sells.

E.g.

Purchase 100 shares @£1/share
Purchase 50 shares @£1.50/share
Sell 25 shares @£2/share
Purchase 30 shares @£1.60/share

Weighted total cost after each transaction
£100 (100 * £1; implies £1/share cost)
£175 (50 more bought: 50 * £1.5 + 100 * £1 = £175 total cost, implies £1.17/share)
£145.83 (25 sold, therefore 125 remain. 125 * £1.17 = £145.83 total cost for the remaining 125)
£180.83 (30 more bought, 125 * £1.17 + 30 * £1.60 = £180.83, implies £1.25/share)

Could someone help suggest a formula for the weighted costs (£100, £175.50, £145.83 and £180.83 respectively) please? The transactions will always begin with a purchase.

Thank you in advance,
Jack

 

[PhilS2520]

Hi Jack

Opps! Sorry Hello Jack

I’m sorry but did you not solve your own query by providing the calculations?!

Simply put each of these numbers in a cell, and in one cell or more put the calculations you provided.

Or am I missing something? If this is true, the. Please describe your question in simple terms.

 

[JackDanIce]

No terrorists around here!

Yes and no, in principal I understand the problem but I can't determine a single formula (one size fits all) that outputs correctly the total average cost after each transaction.

I.e. I'm looking for a formula, I can enter into a new column to calculate the total average cost after each transaction.

Each transaction row/record only contains values for direction (buy/sell, always starts with a buy), quantity and price per unit.

The formula needs to account for direction when calculating the running cost of the current number of shares owned.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

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

B​

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

C​

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

D​

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

E​

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

F​

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

1​

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

Qty​

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

Unit Price​

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

Own​

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

Total Cost​

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

Avg Cost​

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

​

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

2​

[/td][td]

100​

[/td][td]

$ 1.00​

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

100​

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

$ 100.00​

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

$ 1.00​

[/td][td]C2: =SUM(C1, A2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

50​

[/td][td]

$ 1.50​

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

150​

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

$ 175.00​

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

$ 1.17​

[/td][td]D2: =SUM(D1, A2*B2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

(25)​

[/td][td]

$ 2.00​

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

125​

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

$ 125.00​

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

$ 1.00​

[/td][td]E2: =D2/C2[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

30​

[/td][td]

$ 1.60​

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

155​

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

$ 173.00​

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

$ 1.12​

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

 

[shg]

Or less transparently,

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

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

B​

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

C​

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

D​

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

1​

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

Qty​

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

Unit Price​

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

Avg Cost​

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

​

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

2​

[/td][td]

0 ​

[/td][td]

$ - ​

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

3​

[/td][td]

100​

[/td][td]

$ 1.00​

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

$ 1.00​

[/td][td]C3: =SUMPRODUCT(A$2:A3, B$2:B3) / SUM(A$2:A3)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

50​

[/td][td]

$ 1.50​

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

$ 1.17​

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

5​

[/td][td]

(25)​

[/td][td]

$ 2.00​

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

$ 1.00​

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

6​

[/td][td]

30​

[/td][td]

$ 1.60​

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

$ 1.12​

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

 

[JackDanIce]

Hi shg, the average cost after the sell (row 5 in both tables) doesn't appear to be correct.

I buy 100 shares for £1 each - total spend is £100
I buy 50 more shares for £1.50 - total spend is £175
I sell 25 shares for x - total spend is £145.83 (i.e. the new/adjusted average cost of the remaining 125 shares, sell price is irrelevant)
I buy 30 shares for £1.60 - total spend is £180.83

I'm try to calculate the average cost of total spend for the number of shares (still) held at the end of each transaction, unless the holding is reduced to 0

 

[shg]

... sell price is irrelevant)

Reckon I don't understand that. The average cost I calculate tells you what you need to sell your current shares for in order to break even, which seems like a useful thing to know.

 

[shg]

Or maybe you're looking for a FIFO or LIFO cost. That's more complicated.

 

[JackDanIce]

Ah that might be what the confusion is - not looking to break even, I want to track the average price of each held unit held after each buy or sell transaction.

Buying is straightforward, just the usual weighted average calculation.

When partial selling is involved then the cash returned is ignored but the average buy price of each share still held changes. I'll try an example again:

I buy 100 apples for £1: Average cost per apple is £1.00
I buy another identical 50 apples for £1.50/apple: Average cost per apple is (100*£1)+(50*£1.50) / 150 = £1.17

I sell 25 apples (from the 150 held) for £2. This leaves me with 125 apples which cost me an average total of £145.83 (125 * £1.17)

Using blue to show that the average cost per apple from the last previous "buy" transaction is now used to calculate the total average cost per apple after any sell transaction

Then I buy 30 more identical apples for £1.60/apple, this costs me: £48
So now I have 155 apples, which cost on average £1.25 each with an average total spend of £193.83

I would like to know what my average unit spend (only money going out) and total average spend is after each transaction, does this help make clearer?

It's not working on a sequential First In First Out but looking to calculate averages (unit and total spend) after every transaction but ignoring sales...

 

[sheetspread]

£180.83 (30 more bought, 125 * £1.17 + 30 * £1.60 = £180.83, implies £1.25/share)

125*1.1666666666 = 145.83

+ 30*1.60 (48) = 193.83

????

(your post in #9 seems to correct this, but please confirm)


Something like this is close if you're trying to average the cost over available units:

Excel 2010
	A	B	C	D
1	Type	Shares	Price	weighted cost
2	1	100	1	100
3	1	50	1.5	175
4	-1	25	2	145.8333333
5	1	30	1.6	192.0277778
Sheet2

Cell Formulas
Range	Formula
D2	=SUMPRODUCT($A$2:A2,$B$2:B2)*SUMPRODUCT(--($A$2:A2=1),$B$2:B2,$C$2:C2)/SUMPRODUCT(--($A$2:A2=1),$B$2:B2)