calculate weighted average of stock cost

[Tim]

I have a sheet of stock buys and sells, sorted by date, and I am trying to
track the average cost of my stock purchases, but I can't seem to get it to
work.

Here is an example:
stock date Transaction shares price commision total
avg cost/shr
ALS 4/02/07 BUY 1000 5.00 9.99 5009.99
5.01
ALS 4/30/07 SELL 500 6.00 9.99 3009.99
---
ALS 5/07/07 BUY 1000 4.00 9.99 4009.99
???

I have 2 buys and 1 sell. Since the sell changes the weight of the 4/02/07
purchase to 500 shares, then the weighted average of the 2 buys should come
to 4.35. My calculations keep coming up to an average of 4.50, which is
wrong, because I can't find a way to account for the drop of 500 shares on
the 4/30/07 transaction. I tried SUMPRODUCT, and it works fine as long as
there are only buy transactions. But the SELLS screw it up.

I want to be able to keep adding buys and sells to the sheet in any order.
I may buy 3 or 4 times in a row, then sell all at once or sell a bit at a
time. In each case, I want to know what my average buy cost is at the time.
Please help me write a formula for the average cost/share column.

thanks in advance,
Tim

 

[Art]

I made a simplified example:

Beginning in Row1:

A -- number of shares
B -- +1 for a buy, -1 for a sell
C -- price
D -- average price

Cell D1=C1
D2:
=IF(B2=1,(D1*SUMPRODUCT(A$1:A1,B$1:B1)+C2*A2)/SUMPRODUCT(A$1:A2,B$1:B2),D1)

D3:
=IF(B3=1,(D2*SUMPRODUCT(A$1:A2,B$1:B2)+C3*A3)/SUMPRODUCT(A$1:A3,B$1:B3),D2)

and so on.

I hope this works

 

[Tim]

Art,
Thanks, it's a rudimentary start. Some key components are missing, such as
commissions, which have an effect on the average I'm trying to calculate. If
we include commissions into the formula, we probably need to include a total
(share*price)+commission.

I kinda of understand what you're doing. Is there a way to change the +1/-1
field to a character text "buy" and "sell"? Would that distort the
SUMPRODUCT formula? It just makes it alot easier to view with text fields.

thanks,
Tim

 

[Art]

To use buy & sell instead of 1 and -1, try this:

D2:
=IF(B2="buy",(D1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="buy")-1))+C2*A2)/SUMPRODUCT((A$1:A2)*(2*(B$1:B2="buy")-1)),D1)

D3:
=IF(B3="buy",(D2*SUMPRODUCT((A$1:A2)*(2*(B$1:B2="buy")-1))+C3*A3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="buy")-1)),D2)

 

[Tim]

Thanks Art, that worked for the buy & sell. Just the commission to add, and
I'll be totally happy.

Here's my stab at it - I added another column, so we now have
A -- number of shares
B -- buy or sell
C -- price
D -- commission
E -- average price

Then I added the commission column (D2 & D3) to the formula as follows:

E3:
=IF(B2="BUY",(E1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="BUY")-1))+C2*A2+D2)/SUMPRODUCT((A$1:A2)*(2*(B$1:B2="BUY")-1)),E1)

E4:
=IF(B3="BUY",(E2*SUMPRODUCT((A$1:A2)*(2*(B$1:B2="BUY")-1))+C3*A3+D3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="BUY")-1)),E2)

Now, this appears to work for the "buy" transactions, but the "sell" doesn't
work since the ELSE part of the IF defaults to the previous row average. Is
there a way we can calculate the commision for the "sells" as part of the
ELSE?

thanks,
Tim

 

[Art]

Tim,

Sorry I didn't have a chance to look at this until now. I'm trying to
remember how this worked -- as I recall I did not include any aspect of the
sell price in the average price. That is, a sell would sell at whatever the
average price was at that point. The sale price itself didn't affect
anything. Wouldn't this hold true for the commission on a sale as well?

 

[Tim]

Hi Art,

That's ok for not getting back to me. No worries. You've helped alot
already. At first I didn't think the sell should have any effect on the
average, since no buying was taking place. But when I considered the effect
of commissions, they take place regardless if there is a buy or sell. So, if
I am doing a partial sell, and still holding some shares, the commission on
the sell does matter after all and should be accounted for in the average.

I ended up editing the ELSE part of the IF as follows. I used the previous
row for the calculations and adjusted by current row commisions...I'm not
sure if it's totally correct...this formula is getting complicated. Please
check and let me know what you think.

E2:
=IF(B2="BUY",(E1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="BUY")-1))+C2*A2+D2)/SUMPRODUCT((A$1:A2)*(2*(B$1:B2="BUY")-1)),(A1*E1+D2)/A1)

Here's my columns again, to refresh your memory. Notice the E2 cell is now
updated to 5.00999 from 5 to account for the new code in the ELSE.
1000 Buy 5 9.99 5
500 Sell 6 9.99 5.00999
1000 Buy 4 9.99 4.343323333

Tim

 

[Art]

Tim,

Okay, I think there is a problem with your formula and I re-wrote it getting
rid of the IF:

=(E1*(SUMPRODUCT((A$1:A1)*(2*(B$1:B1="BUY")-1))-(B2="Sell")*A2)+C2*(B2="Buy")*A2+D2)/SUMPRODUCT((A$1:A2)*(2*(B$1:B2="BUY")-1))

The thing is in the "else" part of your formula I don't think you can use
A1, you need the total number of the shares you're holding. This starts to
get even longer. So what I did was to take out the IF. Instead I used
(B2="Buy") or (B2="Sell") in order to get 1 or 0 in the formula. So if it's
a buy, it's the same as your formula. If it's a sell, it mulitplies the
share price by zero, and also doesn't add in the latest count when doing the
average.

The other thing I think you need to do is to change E1 to include the
commission, so it would be E1=C1+D1/A1.

That is, if I understand what you're trying to do.

 

[Tim]

Art,
Thanks for re-writing it. I think we're almost there. I think there might
be a small bug in the formula. Maybe you don't understand totally what I'm
try to do. I'll explain. First off, here is what I get using your new
formula:
1000 Buy 5 9.99 5.00999
500 Sell 6 9.99 5.02997
1000 Buy 4 9.99 4.34998

Now, if we do the calculation long-hand for the 3 E cells, this is what we
get. By the way, E1 does look correct:
5 + 9.99 / 1000
5 + .00999
5.00999

E2 looks incorrect to me. The calculation I want is to only add the
commission (9.99) to the last average since it's a sell transaction, so the
average should increase by only 1 cent, not 2 cents.
5 + 9.99 (1st commission) + 9.99 (2nd commision) / 1000
5 + 19.98 / 1000
5 + .01998
5.01998
The reason I want it this way is that my only expense when doing a sell
transaction is the commission. The amount of shares does not matter since
they are already accounted for in the buy transaction done previously. I
hope you understand what I mean.

As for E3, I can't say whether it's correct or not...the formula's a bit too
complicated for me, but I'm assuming it's correct.

Tim

 

[Art]

Tim,

I'm not really understanding this. Suppose you had 2 sells in a row. In
order to figure out the per share cost of the commission do you ignore both
of the sells and only go back to the amount of shares you had as of your last
buy?

If so, the formula will start to get much worse (I think). Could you either
refer me to something with the math that's used in this calculation or
perhaps a longer example with the desired results calculated by hand?

 

[Tim]

Art,

Ok, let's take it from the top. Maybe what I'm trying to do at any give
stage here is technically wrong, and if so, please feel free to correct me.
There's no point doing this if we can't do it correctly. I'll try to explain
it in more detail. As you know, the whole point of what I'm trying to do is
to keep track of my average cost per share. If it's a buy transaction, the
average cost calculation would be something like

(Shares Purchased * Price) + Commission / Shares Purchased

or as you put it in your formula

Shares + commission / Price (since the division is done first)

So now we have the average cost stored in a cell on row 1.

If we have another buy transaction, we need a weighted average calculated
since the price and shares of the 2nd buy are most likely different than the
previous buy. I believe SUMPRODUCT handles this well, so no need for me to
extrapolate further.

So now we have a new average cost stored in a cell on row 2.

Now the tricky part. Let's say we have a sell now. This is where I need
your help. My logic in thinking about this is that since my only cost on a
sell transaction is the commission, I only need the commission accounted for
in the new average cost. I don't believe the amount of shares really
matters. Is this method correct? So to continue, since I already have the
average cost of the last transaction stored in the previous row, I would
think it would be fairly easy to include the commission cost into the average
cost calculation. If it's any help, we also know the total amount of shares
held before the sell, although we are not explicitly storing these in a cell
to refer to.

So prior to the sell I have 1500 shares, at an average cost of 5.09
Now I sell 500 shares at a price of 6 with a commission cost of 9.99
The average cost on the sell is

commission of 9.99 / 500 shares
= .01998

I would think the correct way to incorporate this amount into the weighted
average is to simply add it to the previous row average cost. (But my caveat
is I'm now sure this is the correct way to handle this. Maybe shares on a
sell really do matter. What do you think?)
Previous row average cost 5.09
Current row average cost .01998
New average cost 5.09 + .01998
5.10998

And we store this new average cost in a cell on row 3.

If another sell takes place immediately after the prior sell, again, my only
cost is the commission, so I can use the previous row average cost and add in
my commission cost per share as in row 3.

I hope that makes sense now. I was thinking that since we always have the
average cost available and the weight of that average cost based on the
number of shares owned at any given stage, we can always calculate the new
average cost fairly easily. I'm sorry to give you so much trouble on this. I
didn't realize the formula would get that complicated.

Tim

 

[Art]

Tim,

I spoke with the treasurer of the company I work for -- she does these sorts
of calculations all the time. She told me that the commission on the sale
should not be included. Since I've rewritten the formula without the "If" we
should leave it that way. I think the new formula now comes out to:

E2=(E1*(SUMPRODUCT((A$1:A1)*(2*(B$1:B1="BUY")-1))-(B2="Sell")*A2)+(B2="Buy")*(C2*A2+D2))/SUMPRODUCT((A$1:A2)*(2*(B$1:B2="BUY")-1))

Assuming that the commission on sales should not be taken into account, see
if this gives you the correct answers. If so, it's probably right.

Good luck!

 

[Tim]

Art,

That's fine. If somebody in the know has the answer, then I'm happy with
that. There is still one problem with the formula. Using the examples that
we have been using throughout, I then added a sell transaction for 1500
shares, since this will zero out all the buys. The formula returns #DIV/0!
in E4.

 

[Art]

Try this:

E2=
IF(SUMPRODUCT((A$1:A2)*(2*(B$1:B2="BUY")-1))=0,0,(E1*(SUMPRODUCT((A$1:A1)*(2*(B$1:B1="BUY")-1))-(B2="Sell")*A2)+(B2="Buy")*(C2*A2+D2))/SUMPRODUCT((A$1:A2)*(2*(B$1:B2="BUY")-1)))

It just keeps getting worse and worse.

 

[Tim]

Art,

Your formula doesn't work. Just because I sell all the shares, doesn't mean
the average is 0. Thanks for your effort anyway. I will find another way to
calculate it.