Compute Maximum Drawdown With Four Columns

[Fairman]

Compute Maximum Drawdown With Four Columns

Goal: a drawdown calculator you can paste any trade list into.

Layout. Column A holds each trade's result in R, starting in row 2. Row 1 holds headers.

Column B, cumulative result. In B2: =SUM($A$2:A2) and fill down. Column C, running peak, including a starting value of zero so a losing first trade counts as a drawdown. In C2: =MAX(0,MAX($B$2:B2)) and fill down. Column D, drawdown from peak: in D2: =B2-C2.

The maximum drawdown is the smallest value in column D. In a summary cell: =MIN(D2:D100).

Test with five trades: plus 1, minus 1, minus 1, plus 2, minus 1. Column B gives 1, 0, minus 1, 1, 0. Column C gives 1, 1, 1, 1, 1. Column D gives 0, minus 1, minus 2, 0, minus 1. The maximum drawdown is minus 2R.

Now test a start with a loss: minus 1, minus 1, plus 3. Column B: minus 1, minus 2, 1. Column C with the zero floor: 0, 0, 1. Column D: minus 1, minus 2, 0. The maximum drawdown is minus 2R. Without the zero floor, a first-trade loss would not register, a subtle error in many sheets.

To convert to percent at fixed risk, multiply by your risk per trade. At 1 percent, a 2R drawdown is about 2 percent.

Add a second summary: the longest losing streak, using a helper column that counts consecutive negatives.

Exercise: paste your last fifty trades and find your maximum drawdown in R.