How to Calculate Maximum Drawdown in Excel

Maximum drawdown is an important trading statistic that you should know in your backtesting and live trading. In backtesting, it shows you the downside risk of a strategy. Tracking max drawdown in live trading helps you understand when your strategy might not be working as expected.

Maximum drawdown (MDD) is calculated in percent, and is the most that your account has lost between high watermarks. In order to get your maximum drawdown, calculate your running percent profit and loss total, then use the Excel MIN function to get the maximum drawdown, which is the most negative number. Even if there is currently no new high watermark, but your current drawdown is greater than previous maximum drawdowns, then your current drawdown can be used as your maximum drawdown.  

Here's a video to show you maximum drawdown in action. If you prefer the text version, it's provided below the video.

Step-By-Step Guide to Calculating Maximum Trading Drawdown in Excel

This is the exact process for calculating max drawdown in Excel.

You can use similar formulas in spreadsheet programs like:

• Mac Numbers
• Google Sheets
• OpenOffice

First import your trades into Excel. I'll be using an export from Forex Tester to analyze my backtesting results. You can learn more about Forex Tester here.

Go to: File > Import. Then find your file.

Next, create a column called Balance and add the profit from each trade to the running balance. The formula is shown here.

In the next column, create a percent profit or loss for each trade. Since max drawdown should be calculated in percent, we have to figure out the percent change on each trade.

Then add the percent profit or loss in the next column to create a running total.

Once you have this running total in percent, you can use the MIN function to find the smallest (most negative) number in this column to get the max drawdown. The formula in this example is:

=MIN(t3:t17) 

Finally, you can also create a graph of your drawdown, so it's easier to visualize. Simply highlight row T (or your running percent change total column), then click the graph button to create a graph on the side of the table.

That's all there is to it!

Even if you don't have a new high watermark in your account balance, but your current drawdown is bigger than previous drawdowns, you can consider your current drawdown your max drawdown.

How do You Find Max Drawdown of a Portfolio?

The process of calculating max drawdown for a portfolio is the same. Simply add all of the trades in the portfolio to the spreadsheet.

After that, sort all of the trades by exit date. Then calculate the running profit/loss in percent.

Finally, use the MIN function in Excel to find the biggest drawdown in the running total.

What Does Maximum Drawdown Tell You?

There are 3 different scenarios when you should look at maximum drawdown:

• Backtesting
• Beta Testing
• Live Trading

The max drawdown in each situation gives you different information.

Backtesting

You should find out what your max drawdown is for a particular system in backtesting, so you know what to expect in live trading. 

Your backtesting results may have produced solid returns, but if you couldn't realistically endure the biggest drawdown, then the system won't work for you. It's good to know that before you start trading with real money.

The great thing about backtesting is that you can test many different ideas, to see how a little tweak in the strategy changes the results. Once you have a strategy that you like, you can move on to the next step.

Beta Testing

When you are Beta Testing (also known as forward testing), this is the first opportunity to see if your backtesting results will translate into live market conditions.

Sometimes they don't, for reasons that I talk about here.

If your Beta Testing max drawdown is much bigger than your backtesting drawdown, then you might be doing something differently in Beta Testing. Compare your backtesting trades to your Beta trades to see why you are having a bigger drawdown. 

This intermediate step acts as the final check on your trading strategy, before you go live. Keep in mind that the strategy might be working fine, but you simply hit a run of bad luck.

Live Trading

Finally, your max drawdown in live trading will show you how well you are doing, compared to your testing. If your live trading max drawdown is higher than your backtesting or beta testing, then you should like at your live trading more closely.

Here are some things to consider:

• Are you taking too many impulsive trades?
• Have market conditions changed?
• Are you not following the rules of your strategy?
• Have you been revenge trading?

Tracking your max drawdown is a warning system that will show you when one or more of these things could be out of line. Without this information, you might not know that you are trading poorly…until it's too late. 

At that point, it might be really hard to make up for the losses. 

In addition, when you have backtesting and Beta Trading data, you can compare your testing trades to your live trades to see if there are any noticeable differences. If you don't have testing trades to reference, you'll have to build up your “library” of trades with live trades only and that can take some time.

Expected Maximum Drawdown

Testing isn't the only way to figure out your expected maximum drawdown.

You can also use a Monte Carlo simulation to find out how much your strategy could potentially lose. 

Backtesting and forward testing are good approximations of how your strategy will perform, but it's also good to plug your stats into a simulator to see what your worst possible result could be.

A Monte Carlo simulation simply uses the parameters of your strategy like win rate and win/loss per trade. Then it simulates thousands of trades with those properties, to see what your worst drawdown might possibly turn out to be.

For example, you might have a maximum of 4 losing trades in a row in testing. However, a Monte Carlo simulation shows that you can potentially have up to 10 losing trades in a row. 

This is important information because if you trade this live and you hit 8 losing trades in a row, you might think that your strategy has stopped working.

In reality, this is within the normal parameters of how your system works and you shouldn't freak out about it.

However, if you hit 12 losing trades in a row, then it might be time to stop trading and review your results because this is outside the maximum loss that you saw in the Monte Carlo simulation.

You should plugin backtesting, Beta Testing and live trading results into a Monte Carlo simulator to see what your expected max drawdown might be.

The more data you have the better.

What is a Good Maximum Drawdown?

There's no such a thing as a “good” maximum drawdown. Acceptable maximum drawdown will vary by trader. 

Many new independent traders strive to have a low maximum drawdown. But with low risk also comes low rewards. If you are OK with that, then low drawdowns should be one of your goals.

However, if you want to see higher returns, then you will usually have to endure higher drawdowns.

That's just how trading works, there are no free lunches. 

Another thing to consider when looking at max drawdown is the psychological effect that the drawdown might have on you. 

Some traders are able to withstand a 60% drawdown, in exchange for also having higher returns.

But for a lot of traders, a 60% drawdown would freak them out!

So you should find your “freak out” point and tailor your trading strategy accordingly. Read this post on finding your Risk Tolerance Personality to learn more about how to figure out your risk tolerance.

A good max drawdown for you might be more like 10%. If that's the case, you will probably have to risk less per trade.

Conclusion

Every trader should know their max drawdown in live trading. It also helps to know your drawdown in backtesting and forward testing because that data will give you reference points to help improve your trading.

Take a few minutes to do this simple calculation right now, and find out how you're doing.

Then also run your data through a Monte Carlo simulator to see how big your drawdown could possibly get. If you aren't comfortable with that expected max drawdown, then dial back your risk per trade until you can tolerate the maximum risk.

The post How to Calculate Maximum Drawdown in Excel appeared first on Trading Heroes.

How to Calculate Maximum Drawdown in Excel posted first on your-t1-blog-url