• Skip to primary navigation
  • Skip to main content
  • Skip to footer
Trading Heroes

Trading Heroes

Forex Trading Education

  • Free Tutorials
  • Products
  • Resources
  • About
  • Login

How to Calculate Maximum Drawdown in Excel

Learn how to calculate max drawdown in a spreadsheet and find out what this metric tells you in backtesting, forward testing and live trading.

Home / Forex Trading For Beginners / 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.  

Max drawdown in testing results

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.

SEE ALSO: Learn how this verified profitable trader trades (full interview)

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

Import Excel

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

Running balance in Excel

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.

Percent profit or loss

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

Running total percent change

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) 

Find max drawdown

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.

Create graph in Excel

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?

Calculating max drawdown

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.

SEE ALSO: The Trading Books That Changed My Life

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.

You Might Also Enjoy

How to Get Started in Forex Trading
5 Proven Ways to Stop an Epic Trading Drawdown Faster Than Hitting a Brick Wall
What is a Forex Broker?

Category: Forex Trading For Beginners Tag: Drawdown, Excel Tutorials, Trading Terms Explained

About Hugh Kimura

Hi, I'm Hugh. I'm an independent trader, educator and international speaker. I help traders develop their trading psychology and trading strategies. Learn more about me here.

Top

 

Share This Article


First posted: March 26, 2020
Last updated: March 26, 2020

Footer

Company

  • Support
  • About Trading Heroes
  • Trading Courses and Education
  • Recommended Resources
  • T-Shirts & Trading Gear
  • Charities We Support

Tutorials & Guides

  • Free Forex Trading Course for Beginners
  • Best of Trading Heroes
  • Forex Trading Blog
  • Best Trading Books
  • Forex Trading Strategies
  • What is Forex?
  • Podcast

Community

  • YouTube
  • Twitter
  • Dtube
  • Odysee
  • Flote
  • Minds
  • Private Trading Community
  • Contact
Trading involves substantial risk and there is always the potential for loss. All content on this website is for educational and informational purposes only and is not trading, investment, or medical advice. You should be aware of the risks associated with trading and seek advice from an independent certified financial adviser if you have any doubts. Some links on this page might be affiliate links, where we get a small commission if you purchase through the link. It doesn't cost you anything extra and we only recommend products that we absolutely love. This site uses cookies and using this site means that you agree to the use of cookies.

 

CFTC Rules 4.41 - Hypothetical or Simulated performance results have certain limitations. Unlike an actual performance record, simulated results do not represent actual trading. Also, because the trades have not actually been executed, the results may have under-or-over compensated for the impact, if any, of certain market factors, such as lack of liquidity. Simulated trading programs, in general, are also subject to the fact that they are designed with the benefit of hindsight. No representation is being made that any account will or is likely to achieve profit or losses similar to those shown. Testimonials appearing may not be representative of other clients or customers and is not a guarantee of future performance or success.

 

 

 

 

 

Copyright © 2007–2021 TrueLiving Media LLC | Terms | Privacy | Risk
How to choose a trading system guide

Get the FREE Guide to Picking the Best Trading Strategy For YOU

x