Drawdown Analysis
 

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!!!

Drawdown Analysis
 

What happened to my first response??????

Drawdown Analysis
 

Natali

First response seems to have gone missing

Assuming that the data is in cells A1:C15, enter the following formula

B2: Format, Conditonal Formatting, Formula Is; =AND($B20,$B3<0), select format. Copy down Column B format only
D2: =IF(AND(B10,B2<0),C1,D1
E2: =IF(AND(B20,B1<0),C1/D2-1,
F2: =C2=MAX($C1:C$2
G2: =IF(AND(F2,NOT(F1)),1,0
H2: =IF(AND(NOT(F2),F1),0,H1+1
I2: =IF(G2,G2+H2,""

Copy down to row 15

1) Formatted entries in Column
2) Result in
3) Column G - entry will be
4) Column I. Don't worry about the entry in I2 as this should probably be removed

Some of the items should be tidied up (as in Column G)

I am not sure what you want in item 5. Email me directly at if you want to persue. I can send you a copy of the test file I created if required

Ton

----- Natalie wrote: ----

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 % Cu
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!!