Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 30th 09, 03:02 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 14
Default Calculate profits on stock sales on FIFO basis

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

  #2   Report Post  
Old July 30th 09, 05:15 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,059
Default Calculate profits on stock sales on FIFO basis

"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

  #3   Report Post  
Old July 30th 09, 06:56 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 14
Default Calculate profits on stock sales on FIFO basis

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

  #4   Report Post  
Old July 30th 09, 09:47 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,059
Default Calculate profits on stock sales on FIFO basis

"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


  #5   Report Post  
Old July 31st 09, 06:41 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 14
Default Calculate profits on stock sales on FIFO basis


"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.



  #6   Report Post  
Old July 31st 09, 07:39 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,059
Default Calculate profits on stock sales on FIFO basis

"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.

  #7   Report Post  
Old February 15th 11, 07:22 PM
Junior Member
 
First recorded activity by ExcelBanter: Feb 2011
Posts: 1
Default

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


Quote:
Originally Posted by JoeU2004 View Post
"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.
  #8   Report Post  
Old March 28th 21, 10:51 PM
Junior Member
 
First recorded activity by ExcelBanter: Feb 2021
Posts: 8
Default

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.

Last edited by vondesiong : March 30th 21 at 08:11 PM


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Summing periodic sales on a rolling basis JohnnStar Excel Worksheet Functions 2 July 21st 06 01:17 PM
How to prepare Multi Stock Valuation on Fifo Basis in Excel md12nov Excel Worksheet Functions 0 April 12th 06 09:09 AM
HELP! Single cell formula to calculate weeks cover of stock on forward sales. [email protected] Excel Worksheet Functions 2 January 13th 06 11:24 AM
How Do I figure sales profits on an excel worksheet? roseanna Excel Discussion (Misc queries) 1 October 17th 05 08:03 PM
Percentage of amounts and profits of individual sales lulubelleshell Excel Worksheet Functions 1 October 12th 05 07:56 PM


All times are GMT +1. The time now is 08:45 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017