How to Calculate Maximum Drawdown in Excel

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

To get started, you'll need 2 things:

• 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

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

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.

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.

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)

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

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) 

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

The maximum drawdown in this backtest was 1.03%.

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?

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?

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.