If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Profit and Loss Formula
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Profit and Loss Formula

#1
January 25th 06, 08:11 PM posted to microsoft.public.excel.worksheet.functions
 carl external usenet poster Posts: n/a
Profit and Loss Formula

I have my data like so:

ColA ColB ColC ColD ColE ColF
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.

#2
January 25th 06, 09:32 PM posted to microsoft.public.excel.worksheet.functions
 Kevin Vaughn external usenet poster Posts: n/a
Profit and Loss Formula

I couldn't really follow everything you were saying, but the following
formula seems to work, except the quantity needs to be 1000 rather than 10.
Or it might mean the formulae results needs to be multiplied by 100. Since I
was totally guessing, you'll probably want to check to make sure that this
really is what you intended.

=IF(D2="sell",(E2*F2)-(E2*B2),(E2*A2)-(E2*F2))
--
Kevin Vaughn

"carl" wrote:

> I have my data like so:
>
> ColA ColB ColC ColD ColE ColF
> 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.
>
>

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Profit & Loss Account Costs shown as a percentage of Sales Dritz Excel Discussion (Misc queries) 2 September 9th 05 10:17 AM Calculating Profit and Loss in one column Dismal Excel Discussion (Misc queries) 0 June 11th 05 02:32 AM Calculating Profit and Loss in one column Dismal Excel Discussion (Misc queries) 0 June 7th 05 02:14 PM Calculating Profit and Loss in one column Dismal Excel Worksheet Functions 0 June 6th 05 02:18 AM Line or bar graphs for tracking stocks profit and loss. Mocity Charts and Charting in Excel 1 January 21st 05 01:21 AM

All times are GMT +1. The time now is 02:14 AM.