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)

[TABLE="width: 164"]
<tbody>[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0.25[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]0.1[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]0.2[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]0.2[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]0.2[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]0.3[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]0.4[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]0.4[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]0.4[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]0.1[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]0.2[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]0.3[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0.3[/TD]
[/TR]
</tbody>[/TABLE]

 

[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