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.