|
calculate weighted average of stock cost
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 |
calculate weighted average of stock cost
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" wrote: 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 |
calculate weighted average of stock cost
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" wrote: 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" wrote: 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 |
calculate weighted average of stock cost
To use buy & sell instead of 1 and -1, try this:
D2: =IF(B2="buy",(D1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="b uy")-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="b uy")-1))+C3*A3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="buy")-1)),D2) "Tim" wrote: 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" wrote: 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" wrote: 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 |
calculate weighted average of stock cost
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="B UY")-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="B UY")-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" wrote: To use buy & sell instead of 1 and -1, try this: D2: =IF(B2="buy",(D1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="b uy")-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="b uy")-1))+C3*A3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="buy")-1)),D2) "Tim" wrote: 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" wrote: 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" wrote: 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 |
calculate weighted average of stock cost
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" wrote: 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="B UY")-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="B UY")-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" wrote: To use buy & sell instead of 1 and -1, try this: D2: =IF(B2="buy",(D1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="b uy")-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="b uy")-1))+C3*A3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="buy")-1)),D2) "Tim" wrote: 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" wrote: 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" wrote: 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 |
calculate weighted average of stock cost
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="B UY")-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" wrote: 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" wrote: 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="B UY")-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="B UY")-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" wrote: To use buy & sell instead of 1 and -1, try this: D2: =IF(B2="buy",(D1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="b uy")-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="b uy")-1))+C3*A3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="buy")-1)),D2) "Tim" wrote: 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" wrote: 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" wrote: 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 |
calculate weighted average of stock cost
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" wrote: 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="B UY")-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" wrote: 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" wrote: 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="B UY")-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="B UY")-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" wrote: To use buy & sell instead of 1 and -1, try this: D2: =IF(B2="buy",(D1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="b uy")-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="b uy")-1))+C3*A3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="buy")-1)),D2) "Tim" wrote: 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" wrote: 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" wrote: 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 |
calculate weighted average of stock cost
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" wrote: 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" wrote: 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="B UY")-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" wrote: 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" wrote: 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="B UY")-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="B UY")-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" wrote: To use buy & sell instead of 1 and -1, try this: D2: =IF(B2="buy",(D1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="b uy")-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="b uy")-1))+C3*A3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="buy")-1)),D2) "Tim" wrote: 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" wrote: 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" wrote: 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 |
calculate weighted average of stock cost
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" wrote: 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" wrote: 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" wrote: 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="B UY")-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" wrote: 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" wrote: 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="B UY")-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="B UY")-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" wrote: To use buy & sell instead of 1 and -1, try this: D2: =IF(B2="buy",(D1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="b uy")-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="b uy")-1))+C3*A3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="buy")-1)),D2) "Tim" wrote: 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" wrote: 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" wrote: 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 |
calculate weighted average of stock cost
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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="B UY")-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" wrote: 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" wrote: 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="B UY")-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="B UY")-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" wrote: To use buy & sell instead of 1 and -1, try this: D2: =IF(B2="buy",(D1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="b uy")-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="b uy")-1))+C3*A3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="buy")-1)),D2) "Tim" wrote: 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" wrote: 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" wrote: 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 |
calculate weighted average of stock cost
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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="B UY")-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" wrote: 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" wrote: 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="B UY")-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="B UY")-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" wrote: To use buy & sell instead of 1 and -1, try this: D2: =IF(B2="buy",(D1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="b uy")-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="b uy")-1))+C3*A3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="buy")-1)),D2) "Tim" wrote: 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" wrote: 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" wrote: 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 |
calculate weighted average of stock cost
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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="B UY")-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" wrote: 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" wrote: 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="B UY")-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="B UY")-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" wrote: To use buy & sell instead of 1 and -1, try this: D2: =IF(B2="buy",(D1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="b uy")-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="b uy")-1))+C3*A3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="buy")-1)),D2) "Tim" wrote: 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" wrote: 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" wrote: 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 |
calculate weighted average of stock cost
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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="B UY")-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" wrote: 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" wrote: 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="B UY")-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="B UY")-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" wrote: To use buy & sell instead of 1 and -1, try this: D2: =IF(B2="buy",(D1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="b uy")-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="b uy")-1))+C3*A3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="buy")-1)),D2) "Tim" wrote: 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" wrote: 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" wrote: 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. |
calculate weighted average of stock cost
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. "Art" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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="B UY")-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" wrote: 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" wrote: 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="B UY")-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="B UY")-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" wrote: To use buy & sell instead of 1 and -1, try this: D2: =IF(B2="buy",(D1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="b uy")-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="b uy")-1))+C3*A3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="buy")-1)),D2) "Tim" wrote: 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" wrote: 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" wrote: 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 |
| All times are GMT +1. The time now is 05:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com