excel求指数最大回撤

在excle中,按Alt+F11打开vba界面，输入下面的代码：

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

然后在需要的单元格内输入=MaxDrawDown(D1:D10)即可。

使用VBA要先启用宏：文件 > 选项 > 信任中心 > 宏设置 > 启用宏， 然后确定就ok啦。