C
Hi everyone,
I have one question regarding the cost of buying stock.
Please see the link for example in my worksheet.
If I buy 100 stocks with price p1 at time t1, then sell 50 stocks. Then I just have got 50 stocks left. Now I buy another 100 stocks with price p2 at time t2, which means I get 150 stocks in my portfolio. Now, I try to sell 70 stocks, which include 50 stocks from buy time t1 and 20 stocks from buy time t2. Consequently I have to pay 50*p1 + 20*p2.
Let's take stock SNE in my worksheet for example.
I bought 100 stocks SNE with price of 650, and sell 20, then 30, then 40, and I got 10 stocks left (which is represented in H23). Now I bought 20 more stocks with price of 599. And I sold 20, which included 10 stocks with price of 650 and 10 stocks with price of 599. Hence, I had to pay 10*650+10*599=12490 as in cell H25 which represents the cost I have to pay.
Actually I hard-coded column H. Does anyone know the formula for column H? Requirements are:
Regarding a stock (for example SNE):
if G25=buy,
then I25=D25*E25
else {
if the remaining stocks from the first buy is greater than D25, I25=D25*(price of the the first buy time)
else I25= Remaining stocks * price of the first buy + (D25-remaining stocks) * price of second buy
}
The work should be done in generally and I have no idea to such a complicated problem. I would very appreciate your help at this moment.
I have one question regarding the cost of buying stock.
Please see the link for example in my worksheet.
If I buy 100 stocks with price p1 at time t1, then sell 50 stocks. Then I just have got 50 stocks left. Now I buy another 100 stocks with price p2 at time t2, which means I get 150 stocks in my portfolio. Now, I try to sell 70 stocks, which include 50 stocks from buy time t1 and 20 stocks from buy time t2. Consequently I have to pay 50*p1 + 20*p2.
Let's take stock SNE in my worksheet for example.
I bought 100 stocks SNE with price of 650, and sell 20, then 30, then 40, and I got 10 stocks left (which is represented in H23). Now I bought 20 more stocks with price of 599. And I sold 20, which included 10 stocks with price of 650 and 10 stocks with price of 599. Hence, I had to pay 10*650+10*599=12490 as in cell H25 which represents the cost I have to pay.
Actually I hard-coded column H. Does anyone know the formula for column H? Requirements are:
Regarding a stock (for example SNE):
if G25=buy,
then I25=D25*E25
else {
if the remaining stocks from the first buy is greater than D25, I25=D25*(price of the the first buy time)
else I25= Remaining stocks * price of the first buy + (D25-remaining stocks) * price of second buy
}
The work should be done in generally and I have no idea to such a complicated problem. I would very appreciate your help at this moment.
