Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
carl
 
Posts: n/a
Default Calculating Net Position and Average Price


my table looks like so:

Quantity Price

10 1.50
10 1.50
-5 1.25

Is there a way for me to calculate my net position (+15) and my average
price of my net position.

thank you in advance.



  #2   Report Post  
Myrna Larson
 
Posts: n/a
Default

You can certainly sum the Quantity column to get the net position.

WRT "average price of my net position": I assume this is investment data. What
if the 2nd purchase was at 1.75 rather than 1.50. Did you sell 5 shares from
the 1st purchase or 5 from the 2nd? Do you want the average of 10 shares at
1.50 + 5 at 1.75, or 5 @ 1.50 + 10 @ 1.75? I think you'll need to identify the
shares by lot number and also include a column where you write BUY or SELL. If
you do that, you could set up a pivot table, using the lot numbers and the
transaction type for grouping.


On Fri, 29 Oct 2004 12:26:12 -0700, "carl"
wrote:


my table looks like so:

Quantity Price

10 1.50
10 1.50
-5 1.25

Is there a way for me to calculate my net position (+15) and my average
price of my net position.

thank you in advance.



  #3   Report Post  
mzehr
 
Posts: n/a
Default

Hi Carl
Your net position is the is the sum of the Quantity or =Sum(A2:A4)
The average price is calculated as an array formula (Shift-Ctrl-Enter) as
follows:

=SUM((A2:A4)*(B2:B4))

"carl" wrote:


my table looks like so:

Quantity Price

10 1.50
10 1.50
-5 1.25

Is there a way for me to calculate my net position (+15) and my average
price of my net position.

thank you in advance.



  #4   Report Post  
NHarkawat
 
Posts: n/a
Default

Net Position = sum(a1:A3)
Average price = sumproduct((a1:a3),(b1:b3))/sum(a1:a3)
"carl" wrote in message
...

my table looks like so:

Quantity Price

10 1.50
10 1.50
-5 1.25

Is there a way for me to calculate my net position (+15) and my average
price of my net position.

thank you in advance.





  #5   Report Post  
Fred Smith
 
Posts: n/a
Default

If you are calculating average price for tax purposes, it's only buys
(positive quantities) which affect the average. So use the formulas
suggested only for positive quantities.

In the example you gave, your average cost is $1.50. Even though you sold 5
at $1.25, the cost of the remaining 15 shares is still $1.50 each.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"mzehr" wrote in message
...
Hi Carl
Your net position is the is the sum of the Quantity or =Sum(A2:A4)
The average price is calculated as an array formula (Shift-Ctrl-Enter) as
follows:

=SUM((A2:A4)*(B2:B4))

"carl" wrote:


my table looks like so:

Quantity Price

10 1.50
10 1.50
-5 1.25

Is there a way for me to calculate my net position (+15) and my average
price of my net position.

thank you in advance.







  #6   Report Post  
Myrna Larson
 
Posts: n/a
Default

And if the prices were different on the two purchases?

On Fri, 29 Oct 2004 19:16:48 -0600, "Fred Smith"
wrote:

If you are calculating average price for tax purposes, it's only buys
(positive quantities) which affect the average. So use the formulas
suggested only for positive quantities.

In the example you gave, your average cost is $1.50. Even though you sold 5
at $1.25, the cost of the remaining 15 shares is still $1.50 each.


  #7   Report Post  
Fred Smith
 
Posts: n/a
Default

Then you take the average.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Myrna Larson" wrote in message
...
And if the prices were different on the two purchases?

On Fri, 29 Oct 2004 19:16:48 -0600, "Fred Smith"
wrote:

If you are calculating average price for tax purposes, it's only buys
(positive quantities) which affect the average. So use the formulas
suggested only for positive quantities.

In the example you gave, your average cost is $1.50. Even though you sold
5
at $1.25, the cost of the remaining 15 shares is still $1.50 each.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 03:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"