formula for average cost per share

[banjanxed]

Hello All,
I know this sounds easy, but I just can't figure out a way to do it accurately. Here's the simplified scenerio: I have column(B) with a current price of a stock, B is updated daily and always has a value. I have a column (C) that has the total number of shares currently held, C can sometimes be zero. I have a buy signal column(K) that is frequently zero, but sometimes gives me a buy signal in number of shares. And I have a sell column (M) that also is frequently zero, but gives sell signals in number of shares. Last I have a total cost of shares column (N) which *tries* to calculate the cost of shares currently held so I can divide that by the current stock price (B) to get average cost per share.
The main problem I have is when I get a sell signal, how do I calculate what amout to subtract form the total cost of shares? I tend to end up with a negative number because I pull shares out at higer prices and put them in at lower prices, so over time the total cost of shares goes negative. There must be a simple solution, but I just can't see it. Any help would be appreciated. Thanks,

Banjanxed

 

[Yogi Anand]

Hi banjanxed:
It appears you may a logic issue in the way you have set up the table. If your purchase price for all the stocks is set up correctly, your average purchase price can not go negative unless you got some stock for free ... even in that case your price can at most be zero. A sample layout of your table and the formulas will be helpful in looking at the problem.

 

[Legacy 98055]

Hi
I'm a little confused?

"which *tries* to calculate the cost of shares currently held so I can divide that by the current stock price (B) to get average cost per share."

Currently held at what price?

Seems you may need a Column 'D' to show the price at which you purchased your current holdings?

If the price changes daily, then how do you derive an average without the cost of the original stock?

Am I missing the point here?

Tom

 

[daleyman]

hey, am glad i never landed that job in investment banking!

 

[daleyman]

are you sure the logic of your process is right, you might want to handle it differently, ie. have a new row for every transaction that holds the price of each buy or sell action together with the quantities, then aggregate this information on a separate summary to show you how much you are up, how many shares you have remaining, etc...

 

[Jay Petrulis]

On 2002-04-14 08:05, banjanxed wrote:
Hello All,
I know this sounds easy, but I just can't figure out a way to do it accurately. Here's the simplified scenerio: I have column(B) with a current price of a stock, B is updated daily and always has a value. I have a column (C) that has the total number of shares currently held, C can sometimes be zero. I have a buy signal column(K) that is frequently zero, but sometimes gives me a buy signal in number of shares. And I have a sell column (M) that also is frequently zero, but gives sell signals in number of shares. Last I have a total cost of shares column (N) which *tries* to calculate the cost of shares currently held so I can divide that by the current stock price (B) to get average cost per share.
The main problem I have is when I get a sell signal, how do I calculate what amout to subtract form the total cost of shares? I tend to end up with a negative number because I pull shares out at higer prices and put them in at lower prices, so over time the total cost of shares goes negative. There must be a simple solution, but I just can't see it. Any help would be appreciated. Thanks,

Banjanxed

Hi Banjanxed,

It is unclear to me exactly what you are trying to calculate. Your average cost is equal to the total cost/shares held. That is independent of the current stock price. If you paid 1/share for 100 shares, and sell 40 of those shares @ 10/share, your average cost is still 1/share.

If you short the postion, it will complicate things, but you need to have a formula for cost removed on sales and include that in your calculation for any purchases. Possibly a column for running totals would be easy to maintain.

HTH,
Jay