Maximum Drawdown in VBA

This VBA function and the accompanying Excel spreadsheet calculate the maximum drawdown of a series of investment returns.

The maximum drawdown is the largest percentage drop in asset price over a specified time period. In other words, it is the greatest peak-to-trough of the asset returns. It is a measure of downside risk, and is used when calculating the Calmar Ratio.

However, investors need to be careful when making trading decisions based on this downside risk measure.

  • Maximum drawdown only quantifies the worst-possible loss over an actual period of time, and is of limited value when describing future potential losses
  • Investments with a longer history will tend to have larger maximum drawdowns; hence care must be taken when comparing drawdown values for different assets
  • Maximum drawdown needs to be closely allied with the time to recovery when analyzing potential or current investments.  Without the latter, the former loses much of its analytical value

Maximum Drawdown Plot

Function MaxDrawDown(returns As Variant) As Variant
   Dim TS As Variant
   Dim n As Integer
   Dim Min As Double
   TS = returns
   n = UBound(TS)
   Min = 0
   For i = 1 To n
      For j = i To n
         temp = TS(j, 1) / TS(i, 1) - 1
         If temp < Min Then
            Min = temp
         End If
      Next
   Next
   MaxDrawDown = Min
End Function

This Excel spreadsheet uses this VBA function to calculate the maximum drawdown of the S&P500 over the three years from 24th October 2008. It also calculates the Calmar Ratio.

Download Excel Spreadsheet to Calculate Maximum Drawdown in VBA


-->

8 thoughts on “Maximum Drawdown in VBA

  1. Your max drawdown code results in an incorrect answer. Email me and I can send you an excel spreadsheet with the correct answer. I would like to see VBA code that results in the correct answer. Thanks for the work, it is very helpful.

    Matt

  2. Sorry, not incorrect but wondering if you could alter to calculate on return stream in opposite date order. Newest to oldest.

    Thanks

    Matt

  3. Try this formula instead of the VBA. In regular Excel, either get a series of prices or turn a series of percent returns into dollar amounts (say, by multiplying an arbitrary starting value of $1.00 by (1+Return%) then dragging down).

    Now, in the next column AFTER the dollar growth series (or price series, whichever one you need), enter the following formula:

    =IF(C3>C2,MAX($C$2:C3)-C3,D2)

    In this example, my dollar value series was in Column C (with headers), and I automatically made Cell D2 a zero. When dragged down, however, that D2 becomes D3, D4, etc. and will update accordingly via the IF statement.

    Set the formatting of Column D to percentages. Now what you do is to take the MAX of column D. This is your Maximum Drawdown percentage for the period of the data entered by the user.

    This can obviously be done programatically through VBA but wanted it to be accessible to non-programmers.

    Best,

    Victor

  4. Let me correct that formula I posted earlier to the following:

    =IF(C3<C2,((MAX($C$2:C3)-C3)/MAX($C$2:C3)),D2)

    Sorry for the confusion. One other thing to note is that as you experiment with this formula, due to the choppiness of price series, calculating this metric can mean one thing to one person, and something entirely different to another, depending on what their definition of "relative" mins and maxes are within the data sample. This newest formula I provide here gives more attention to the previous high RATHER than the more recent high (i.e. if there are recent increases it still is defeated by the fact that the trend is downward — we are making the assumption that the difference between the LOCAL minima and maxima are irrelevant to the investor's psychological "experience" within the entire data sample examined).

    Also be aware as you play around with this formula that as you spot check percentage changes to ensure the formula is correct, the formula focuses on the largest PERCENTAGE dip, i.e., later in your time series you could end up having a larger DOLLAR drop, but we assume you are interested in how much you dropped in value on a percentage basis.

    Best,

    Victor

  5. Hi, thanks for the nice macro. I’m wondering if the function can be modified to allow for flexible date input. For example, if I want to specify the maximum drawdown calculation for the period between 01-Dec-2008 and 31-DEC-2008, then the macro will not able to do this.

    If this can be improved, it will be perfect. Thanks.

  6. Here is another way to calculate your max drawdown.
    Define your set of returns as “Returns”
    Create the names MaxRet, LocMaxRet, MinRet, & Max_Drawdown as single cells)
    MaxRet =MAX(Returns)
    LocMaxRet {=SUM(IF(MaxRet=Returns,1,0)*ROW(OFFSET($A$1:$A$15,0,0,COUNT(Returns),1)))} This cell is array entered
    MinRet {=MIN(OFFSET(Returns,LocMaxRet,0,COUNT(Returns-LocMaxRet),1))} This cell is a array entered
    Max_Drawdown =MaxRet-MinRet

  7. How would you accommodate this for flexible dates? Say in column A I have 100 dates (100 rows long), and in column B I have 100 prices (100 rows long). Next in column C I have a formula which states, only display the values from column B if the dates in column A fall within a certain allowable range, which is defined by date in values in cells say, D1 and E1.

    For example, column A has the dates for the first 100 trading days of the year, next column B has the prices for the first 100 trading days of the year. Column C follows the above mentioned formula, and lets assume in cells D1 and E1 I have the dates 2/3/2014 and 2/28/2014, so in other words column C displays only the prices of the trading days in February.

    So my problems begins here. The solution seems to be defining a formula using the Name Manager…but that has not worked. An alternate solution that works a good percent of the time was too us a formula to look up the first and last numeric values in this column C and then use a combination of the indirect/address/match/index functions and then use the maxdrawdown function which you defined.

    This is the formula I use:

    =maxdrawdown(INDIRECT(ADDRESS(MATCH(INDEX(W:W,MATCH(TRUE,INDEX(ISNUMBER(W:W),0),0)),W:W,0),23)):INDIRECT(ADDRESS(MATCH(INDEX(W:W,MATCH(9.99999999999999E+307,W:W)),W:W),23)))

    Column W is the column with the time series whose drawdown I want to calculate. This function works for calculating the drawdown of this dynamic column W (or column C as I describe above) except when the last value in the dynamic time series is larger than the first value in the dynamic time series. For example, lets say the column has values 1550, 1560, 1570, 1540, 1490, and 1555 – the formula appropriately calculates the drawdown. Now lets say you have 1550, 1560, 1570, 1540, and 1549 – here the formula does not work. The formula does not work only in cases where last number (1549 above) is smaller than the first number (1550). Another issue I have with the overall approach is that it is taxing my resources/ slowing my spreadsheet down as I have 12 tabs whose drawdowns I am calculating each time alter the desired data ranges (via cells D1 and E1 in the example above).

    Thank you

  8. For the above comment, the phrase “…except when the last value in the dynamic time series is larger than the first value in the dynamic time series” should be say “…except when the last value in the dynamic time series is SMALLER than the first value in the dynamic time series.”

Leave a Reply

Your email address will not be published. Required fields are marked *

What is 11 + 2 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)