SQL Server maximum drawdown of asset returns

MAXDD2

Updated: 09 May 2014

Use MAXDD2 to calculate the maximum drawdown based on net asset or portfolio returns. The inputs into the function are dates and returns and the maximum drawdown is calculated as the largest percentage drop in the asset values from peak to trough in chronological order.

Syntax

Arguments

@PDate

The date associated with @PValue. @PDate is an expression of type datetime or of a type that can be implicitly converted to datetime.

@PValue

the return value. @PValue is an expression of type float or of a type that can be implicitly converted to float.

Return Type

float

Remarks

·Returns are calculated by sorting @Pvalue in chronological order based on @Pdate, calculating the cumulative return and calculating the largest percentage drop from peak to trough.

In this example we have portfolio returns from 2012-01-31 to 2014-12-31 and we want to calculate the maximum drawdown.

SELECT

wct.MAXDD2(pdate,pvalue)as [Max Drawdown]

FROM (

VALUES

('2012-01-31',-0.063351),

('2012-02-29',0.005075),

('2012-03-31',-0.005826),

('2012-04-30',0.105978),

('2012-05-31',-0.178133),

('2012-06-30',-0.031485),

('2012-07-31',-0.117386),

('2012-08-31',0.365053),

('2012-09-30',-0.001842),

('2012-10-31',-0.046868),

('2012-11-30',-0.014326),

('2012-12-31',0.289404),

('2013-01-31',-0.030388),

('2013-02-28',-0.272956),

('2013-03-31',-0.297213),

('2013-04-30',0.663182),

('2013-05-31',-0.119142),

('2013-06-30',-0.193599),

('2013-07-31',0.100716),

('2013-08-31',-0.023643),

('2013-09-30',0.299915),

('2013-10-31',-0.051882),

('2013-11-30',0.412909),

('2013-12-31',-0.134126),

('2014-01-31',-0.119409),

('2014-02-28',-0.132136),

('2014-03-31',0.139014),

('2014-04-30',-0.034963),

('2014-05-31',-0.194723),

('2014-06-30',0.032645),

('2014-07-31',0.061501),

('2014-08-31',0.082088),

('2014-09-30',0.286329),

('2014-10-31',-0.154828),

('2014-11-30',-0.064807),

('2014-12-31',-0.190373)

)n(pdate,pvalue)

This produces the following result.

Max Drawdown

----------------------

-0.504569891864632

Here is a graphical representation of the solution:

As indicated by the graph, the monthly returns are turned into cumulative returns and the maximum drawdown is calculated as the largest percentage drop from a peak cumulative return to its trough. In this case the peak was on 2012-12-31 when the cumulative return was 20.03% and the trough was on 2013-03-31 when the cumulative return was -40.534%, producing a peak-to-trough drawdown of -50.457%.