Percentage Formula

[ehsanji]

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.

 

[KRice]

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
	A	B	C	D	E	F
1	Options Trading	"Martingale"
2
3	gain goal/round	$1.00
4	initial investment	$1.00
5	profit margin	90.0%
6
7		Inv	Balance	Balance	q	Q
8	Round	Investment	if Win	if Loss	Round Loss	Cum Loss
9	1	$1.00	$1.90	0	$1.00	$1.00
10	2	$2.22	$4.22	0	$2.22	$3.22
11	3	$3.58	$6.80	0	$3.58	$6.80
12	4	$5.09	$9.67	0	$5.09	$11.89
13	5	$6.77	$12.85	0	$6.77	$18.66
14	6	$8.63	$16.39	0	$8.63	$27.29
ehsanji

Cell Formulas
Range	Formula
B9	B9	=$B$4
C9:C14	C9	=(1+$B$5)*B9
B10:B14	B10	=(E9+$B$3)/$B$5
E9:E14	E9	=B9
F9:F14	F9	=SUM(E$9:E9)

 

[KRice]

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
	A	B	C	D	E	F	G
1	Options Trading	"Martingale"
2
3	gain goal	$1.00
4	initial investment	$1.00	$1.11
5	reward:risk ratio	90.0%
6
7		Inv	Rnd Bal	Rnd Bal	q	Cum Inv =
8	Round	Investment	if Win	if Loss	Round Loss	Cum Loss	Net Win
9	1	$1.00	$1.90	0	$1.00	$1.00	$0.90
10	2	$2.22	$4.22	0	$2.22	$3.22	$1.00
11	3	$4.69	$8.91	0	$4.69	$7.91	$1.00
12	4	$9.90	$18.82	0	$9.90	$17.82	$1.00
13	5	$20.91	$39.73	0	$20.91	$38.73	$1.00
14	6	$44.14	$83.87	0	$44.14	$82.87	$1.00
15	7	$93.18	$177.05	0	$93.18	$176.05	$1.00
ehsanji

Cell Formulas
Range	Formula
C4	C4	=B3/B5
B9	B9	=B4
C9:C15	C9	=(1+$B$5)*B9
B10:B15	B10	=(F9+$B$3)/$B$5
E9:E15	E9	=B9
F9:F15	F9	=SUM(E$9:E9)
G9:G15	G9	=C9-B9-IF(ISNUMBER(F8),F8,0)

 

[ehsanji]

@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]

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:

Dropbox

www.dropbox.com

 

[ehsanji]

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:

Dropbox

www.dropbox.com

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]

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
	I	J	K	L	M	N	O
1	Options Trading	"Martingale"
2
3	gain goal	$1.00
4
5	reward:risk ratio	90.0%
6
7		Round	Rnd Bal	Rnd Bal	Rnd Loss =	Cum Loss =
8	Round	Investment	if Win	if Loss	Rnd Inv	Cum Inv	Net Win
9	1	$1.11	$2.11	0	$1.11	$1.11	$1.00
10	2	$2.35	$4.46	0	$2.35	$3.46	$1.00
11	3	$4.95	$9.41	0	$4.95	$8.41	$1.00
12	4	$10.45	$19.86	0	$10.45	$18.86	$1.00
13	5	$22.07	$41.93	0	$22.07	$40.93	$1.00
14	6	$46.59	$88.53	0	$46.59	$87.53	$1.00
15	7	$98.36	$186.89	0	$98.36	$185.89	$1.00
ehsanji

Cell Formulas
Range	Formula
J9:J15	J9	=(SUM(J$8:J8)+$J$3)/$J$5
K9:K15	K9	=(1+$J$5)*J9
M9:M15	M9	=J9
N9:N15	N9	=SUM(J$9:J9)
O9:O15	O9	=K9-N9

 

[ehsanji]

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.