How do I calculate the gains/loses made from buying and selling a stock?

[radiotower]

If I bought and sold the same stock for many years, how can I calculate the total gains/loses made from all of the transactions assuming that I have a column containing all of the stock's prices, a column containing all of the buy dates, and a column containing all of the sell dates?

Cross posts: http://www.msofficeforums.com/excel/...oses-made.html
http://www.mrexcel.com/forum/excel-q...ing-stock.html

[Richard Buttrey]

Hi,

You've not uploaded an example workbook so that we know your layout, but add the costs of all the buy trades and deduct it from the receipts from all the sell trades.

To see a running total assuming the costs are listed starting in A2 and the receipts trades in B2, then in C2 use

Please Login or Register  to view this content.

[guru0008]

Hi,

To calculate gain/losses you need to have column identifying the stock sold on a date , so that you can lookup what prices on the date you bought and date of sale, once we get both prices we can use the logic (price sold - price buy )

[guru0008]

Hi,

To calculate gain/losses you need to have column identifying the stock sold on a date , so that you can lookup what prices on the date you bought and date of sale, once we get both prices we can use the logic (price sold - price buy )

Please provide your data example

[radiotower]

Hi,

You've not uploaded an example workbook so that we know your layout, but add the costs of all the buy trades and deduct it from the receipts from all the sell trades.

To see a running total assuming the costs are listed starting in A2 and the receipts trades in B2, then in C2 use

Please Login or Register  to view this content.

I attached a sample from my large spreadsheet. Essentially, any time you see a "1," that is the date I bought the stock. I would need to calculate the percent change of the stock price from that date until the date the stock was sold, which is represented by a "-1." As you can see, the stock was bought and sold many times over the years. So I would need to calculate all of the gains/loses to determine whether the decisions would have yielded an overall positive result (gain).

[Logit]

Formulas ( all simple, nothing compounded, etc) :

In E3, paste and copy down :

Please Login or Register  to view this content.

In F4, paste and copy down :

Please Login or Register  to view this content.

In G3, paste and copy down :

Please Login or Register  to view this content.

In H3, paste and copy down :

Please Login or Register  to view this content.

In I3, paste and copy down :

Please Login or Register  to view this content.

[radiotower]

Formulas ( all simple, nothing compounded, etc) :

In E3, paste and copy down :

Please Login or Register  to view this content.

In F4, paste and copy down :

Please Login or Register  to view this content.

In G3, paste and copy down :

Please Login or Register  to view this content.

In H3, paste and copy down :

Please Login or Register  to view this content.

In I3, paste and copy down :

Please Login or Register  to view this content.

This approach assumes that I held the stock the entire time. In my case, I bought it and then sold it many times over the years.

[SPARKY347]

As mentioned above previously. You have to indicate every time you bought and sold. And also how many units you bought and sold on that date. Also you need a starting point on how much money you started with? Seems like something your advisor or bank should have helped you with though. Orherwise stock tracking may be difficult for you?

[radiotower]

I solved the problem.