Page 1 of 3 123 LastLast
Results 1 to 10 of 25

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

  1. #1
    New Member
    Join Date
    Jul 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    New Member
    Join Date
    Jul 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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)

  3. #3
    New Member
    Join Date
    Jul 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    No One expert here to help me ?

  4. #4
    New Member
    Join Date
    Jul 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Up ,
    Please , help

  5. #5
    New Member
    Join Date
    Jul 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Up , Please experts help me with the formula !

  6. #6
    New Member
    Join Date
    Jul 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    up

  7. #7
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,730
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

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

    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
    1
    2 S/N Symbol Type Entry Point Take Profit Stop Loss P/L/BEP? Pips in Loss Pips in Profit Symbol Multiplier
    3 1 OIL Sell Limit 74.28 72.2 74.73 P 20.8 OIL 10
    4 2 GBP/JPY Buy Limit 145.83 145.98 144.69 P 15 GBP/JPY 100
    5 3 GBP/JPY Buy Limit 145.36 145.98 144.69 P 62 EUR/USD 10000
    6 4 EUR/USD Buy 1.165 1.1644 1.158 L -70 EUR/JPY 100
    7 5 EUR/JPY Buy 128.95 129.31 12.3 P 36 GBP/USD 10000
    8 6 GBP/USD Buy 1.3178 1.3128 L -50
    9 7 EUR/USD Buy 1.1629 1.1644 1.158 P 15
    10 8 GBP/USD Buy 1.3159 1.3128 L -31
    11 9 OIL Sell Limit 74.1 74.1 74.1 BEP 0 0
    12 10 EUR/JPY Buy 128.52 129.31 127.7 P 79
    Sheet13

    Worksheet Formulas
    Cell Formula
    J3 =IF(I3="BEP",0,IF(I3="P","",-ABS(H3-F3)*VLOOKUP(D3,$N$3:$O$7,2,0)))
    K3 =IF(I3="BEP",0,IF(I3="L","",ABS(F3-G3)*VLOOKUP(D3,$N$3:$O$7,2,0)))



    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.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  8. #8
    New Member
    Join Date
    Jul 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.1650
    if 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"
    Last edited by Polat; Jul 6th, 2018 at 01:20 AM.

  9. #9
    New Member
    Join Date
    Jul 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  10. #10
    New Member
    Join Date
    Jul 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Last edited by Polat; Jul 6th, 2018 at 04:10 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •