Calculation Issues

How to Calculate the Worst Drawdown in Excel (3-Month & 12-Month)

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs an Excel formula or methodology to calculate the maximum (worst) drawdown over specific rolling periods, such as three months and twelve months, based on a dataset of monthly returns.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Analyzing financial performance data to determine the most severe peak-to-trough decline (drawdown) within fixed 3-month and 12-month rolling windows.
Observed behavior: The user requires the correct sequence of functions to accurately measure the drawdown from cumulative peaks over the specified date ranges.

Before you start

Ensure your monthly return data is organized chronologically in a continuous column, and format the cells as percentages to accurately track cumulative performance over time.

Solution 1Recommended

Calculate Drawdown Using Cumulative Values and the MIN Function

This method converts monthly returns into a cumulative index, identifies the running peak, and calculates the worst percentage drop (drawdown) over specific rolling periods.

Drawdown is typically measured by tracking the cumulative performance of an asset. To find the worst drawdown, you must first calculate a cumulative index from your monthly returns, find the peak (maximum value) up to that point, and then measure the percentage decline from that peak.

Once the overall drawdown series is established, you can use the MIN function combined with rolling date ranges to isolate the worst drawdown for any 3-month or 12-month period.

Create a Cumulative Return Column

In an adjacent column (e.g., Column B), establish a base value of 1 or 100. For subsequent rows, calculate the cumulative return by multiplying the previous cumulative value by (1 + current monthly return).

Calculate the Running Peak

Create a new column (e.g., Column C) and use the MAX function anchored at the start of your cumulative series (e.g., `=MAX($B$2:B2)`) to continuously track the highest index value reached so far.

Determine the Period Drawdown

In the next column (Column D), calculate the current drawdown by dividing the current cumulative return by the running peak, then subtracting 1: `=(B2/C2)-1`. Format this column as a percentage.

Find the Worst Drawdown for Specific Periods

To find the worst drawdown over a rolling window, apply the MIN function to the drawdown column. For a 3-month worst drawdown, use `=MIN(D2:D4)`. For a 12-month period, use `=MIN(D2:D13)`. Drag the formula down to calculate this across all rolling periods.

Data Layout Check: Before applying these formulas, confirm your exact data layout. Calculating rolling periods accurately requires a continuous, gap-free sequence of dates and monthly returns.

Advanced Financial Analysis

Calculate Financial Drawdowns Seamlessly with WPS Spreadsheet

WPS Spreadsheet offers full compatibility with standard financial, statistical, and logical functions. You can easily build complex cumulative return models and calculate drawdowns without paying for expensive software subscriptions.

1. Import Data: Open WPS Spreadsheet and paste your chronological monthly return data into a blank worksheet.
2. Apply Formulas: Use standard formulas like `=MAX()` and `=MIN()` to calculate the running cumulative peaks and maximum drops as outlined in the solution.
3. Visualize Drawdown: Highlight your results and insert a line chart from the 'Insert' tab to visually represent the drawdown periods and recovery times.

Fully compatible with Microsoft Excel formulas like MAX, MIN, and OFFSET.Create dynamic financial models and track investment performance easily.Lightweight and fast, even when handling large datasets of historical returns.Built-in professional charts to visualize peak-to-trough drawdowns effortlessly.

Frequently Asked Questions

What exactly is a drawdown in financial terms?

A drawdown is the peak-to-trough decline during a specific record period of an investment, fund, or commodity. It is usually quoted as the percentage between the historical peak and the subsequent lowest trough.

Can I use a built-in Excel function to calculate maximum drawdown directly?

There is no single native function like =MAXDRAWDOWN() in Excel or WPS Spreadsheet. You must construct it manually by calculating cumulative returns, finding the running maximum, and then calculating the percentage drop from that maximum using the MIN function.

How do I adjust the formula for rolling 3-month periods instead of 12-month periods?

When applying your MIN formula to the drawdown column, simply adjust the range size. For a rolling 3-month period, the range should cover 3 rows (e.g., =MIN(D2:D4)). For 12 months, it should cover 12 rows (e.g., =MIN(D2:D13)).

Why is my drawdown formula returning a positive number?

Drawdowns are conceptually negative as they represent a loss. If your formula calculates (Peak - Current) / Peak, it will return a positive number representing the drop size. If you prefer a negative percentage, calculate it as (Current / Peak) - 1.

Excel & Data Problems