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
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
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
Sorry, not incorrect but wondering if you could alter to calculate on return stream in opposite date order. Newest to oldest.
Thanks
Matt
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
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
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.