Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: formula for average cost per share

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...

    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •