Excel (no VBA) Max Drawdown Formula between Two Dates

[tjkramer]

I have two columns - "A" is a list of daily dates for 10 years; "B" is a list of corresponding prices for 10 years. I'd like to be able to create a "Start Date" cell and an "End Date" cell so that I can change the Start/End Dates to specific, different periods and get the max drawdown for that period (not the max 1-day drawdown, but the maximum price drop representing the biggest equity drawdown during that period). I need to be able to change the dates to get max drawdowns for different quarters, different years and different "shock" events (COVID, rate hikes, et. al.).

Many thanks in advance for the patience and help from the MrExcel community.

 

[Leo Skywalker]

Hi there,

How do you get the max drawdown of any specific period?
Do you use a unique formula for the whole range of values of cells in column B?, or do you use another column to perform day to day partial calculations first?, or maybe you have a sub or udf that calculates the max drawdown from given values obtained from each row?

 

[tjkramer]

I'm not "column constrained" on my sheet, so I can add as many columns if needed for sub-calculations. But I've been using the formula in column "C" of =MIN(PriceAtDate2:$PriceAtDate$End)/PriceAtDate1-1. The "MIN" function covers all the dates in the range I need and is pinned ($$) at the end date of the range, so that when I drag the formula down the number of days in the MIN selection decreases. I then have to do another "Min" in a separate cell to get the lowest number from column C, and I'm calling this my Max Drawdown for the period.
So I'm guessing I need some sort of Index/Match to read the Start/Stop dates inside the first MIN formula but I haven't been able to get that to work. I'm also not sure if I have the right Max Drawdown Between Periods calculation - there could be sub-drawdowns or drawdowns across periods that I'm not properly capturing.

I do appreciate your patience and quick response - many thanks!

 

[JamesCanale]

Version of excel?

 

[tjkramer]

Version of excel?

2013 32-bit

 

[Fluff]

2013 32-bit

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[JamesCanale]

This was mostly for my amusement - I got it working for 365. I show it below. It hurts my brain just thinking about converting to 2013 - maybe someone here is more ambitious.

MrExcelPlayground13.xlsx
	A	B	C	D	E
1	Date	Price
2	7/6/2009	10000		Start	1/1/2015
3	7/7/2009	10116		End	12/31/2015
4	7/8/2009	9965
5	7/9/2009	9875		5/4/2015	15534
6	7/10/2009	9939		5/28/2015	14033
7	7/11/2009	9864		Max Drop	1501
8	7/12/2009	9963
9	7/13/2009	10147
10	7/14/2009	9948
11	7/15/2009	10094
12	7/16/2009	10154
13	7/17/2009	10313
14	7/18/2009	10260
15	7/19/2009	10288
16	7/20/2009	10310
17	7/21/2009	10253
18	7/22/2009	10185
19	7/23/2009	10168
20	7/24/2009	10054
21	7/25/2009	10055
22	7/26/2009	10229
23	7/27/2009	10033
24	7/28/2009	10055
25	7/29/2009	10080
26	7/30/2009	10064
27	7/31/2009	10177
28	8/1/2009	10375
Sheet9

Cell Formulas
Range	Formula
D5:E7	D5	=LET(d,FILTER(A2:B3654,(A2:A3654>=E2)*(A2:A3654<=E3)), rr,ROWS(d), s,SEQUENCE(rr), ss,SEQUENCE(1,rr), aa,MOD(-1+SEQUENCE(rr,rr),rr)+s-1, ab,IF(aa<rr,1+aa,""), ac,IFERROR(INDEX(d,s,2)-INDEX(d,ab,2),0), m,MAX(ac), st,SUM(--(ac=m)*s), fn,st+SUM(--(ac=m)*ss)-1, hd,INDEX(d,st,1), hdx,INDEX(d,st,2), ld,INDEX(d,fn,1), ldx,INDEX(d,fn,2), CHOOSE(SEQUENCE(3,2),hd,hdx,ld,ldx,"Max Drop",m))
Dynamic array formulas.

 

[Anthony47]

The first problem is identifying the datas that belong to a certain interval of dates...

Your dates are in column A and the data to process in column B
Use E1 to set the "Starting date" and F1 for the "Ending date"
In E2 and F2 use the following formulas

=IF(E1=0,MIN(A:A),E1)

=IF(F1=0,MAX(A:A),F1)

These are the "modified starting & endig dates"

Now create a "named range":
-call it SLOT
-in the "refers to box" insert the formula

=OFFSET(Sheet1!$A$1,MATCH(Sheet1!$E$2,Sheet1!$A$1:$A$10000)-1,1,1+MATCH(Sheet1!$F$2,Sheet1!$A$1:$A$10000)-MATCH(Sheet1!$E$2,Sheet1!$A$1:$A$10000),1)

This assumes that we are working on Sheet1 with a max of 10k dates; adapt the formula for a different sheet name and a possible longer list of dates, but strictly respect the syntax I used

At this point the range SLOT will refer to column B of the range of dates visible in E2-F2, that reflect the dates set in E1-F1, if they have been set

The result is shown in the green area of the attached XL2BB minishet

At this point if you use (for example) =MIN(SLOT) you will calculate the MIN of the "selected" range; in other words you may use SLOT in your formula to calculate what you need referred to the dates set in E1-F1

BR1 Sales_Ledger RECON(9.2.3) ott-2022.xlsm
	A	B	C	D	E	F	G	H	I
1	Dates	Values			05-feb	10-giu
2	01-gen	1			05-feb	10-giu		Slot Values
3	04-gen	2						4
4	20-gen	3						5
5	25-gen	4						6
6	08-feb	5						7
7	12-feb	6						8
8	04-mar	7						9
9	15-mar	8						10
10	01-apr	9						11
11	15-apr	10						12
12	28-apr	11						13
13	12-mag	12						14
14	19-mag	13						15
15	27-mag	14
16	30-mag	15
17	16-giu	16
18	20-giu	17
19	28-giu	18
20	14-lug	19
21	15-lug	20
22	23-lug	21
23	25-lug	22
24	31-lug	23
Sheet1

Cell Formulas
Range	Formula
E2	E2	=IF(E1=0,MIN(A:A),E1)
F2	F2	=IF(F1=0,MAX(A:A),F1)
H3:H14	H3	=SLOT
Dynamic array formulas.

 

[tjkramer]

The first problem is identifying the datas that belong to a certain interval of dates...

Your dates are in column A and the data to process in column B
Use E1 to set the "Starting date" and F1 for the "Ending date"
In E2 and F2 use the following formulas

=IF(E1=0,MIN(A:A),E1)

=IF(F1=0,MAX(A:A),F1)

These are the "modified starting & endig dates"

Now create a "named range":
-call it SLOT
-in the "refers to box" insert the formula

=OFFSET(Sheet1!$A$1,MATCH(Sheet1!$E$2,Sheet1!$A$1:$A$10000)-1,1,1+MATCH(Sheet1!$F$2,Sheet1!$A$1:$A$10000)-MATCH(Sheet1!$E$2,Sheet1!$A$1:$A$10000),1)

This assumes that we are working on Sheet1 with a max of 10k dates; adapt the formula for a different sheet name and a possible longer list of dates, but strictly respect the syntax I used

At this point the range SLOT will refer to column B of the range of dates visible in E2-F2, that reflect the dates set in E1-F1, if they have been set

The result is shown in the green area of the attached XL2BB minishet

At this point if you use (for example) =MIN(SLOT) you will calculate the MIN of the "selected" range; in other words you may use SLOT in your formula to calculate what you need referred to the dates set in E1-F1

BR1 Sales_Ledger RECON(9.2.3) ott-2022.xlsm
	A	B	C	D	E	F	G	H	I
1	Dates	Values			05-feb	10-giu
2	01-gen	1			05-feb	10-giu		Slot Values
3	04-gen	2						4
4	20-gen	3						5
5	25-gen	4						6
6	08-feb	5						7
7	12-feb	6						8
8	04-mar	7						9
9	15-mar	8						10
10	01-apr	9						11
11	15-apr	10						12
12	28-apr	11						13
13	12-mag	12						14
14	19-mag	13						15
15	27-mag	14
16	30-mag	15
17	16-giu	16
18	20-giu	17
19	28-giu	18
20	14-lug	19
21	15-lug	20
22	23-lug	21
23	25-lug	22
24	31-lug	23
Sheet1

Cell Formulas
Range	Formula
E2	E2	=IF(E1=0,MIN(A:A),E1)
F2	F2	=IF(F1=0,MAX(A:A),F1)
H3:H14	H3	=SLOT
Dynamic array formulas.

 

[tjkramer]

This is great, thanks!
Much appreciated to everyone for the help and patience.
Exactly what I was looking for.

Sincere gratitude for the help!