ehsanji

New Member
Joined
Feb 4, 2021
Messages
4
Office Version
  1. 2007
Platform
  1. Windows
Hello

I need to explain myself in details in order to make some sense. Please read this carefully as I desperately need some help.

I am an option trader. On the trading platform, let's suppose I want to invest 1$ as my initial trade, with 90% profit in case if the trade is a winning trade, I will make 1.9$.

In case if I lose this trade, then I'll go with the 2nd trade of 2.22$. Why? Because 2.22$ will help me recover the previously lost 1$ and also help me make another 1$.

I do this up to 6 times unless I win a trade. In case if I win, I go back to the 1$ value and start trading again with one dollar. This strategy is called martingale.

Now, I do all this calculation manually by visiting a website called percentage calculator and also using a calculator on my PC.

I would like to know if I can automate all this.

I want to use a formula in excel that'll require me to change the percentage and my initial investment values only.

Why I want to use excel? Calculating all that manually requires time, and especially during those one minute trades, you don’t have enough time to calculate all that.

Please be informed that I am a newbie when it comes to excel. The only formula I have learned so far is the SUM formula. So please be specific if you're going to help me with this.

Thanks in advance.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
Welcome to the board! Interesting problem...let me know if this looks right. There are three inputs (blue cells): the initial investment, the fixed profit percentage per round, and the gain goal per round (you mentioned that you would like to adjust your next round investment so that if you win, you will gain an extra $1...that is what the this input variable means). I have shown results for each of the six rounds, including your balance if you win or lose...and if you lose, what your cumulative losses are through that round. One thing I am not certain of...when you determine how much to invest in the next round, are you trying to recover the loss from the previous round or the cumulative loss? The formula here assumes the investment amount is based on the previous round's loss amount, so if the answer to my question is cumulative loss, then the formula needs a small modification.

MrExcel20210204.xlsx
ABCDEF
1Options Trading"Martingale"
2
3gain goal/round$1.00
4initial investment$1.00
5profit margin90.0%
6
7InvBalanceBalanceqQ
8RoundInvestmentif Winif LossRound LossCum Loss
91$1.00$1.900$1.00$1.00
102$2.22$4.220$2.22$3.22
113$3.58$6.800$3.58$6.80
124$5.09$9.670$5.09$11.89
135$6.77$12.850$6.77$18.66
146$8.63$16.390$8.63$27.29
ehsanji
Cell Formulas
RangeFormula
B9B9=$B$4
C9:C14C9=(1+$B$5)*B9
B10:B14B10=(E9+$B$3)/$B$5
E9:E14E9=B9
F9:F14F9=SUM(E$9:E9)
 
Last edited:

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
After reading up a little bit on Martingale, I think the answer to my earlier question is that subsequent investment amounts made in rounds 2 and above occur only because a steady series of losing rounds have occurred. And in that case, the investment amount is adjusted to cover the cumulative losses suffered up to that point while also ensuring that an additional amount (the gain goal) will be earned if the current round results in a win. So the formula above needs to be modified slightly, which is done in the example below.

I've also added a Net Winnings column to show the actual profit realized should that round result in a win. Note that round 1 in your example is an anomaly. Investing $1 with a risk:reward ratio of 0.9 means that only $0.90 will be made if round 1 results in a win. If a loss occurs in round 1, then the investment amount for round 2 is adjusted such that a win in round 2 and thereafter will result in the gain goal of $1.00 being realized. With that in mind, one idea to consider is that the initial investment amount for round 1 could be that shown in cell C4 (rather than B4)...and that would result in net winnings of $1.00 (the target amount specified in B3) for every round.
MrExcel20210204.xlsx
ABCDEFG
1Options Trading"Martingale"
2
3gain goal$1.00
4initial investment$1.00$1.11
5reward:risk ratio90.0%
6
7InvRnd BalRnd BalqCum Inv =
8RoundInvestmentif Winif LossRound LossCum LossNet Win
91$1.00$1.900$1.00$1.00$0.90
102$2.22$4.220$2.22$3.22$1.00
113$4.69$8.910$4.69$7.91$1.00
124$9.90$18.820$9.90$17.82$1.00
135$20.91$39.730$20.91$38.73$1.00
146$44.14$83.870$44.14$82.87$1.00
157$93.18$177.050$93.18$176.05$1.00
ehsanji
Cell Formulas
RangeFormula
C4C4=B3/B5
B9B9=B4
C9:C15C9=(1+$B$5)*B9
B10:B15B10=(F9+$B$3)/$B$5
E9:E15E9=B9
F9:F15F9=SUM(E$9:E9)
G9:G15G9=C9-B9-IF(ISNUMBER(F8),F8,0)
 
