Hello this is Mark from tradinformed.com and welcome to this video on how to
calculate drawdown in Excel - for traders. this video is part of a series that I am
doing on the most important trading metrics both to analyze your trading
performance and also to help you develop new trading strategies and backtest them
drawdown may well be the most important a single most important trading metric
because this is the one that makes us abandon our trading strategies both if
we have a succession of too many large losses and also if our losses last for
too long so I'm going to calculate drawdown in two different ways and show
you how you can do that in Excel the first of these is drawdown expressed as
a percentage from the previous highest point in our capital and the second of
these is drawdown duration this is how long in time our drawdown has lasted
from peak to peak so we're going to calculate that in the spreadsheet that
is on the screen at the moment and this is a Tradinformed backtest model and we
can do all sorts of different analysis in this model and I'm gonna click on to
here which gives us the thing that we need to start analyzing drawdown and
that is capital this is our capital over time so as each period time period
passes our capital will get higher and lower depending on our trading strategy
performance so first of all we calculate drawdown the first thing we need to do
is take this current period and divide it by the
previous maximum and I'm going to do this as the maximum of all the previous
periods and we can do that in Excel by this formula here equals current period
divided by the previous max and if we press f4 we make this a fixed absolute
reference which will not change if we drag the formula down and I'm going to
express this as a positive percentage so I'm going to put it as 1 minus this
figure and copy that down okay so we've got our drawdown here the next thing we
need to have is our peaks so I'm gonna do very simple if formula we use if
formulas for any sort of logical test in any sort of programming and particularly
in Excel so if current capital is greater than previous value then we're
going to take that so that's going to be our peak up until that point otherwise
we will take our previous value so a nice easy if statement there copy you
down now we have a column that is just showing our peak value at any point in
time then I'm going to use this third column here which is going to calculate
the time in between each peak again I'm gonna start off with a nice if logical
statement and I'm going to say that if a current capital equals the peak in other
words if we are currently at a peak then there is no time at all because we are
at a peak but in every other situation I'm going to add one
so there are two very easy if statements and copy them down now I've got the
value here in a row and what I'm going to do is we always want to automate our
Excel spreadsheets so we don't have to mess around changing ranges so what I've
done already is put these into a range and if you don't currently use ranges
named ranges in Excel then I recommend you do do that they're very easy to use
I've got one here I've already named it as drawdown and the way I do it and the
way you could do it is using the offset function here and we basically offset
from this first point by the amount of values in the row so this will create a
range which will expand when we add new data so now I can go back here and we've
got two cells here that need populating and very easily we know that we're
looking at the maximum drawdown so we use that equals max built-in excel
function and I can just use this named range that I was just describing there
it's called drawdown and it will automatically pull through the maximum
drawdown that we've calculated now if we want to get the maximum drawdown
duration I've done another range for that and we use again the excel
formula equals max if I just start typing this in we can put it in here
= drawdown duration and then we see we have a time period of 690 trading days
where we failed to go any advance on our peak-to-peak and if we look here
at the chart it's not too difficult too see where that may have occurred we're
looking here at 2007 and we just peak above there in 2010
we can express this in years if we like, approximately 250 trading days per year
and so we can see that we're looking at two years something like two years nine
months so we have a long period where this strategy has not made any profit so
that's interesting information however we can see also that we have a flat line
and the flat line here is telling us we didn't take any trades at all during
this period this was the financial crisis 2008 2009 and so this strategy
using a filter of long only trades didn't trade at all but we still have
this information and it is a useful metric for us to know and to understand
I hope you found this video useful and how to calculate drawdown for traders if
you would like more videos like this please remember to subscribe if you
enjoyed this video please hit the like button and for more information about
backtesting in excel and about the financial markets please go to
www.tradinformed.com