Drawdown Nightmare

[oskarrakso]

Before I start - sorry for my mistakes in English.

In C1 I would like count drawdown from fixed range (offset from A10), I would like to copy that formula to C2 etc.

If A10 = 3 range would be B1:B4 that's easy (40-20) = 50% drawdown. What if A10 = 6? (Range would be B1:B7. Simple max or mins will not work because lowest value is older than peak.

That drives me crazy. Help please.

 

[oskarrakso]

Anyone could help?

 

[oskarrakso]

Up, still not solved

 

[Alex Blakenburg]

It is not clear from your example what you are trying to do.
Is the minus 20 always the row ?
How are you deciding what other number to use ? What was the expected result of your 2nd Example with the range B1:B7 ?

 

[oskarrakso]

It is not clear from your example what you are trying to do.
Is the minus 20 always the row ?
How are you deciding what other number to use ? What was the expected result of your 2nd Example with the range B1:B7 ?

Hello Alex, Result from range B1:B7(formula typed into C1, and $a$10=6) is 100 --> 19 (81% stop)
Another example - $A$10 = 2, formula typed into C2. (Range is C2:C4(C2+A10), result is 0.

 

[Alex Blakenburg]

I afraid that unless you are able to explain the actual logic, I am not going to be able to provide a way of doing it in Excel.
(I am also guessing that the C2:C4 is meant to be B2:B4)

 

[oskarrakso]

I afraid that unless you are able to explain the actual logic, I am not going to be able to provide a way of doing it in Excel.
(I am also guessing that the C2:C4 is meant to be B2:B4)

Yes sorry about that. Basically this formula need to find biggest possibile percentage drop. It cannot go back on time.

 

[Alex Blakenburg]

See if this helps:

How to Calculate Maximum Drawdown in Excel - Trading Heroes

Learn how to calculate maximum trading drawdown in Excel. Also learn how to use max drawdown and how it can help you become a better trader.

www.tradingheroes.com

 

[oskarrakso]

See if this helps:

How to Calculate Maximum Drawdown in Excel - Trading Heroes

Learn how to calculate maximum trading drawdown in Excel. Also learn how to use max drawdown and how it can help you become a better trader.

www.tradingheroes.com

No, Ive looked everywhere... Even gpt4 couldnt solve this problem.

 

[Alex Blakenburg]

See if this is along the lines of what you are after:

20231204 Drawdown calc oskarrakso.xlsx
	A	B	C
1	New Data	20	81.0%
2	Older -1	40
3	Older -2	19
4	Older -3	19
5	Older -4	100
6	Older -5	100
7	Older -6	2
8
9	offset
10	6
Sheet1

Cell Formulas
Range	Formula
C1	C1	=LET(rInit,B$1:B$7, rEnd,INDEX(rInit,$A$10), rFull,B$1:rEnd, maxVal,MAX(rFull), rowMax,MATCH(maxVal,rFull,0), minVal,MIN(B$1:INDEX(rInit,rowMax)), (maxVal-minVal)/maxVal)