Hi,

Want to know whether I can use formulas to calculate profit/loss on sales of stocks based on FIFO - First In First Out - or does it need VBA.

I have my stock 'Transactions' sheet set up as below:

Date Stock Action Qty Price Brokerage Trade Value

'Stock' would be short name for the stock involved.
'Action' would be either Buy or Sell.
'Trade Value' would be calculated as Qty*Price+Brokerage if Buy, Qty*Price-Brokerage if Sell.

Ideally, I would like a column after the above columns indicating the gain or loss if Action is Sell. Is it possible using Array/other formulas?

Thanks,
Vivek

Hi Vivek,

Yes, you can definitely use formulas to calculate profit/loss on sales of stocks based on FIFO. You don't need VBA for this.

Here's how you can calculate the gain or loss if the Action is Sell:

1. First, you need to calculate the cost of the shares that you are selling. For this, you can use the FIFO method. To do this, you can use the SUMIF function to add up the cost of the shares that were bought first until you reach the number of shares you are selling. Here's an example formula:
   
   =SUMIF($B$2:B2,B2,$F$2:F2)
   
   In this formula, B2 is the cell containing the stock name, and F2 is the cell containing the trade value. You need to copy this formula down for all the rows in your Transactions sheet.
   
2. Once you have calculated the cost of the shares you are selling, you can subtract it from the trade value to get the gain or loss. Here's an example formula:
   
   =IF(C2="Sell",E2-D2,"")
   
   In this formula, C2 is the cell containing the Action (Buy or Sell), E2 is the cell containing the trade value, and D2 is the cell containing the cost of the shares being sold. This formula will only calculate the gain or loss if the Action is Sell.
   
3. Finally, you can use conditional formatting to highlight the cells with a positive gain in green and the cells with a negative gain in red. To do this, select the cells with the gain/loss values, go to Home Conditional Formatting New Rule, select "Format only cells that contain", choose "Less than" for negative gains and "Greater than" for positive gains, and select the appropriate color.

"Vivek" wrote:
Ideally, I would like a column after the above columns
indicating the gain or loss if Action is Sell. Is it
possible using Array/other formulas?

As you described it, the gain/loss can be calculated on a line-by-line
basis, and it does not rely on FIFO. That is, if qty sold per lot is
entered manually.

I wonder if you omitted part of the problem.

Would you like to enter the total shares sold into some cell, then have a
column ("qty sold") automagically select the number of shares included in
the sale from each lot based on FIFO?

And would you like to enter the total brokerage commissions and fees into
some cell, then automagically distribute those fees into the Brokerage
column per lot sold based on qty sold per lot as a percentage of total
shares sold?

I don't know if/how that can be done with Excel formulas alone; the FIFO
requirement is the trick. I would use VBA.

By the way, you have only one Qty column and only one Price column. You
seem to use them as qty and price sold. (I assume "Brokerage" is the
commissions and fees per lot.) In order to do the FIFO selection
automatically, you also need a column for Qty Held. And in order to
calculate gain/loss, you also need a column for Basis. There is a probably
a template for all of this.


----- original message -----

"Vivek" wrote in message
...
Hi,

Want to know whether I can use formulas to calculate profit/loss on sales of
stocks based on FIFO - First In First Out - or does it need VBA.

I have my stock 'Transactions' sheet set up as below:

Date Stock Action Qty Price Brokerage Trade Value

'Stock' would be short name for the stock involved.
'Action' would be either Buy or Sell.
'Trade Value' would be calculated as Qty*Price+Brokerage if Buy,
Qty*Price-Brokerage if Sell.

Ideally, I would like a column after the above columns indicating the gain
or loss if Action is Sell. Is it possible using Array/other formulas?

Thanks,
Vivek

Would you like to enter the total shares sold into some cell, then have a
column ("qty sold") automagically select the number of shares included in
the sale from each lot based on FIFO?


The 'Action' column will have either 'Buy' or 'Sell'.
If it's Sell, I need the gain made by me on that particular 'Stock' sale based on FIFO.

