Rolling returns calculator for mutual funds and indices

Open the Google Sheet

It opens view-only. Use File → Make a copy to get your own editable version; nothing you type is shared with us or anyone else.

Prefilled with: UTI Nifty 50 Index Fund (regular, growth) NAV to 31 March 2026, from AMFI via mfapi.in.

What it does

A fund’s “5-year return” on a factsheet is one number from one start date. Move the start date by a few months and the number can change a lot. This sheet works out the CAGR (compound annual growth rate) for every N-year window in a daily series, one window per start date, and summarises the spread: the worst, the median, the best, and how often the return fell below zero or below a threshold you choose.

How to use it

Open the sheet and use File → Make a copy.
On the Calculator tab, change the window length or the threshold.
To use another fund or index, paste its dates and values into the Data tab (columns A and B, from row 2, oldest first) and update the description cell.
Recalculating several thousand rows can take a few seconds after you change an input.

With the default data and inputs, every check on the Calculator tab reads TRUE: the sheet reproduces the table in the explainer post.

Price or total return?

Say which you are using. A price index leaves out dividends, so its rolling returns understate what an investor earned. A fund’s growth NAV (net asset value) and a total return index include them. The benchmarks post shows how big the gap gets.

What it can’t tell you

Rolling returns describe the past spread of outcomes for the windows that history happened to contain. They don’t predict the next window, and a series that is only a few windows longer than N says very little.

How it works

The method, step by step, with the worked example this tool reproduces: Rolling returns in mutual funds: how to calculate them.

← All tools