RE: Genincode rising steadily now!18 Feb 2023 09:58
I was doing some thinking and for your own manual simple spreadsheet you are probably better of completely ignoring the average price anyway. If you include an accurate average - you are just doing the exact same thing as your brokers calculations anyway so what is the point in repeating it? All you need are quantity of shares and buy/sell prices. Lets look at both using our previous 3,6,9 prices but lets add in more after this to represent an example like He1 where you do a buy/sell below the average. Lets buy at 2p and sell at 3p.
I believe the correct formula for average would be ( (number of existing shares * average SP) + (number of new shares * new buy SP - fees )) / total number of shares = new average SP
fees are your taxes and commission. Lets ignore those for simplicity and mostly not paying stamp duty and things on AIM.
buy 1 share at 3p
buy 1 share at 6p
(1*3) + (1*6 ) /2 = 4.5 average and total of 2 shares
buy 1 share at 9p
(2*4.5) + (1*9) / 3 = 6 average and total of 3 shares
now sp drops to 2p and you want to take advantage of it, so lets buy 3
(3*6) + (3*2) /6 = 4 average and total of 6 shares
you now sell these 3 shares at 3p but you will not be using the buy price of 2p - you are using your average price of 4p
(3*3) - (3*4 ) = "loss" of 3p
average stays the same at 4p now total of 3 shares left
lets say the price now rises to 12p lets sell all the shares you have left again using the average buy price
(3*12) - (3*4) = profit 24p
take away your previous 3p loss gives total profit of 21p
It's easy to see in your head that buying 3 shares at 2p and selling at 3p gives a profit of 3p but using the averages in the above example it is a loss of 2p and this is how your broker shows it. So for your spreadsheet I think it is better to completely ignore the averages and just rely on your buy and sell prices
buy 1 share at 3p
buy 1 share at 6p
buy 1 share at 9p
buy 3 shares at 2p
now completely ignore the average prices use your original buying prices
then you sold 3 shares at 3p ( you bought these at 2p)
(3*3) - (3*2) = profit 3p
sold 3 shares at 12p ( you bought these at 3,6,9 )
(1*12) - (1*3) = profit 9p
(1*12) - (1*6) = profit 6p
(1*12)-(1*9) = profit 3p
total profit 3+9+6+3 = 21p
So the total profit is obviously the same in both situations once all shares sold, it's just how you are looking at it in the middle of the process which can get confusing. If you want to consider average prices, I say completely trust the broker as it will be correct and you will have to accept those trades that show as a loss (even if in your head you think of them as profits). You would not need a spreadsheet in that case.
Or just completely ignore the averages altogether and keep a log in a simple spreadsheet yourself. I hope this makes some kind of sense!!!