Draw down analysis

[Natalie]

I have 3 columns of data: Date (MM-YY), % return for the
month, cumulative % return since 1st date (eg. cul return
in May-02, 1.02809 = 1.01436*(1+1.35%)

Pls refer to the following:

Date % Month % Cul
Start 1.00000
Apr-02 1.44% 1.01436
May-02 1.35% 1.02809
Jun-02 0.23% 1.03043
Jul-02 -0.65% 1.02370
Aug-02 -0.10% 1.02268
Sep-02 -0.30% 1.01966
Oct-02 0.23% 1.02197
Nov-02 0.19% 1.02396
Dec-02 1.65% 1.04085
Jan-03 0.57% 1.04679
Feb-03 0.00% 1.04679
Mar-03 -0.29% 1.04371
Apr-03 2.27% 1.06740

I want to automate the spreadsheet to yield the following:
1. Identify start date of the month that turned from +ve
to -ve (eg. Jul-02 and Mar-03)
2. Cumulative -ve % return starting that date (eg. for
Jul-02 its -1.05 [1.01966/1.3043-1] and Mar-03 its -0.29)
3. Identify the date when cul return recovers to the
level before the drawdown (eg. for Jul-02's drawdown, cul
return only recuperated losses in Dec-02, ie. first date
when cul return is > cul return right before the drawdown)
4. No. of months from the start until the end of the
drawdown (eg. from Jul-02 to Dec-02, it was 6 months; for
Mar-03, it was 1 month)
5. Auto sort the above results (perhaps a named range) by
cul -ve return, in descending order.

I imagine I should create 4 separate columns to hold each
of the results from 1-4.

Pls let me know if you need further clarifications or I
can email whoever a spreadsheet to illustrate. Thank
you!!!

 

[Jonathan Rynd]

I have 3 columns of data:

Looks to me like you have two columns of data, and the Cumulative column
is calculated from the % Month column.

Also it's a little bit inaccurate to refer to it as "cumulative
percentage". More like "Percent of original value".

Pls refer to the following:

You forgot to say that you rounded the percents in the % Month column to
two decimal places.

1. Identify start date of the month that turned from +ve
to -ve (eg. Jul-02 and Mar-03)

note -- nonnegative is *not* the same as positive, you meant nonnegative.

put in column D (starting in row 3) and fill down
=and(B3<0,B2>=0)

will show "TRUE" for the first negative after a string of nonnegatives.
To find the number of the first month this takes place, use
=match(TRUE,d3:d15,0)

Then it's easy to use index() to get the month name.

2. Cumulative -ve % return starting that date (eg. for
Jul-02 its -1.05 [1.01966/1.3043-1] and Mar-03 its -0.29)

I don't understand your math here. Could you explain why you're
subtracting 1 in the Jul-02 case and multiplying by 100 in the Mar-03
case? If you want to be consistent you should be *adding* 1 in both
cases, and not multiplying.

I hesitate to go farther until I'm sure I understand you properly.