Excel formula for max. drawdown

Discussion in 'Trading Software' started by patrickpope, Nov 11, 2002.

  1. Hi all

    am not massively proficient with spreadsheets - and would love it if someone had to hand a ready made formula for returning a figure for maximum drawdown using Excel. I list my daily equity in a column, so I guess it would use some sort of VLOOKUP function .....

    Thankin' you -

  2. Here's how I do it, although there might be a more elegant way of calculating it:

    Assuming Column A contains your equity curve:

    Cell B2 : =max(A2,B1)
    cell C2 : =if(A2=B2,0,MAX(C1,B2-A2)
    cell D2 : =if(and(C1>0,C2=0),C1,"")

    Fill columns B to D down the page next to your equity curve. Column B should give you every maximum drawdown from a new equity high.

    Then you can use the Max(d:d) function to get your biggest drawdown, or large(d,d,5) to get the fifth largest drawdown, etc.

    Hope this helps.
    rb7 likes this.
  3. Ding ! Perfect.

    Zentrader - thank you - I really appreciate that. All seems perfectly obvious now .......