Formula for calculating current drawdown of a portfolio.

[colombodave]

Hi All

I am looking to analyse equity drawdown of a portfolio. A drawdown is the loss from a peak to a trough of a portfolio, before a new peak is attained.

I would like a formula which returns me the current drawdown percentage since the latest peak, and so far I have managed the following:

=(MAX(C$2:C2)-MIN(INDIRECT(CONCATENATE(CONCATENATE("C",TEXT(MATCH(MAX(C$2:C2),C$2:C2,0)+1,"#")),":",CONCATENATE("C",CELL("row",C2))))))/MAX(C$2:C2)
where equity values are listed in column C starting from C2 going down chronologically.

In plain english:
Maximum value (latest peak) - Minimum value since latest peak / Maximum value

I feel like there could be a 'neater' way to write this formula, but don't know where to start. Any suggestions?

Appreciate any help. I include below an example data set (equity in left column, current drawdown in right column)

1	0
2	0
3	0
4	0
3	0.25
6	0
7	0
8	0
9	0
10	0
9	0.1
8	0.2
9	0.2
8	0.2
7	0.3
6	0.4
8	0.4
10	0.4
12	0
14	0
16	0
18	0
20	0
18	0.1
16	0.2
14	0.3
15	0.3

<tbody>
</tbody>

 

[StephenCrump]

Welcome to the Forum!

Another way: =1-MIN(IF(ROW(C$2:C2)>MATCH(MAX(C$2:C2),C$2:C2,)+ROW(C$2)-2,C$2:C2))/MAX(C$2:C2) array-entered