Is This the Top? A Drawdown and Recovery Worksheet
Use the interactive tool below, or jump to the written guide.
Loading the interactive worksheet. The complete written guide remains below.
The original worksheet guide
A market can be near a high and still rise. It can also fall a long way and keep falling. The question this worksheet can answer is narrower and more useful: how far below a chosen high is the price, and what gain would restore it?
Enter your own verified prices in a spreadsheet using the layout below. This is a manual calculation worksheet, not a live market feed or a prediction of the next top. Its edge is making the recovery arithmetic—and the assumptions behind it—hard to ignore.
Choose the comparison before doing the math
- Asset: identify the exact index, fund, stock, or cryptocurrency and currency.
- Price basis: use closing prices with closing prices, or intraday prices with intraday prices.
- Reference high: record its date and the historical window searched. A 52-week high is not an all-time high.
- Adjustments: keep split adjustments consistent. Use a total-return series if your question includes reinvested distributions.
- Source and timestamp: record both; a number without a date is not a market observation.
The three calculations
Decline from reference high (%) = (high − current) ÷ high × 100 Gain required to regain reference high (%) = (high ÷ current − 1) × 100 Price after an additional decline = current × (1 − additional decline as a decimal)
Both the high and current price must be positive. If current exceeds the supplied high, the old reference is stale or was not the high for the selected series. Update the reference before calling the result a drawdown. If current is zero, recovery to a positive high has no finite percentage solution.
Worked example: down 25% is not up 25% to recover
Suppose a fictional asset’s reference high is 100 and its current price is 75. It is 25% below that high. It needs a 33.33% gain from 75 to return to 100. If it falls another 20% from 75, it reaches 60: now 40% below the high and requiring a 66.67% gain to recover.
| Loss from starting value | Value remaining from 100 | Gain needed to recover |
|---|---|---|
| 10% | 90 | 11.11% |
| 20% | 80 | 25.00% |
| 25% | 75 | 33.33% |
| 40% | 60 | 66.67% |
| 50% | 50 | 100.00% |
| 75% | 25 | 300.00% |
Build the worksheet in eight cells
In a blank spreadsheet, put the labels in column A and the inputs or formulas in column B. Use ordinary numbers for prices. Enter a further decline as a decimal, such as 0.20 for 20%. Format B4, B5, and B8 as percentages.
| Cell | Label | Value or formula |
|---|---|---|
| B1 | Reference high | 100 |
| B2 | Current price | 75 |
| B3 | Additional decline, decimal | 0.20 |
| B4 | Decline from high | =(B1-B2)/B1 |
| B5 | Gain needed to recover | =B1/B2-1 |
| B6 | Price after additional decline | =B2*(1-B3) |
| B7 | Price regained after 20% rise from current | =B2*1.20 |
| B8 | Recovery needed after additional decline | =B1/B6-1 |
With these example inputs, B4 is 25%, B5 is 33.33%, B6 is 60, B7 is 90, and B8 is 66.67%. Check that the high is at least the current price, and that B3 is at least zero and below one. Validate inputs before using these formulas with a new example.
Two traps worth seeing
Equal percentages do not cancel. A 20% loss followed by a 20% gain turns 100 into 96. The order can be reversed and the result is still 96 when no cash moves in or out.
A sequence of small losses compounds. Five consecutive 10% declines leave 59.049% of the starting amount, a cumulative loss of 40.951%. Adding the percentages would give the wrong result.
Write the observation without turning it into a forecast
Asset and currency: Price basis and adjustments: Reference high, date, and window: Current observation and timestamp: Source links: Decline from high: Gain needed to regain high: Additional-decline scenario: What this calculation does not establish: Next data-check date:
Frequently asked questions
Does a large drawdown mean the asset is cheap?
No. Distance from a past price says nothing by itself about fair value, financial health, or whether the old price will return.
Can I use this for my account balance?
Only carefully. Deposits and withdrawals can change balances without representing investment gains or losses. A price worksheet is not a time-weighted or money-weighted portfolio return calculation.
How is this different from maximum drawdown?
This worksheet compares a current price with a chosen reference high. Maximum drawdown requires searching an entire series for its largest peak-to-subsequent-trough decline; the two numbers can differ.
Next: the math of trading risk and how stop orders work. Investor.gov’s allocation guidance explains why risk decisions also depend on your goals and time horizon.
By Stefan Cheplick. Educational research tool; no buy/sell recommendations. Read the site disclaimer.