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

Trading Heroes

Forex Trading Education

  • Articles
  • Products
  • About
  • Resources
  • 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 / Trading Software Tutorials / How to Calculate Maximum Drawdown in Excel

Last updated: August 11, 2022
By Hugh Kimura

Maximum drawdown is an important trading statistic to track 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 or you might be in a less than optimal mental state.

Maximum drawdown is calculated in percent, and is the most that an account has lost between all-time highs. The calculation is performed on the running account balance column. This is the Excel formula. 

=MIN((A1-MAX($A$1:A1))/MAX($A$1:A1),0)

The rest of this post will explain how the formula works, how to setup your spreadsheet and answer frequently asked questions.

Here's a video to show you maximum drawdown in action.

If you prefer the expanded text version, it's provided below the video.

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

Max drawdown in testing resultsTo get started, you'll need 2 things:

SEE ALSO: Learn the RSI Divergence trading strategy that works
  • A list of trades with a running account balance or profit/loss per trade
  • Your favorite spreadsheet program

You can use the formula above in spreadsheet programs like:

  • Microsoft Excel
  • macOS Numbers
  • Google Sheets
  • OpenOffice

First, you'll need to have a set of trades to analyze.

These trades can come from any source. Just be sure that they are in CSV or another text file that your spreadsheet program can import.

I'll be using an export from Forex Tester to analyze my backtesting results. You can learn more about Forex Tester here.

I exported my trade history file from Forex Tester as a CSV file.

Once you have your file saved, then in Excel, go to:

Data > Get External Data > From Text

Microsoft Excel ribbon

Then find your file and click the Import button at the bottom.

Find CSV file

From there, you'll enter the import wizard. The settings you choose in the wizard will depend on the format of your file. For this example, I'll use the settings for importing a Forex Tester file.

In step 1, do the following:

  • Select Delimited as the original data type
  • Set Start import at row to 2 to remove the header row. Start at row 1 to keep the header row.

Click on the Next button to go to the next step.

Then select Tab and Comma and click on the Finish button.

Excel will ask you where you want to import the data. Put it into cell A1.

Paste imported data

In this example, the last column contains the initial deposit, as well as the profit/loss from each trade. So I'm going to create a new column to keep a running total of the account balance.

I start at the second line and add the initial deposit to the current line.

Totals column

In the next cell in the same column, add the cell above to the current profit or loss.

Then double click the handle (the little square) in the bottom right corner of the cell with your formula in it. This will copy the formula all the way down the column.

Now you have a column with the running account balance.

In the next column to the right, copy and paste this formula:

=MIN((A1-MAX($A$1:A1))/MAX($A$1:A1),0)

Change the cell references to match your spreadsheet.

For this example, my formula is:

=MIN((R2-MAX($R$2:R2))/MAX($R$2:R2),0)

Drawdown formula

Double click the handle in the lower right corner of the cell to copy the formula all the way down the column.

Formula copied

You can now see the percent drawdowns in your trades. The formula only shows drawdowns, not account growth.

Now there's just one more step…

Find the largest drawdown in the column to get your max drawdown.

Do this by using the minimum (MIN) function to show the lowest value in the column, or the biggest drawdown.

In this example, the formula is:

=MIN(S2:S15) 

Using MIN to find max drawdown

Here's what the 3 columns will look like after you're done.

The maximum drawdown in this backtest was 1.03%.

Completed max drawdown calculation

That's all there is to it!

How do You Find the Max Drawdown of a Portfolio?

The process of calculating the max drawdown of 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 follow the steps shown above.

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.

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. 

If that happens, 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 might show you that you can potentially have up to 8 losing trades in a row. 

This is important information because if you trade this live and you hit 6 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?

Trader at computer

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 20%. If that's the case, you will probably have to risk less per trade.

What's a High Watermark?

A term that you might hear related to max drawdown is “high watermark.”

That's simply the current all-time high of the account balance. The maximum drawdown is the highest percentage drawdown that has occurred between high watermarks.

So if your account started at $10,000, then went to $12,000 after 5 winning trades, then $12,000 is your high watermark. Whatever drawdown takes place between now and when your account balance is higher than $12,000 will be your current maximum drawdown.

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.

Related Articles

  • install ea in mt4How to Install an EA in MT4
  • how to install indicators on mt5How to Install Indicators on MT5
  • Backup and Sync MT4 with DropboxHow Sync MetaTrader Between Multiple Computers
  • best day trading indicatorsBest TradingView Indicators for Day Trading Forex
  • Backtesting with MT5How to Do MetaTrader 5 Manual Backtesting
  • Activate ask line in MT4How to Add the Bid/Ask Lines to MetaTrader 4 Charts
  • Current swing exampleHow to Find the Best Place to Set Your Stop Loss
  • The Fastest Way to Calculate Risk in ForexThe Fastest Way to Calculate Risk in Forex

Category: Trading Software Tutorials Tag: Excel Tutorials, Trading Risk Management

About Hugh Kimura

Hi, I'm Hugh. I'm an independent trader, educator and researcher. I used to work at a hedge fund and the largest bank in Hawaii. Now I help traders optimize their trading psychology and trading strategies. Learn more about me here.

Financial freedom is probably closer than you think. Stop paying for things that don’t make you truly happy.
“Freedom (n.): To ask nothing. To expect nothing. To depend on nothing.”

― Ayn Rand

Primary Sidebar

Trading Guides

The Ultimate Beginner’s Guide to Forex Backtesting

How to Figure Out Your Trader Personality Profile

The Forward Testing Guide for Beginners

How to Create a Precise Trading Plan (with PDF worksheet)

Learn the weird trading method that doesn't use stop losses...

Forex Hedging Guide
FREE Guide
Download Now

Footer

Company

  • Trading Courses and Education
  • Trading Blog
  • About
  • Contact
  • Beware of Imposters

Community

  • YouTube
  • Twitter
  • Instagram
  • Facebook

Tutorials & Guides

  • Best Trading Resources
  • Free Forex Trading Course for Beginners
  • Free Backtesting Guide for Beginners
  • Trader Personality Profile
  • Best Trading Books

Articles

  • Backtesting
  • Podcast
  • Trader Life
  • Trading Psychology
  • Trading Software Tutorials
  • Trading Strategies

Trading involves risk and can result in the loss of your investment. Invest at your own risk. Past performance does not guarantee future results. All information on this site is for informational purposes only and is not trading, investment, tax or health advice. The reader bears responsibility for his/her own investment research and decisions. Seek the advice of a qualified finance professional before making any investment and do your own research to understand all risks before investing or trading. TrueLiving Media LLC and Hugh Kimura accept no liability whatsoever for any direct or consequential loss arising from any use of this information. You are 100% responsible for your losses...and gains. You are more powerful than you know, keep expanding.

 

Copyright © 2007–2023 TrueLiving Media LLC | Terms | Privacy | Risk

Learn Zen8 Forex Hedging

Hedging can be a low-stress and consistent way to trade. Enter your email and I'll send you the FREE guide. 

x