Weighted average cost formula required

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,922
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
[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]
 
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0
... 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.
 
Last edited:
Upvote 0
Or maybe you're looking for a FIFO or LIFO cost. That's more complicated.
 
Upvote 0
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...
 
Last edited:
Upvote 0
£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
ABCD
1TypeSharesPriceweighted cost
211001100
31501.5175
4-1252145.8333333
51301.6192.0277778
Sheet2
Cell Formulas
RangeFormula
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)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

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