You can use the following dynamic array formula to calculate maximum drawdown (MDD) in Excel and Google Sheets:

=LET(
   prices, range, 
   running_max, IF(prices, SCAN(CHOOSEROWS(prices, 1), prices, LAMBDA(acc,val, MAX(val, acc))),), 
   drawdown, prices-running_max, 
   drawdown_P, IFERROR(drawdown/running_max,0), 
   MIN(drawdown_P)
)

Where range refers to the prices column. When using this formula in Google Sheets, enter it as an array formula.

Understanding Maximum Drawdown (MDD)

Maximum Drawdown (MDD) is a commonly used metric among investors to evaluate the risk and performance of investments. The calculation is represented mathematically as:

(Trough Value – Peak Value) ÷ Peak Value

It quantifies the largest decline from a historical peak to a trough in a series of values before establishing a new peak.

Key Characteristics of the Formula:

• Compatibility: Works in both Excel and Google Sheets.
• Dynamic Array Formula: Returns the result in a single step without requiring helper columns.
• Ease of Use: Modify the range reference in one place to adapt the formula to your dataset.
• Flexible Output: Calculate the maximum drawdown percentage (default) or the maximum drawdown by replacing MIN(drawdown_P) with MIN(drawdown).
• Detailed Analysis: Display the peak (running max), drawdowns, and drawdown percentages row by row by replacing MIN(drawdown_P) with the relevant variable names.

Example: Calculating Maximum Drawdown in Excel

Assume you have the following sample data in A1:B8 in your Excel spreadsheet:

To calculate the maximum drawdown, use the following formula in an empty cell:

=LET(
   prices, B2:B8, 
   running_max, IF(prices, SCAN(CHOOSEROWS(prices, 1), prices, LAMBDA(acc,val, MAX(val, acc))),), 
   drawdown, prices-running_max, 
   drawdown_P, IFERROR(drawdown/running_max,0), 
   MIN(drawdown_P)
)

The formula will return -0.25 as the maximum drawdown (MDD).

Formula Breakdown:

1. IF(prices, SCAN(CHOOSEROWS(prices, 1), prices, LAMBDA(acc, val, MAX(val, acc)))): Calculates the running maximum (running_max).
2. prices - running_max: Calculates the drawdown (drawdown).
3. IFERROR(drawdown / running_max, 0): Calculates the drawdown percentage (drawdown_P).
4. MIN(drawdown_P): Finds the minimum drawdown percentage, which is the maximum drawdown.

Viewing Detailed Calculations:

To see the values of running max, drawdown, and drawdown percentage row by row:

1. Clear the range C2:E8.
2. Copy the maximum drawdown formula into C2.
3. Replace MIN(drawdown_P) in the formula with HSTACK(running_max, drawdown, drawdown_P).

The updated formula will be:

=LET(
   prices, B2:B8, 
   running_max, IF(prices, SCAN(CHOOSEROWS(prices, 1), prices, LAMBDA(acc,val, MAX(val, acc))),), 
   drawdown, prices-running_max, 
   drawdown_P, IFERROR(drawdown/running_max,0), 
   HSTACK(running_max, drawdown, drawdown_P)
)

Example: Calculating Maximum Drawdown in Google Sheets

The array formula behaves slightly differently in Excel and Google Sheets. In Excel 365, the formula dynamically applies to the range used. In Google Sheets, you must enter it as an array formula.

Using the same sample data from Excel in Google Sheets, enter the following formula:

=ARRAYFORMULA(
   LET(
      prices, B2:B8, 
      running_max, IF(prices, SCAN(CHOOSEROWS(prices, 1), prices, LAMBDA(acc,val, MAX(val, acc))),), 
      drawdown, prices-running_max, 
      drawdown_P, IFERROR(drawdown/running_max,0), 
      MIN(drawdown_P)
   )
)

This will return the maximum drawdown in Google Sheets.

The only difference is the outer ARRAYFORMULA in Google Sheets, which ensures the formula works across a range.

In Excel, you can specify a larger range, such as B2:B1000, since dynamic ranges are not supported the same way as in Google Sheets.

In Google Sheets, you can extend the range dynamically by replacing B2:B8 with B2:B to include future values.

Conclusion

This guide demonstrates how to calculate maximum drawdown in Excel and Google Sheets using a single dynamic formula. The formula provides a simple, helper-column-free solution to calculate MDD.

Additionally, the formula is flexible enough to accommodate future values and display underlying calculations. To gain more insights, use the formula to output running max, drawdown, and drawdown percentages in separate columns.

With this method, you can efficiently assess the maximum drawdown and improve your investment analysis in both Excel and Google Sheets.