The formula
Running peak (fill down)ExcelGoogle Sheets
=MAX($B$2:B2)In C2 → $10,000.00
Drawdown (fill down)ExcelGoogle Sheets
=B2/C2-1In D2 → 0%
Max drawdownExcelGoogle Sheets
=MIN(D2:D9)In F2 → -21.6%
Single cell (Excel 365)Excel
=MIN(B2:B9/SUBTOTAL(4,OFFSET(B2,0,0,ROW(B2:B9)-ROW(B2)+1))-1)In F3 → -21.6%
✓ Tested. All 18 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Equity | ||||
| 2 | Jan 1, 2026 | 10,000 | $10,000.00 | 0% | -21.6% | |
| 3 | Feb 1, 2026 | 11,200 | $11,200.00 | 0% | -21.6% | |
| 4 | Mar 1, 2026 | 10,400 | $11,200.00 | -7.14% | ||
| 5 | Apr 1, 2026 | 12,500 | $12,500.00 | 0% | ||
| 6 | May 1, 2026 | 11,000 | $12,500.00 | -12% | ||
| 7 | Jun 1, 2026 | 9800 | $12,500.00 | -21.6% | ||
| 8 | Jul 1, 2026 | 11,900 | $12,500.00 | -4.8% | ||
| 9 | Aug 1, 2026 | 13,100 | $13,100.00 | 0% |
How it works
- MAX($B$2:B2)
- Highest equity up to this row.
- B2/C2-1
- Percent below that peak (0% at a new high).
- MIN(D2:D9)
- The deepest drawdown.
- SUBTOTAL(4,OFFSET(…))
- Single-cell version: running maxima of growing ranges.
Common errors and fixes
Max drawdown shows 0%?
You used MAX instead of MIN; drawdowns are negative.
Peak resets?
The peak range isn't anchored ($B$2).
Does this work in Google Sheets?
The formulas tagged "Google Sheets" work as-is. Formulas tagged only "Excel" may need ARRAYFORMULA or a different function in Sheets.