How to Calculate Maximum Drawdown

Hi, welcome to the forum

Try this...

	A	B	C
2	$120	Lowest	-90
3	($90)	Highest	120
4	$20
5	$100
6	($80)
7	($60)
8	($60)
9	$110

C2=SMALL($A$2:$A$9,1)
C3=LARGE($A$2:$A$9,1)

I think you'll need to use a helper column.

Data Range

	A	B	C	D	E
1				Max	Min
2	$120	$120		$150	($50)
3	($90)	$30
4	$20	$50
5	$100	$150
6	($80)	$70
7	($60)	$10
8	($60)	($50)
9	$110	$60
10	------	------	------	------	------

This formula entered in B2:

=A2

This formula entered in B3 and copied down:

=B2+A3

Then, for the max and min...

This formula entered in D2:

=MAX(B2:B9)

This formula entered in E2:

=MIN(B2:B9)

If you do not want helper columns try this

=MAX(MMULT(N(TRANSPOSE(ROW(A2:A9))<=ROW(A2:A9)),A2:A9))
and
=MIN(MMULT(N(TRANSPOSE(ROW(A2:A9))<=ROW(A2:A9)),A2:A9))

Both array entered i.e. confirmed with Ctrl+Shift+Enter and not just Enter

Nice formulas!

Good job!

Nice formulae Ace_XL!!

MMULT is a real brain buster for me but this is something else! I've manually constructed the virtual range that you created with "N(TRANSPOSE(ROW(A2:A9))<=ROW(A2:A9)) but can't get the formula to work using the values produced.

Is there a way to lay this out to get the logic straightened out in my mind?

Hey Ron,

I'm not the best at explaining and Sucuri makes an appearance every time I'm trying to post on the forum. But here goes..

The idea is to get a matrix multiplication (MMULT) going.

The matrix of 64 (8 sets of 8 each, since range of data is 8 rows) will start with a single 1 and seven 0s, two 1s and six 0s, three 1s and 5 0s, four 1s and four 0s ....upto all eights 1s. Each of these eight sets will now get multiplied by the respective values in A2:A9 and summed, essentially a SUMPRODUCT(), thereby giving you a result of 8 summed up values. The increasing number of 1s in each set means that the next value in A2:A9 is added to the respective SUMPRODUCT calculation, thereby giving you a cumulative total of the 'x' number of entries depending upon which set you are looking at i.e. 5 values summed in the 5th set, 6 in the 6th and so on..

To create this matrix, the TRANSPOSE(ROW(A2:A9))<=ROW(A2:A9)) comes into picture. The N() simply converts this to 1s or 0s depending upon TRUE or FALSE.

Once you have the calculated array of 8, a MIN/MAX function will give you desired results.

Hope I've explained this well enough. A simpler way of seeing the formula in action is to reduce the range to say only 4 or 5 cells and jump into evaluate formula.

Trust this helps

Looks like I totally missunderstood that 1

Thank you Ace_XL. That is an excellent explanation. I am going to save that and play around with it.

Thank you very much.

Ron W.

@ Ace_XL

Here is the proof that your explanation was right on because even I could do something with it

	A	B	C	D	E	F	G	H	I	J	K	L
29												To make array C30:J37
30	$120		1	0	0	0	0	0	0	0		=N(TRANSPOSE(ROW(B2:B9))<=ROW(B2:B9))
31	-$90		1	1	0	0	0	0	0	0
32	$20		1	1	1	0	0	0	0	0
33	$100		1	1	1	1	0	0	0	0		=MAX(MMULT(C30:J37,A30:A37))
34	-$80		1	1	1	1	1	0	0	0		150
35	-$60		1	1	1	1	1	1	0	0
36	-$60		1	1	1	1	1	1	1	0		=MIN(MMULT(C30:J37,A30:A37))
37	$110		1	1	1	1	1	1	1	1		-50

Another alternative, but volatile:

Row\Col	A	B	C
1	$120	-50	B1: {=MIN(SUBTOTAL(9, OFFSET(A1, 0, 0, ROW(A1:A8)-ROW(A1)+1)))}
2	($90)	150	B2: {=MAX(SUBTOTAL(9, OFFSET(A1, 0, 0, ROW(A1:A8)-ROW(A1)+1)))}
3	$20
4	$100
5	($80)
6	($60)
7	($60)
8	$110

That is amazing! Thanks guys. BUT, I have another question.

Let's say I want to do the exact same thing as before, however, during the summation process, we will only consider the values with an Y in an adjacent cell, and ignore the ones with N.

For example:
+$120 Y
-$90 Y
+$20 N
+$100 Y
-$80 Y
-$60 N
-$60 Y
+$110 Y

By only summing up the values with a Y, we have a total of $100. I would have been at a maximum position with $130, and the minimum would have been -$10. How would I add this filter to what you guys came up with before?

With or without helper columns. Doesnt matter to me. I do like the helper column because it also shows me visually for how long I will stay in negative or positive money. Therefore, how do I sum with a previous cell only if there is a Y next to the value?

Enter the N and Ys in column c and enter this formula in D2 and fill down:

Formula:

Please Login or Register  to view this content.

then for MAX

Formula:

Please Login or Register  to view this content.

then for MIN

Formula:

Please Login or Register  to view this content.

	B	C	D	E	F	G
2	$120	y	120		Max	130
3	-$90	y	30		Min	-10
4	$20	n	30
5	$100	y	130
6	-$80	y	50
7	-$60	n	50
8	-$60	y	-10
9	$110	y	100

Where N is present, the value above it is repeated

You're welcome.

Maybe Ace_XL will have a solution using MMULT. Like the other formula that he provided.

With the MMULT, simply add in a (C2:C9="Y") at the end

=MIN(MMULT(N(TRANSPOSE(ROW(B2:B9))<=ROW(B2:B9)),B2:B9*(C2:C9="Y")))
and
=MAX(MMULT(N(TRANSPOSE(ROW(B2:B9))<=ROW(B2:B9)),B2:B9*(C2:C9="Y")))

Array entered

With this data the MIN formula returns 0 when it should return 7.

Data Range

	B	C	D	E
1			Min	Max
2	-5	N	0	10
3	7	Y
4	-5	N
5	3	Y
6	10	N
7	9	N
8	-1	Y
9	1	Y
10	------	------	------	------

Originally Posted by pimichel

You did a good and an efficient job here....but what if, instead of net values in column A we had (say) the prices of a stock/currency (eg daily close)? I hope I could do this without the helper column as here (which refers to the net difference up or down every day)....

If you are asking a question, please start your own thread. We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread