Rolling Returns: Why CAGR Alone Can Mislead You (And What To Use Instead)

Published: March 5, 2026

There is a common misunderstanding about CAGR. People hear "Compound Annual Growth Rate" and assume some kind of averaging is happening. It isn't. CAGR is purely point-to-point. Start NAV on date A, end NAV on date B, apply the compounding formula. That's it. One start date. One end date. One number.

The problem? That one number is fragile. Shift your start date by a week - especially around a market event - and your CAGR can look meaningfully different. A fund that shows 14% CAGR from January 3rd might show 11% if you start from January 10th. Same fund, same end date. Different number because the market had a bad week.

For shorter evaluation periods - 1 year, 2 years - this gets worse. Your entire assessment of a fund can swing based on whether the start date happened to land on a good day or a bad day. And when you're comparing two funds, both numbers are hostage to that same date sensitivity. You might pick the wrong fund simply because one of them got lucky with the calendar.

This is not a theoretical problem. It happens all the time.

What Rolling Returns Fix

Rolling returns shift the question from "what happened between these two specific dates" to "what does a typical holding period actually look like across the fund's entire history."

Here is how it works. Take every single trading day in a fund's history as an end date. For each one, look back exactly 1 year and compute the CAGR for that window. Do this for every possible end date. Now instead of one number, you have thousands of individual 1-year CAGRs.

A fund with 7 years of history will give you roughly 1,500 to 1,700 individual 1-year CAGR observations. Average those. Find the median. Find the worst. Find the best. Count how many were negative.

That average is a far more reliable picture of what a 1-year holding typically looks like. No single date can distort it. No lucky or unlucky start point can skew the result.

Two Periods That People Confuse

Rolling returns involve two distinct time spans. Getting them mixed up is easy - and common.

Rolling Window - this is the holding period being simulated. If you select "1 Year", each individual observation is a 1-year CAGR. Select "3 Years" and each observation is a 3-year CAGR. This answers: "what would I have earned if I held for this long?"

Evaluation Period - this is the overall historical span across which those windows are computed. For example, January 2013 to March 2026. A longer evaluation period means more observations, and more observations means more reliable statistics.

The statistics you see - average, median, % negative - are summaries across all the rolling observations within the evaluation period.

Important: the evaluation period must be meaningfully longer than the rolling window. A 5-year rolling window needs at least 7-8 years of data to produce a useful distribution. A 3-year window needs at least 4-5 years. If a fund launched in 2022 and you select a 5-year window, there's barely enough history for a single valid observation. The number of observations shown in the results tells you how much data went into the calculation.

How the Computation Works

A few technical details for those who want to go deeper.

Calendar days, not trading days. A "1 Year" window looks back exactly 365 calendar days from each end date - not 250 trading days. Since markets are closed on weekends and holidays, the target date 365 days ago may not have a NAV. The system uses something called an ASOF JOIN - it finds the most recent available NAV on or before the target date. So if the target falls on a Saturday, it picks up the Friday NAV.

The CAGR is then computed over the actual calendar days between the two matched dates - not an assumed 365.

The formulas:

For 1-year and longer windows: CAGR = (End NAV / Start NAV) ^ (365.25 / Actual Days) - 1

For shorter windows (3 months, 6 months): Absolute Return = (End NAV / Start NAV - 1) x 100

Why the split? Annualizing short periods can be misleading. A 5% return in 3 months annualized to ~21% sounds great but that's not what actually happened. So for sub-1-year windows, I show the actual absolute return.

Minimum gap thresholds. Each window has a minimum gap requirement. The actual days between start and end NAV must exceed this minimum to be counted. This filters out edge cases where extended market closures cause the matched dates to land too close together.

The ASOF JOIN. The whole computation runs in DuckDB SQL on the backend. For each trading day (end date), the ASOF JOIN finds the most recent NAV on or before the target start date in a single pass. This is what makes it efficient - computing rolling returns for all 95+ funds across all 5 windows takes under 10 seconds.

Here is the simplified logic:

SELECT
  t.fund,
  t.date AS end_date,
  p.date AS start_date,
  POWER(t.nav / p.nav, 365.25 / days_between) - 1 AS rolling_cagr
FROM nav_daily t
ASOF JOIN nav_daily p
  ON t.fund = p.fund
  AND p.date <= t.date - INTERVAL 365 DAY
WHERE days_between >= 330

If you're not familiar with ASOF JOIN - it's a DuckDB feature that matches each row to the nearest row meeting the condition, without scanning everything. Regular JOINs need exact matches. ASOF finds the closest match. Very useful for time-series data where dates don't align perfectly.

Reading the Results - What Each Column Means

If you run a fund-vs-fund or fund-vs-index comparison in MFPRO, you get a rolling returns table with these columns. Here is what each one tells you:

Average CAGR - The mean of all rolling returns. Think of it as your "expected" return if you had invested on a random day within the evaluation period. More stable than point-to-point CAGR because thousands of observations go into it.

Median CAGR - The middle value. Less affected by extreme observations than the average. When the median is significantly lower than the average, the fund has some very high outlier returns pulling the average up - which means typical experience may be lower than the average suggests.

Min CAGR & Date - The worst rolling return and when that window ended. Usually corresponds to investing just before a major crash. Tells you the worst case scenario for that holding period.

Max CAGR & Date - The best rolling return and when it ended. Usually corresponds to investing at a market bottom and catching the recovery.

% Negative - What percentage of all rolling windows produced a negative return. This is probably the most practical column. A fund showing 10% negative on 1-year rolling means roughly 1-in-10 chance of losing money if you held for a year, regardless of when you invested. A fund with near-zero % negative on 3-year rolling? Very consistent - you almost never lost money over any 3-year period.

Observations - How many rolling windows were computed. More observations = more reliable statistics. If this number is low (say under 500 for 1-year rolling), treat the stats with caution.

A practical way to use this: A fund with high average CAGR but also high % negative is high-return but inconsistent. A fund with slightly lower average but near-zero % negative is more reliable. Which one you prefer depends on your holding discipline and risk tolerance. Rolling returns make that trade-off visible in a way that a single CAGR number never can.

How to Validate This Yourself in Excel

One thing I'm particular about - every number on MFPRO should be independently verifiable. I don't want anyone taking my calculations on faith.

Go to the Returns section in MFPRO, then the Validation tab. There you will find:

• Raw NAV and index data downloadable as CSV
• A pre-built Excel file with CAGR and rolling return formulas already set up
• Step-by-step instructions on how to replicate every number

I ran this validation on BSE 100 Index data. 7,485 individual rolling return values across 3 windows and 3 evaluation periods. Every single value came within 0.50 basis points of the manually computed Excel result. The tiny differences (sub-basis-point) come from date matching - the ASOF JOIN picks the nearest trading day, which can differ by a day from a strict calendar lookup - and floating-point precision.

The validation detail:

Download the files, plug in your own formulas, and check. If you find a discrepancy beyond 0.50 bps, I want to know about it.

See It Live

All of this is live on MFPRO. 95 funds across 6 categories, 9 indices. Compare rolling returns across funds and benchmarks, custom evaluation periods, data downloads for offline analysis.

Full methodology docs with detailed examples are on the app.

mfpro.tigzig.com