The formula, the Excel GEOMEAN shortcut, the full table of windows, and why a fund that wins half its calendar years can lose most of its three-year periods.
A rolling three-year return is the annualised return over every consecutive three-year window in the record: compound the three annual returns, take the cube root, subtract one, then move the window forward a period and repeat. On an illustrative ten-year record, the fund's eight rolling returns run from 3.67 to 9.76 per cent a year, and it beats its benchmark in only 2 of the 8 windows, although it beat it in 5 of the 10 single years.
Worked in full in Asset Management by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →
A single trailing three-year figure, the one on every factsheet, depends heavily on where the window happens to end. Rolling windows show the whole distribution: how often the manager was ahead over a full three years, by how much at best and at worst, and whether the latest figure is typical or an outlier. That is why consultants and investment committees ask for them before they ask for anything else.
| Year | Fund | Benchmark | Difference |
|---|---|---|---|
| 2016 | 12.0 | 10.5 | 1.5 |
| 2017 | −4.5 | −2.0 | −2.5 |
| 2018 | 8.0 | 7.0 | 1.0 |
| 2019 | 15.5 | 13.0 | 2.5 |
| 2020 | 6.0 | 7.5 | −1.5 |
| 2021 | −9.0 | −6.5 | −2.5 |
| 2022 | 18.0 | 16.0 | 2.0 |
| 2023 | 7.5 | 8.5 | −1.0 |
| 2024 | 3.0 | 4.5 | −1.5 |
| 2025 | 11.0 | 9.0 | 2.0 |
| Arithmetic mean | 6.75 | 6.75 |
The returns are time-weighted and net of fees, so that cash flows in and out of the fund do not distort them. With monthly data the method is identical: 36 monthly returns per window, raised to the power of 12/36, and the window moves forward one month at a time.
Rolling n-year return = [(1 + r1) × (1 + r2) × … × (1 + rn)]1/n − 1
Rolling excess return = fund rolling return − benchmark rolling return
In Excel, with returns as decimals in B2:B11, put =GEOMEAN(1+B2:B4)-1 in the row for the third year and fill down; older versions need it entered as an array formula. =PRODUCT(1+B2:B4)^(1/3)-1 gives the same answer.
First window, 2016 to 2018. 1.12 × 0.955 × 1.08 = 1.1552. The cube root, 1.1552 raised to the power 0.3333, is 1.0493, so the fund returned 4.93 per cent a year. The benchmark, on the same arithmetic, returned 5.03. Excess: −0.10.
Then move one year. Drop 2016, add 2019, and recompute. Ten years of data give eight three-year windows.
| Window | Fund | Benchmark | Excess |
|---|---|---|---|
| 2016 to 2018 | 4.93 | 5.03 | −0.10 |
| 2017 to 2019 | 6.01 | 5.82 | 0.19 |
| 2018 to 2020 | 9.76 | 9.13 | 0.63 |
| 2019 to 2021 | 3.67 | 4.34 | −0.67 |
| 2020 to 2022 | 4.41 | 5.25 | −0.84 |
| 2021 to 2023 | 4.90 | 5.58 | −0.68 |
| 2022 to 2024 | 9.32 | 9.56 | −0.24 |
| 2023 to 2025 (trailing) | 7.12 | 7.31 | −0.19 |
The fund's rolling three-year return ranged from 3.67 to 9.76 per cent a year, a spread of 6.09 points from nothing more than the choice of start date. It outperformed in 2 of 8 windows, 25.0 per cent, with a best excess of 0.63 and a worst of −0.84; the average rolling excess was −0.24. Over the full ten years it compounded at 6.44 per cent against 6.55 for the benchmark.
The fund won 5 of the 10 calendar years and had exactly the same arithmetic mean as the benchmark, 6.75 per cent, yet it trails over most three-year periods. Its years are more extreme in both directions, and losses cost more than equal gains recover. Rolling windows expose this volatility drag; a table of calendar years hides it.
| Window | Windows | Outperformed | Hit rate | Best excess | Worst excess | Range of fund returns |
|---|---|---|---|---|---|---|
| 1 year | 10 | 5 | 50.0% | 2.50 | −2.50 | 27.00 |
| 3 years | 8 | 2 | 25.0% | 0.63 | −0.84 | 6.09 |
| 5 years | 6 | 2 | 33.3% | 0.15 | −1.00 | 4.44 |
Longer windows narrow the range, from 27.00 points on single years to 4.44 on five-year windows, which is why mandates are judged over three or five years rather than one. They also mean fewer windows, and those windows overlap: the eight three-year windows share most of their data, so 2 out of 8 is far weaker evidence than eight independent periods would be.
The common mistake is to average the three annual returns instead of compounding them. For 2016 to 2018 the arithmetic mean is 5.17 per cent against a true annualised 4.93, an overstatement of 0.24 points; across the eight windows it reaches 0.60. Because the error grows with volatility, it flatters the more volatile of two managers, which is exactly the comparison the rolling table is meant to make fairly. The second mistake is to roll returns that are not time-weighted: a money-weighted figure moves with the client's own contributions and withdrawals, which says nothing about the manager.
The time-weighted series behind any rolling table is built in how to calculate time-weighted return with cash flows, and the gap between mean and compound return in why CAGR is lower than the average. The book computes four correct returns for one mandate, including a rolling three-year net figure, in the free workbook for this case on the Asset Management companion page.
With annual returns as decimals in B2:B11, enter =GEOMEAN(1+B2:B4)-1 against the third year and fill down; each row gives the annualised return of the three years ending there. For monthly data use 36 months and raise the product to 12/36. On the illustrative record the first window, 2016 to 2018, gives 4.93 per cent a year.
The share of windows in which the fund beat its benchmark. In the illustrative record it is 2 of 8 three-year windows, 25.0 per cent, against 5 of 10 calendar years. Because the windows overlap they are not independent, so a hit rate on eight windows is weaker evidence than it looks; read it with the size of the best and worst excess, here 0.63 and -0.84.
Because compounding penalises volatility. The illustrative fund has exactly the same arithmetic mean as its benchmark, 6.75 per cent, but larger losses in its down years, so over ten years it compounds at 6.44 against 6.55. Three-year windows capture that drag; a list of calendar-year wins does not.
This article is one calculation from Asset Management. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
Get the book on Amazon →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Reading guide: corporate finance, valuation and markets → · All 1340 articles →
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.