Maximum drawdown in Excel: the formula, step by step

The three-column setup

Put your equity series in column A, starting at A2 with headers in row 1. Values in A2, A3, A4 and so on, one per period, at a consistent interval — daily closes are the usual choice.

In B2, the running peak: `=MAX($A$2:A2)`. The dollar signs matter. `$A$2` is an absolute reference that never moves, while `A2` is relative and will become A3, A4 and so on as you fill down. The formula therefore always asks for the maximum of everything from the first value up to the current row, which is exactly the running peak.

In C2, the drawdown: `=A2/B2-1`. This is zero when equity equals its running peak and negative when it is below. Fill both formulas down to the end of the data.

The maximum drawdown is then `=MIN(C:C)`, which returns the most negative value in the column, formatted as a percentage. The row where that minimum sits tells you the trough; the row where column B last changed before it tells you the peak.

A version that keeps everything in one cell, if you prefer: `=MIN(A2:A100/MAX($A$2:A2)-1)` entered as an array formula. It works, but you cannot see the intermediate steps, which is the main advantage Excel has over a script.

Trap one: the relative reference

If you write `=MAX(A1:A2)` in B2 and fill down without the dollar signs, Excel will obediently shift both ends: B3 becomes `=MAX(A2:A3)`, B4 becomes `=MAX(A3:A4)`, and so on. Each cell now reports the maximum of a two-row window rather than the running maximum.

The result is a drawdown figure that is too small, and it is not obviously wrong — it still looks like a drawdown, it still goes negative, and it still has a plausible magnitude. This is the Excel equivalent of resetting the peak at every bar, and it is the single most common reason a hand-built drawdown disagrees with a platform's.

The check: look at column B. It must be non-decreasing all the way down. If it ever falls, the reference is wrong. A running maximum can only stay the same or increase — that property is the whole point of the column, and it makes the error trivially detectable.

Trap two: measuring from the wrong base

Drawdown is a ratio, and the denominator is the running peak, not the starting balance and not the previous row. Three variants circulate and they give three different answers.

Dividing by the starting value (`=A2/$A$2-1`) measures the fall from the initial deposit, which is a different quantity once the account has grown. A 5,000 fall from a 20,000 peak is 25% of the peak but 50% of an original 10,000 — the same fall, two different numbers, and only one of them is a drawdown. The peak is the correct denominator.

Dividing by the previous row measures period-to-period change, which is volatility rather than drawdown. It will happily report a small number for a long grinding decline made of many small down-steps.

The correct denominator is the highest value reached so far, which is exactly what column B contains. If your formula references anything else, you are computing a different statistic.

Adding the recovery period

The depth of a drawdown is only half the story; the other half is how long it lasted. Excel makes this straightforward once you have the columns.

Find the row of the trough — the row where column C reaches its minimum. Then look down column A from that row for the first value that is greater than or equal to the peak value from the row where the decline began, which you can read from column B at the trough row. That row is the recovery. The difference in row numbers is the recovery period in whatever unit your series uses.

A formula version: in column D, `=IF(A2>=B2,0,D1+1)` filled down counts the number of periods since the last new high. The maximum of column D is the longest drawdown duration, and its value at the trough row is how long that particular drawdown lasted.

Worth adding, because the two dimensions often tell opposite stories. A 15% drawdown that recovered in a month and a 15% drawdown that took two years have the same depth and very different consequences for whether a person can stay in the strategy.

A worked example you can type in

Put 100, 110, 120, 108, 102, 115, 123 in A2:A8.

Column B becomes 100, 110, 120, 120, 120, 120, 123 — non-decreasing, as it must be.

Column C becomes 0%, 0%, 0%, −10.00%, −15.00%, −4.17%, 0%.

`=MIN(C:C)` returns −15.00%, which is the maximum drawdown: the fall from the 120 peak in row 4 to the 102 trough in row 6.

Note that row 7 shows −4.17% even though equity rose from 102 to 115. That is correct: the account is still below its high, so it is still in drawdown. A drawdown ends only when equity exceeds the previous peak, which happens in row 8.

The recovery arithmetic is worth computing next to it: from 102 back to 120 is a 17.65% gain. That asymmetry is what makes deep drawdowns expensive in time rather than just in confidence.

What Excel cannot tell you

None of this reveals what will happen next. The maximum drawdown over a historical window is a lower bound on future drawdown, because extending the window can only make a maximum larger or leave it unchanged.

It also depends entirely on the values you pasted in. Drawdown computed on closed-trade balance is smaller than the same strategy's equity drawdown, because it ignores the unrealised loss while a position is open. Whichever you use, state it.

And the sampling frequency changes the answer: a daily series will not show intraday excursions, so it will report a smaller figure than the same strategy measured on finer data. Neither is wrong; they answer different questions about what you would have had to sit through.