The sheet could look like this:
Date Stock Action Qty Price Gain
01-01-09 IBM Buy 50 100
01-01-09 GE Buy 100 15
01-02-09 IBM Buy 25 105
01-03-09 IBM Buy 75 110
01-04-09 IBM Sell 100 120 1625 ( 1625 is 50*20 + 25*15 + 25*10 )
01-04-09 ORCL Buy 100 20
01-05-09 IBM Sell 25 130 500 ( 25*20 )


I think let's ignore 'Brokerage' for now as it doesn't seem to be
much of an issue as much as deriving the gain on each sale in
the first place.

I hope this clears it up. Thanks again.



"JoeU2004" wrote in message ...
"Vivek" wrote:
Ideally, I would like a column after the above columns
indicating the gain or loss if Action is Sell. Is it
possible using Array/other formulas?

As you described it, the gain/loss can be calculated on a line-by-line
basis, and it does not rely on FIFO. That is, if qty sold per lot is
entered manually.

I wonder if you omitted part of the problem.

Would you like to enter the total shares sold into some cell, then have a
column ("qty sold") automagically select the number of shares included in
the sale from each lot based on FIFO?

And would you like to enter the total brokerage commissions and fees into
some cell, then automagically distribute those fees into the Brokerage
column per lot sold based on qty sold per lot as a percentage of total
shares sold?

I don't know if/how that can be done with Excel formulas alone; the FIFO
requirement is the trick. I would use VBA.

By the way, you have only one Qty column and only one Price column. You
seem to use them as qty and price sold. (I assume "Brokerage" is the
commissions and fees per lot.) In order to do the FIFO selection
automatically, you also need a column for Qty Held. And in order to
calculate gain/loss, you also need a column for Basis. There is a probably
a template for all of this.


----- original message -----

"Vivek" wrote in message
...
Hi,

Want to know whether I can use formulas to calculate profit/loss on sales of
stocks based on FIFO - First In First Out - or does it need VBA.

I have my stock 'Transactions' sheet set up as below:

Date Stock Action Qty Price Brokerage Trade Value

'Stock' would be short name for the stock involved.
'Action' would be either Buy or Sell.
'Trade Value' would be calculated as Qty*Price+Brokerage if Buy,
Qty*Price-Brokerage if Sell.

Ideally, I would like a column after the above columns indicating the gain
or loss if Action is Sell. Is it possible using Array/other formulas?

Thanks,
Vivek

"Vivek" wrote:
The sheet could look like this:
Date Stock Action Qty Price Gain
01-01-09 IBM Buy 50 100
01-01-09 [....]
01-02-09 IBM Buy 25 105
01-03-09 IBM Buy 75 110
01-04-09 IBM Sell 100 120 1625 ( 1625 is 50*20 + 25*15 +
25*10 )
01-04-09 [....]
01-05-09 IBM Sell 25 130 500 ( 25*20 )

I see now. You maintain records differently than I do. I wonder how you
would handle splits and stock dividends that change basis. Well, maybe
that's your next question ;).

Anyway, returning to your original question....


Is it possible using Array/other formulas?

For your method of recording, I can only imagine doing it using VBA. It may
or may not be challenging to write. That depends, in part, on whether you
would want a macro or a UDF (your original question suggests the latter),
and whether you would want to handle wash sales automagically (gulp!).

Sorry, but I cannot help you further. Maybe someone else has the time.


----- original message -----

"Vivek" wrote in message
...
Would you like to enter the total shares sold into some cell, then have a
column ("qty sold") automagically select the number of shares included in
the sale from each lot based on FIFO?


The 'Action' column will have either 'Buy' or 'Sell'.
If it's Sell, I need the gain made by me on that particular 'Stock' sale
based on FIFO.

The sheet could look like this:
Date Stock Action Qty Price Gain
01-01-09 IBM Buy 50 100
01-01-09 GE Buy 100 15
01-02-09 IBM Buy 25 105
01-03-09 IBM Buy 75 110
01-04-09 IBM Sell 100 120 1625 ( 1625 is 50*20 + 25*15 +
25*10 )
01-04-09 ORCL Buy 100 20
01-05-09 IBM Sell 25 130 500 ( 25*20 )


I think let's ignore 'Brokerage' for now as it doesn't seem to be
much of an issue as much as deriving the gain on each sale in
the first place.

I hope this clears it up. Thanks again.



