HI All,
I would be greatful if someone can show me the easiest way to calculate drawdowns on excel.
I have a return series for a product back to 2001 in a column.
I am trying to calculate the top 10 drawdowns on a monthly basis.
What would be the best way to go about this?
Thanks in advance.
p.s I am looking for a clean solution that does not clutter the workbook. So preferable nothing that requires say having another row of data and taking max min functions of that.
If there is one clean drawdown function on excel that would be awesome but it does not look like It as I cant see it anyhwhere.
THanks
what do you mean by drawdown? Like a capital call?
You’ll have to take the returns and generate an equity curve. From there you can take the equity value at each interval and compare it to prior max to get the drawdown. You can create a function to do this but there’s no way around doing the calculation.
not a capital call.
Essentially looking at the maximum loss from a peak to trough of a portfolio before a new peak is attained. It’s expressed in % (e.g -20%) is a 20% drawdown. It’s a commonly used analytic in the hedge fund space, analysts like to see, obviously hoping not to see many large drawdowns in a company’s history.
I have a return series of monthly data going back to 1995 and I wanted to find the quickest way of seeing the 10 worst drawdowns.
I’ve got an old VBA macro I wrote a while back for a user defined function to calc max drawdowns… I’m out and about at the moment but will copy and paste when im back in front of my screen.
I think the question was already answered…
Yep, no way around doing the calculation. The easiest way requires adding a few columns of data (one to generate an equity curve from returns, another one to track the max value to-date, another one to track the current value as a proportion of max value - 1 (which is the drawdown). if your data is not monthly and you need monthly values, then you may need a few extra columns to pick out month end points too.
It’s really not that hard.
If you want to keep your sheet looking neater, one way to do this is to hide the intermediate columns for the intermediate calculations. The other way is to write a VBA function to do all the intermediary calcs for you. There may be other ways as well. Each of those has drawbacks. The hidden column ones will cause annoy people if someone else wants to audit your book and they don’t know about the hidden columns and have to go looking for them. VBA will have your end users continuously clicking on the “This excel sheet might contain viruses” warning that microsoft pops up.
Personally, I’d just put the columns in - or if you are printing the result and don’t want intermediate stuff, then maybe try the hidden columns technique.
Run this user defined function on a range containing your equity curve and it will calculate the percentage Max DrawDown. Once calculated it’s probably best to copy the result and “paste as number” so that the function doesn’t keep recalculating - it can slow things up a bit of you have long equity curve history or you are running it in multiple cells. Function MDD(theRange As Range) As Double MDD = 0 Dim i As Long Dim j As Long Dim k As Long k = theRange.Columns.Count For i = 1 To theRange.Rows.Count For j = i + 1 To theRange.Rows.Count If (theRange.Cells(i, k).Value - theRange.Cells(j, k).Value) / theRange.Cells(i, k).Value > MDD Then MDD = (theRange.Cells(i, k).Value - theRange.Cells(j, k).Value) / theRange.Cells(i, k).Value Next j Next i End Function
To all the above,
Thank you very much for taking the time to write your answers / paste your VBA. Much appreciated. You’re all Samaritans! 