View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
carl
 
Posts: n/a
Default Profit and Loss Formula

I have my data like so:

ColA ColB ColC ColD ColE ColF
Bid Ask PL Side QTY TradePrice
12.7 12.8 -100 Sell 10 12.7
12.7 12.8 0 Buy 10 12.7
12.7 12.8 100 Sell 10 12.9
12.7 12.8 -200 Buy 10 12.9


ColD thru E has my trade data while ColA and ColB has the realtime market
prices. I am trying to come up with a formula for ColC which will calculate
my Profit or Loss for each trade.

For example on the last line, I bought 10 shares at 12.90. To close out the
position at that moment in time I would need to sell them on the "bid" side
at 12.70, thus creating a $200 loss.

I think the formula needs to look at the trade side. If the trade was a buy,
then the "bid" side of the realtime market is used in the Profit and Loss
Formula. If the trade price is greater than the "bid" price, then there is a
profit. If the trade price is less than the "bid" price, then there is a loss
- as in the example.

Similar but opposite for trade side = "sell". The formula will need to look
at the "ask" side of the real time market. If the trade price is greater than
the "ask" price, then there is a gain. If the trade price is less than the
ask price, there is a loss.

Thank you in advance.