Average

[Wombat_Assassin]

I have attached my Excel file. It's basically keeping track of stock market trades, the numbers on the white lines are stocks bought, and the ones on the grey lines are stocks sold. What I want to do, is to make, for e.g. C2 display the average price of all stocks I currently own in that company.

For e.g. I buy 20 shares for £50 each, and then later on, I buy another 20 shares for £100 each, it would then show £75 in that section. When I sell those shares, the average disappears. Also, if I buy even more shares, it shows the average of all those shares too.

Any help anyone?

[aloha312]

Are you tracking both the quantity of shares purchased and the purchase price in the spreadsheet?

[Wombat_Assassin]

Yes, I am. The left number is amount about, e.g. 500 and the right one is price per share e.g. 24.54. The price is in British Pounds.

[DonkeyOte]

Surely the Average will in part depend on which process you adopt regards disposal of shares ?

Perhaps it will be easiest for us to determine what you do if you provide us with the answer to a simple example:

You buy 1000 shares at 10
You buy a further 300 shares at 15
You then sell 900 shares at 20.

So the question is - what is your average (by your reckoning) ?

Is it

a) 10 (400 left @10 - ie LIFO)

or

b) 13.75 (100 left @ 10 + 300 @ 15 - ie FIFO)

In the meantime - you don't need the calcs. in AL etc... the below will give you no. of remaining shares

Please Login or Register  to view this content.

[Wombat_Assassin]

I see your point there... What would you suggest I do? I could make it so that I can only sell ALL my currently owned shares in that company, that would work fine with the averages etc, but it would hinder my flexibility, or I could make sure to sell the more expensive shares first/last, but that would put me off with the average price I am trying to reach.

What I could do is sell all the cheapest shares first, thereby always selling above the average, if you know what I mean?

Anyways, what would you suggest?

[DonkeyOte]

Not sure it's really for us to decide...

The point I was raising is that these types of calculations are generally far more complex than people first anticipate.

sell all the cheapest shares first

I agree with you that the above makes sense in terms of the economics but you must then remember that "cheapest" is itself determined at POS ... if you have:

Buy 100 @ 50
Buy 175 @ 30
Sell 150 @ 45
Buy 150 @ 25
Sell 100 @ 45

It follows that at the point of first sale the cheapest was 30 so at that point only 25 remain @ 30 (the @ 50 purchase is untouched)

At the second sale the cheapest are those @ 25 so you would sell 100 of the 150 @ 25

Your Average at that point is then

100 @ 50 + 25 @ 30 + 50 @ 25 -> 40

If you don't work through in that time restricted manner the instinct would to say that you sold 250 as follows:

150 @ 25
100 @ 30

leaving your average as

100 @ 50 + 75 @ 30 -> 41 ish

[Wombat_Assassin]

Wait, you're saying the average at points is 67/91. How can the average ever be ABOVE 50, the highest price?

I calculate the averages to be 40/41ish not 67/91

[DonkeyOte]

Don't worry about that - it helps if I use the right figures... I changed the example a fair few times to come up with a sensible example ... I will correct now !

The main premise of the post being that if you assume the first example to be the correct one (based on the rule of cheapest first) then the calculation is fairly recursive and generally speaking a UDF (VBA function) would be warranted.

The approach you adopt will have an effect on the results and I don't think we're best placed to decide the most appropriate approach - this is for you to decide.

[Wombat_Assassin]

I was just asking because I'm not sure how doable or complex it might be to create a certain type of average. I think I might just go with the "Sells cheapest shares first" method, would you be able to help with that?