Trying to make a formula to calculate the Profit and Lose in Forex trades.

[Polat]

Hello Dears,

As in image above : i am trying to make a formula for column J and K:

- As in column "J" i need the Pips In Lose to be calculated in case i type "L" in column "I"
- As in column "K" i need the Pips in Profit to be calculated in case i type "P" in column "I"
- In column "I" when i type BEP (Break Even Point) it should give 0 in both "J" and "K"

So, To calculate the Pips In Lose and Pips In Profit you have to check the following first:

1 - Check if the deal is (Buy/Buy Limit) SAME JOB Or (Sell/Sell Limit) SAME JOB "But Mist type the 4 types in formula"
2- The Price format for example EUR/USD is x.xxxx e.g 1.1600 , and EUR/JPY is xxx.xx e.g 129.00 , and Oil is xx.xx e.g 72.00.

I THINK FOR (1 AND 2) ABOVE, NEEDS TO USE NESTED IF , AND I DON'T KNOW HOW LOL


Now how to calculate Pips In Lose and Pips In Profit ..

* IN case the position is Buy Or Buy Limit :

Pips In Profit = Take Profit - Entry Point.
Pips In Lose = Entry Point - Stope Lose (result must be in minus.)

* In case the position is Sell or Sell Limit :

Pips In Profit = Entry Point - Take Profit.
Pips In Lose = Stope Lose - Entry Point (result must be in minus.)

Hope you help me with the formula to contains all the above cases ,

My Regards.

 

[Polat]

2- The Price format for example EUR/USD is x.xxxx e.g 1.1600 , and EUR/JPY is xxx.xx e.g 129.00 , and Oil is xx.xx e.g 72.00.

IN-addition, i forgot to add the 4ths case which is GOLD Price xxxx.xx1259.00

So now there are 4 prices format to be added in the formula : (X.XXXX AND XXX.XX AND XX.XX AND XXXX.XX)

 

[Polat]

No One expert here to help me ?

 

[Polat]

Up ,
Please , help

 

[Polat]

Up , Please experts help me with the formula !

 

[Polat]

up

 

[Eric W]

The formulas you need aren't that hard. Consider:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
S/N	Symbol	Type	Entry Point	Take Profit	Stop Loss	P/L/BEP?	Pips in Loss	Pips in Profit	Symbol	Multiplier
OIL	Sell Limit	P		OIL
GBP/JPY	Buy Limit	P		GBP/JPY
GBP/JPY	Buy Limit	P		EUR/USD
EUR/USD	Buy	L		EUR/JPY
EUR/JPY	Buy	P		GBP/USD
GBP/USD	Buy	L
EUR/USD	Buy	P
GBP/USD	Buy	L
OIL	Sell Limit	BEP
EUR/JPY	Buy	P

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]74.28[/TD]
[TD="align: right"]72.2[/TD]
[TD="align: right"]74.73[/TD]

[TD="align: right"]20.8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]145.83[/TD]
[TD="align: right"]145.98[/TD]
[TD="align: right"]144.69[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]145.36[/TD]
[TD="align: right"]145.98[/TD]
[TD="align: right"]144.69[/TD]

[TD="align: right"]62[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]10000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]1.165[/TD]
[TD="align: right"]1.1644[/TD]
[TD="align: right"]1.158[/TD]

[TD="align: right"]-70[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]128.95[/TD]
[TD="align: right"]129.31[/TD]
[TD="align: right"]12.3[/TD]

