Investment Max Drawdown (1-Cell) Formula without VBA

**thecrell** · 11-09-2012, 09:15 AM

Hi Guys, this is my first post so I apologise for anything incorrect.

I'm almost certain that this isn't possible to do without the use of VBA (Which I have developed a code for) but I would like to see if excels in built functions on arrays will make it possible. See the attached:

Running Drawdown Monitor..xlsx

As you can see in the attachment, I want to calculate the Maximum Drawdown of an investment (defined as the largest % drop from a local maximum before recovery), and as you can see, I have done it (cell F5) with the help of two helper columns (the 3rd is just for graphing and isnt important). The only necessary columns are the two performance columns (B:C) and the NAV (D). I have been wondering if it is possible to make this calculation possible in just one cell...

So if i am not mistaken, this problem boils down to - can I make an IF formula work on an array of arrays?

My belief is NO , not without VBA due to the process requiring looping, but I thought I would consult the Elders before I give up as I have never looked into iterations and circular references in excel and if they could be applicable here

Alternatively any further simplification would be cool.

Like I said though, I already have a VBA solution to it, but I like to avoid VBA where possible as it tends to scare some people.

Thanks for any help you can offer,

Thecrell

**Ace_XL** · 11-09-2012, 06:31 PM

A different approach and you can do away with one helper column and the array formulas..

Calculate drawdown by using in Row 11

=MIN((D11-MAX($D$11:D11))/MAX($D$11:D11),0)

Copy down..See Column H of attached

Does this help?

**thecrell** · 11-12-2012, 06:25 AM

Thanks for that, Ace. I didnt see that more elegant way of doing it when I put it together, very helpful

**philipr** · 02-25-2013, 06:38 PM

Hi, is it possible to calculate the max drawdown in the one cell, simply using the monthly return column without using a separate helper column

**miksnake** · 04-10-2013, 05:04 PM

I also would like to know if it's possible to do this in one formula in one cell as well. I haven't come up with a solution. The reason I don't want to use a helper column is that I would like to calculate maximum drawdown with different starting dates, which would require multiple helper columns.

By the way, an even simpler formula for the above excel spreadsheet is just =D11/MAX($D$11:D11)-1.

Thanks if anybody knows a way to loop this into one cell.

**arandall18** · 06-26-2013, 11:10 AM

Can you please explain how to create the dynamic line color in the drawdown graph?

Thanks!

**phvecchiati** · 11-13-2013, 07:54 PM

Does anybody here knows how could I calculate the Maximum drawdown in my spread sheet?

If you want that I translate the spread sheet, just ask me. Basically, I just need this to complete my spread sheet.

If it is possible without VBA it will be better.

**arlu1201** · 11-14-2013, 01:00 AM

phvecchiati,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.