Max Drawdown, portfolio

[aiki100]

Greetings all -


I think I am close to having this right, but certainly am missing something. I am trying to calculate Max Drawdown of a portfolio, and have the following:


CS2 =(running total equity curve)
CT2 =max(CS2,CT1)
CU2 =IFERROR(IF(CS2=CT2,0,MAX(CU1,CT2-CS2)),"")
CV2 =if(and(CU1>0,CU2=0),CU1,"")


I then planned to use =Max(CV:CV) in a final cell to get the largest drawdown.


But something just isn't right….CT and CU appear to be doing what they should, but CV is producing no results at all. And just FYI, there are some blanks in CS, and row 1 are headers.


I am also open to doing this some other way entirely, though I do not wish to use VBA to do it.


Perhaps someone here will catch my mistake and I will be very grateful for the help if you do!


Thanks so much -

 

[JackDanIce]

Difficult to identify solution without the spreadsheet.

What value is contained in CT1, CU1 and CV1?
Can you list examples of values in CS2 and column CS?

If they are not values, can you state the formula then?

Formula in CU2 can be changed to (doubtful this will fix issue though):

=IFERROR(--(CS2=CT2)*MAX(CU1,CT2-CS2),"")

 

[aiki100]

Hello JackDanIce,

I am certainly happy to provide more detail, and thank you for taking a look at this. I have had a problem with the forum add-in tool for posting workbook snippets, so here it is manually:

In CS, there are $ amounts,

CS2= 0
CS3= 0
CS4= 930
CS5= 0
CS6= 0
CS7= 0
CS8=1720
CS9= 0
Etc.

CT2 has the following formula:

=MAX(CS2,CT1)

And produces the following results (also $ amounts):

CT2= 0
CT3= 0
CT4= 930
CT5= 930
CT6= 930
CT7= 930
CT8=1720
CT9= 1720
Etc.

CU2 has the following formula:

=IFERROR(IF(CS2=CT2,0,MAX(CU1,CT2-CS2)),"")

And produces the following (also $ amounts):

CU2= blank
CU3= blank
CU4= 0.00
CU5= blank
CU6= blank
CU7= blank
CU8= 0.00
CU9= blank

...And this makes sense, because there has been no loss yet (any drawdown in this series of rows) in the CS data thus far.

CV2 has the following formula:

=IF(AND(CU1>0,CU2=0),CU1,"")

And produces nothing whatsoever, even when there is a loss in CS and indicated in CU.

Thank you so much for your thoughts! Hopefully this further detail will clarify things.

 

[sheetspread]

You know that "" is not zero, right?

 

[aiki100]

Yes, I do - certainly...But always worth checking, of course, and I thank you for you taking the time to make sure I did indeed know that, just in case...

To the point, do you feel that there is something in that that is causing CV to produce no results in rows where there should be?

Thanks again for your thoughts here...

 

[sheetspread]

I got different numbers from yours in the third column:

Excel 2010
	A	B	C	D
2	0	0	0
3	0	0	0
4	930	930	0
5	0	930	930
6	0	930	930
7	0	930	930
8	1720	1720	0	930
9	0	1720	1720
Sheet16

Cell Formulas
Range	Formula
B2	=MAX(A2,B1)
C2	=IFERROR(IF(A2=B2,0,MAX(C1,B2-A2)),"")
D2	=IF(AND(C1>0,C2=0),C1,"")

 

[aiki100]

Sheetspread -

Thank you again for looking at this. So yes, I redid my columns (more carefully) based on yours and was able to produce what makes more sense now. Thank you! I also believe that what I finally needed was to get the Max of Col C, as that col contains all the drawdowns from equity high. I am not sure why I even have Col D, except that it came along with some research I was doing on the problem.

Do you see the purpose of col D, and why I would wish to keep it? As it is now, it produces no results other than "". I am not certain it is needed or relevant.

Thank you again for your time and thoughts on this - it is so very much appreciated.

 

[sheetspread]

It looks like you need columns B and C to get the "absolute" drawdowns in D, from which you can take a single max.

 

[aiki100]

Thank you Sheetspread - Your help and clarifications are very much appreciated!