[TD="align: right"]36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]10000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]1.3178[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1.3128[/TD]

[TD="align: right"]-50[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]1.1629[/TD]
[TD="align: right"]1.1644[/TD]
[TD="align: right"]1.158[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]

[TD="align: right"]1.3159[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1.3128[/TD]

[TD="align: right"]-31[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]

[TD="align: right"]74.1[/TD]
[TD="align: right"]74.1[/TD]
[TD="align: right"]74.1[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]128.52[/TD]
[TD="align: right"]129.31[/TD]
[TD="align: right"]127.7[/TD]

[TD="align: right"]79[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet13​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J3[/TH]
[TD="align: left"]=IF(I3="BEP",0,IF(I3="P","",-ABS(H3-F3)*VLOOKUP(D3,$N$3:$O$7,2,0)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K3[/TH]
[TD="align: left"]=IF(I3="BEP",0,IF(I3="L","",ABS(F3-G3)*VLOOKUP(D3,$N$3:$O$7,2,0)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



What is tricky is that what you consider a "pip" varies by symbol. Anywhere from a tenth of a percentage to 1/10000. In order to standardize what you call a pip, I created an additional table with the multiplier needed by symbol. However, even with the table, the results do not match your sample sheet. Some can be explained by rounding, some not. EUR/USD in particular works out different on the 2 examples here.

Hope this helps.

 

[Polat]

Hello , Thanks for replying ,

But adding 2 new columns to my sheet will destroy everything , also i can't add more columns because i am hiding the others column for the design ,

SO asking me to add these columns is so hard.

i just need the formula for existing table .. as i said the formula i need must be in both J and K with those considerations :

* Pips is like Points.
* Buy means Buy market now, Buy Limit and BUy stop are pending commands to be executed auto from aimed prices. at the end did same job
* Sell means Sell Market now, Sell Limit and Sell stop are pending commands to be executed auto from aimed prices. at the end did same job

e.g:
============================================================================

1- Buy Or Buy Limit Or Buy Stop the EUR/USD at :

Entry Point: 1.1600
Take Profit: 1.1650
Stop Lose: 1.1550
if the price goes (UP) to 1.1650 there are (50 pips Profit) different. if the price went (DOWN) to 1.1550 there are (-50 pips Lose)
how we calculate the profit and losefor this trade :
Pips In Profit = Take profit - Entry point * 10000
Pips In Lose = Entry Point - Stop Lose * 10000 (should be in minus).

=================================================================================

2- Sell Or Sell Limit Or Sell Stop the EUR/USD at:

Entry Point: 1.1600
Take Profit: 1.1550
Stop Lose: 1.1650if the price went (DOWN) to 1.1550 there are (50 pips Profit) different. if the price goes (UP) to 1.1650 there are (-50 pips Lose)
how we calculate the profit and losefor this trade :

Pips In Profit = Entry Point - Take Profit * 10000
Pips In Lose = Stop Lose - Entry Point * 10000 (should be in minus).

I multiply with 10000 because the price format here is x.xxxx (1.1600)

NOTICE: Keep in mind the prices format will be in :

- X.XXXX e.g EUR/USD = 1.1600
- XXX.XX e.g EUR/JPY = 129.00
- XX.XX e.g OIL = 72.00
- XXXX.XX e.g GOLD = 1257.00

Means sometimes we have to multiply with 10 or 100 or 1000 depends on price format.

======================================================================
"P" = Profit
"L" = Lose
"BEP" = Break Even Point

Entry Point is F3
Take Profit is G3
Stop Lose is H3
Pips In Profit is K3
Pips In Lose is J3
I3 <-- I will type manually "P" or "L" or "BEP" in it to calculate me the :

If I type "P" Must Auto calculate the Pips in profit with checking the Type deal (buy,buy limit, buy stop, sell, sell limit, sell stop) plus checking the price format.
If i type "L" Must Auto calculate the Pips in lose with checking as the above too.
If I type "BEP" bust give zero in both columns (J3 and K3)

I think everything is clear now , no need to add 2 columns and to destroy my design and everything. There is a formula contains all these cases

USING "NESTED IF FORMULA"

 

[Polat]

Another Notice : Your formula giving is only working for the Deals Type : Buy/Buy Limit/Buy Stop And giving wrong results for the All Sell's Types.

 

[Polat]