Hello, I am a keen Excel user but I do not do macros and all that VB
stuff. I try to get the most out of regular formulae because it is
more transparent and more compact. I need someone like-minded to help
me on a simple financial problem: I have a list of funds with their
monthly returns over several years, and I calculate various statistics
(compounded return, volatility, correlation with benchmark etc?). The
only one that I cannot calculate in one single cell is the maximum
drawdown. A drawdown is the return from peak to trough, and maximum
drawdown is the most you could have lost if you had bought and sold at
the worst possible times.

So on a separate row I start from the first monthly return r(1) and
work my way forward in time, noting d(i) the drawdown for month i:
d(1) = MIN [ 0 , r(1) ] 
d(i) = MIN [ 0 , ( 1 + d(i-1) ) x (1 + r(i) ) ? 1 ]
then I do a MIN of all the d(i)s and that is my maximum drawdown. With
MAXs instead of MINs, I get the bets winning run.

Problems:
- nested MAX/MIN statements do not work in Excel
- d(i) depends on d(i-1)
- whatever the length of the series (say Jan-00 to Jun-06), I need to
be able to choose any two dates (say Jan-03 to Dec-05) and all the
stats only apply to that sub-period => for any month outside the
sub-period d(i)=0

I look at hundreds of funds over many years so my spreadsheet is
becoming unwieldy. Even several cells would be better than a whole new
row for each fund. And once you?ve cracked this one, I would need to
extract the start and end date of the max drawdown period? Again just
a few cells?

Any thought?

---

Request for Question Clarification by maniac-ga on 10 Jul 2006 18:36 PDT

Hello Pdpd,

I am not quite sure what you are asking for. I have a solution using
only formulas, but am not sure how firm your need to "caluclate in one
single cell" the maximum drawdown (without using a macro - Note: I
could provide a macro answer with step by step instructions if
needed).

For example, I created a simple sheet with the following information:

Column A - month
Column B - Value (generated from some random numbers I used for the test)
Column C - R (to be the monthly return)
Column D - D (to be the drawdown)

I also used two named cells
  Sdate - the starting date
  Edate - the ending date

The formula in column D is then (combine the lines into one)
  =IF(Sdate=A2,MIN(0,C2),
  IF(((Sdate<A2)*AND(Edate>=A2)),
  MIN(0,(1+D1)*(1+C2)-1,0),0))
[which I believe captures the intent of your formula]

and then add the min function at the bottom of column D to capture the
maximum drawdown.

Would a solution like this be acceptable or is something more compact
(such as using the macro) be required?

Thanks.
  --Maniac

---

Clarification of Question by pdpd-ga on 31 Jul 2006 01:36 PDT

Hello Maniac, first sorry for the delay in responding, I am only just
back from holidays. I think what you are proposing is really what I am
doing now i.e. for every monthly return calculate an intermediate
formula (your column D) and then take the min of that column. When the
sheet holds return series for hundreds of funds, this broadly doubles
the size of the spreadsheet (dates being common to all series) so what
I am after is a way of eliminating most of (if not all) the
intermediate calculations. Many thanks.

---

Request for Question Clarification by maniac-ga on 31 Jul 2006 16:12 PDT

Hello Pdpd,

I can understand the desire to minimize the size of the worksheet.
Would you consider a macro (or a few) that solves this type of
problem? It does not look like a lot of code and I would provide step
by step instructions (plus commentary on how the code works - in case
you need to change it) for adding it to your worksheet.

  --Maniac