"JoeU2004" wrote in message
...
"Vivek" wrote:
Ideally, I would like a column after the above columns
indicating the gain or loss if Action is Sell. Is it
possible using Array/other formulas?

As you described it, the gain/loss can be calculated on a line-by-line
basis, and it does not rely on FIFO. That is, if qty sold per lot is
entered manually.

I wonder if you omitted part of the problem.

Would you like to enter the total shares sold into some cell, then have a
column ("qty sold") automagically select the number of shares included in
the sale from each lot based on FIFO?

And would you like to enter the total brokerage commissions and fees into
some cell, then automagically distribute those fees into the Brokerage
column per lot sold based on qty sold per lot as a percentage of total
shares sold?

I don't know if/how that can be done with Excel formulas alone; the FIFO
requirement is the trick. I would use VBA.

By the way, you have only one Qty column and only one Price column. You
seem to use them as qty and price sold. (I assume "Brokerage" is the
commissions and fees per lot.) In order to do the FIFO selection
automatically, you also need a column for Qty Held. And in order to
calculate gain/loss, you also need a column for Basis. There is a
probably
a template for all of this.


----- original message -----

"Vivek" wrote in message
...
Hi,

Want to know whether I can use formulas to calculate profit/loss on sales
of
stocks based on FIFO - First In First Out - or does it need VBA.

I have my stock 'Transactions' sheet set up as below:

Date Stock Action Qty Price Brokerage Trade Value

'Stock' would be short name for the stock involved.
'Action' would be either Buy or Sell.
'Trade Value' would be calculated as Qty*Price+Brokerage if Buy,
Qty*Price-Brokerage if Sell.

Ideally, I would like a column after the above columns indicating the gain
or loss if Action is Sell. Is it possible using Array/other formulas?

Thanks,
Vivek

"JoeU2004" wrote in message ...
"Vivek" wrote:
The sheet could look like this:
Date Stock Action Qty Price Gain
01-01-09 IBM Buy 50 100
01-01-09 [....]
01-02-09 IBM Buy 25 105
01-03-09 IBM Buy 75 110
01-04-09 IBM Sell 100 120 1625 ( 1625 is 50*20 + 25*15 +
25*10 )
01-04-09 [....]
01-05-09 IBM Sell 25 130 500 ( 25*20 )

I see now. You maintain records differently than I do. I wonder how you
would handle splits and stock dividends that change basis. Well, maybe
that's your next question ;).



I don't mind adopting a different format as long as it works.
My current sheet is a mere export from the trade book
of my trading account, not something I've designed!

If you have a template for calculating gains on stock sales
let me know because I couldn't find anything via Google.
If possible you may mail it by deleting DELTETHISNOW and NOTSO
from the address I give in these newsgroups.

Thanks again.

"Vivek" wrote:
If you have a template for calculating gains
on stock sales let me know

Sorry, nothing worth sharing.


----- original message -----

"Vivek" wrote in message
...

"JoeU2004" wrote in message
...
"Vivek" wrote:
The sheet could look like this:
Date Stock Action Qty Price Gain
01-01-09 IBM Buy 50 100
01-01-09 [....]
01-02-09 IBM Buy 25 105
01-03-09 IBM Buy 75 110
01-04-09 IBM Sell 100 120 1625 ( 1625 is 50*20 + 25*15 +
25*10 )
01-04-09 [....]
01-05-09 IBM Sell 25 130 500 ( 25*20 )

I see now. You maintain records differently than I do. I wonder how you
would handle splits and stock dividends that change basis. Well, maybe
that's your next question ;).



I don't mind adopting a different format as long as it works.
My current sheet is a mere export from the trade book
of my trading account, not something I've designed!

If you have a template for calculating gains on stock sales
let me know because I couldn't find anything via Google.
If possible you may mail it by deleting DELTETHISNOW and NOTSO
from the address I give in these newsgroups.

Thanks again.

If u have trading operantions in excel - just upload into http://www.stockfo.com this tool recalculate your operations with fifo, lifo and hifo methods

I need some help with the formulas. I am currently developing my trading strategy for online trading on the fxpro site https://tradersunion.com/brokers/forex/view/fxpro/. And I get confused with some calculations. So I need someone to look at my sheet with fresh eyes. I believe, this way it will be quicker. It will be easier to identify the mistake in this case.