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

 

[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][/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"]

Avg Cost​

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

2​

[/td][td]

0 ​

[/td][td]

$0.00 ​

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

0 ​

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

$0.00 ​

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

3​

[/td][td]

100​

[/td][td]

$1.00​

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

100​

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

$1.00​

[/td][td]C3: =C2+A3[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

50​

[/td][td]

$1.50​

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

150​

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

$1.17​

[/td][td]D3: =IF(A3 < 0, D2, (C2*D2 + A3*B3)/C3)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

(25)​

[/td][td]

$2.00​

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

125​

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

$1.17​

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

6​

[/td][td]

30​

[/td][td]

$1.60​

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

155​

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

$1.25​

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

I have no idea how to usefully interpret that number.

 

[sheetspread]

[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][/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"]

Avg Cost​

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

2​

[/td][td]

0 ​

[/td][td]

$0.00 ​

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

0 ​

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

$0.00 ​

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

3​

[/td][td]

100​

[/td][td]

$1.00​

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

100​

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

$1.00​

[/td][td]C3: =C2+A3[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

50​

[/td][td]

$1.50​

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

150​

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

$1.17​

[/td][td]D3: =IF(A3 < 0, D2, (C2*D2 + A3*B3)/C3)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

(25)​

[/td][td]

$2.00​

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

125​

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

$1.17​

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

6​

[/td][td]

30​

[/td][td]

$1.60​

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

155​

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

$1.25​

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

I have no idea how to usefully interpret that number.

cost * own in your example is close to what I got (with some rounding differences):

Excel 2010
	E
2	$100.00
3	$175.00
4	$145.83
5	$193.75
Sheet4

Cell Formulas
Range	Formula
E2	=C2*D2

and your structure/formulas are more efficient

 

[JackDanIce]

Hi sheetspread, yes I think I corrected the calc in #9 so values there correct

In effect this UK HMRC example (passed to me today) is what I'm trying to create: https://assets.publishing.service.g...t_data/file/414482/HS284_Example_3__2015_.pdf

Let me test your suggestion in #10 against the given example but thank you for replying and suggesting.

 

[JackDanIce]

Hi shg, I'm working for a client that offers software for managing a particular kind of tax efficient investment in the UK.

Due to being able to defer or carry taxes when assets are sold, the client needs to track the average cost of the remaining holdings (buys only).

The link above may help explain better but understand you reaction of it not being a useful number!

 

[shg]

Is this sorted?

 

[JackDanIce]

For now.. I'm still testing against different transactions, though suggested formula is definitely closer than one I had.

I'll revert if any updates, thank you for asking though.