max() over (order by date),
minus 1, then the minimum. For SPY, the worst rolling 10-year return was
−4.58% a year, and the maximum drawdown −55.2%.
Below are tested queries for both, with results for six funds and ETFs:
how often each holding period lost money, how a fund did against SPY across
every window, and a list of every drawdown with its peak, trough and
recovery dates.
The queries run in Snowflake against
ext_fund_trailing_returns_vw in
Fruit Stand’s Fund Returns
listing, which
we publish. They were run on November 19, 2026, with data through November
18. All returns include reinvested dividends.Rolling returns vs. trailing returns
A trailing return covers one window ending today: the 10-year return as of November 18, 2026. A rolling return repeats that for every end date, then looks at the spread: the worst window, the median, the best, and how often the return was negative. One trailing number depends on the day you ask; rolling returns show how much it could have varied.Rolling returns in SQL
The Fund Returns listing stores trailing returns for every fund for every trading day, so each row is already one rolling window. The rolling-return summary is agroup by:
10-year returns are annualized.
min_by(as_of_date, return_10y) returns
the end date of the worst window, so you can see which decade it was. SPY’s
worst 10 years ended at the March 2009 low, and QQQ’s started near the
March 2000 peak of the dot-com bubble.
The windows aren’t the same for every fund: AGTHX’s start in 1990 and
QQQ’s in 2009, because that’s when each fund had 10 years of history. To
compare funds fairly, filter to the same range of end dates, as in the
benchmark query below.
Longer holding periods aren’t always safer
The same summary for SPY’s 1-, 3-, 5- and 10-year windows, on the same end dates (every day from January 2003, when SPY first had a 10-year return).unpivot turns the four columns into rows:
The range narrows as the window gets longer, as you’d expect. The share of
losing windows doesn’t: since 2003, 5-year windows lost money more often
than 1-year windows did, because so many of them spanned both the 2000–2002
and 2007–2009 bear markets. Rule-of-thumb claims that a longer hold “always”
reduces the chance of a loss depend on the period you measure.
Note that 1-year returns are cumulative and the longer ones are annualized,
which is the listing’s convention (see the dataset
docs). A 5-year window at −8.22% a year lost about
35% in total.
Daily or monthly windows?
Many fund sites measure rolling returns once a month. You get nearly the same picture from month-end windows, but you can miss the extremes:Rolling returns against a benchmark
Join each fund’s row to SPY’s row for the same date and you get the excess return for every window, which says more than a single trailing comparison:
Excess returns are in percentage points a year. Dodge & Cox Stock was ahead
of SPY by 1.4 points a year on average, but only in about half the windows:
large wins in some decades, losses in others. Growth Fund of America was
ahead less dramatically but far more often. The join on
as_of_date keeps
both sides on the same windows. For the single-date version, see How to
Compare a Fund’s Returns With Its Benchmark in
SQL.
If your data has prices rather than ready-made returns, compute the window
yourself: How to Calculate Stock Returns From Daily Prices in
SQL matches each day with the trading
day a year earlier using asof join.
Maximum drawdown in SQL
Drawdown needs a value series, not a return. Rebuild one from the daily returns by compounding them,exp(sum(ln(1 + r))) as a running sum, then
compare each day with the highest value so far:
The value series starts at about 1 and only its ratios matter, so there’s
no need for a price.
coalesce covers the null 1-day return on a fund’s
first date. We checked SPY, QQQ, DODGX and TLT against their adjusted
closes in our Historical Stock Prices
listing; the drawdowns match to the hundredth of a point.
The longest drawdown on this list isn’t a stock fund’s. A long-term
Treasury ETF, TLT, fell 48% from August 2020 to October 2023 and
was still 43% below its peak in November 2026, more than six years later.
VBTLX, the broad bond market, has also not made a new high since August
2020.
Every drawdown, with recovery dates
The maximum is one number. To list every drawdown, number the episodes: each day the fund is at a new high (drawdown = 0) starts a new one, so a
running count_if labels them. The first day of the next episode is the
day the previous one recovered:
These are SPY’s drawdowns of more than 15% since 1993, with dividends
reinvested. Recovery usually takes longer than the fall: after 2009 it took
almost three and a half years, and after 2000 about four.
Dividends make a difference that drawdown tables usually leave out. They
quote the S&P 500 price index, which didn’t close above its October 2007 high
until March 28,
2013. With
dividends reinvested, SPY was back on August 16, 2012, more than seven
months earlier, and its drawdown was −55.2% rather than the index’s −56.8%.
For an investor who reinvests, the total-return series is the one that
counts.
Change
where max_drawdown < -0.15 to list smaller drawdowns, or drop the
code filter to list them for every fund. Episodes are grouped by code,
so the query works across funds unchanged.
Mistakes that give wrong numbers
- Using prices without dividends. A price-only series overstates drawdowns and recovery times, as above. Use adjusted prices or total returns.
- Leaving out
partition by code. The running peak of one fund carries into the next, and every drawdown after the first fund is wrong. - Comparing funds over different periods. A fund with a longer history includes more crashes. Start every fund on the same date, as the drawdown query does from 2007.
- Sampling monthly and calling it the maximum. Month-end data misses troughs between month-ends. SPY’s month-end 10-year windows miss its worst one.
- Mixing annualized and cumulative returns. A −8% a year 5-year return is a −35% loss in total; label which one a table shows.
Limits of this approach
- Fund returns are end-of-day. Intraday lows are lower than any close, so an intraday drawdown would be deeper.
- Thinly traded funds. When a fund doesn’t trade, the listing measures from the last date it did, so one return can span more than one day (see the dataset docs). Peak and trough dates for those funds are only as exact as their trading.
- ETF returns use market prices; mutual fund returns use NAV. For most large ETFs the difference is small, but it can matter in a crisis.
F.A.Q.
What's the difference between rolling and trailing returns?
What's the difference between rolling and trailing returns?
A trailing return is one window ending on a given date, such as the 10-year return as of today. Rolling returns are that window computed for every end date, summarized as the worst, median and best window and how often it was negative.
How do you calculate maximum drawdown?
How do you calculate maximum drawdown?
Build a value series that includes dividends, divide each day’s value by the highest value up to that day, and subtract 1. The most negative result is the maximum drawdown. In SQL the running peak is
max(value) over (partition by code order by date).Can I calculate max drawdown from returns instead of prices?
Can I calculate max drawdown from returns instead of prices?
Yes. Compound the daily returns into a value series with
exp(sum(ln(1 + r)) over (order by date)), then use the running peak on that. It gives the same drawdown as adjusted prices, because only the ratios matter.How do I find how long a drawdown lasted?
How do I find how long a drawdown lasted?
Label episodes with a running
count_if(drawdown = 0), so each new high starts a new episode. The first date of the next episode is the recovery date, and datediff gives the days from peak to trough and from trough to recovery.Should rolling returns use daily or monthly windows?
Should rolling returns use daily or monthly windows?
Either gives a similar median and share of negative windows. Daily windows include the worst and best days, which month-end windows can miss; on SPY’s 10-year returns, the worst daily window was −4.58% a year against −3.45% for month-ends.