Last edited:
Solution

ehsanji

New Member
Joined
Feb 4, 2021
Messages
4
Office Version
  1. 2007
Platform
  1. Windows
@KRice I have no words to thank you for taking your time in explaining that to me. Now, I would like to know how I can use that sheet you created in my excel 2007? Can I copy paste it or download it? Or could you be kind enough to upload it somewhere from where I can download it? I need to work on it a little bit to understand it and also to know if I can achieve the results with it? Thanks a lot!
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

You can click on the clipboard icon located in the upper left corner of the spreadsheet (intersection of row and column labels). That copies the posted spreadsheet to your clipboard...and then paste into your own worksheet (typically you would select the same cell seen in the upper left of the posted sheet and then paste). In order to create these small working samples of worksheets for future posts, you'll need to install the XL2BB add-in (see link in my signature block). Once installed correctly, you'll have a new menu item named MrExcel, and you can select a portion of your sheet, click the MrExcel menu item and then select options such as Capture Range and specify Cell Formulas. As an alternative, I've posted the file to my Dropbox account:
 

ehsanji

New Member
Joined
Feb 4, 2021
Messages
4
Office Version
  1. 2007
Platform
  1. Windows
You can click on the clipboard icon located in the upper left corner of the spreadsheet (intersection of row and column labels). That copies the posted spreadsheet to your clipboard...and then paste into your own worksheet (typically you would select the same cell seen in the upper left of the posted sheet and then paste). In order to create these small working samples of worksheets for future posts, you'll need to install the XL2BB add-in (see link in my signature block). Once installed correctly, you'll have a new menu item named MrExcel, and you can select a portion of your sheet, click the MrExcel menu item and then select options such as Capture Range and specify Cell Formulas. As an alternative, I've posted the file to my Dropbox account:
Thanks a lot for the help. You have saved me time and money. I can't thank you enough for the help. You have resolved my issue. Like I said before, I am learning excel so I am in position right now doing all that by myself. With that excel sheet you made for me, now I am able to trade like a pro.
Please let me know if I can add you on a social media network. I would love to have a friend like you added.
Again, Thanks a lot!!!!
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
You're welcome...I'm happy to help. I appreciate the suggestion, but I'm not on social media.

I wanted to follow up with you on the point I made about your initial investment. If your objective is to attempt to win some fixed amount after each round and your reward:risk ratio is known, then your initial investment is not arbitrary...it is determined by those two inputs. Here is a version of the worksheet that uses only those two inputs (blue cells) and the resulting round-by-round investments necessary to win back cumulative losses plus the current round's investment plus the fixed extra goal. If instead you want the first round to be the exception---recognizing that the potential winnings in the first round may not be what is specified in the input cell---then the previous version of the worksheet will be of greater interest.
MrExcel20210204.xlsx
IJKLMNO
1Options Trading"Martingale"
2
3gain goal$1.00
4
5reward:risk ratio90.0%
6
7RoundRnd BalRnd BalRnd Loss =Cum Loss =
8RoundInvestmentif Winif LossRnd InvCum InvNet Win
91$1.11$2.110$1.11$1.11$1.00
102$2.35$4.460$2.35$3.46$1.00
113$4.95$9.410$4.95$8.41$1.00
124$10.45$19.860$10.45$18.86$1.00
135$22.07$41.930$22.07$40.93$1.00
146$46.59$88.530$46.59$87.53$1.00
157$98.36$186.890$98.36$185.89$1.00
ehsanji
Cell Formulas
RangeFormula
J9:J15J9=(SUM(J$8:J8)+$J$3)/$J$5
K9:K15K9=(1+$J$5)*J9
M9:M15M9=J9
N9:N15N9=SUM(J$9:J9)
O9:O15O9=K9-N9
 

ehsanji

New Member
Joined
Feb 4, 2021
Messages
4
Office Version
  1. 2007
Platform
  1. Windows
Okay you have solved my problem. All I needed was for me to able to change the values of my investment, the percentage of a winning trade, and what my next investment value should be in case if I lost the previous trade. That solves my problem pretty much. Thanks a lot from the bottom of my heart. I am also going to upload a video on YouTube to show you how I am benefiting from your provided excel sheet. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,684
Messages
5,637,790
Members
416,982
Latest member
lisam77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top