Home 
Search 
Today's Posts 
#1




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




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




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 (ShiftCtrlEnter) 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




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




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 email "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 (ShiftCtrlEnter) 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




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




Then you take the average.
 Regards, Fred Please reply to newsgroup, not email "Myrna Larson" wrote in message news 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 
Display Modes  

