Maximum Drawdown (MDD), High-Water Mark & recovery gain % formulas

Why MAX(Portfolio) to MIN(Portfolio) is WRONG when the trough came BEFORE the peak!

A common spreadsheet bug calculates drawdown as (MIN(B2:B7) - MAX(B2:B7)) / MAX(B2:B7)—which fails completely if an account started at $80, grew steadily to $200 with zero drops, and falsely reports a "-60% drawdown"! True Drawdown only measures drops AFTER a prior High-Water Mark peak!

Locking the top cell in an expanding range—HighWaterMark C2 = MAX($B$2:B2)—tracks the highest equity reached UP TO THAT DATE; Underwater Drawdown D2 = (B2 - C2) / C2 is <= 0% on every row; and Maximum Drawdown MDD = MIN(D2:D7) captures the worst peak-to-trough drop (-25.00% when $120k drops to $90k)! Crucially, recovering from a drawdown of d (e.g., -25%) requires an asymmetric gain of -d / (1 + d) = 0.25 / 0.75 = +33.33% (and -50% requires +100%!)!

Use observations in chronological order, a positive starting value and nonnegative later values. This example treats the series as a cash-flow-adjusted value index: deposits and withdrawals in raw account balances can otherwise look like investment gains or losses. Only observed dates are measured; unobserved intraperiod lows are not captured. We report drawdown as negative; some reports present its positive magnitude.

Worst observed percentage drawdown

MIN(D2:D7) finds the deepest negative underwater percentage across the entire equity curve.

=ROUND(MIN(D2:D7), 4)

Required % gain to recover back to High-Water Mark from worst drawdown

Implements exact geometric recovery: 1 / (1 + MDD) - 1 (so -25% requires +33.33%, and -50% requires +100.00%).

A 100% loss leaves zero value, so no finite percentage gain on that zero base restores the former peak. This is arithmetic on the historical series, not a forecast.

=LET(mdd,MIN(D2:D7),IF(mdd<=-1,"No finite recovery gain",ROUND(1/(1+mdd)-1,4)))

Further reading

• Microsoft Support — MAX function
• Microsoft Support — MIN function