Why are the results illogical? It exactly demonstrates the power of compounding, well, in this case in reverse. As you lose your absolute bet size decreases since you are always risking 1% of your current capital. In other words, you are losing a progressively lower amount. Thus it takes more trades to lose the same percentage of the intial capital.
Cumulative Loss Formula
- Thread starter dima777
- Start date
Why are the results illogical? It exactly demonstrates the power of compounding, well, in this case in reverse. As you lose your absolute bet size decreases since you are always risking 1% of your current capital. In other words, you are losing a progressively lower amount. Thus it takes more trades to lose the same percentage of the intial capital.
Isn't it 100x more satisfying when you work out an answer to a problem, all by yourself, instead of looking it up in a musty old book, or blindly trusting the confident pronouncements of total strangers? (including, of course, total strangers who write books or create web pages or post messages to chat forums). Your answer may be right, it may be wrong, but gosh darn it, it's yours. Now that you've "checked" and "corrected" this formula, you probably have a lot of confidence in it.
By the way, this is the first time I've ever seen the Excel "LOG" function used with two arguments, in an Elite Trader posting. Of course it's perfectly legal in Excel to send two arguments to the LOG function, but it's also highly unorthodox. Congratulations on having the guts to write formulas and get results your way, convention be damned!
Code:
first argument = 1-B1
second argument = (100-A1)/100
BTW..thanks for your compliment regarding my guts lol
------------------------------------------
My formula posted (which is equivalent to MTE's later post btw) shows how many trades to get to the remaining balance as a percentage
of original capital. i.e. 30% of original capital, where CL=30%
ntrades = ln(CL)/[ln(1-lt)]
ntrades = number of trades to get to CL
CL = Cumulative loss
lt = loss/trade
----------------------------------------------
I was thrown off a bit by some of the earlier posts; particularly when you use 50% which gives the same answer in both instances.
What you really wanted was 1-CL, or the amount you would have to lose to get to some percentage of the original capital.
simply change formula to:
ntrades = ln(1-CL)/[ln(1-lt)]
where the variable, lt, is the absolute value of loss each trade.
you'll get the same results as the formula
(can use the ceiling function to round up as well) you posted, which in simple math says:
logyx=n (excel's argument reverses the terms)
here y is base :
where x = 1-CL
y = (100-lt)/100
or alternatively,
(1-lt/100)^n = 1 - CL
the only other difference is you have it so B1 (1-CL) is entered as a raw value reflecting a percentage, yet A1 is entered as the value that is converted to the raw percentage value. Both of my variables are entered directly as raw values reflecting the percentage.
i.e.
=CEILING(LOG(1-F11,1-F10),1)
=CEILING(LN(1-F11)/(LN(1-F10)),1)
should both give same,
where
Fll = final percentage of original principle
F10 = percentage loss per trade
ex :
F10 = loss/trade = 1/100
F11 = final percentage of principle = 30/100
both return 36.
Cheers