> ## Documentation Index
> Fetch the complete documentation index at: https://fruitstand.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# How to Compare a Fund's Returns With Its Benchmark in SQL

> Compare a mutual fund's or ETF's trailing returns with a benchmark like SPY in SQL: one join on ready-made returns, with real results for three active funds.

*By [Fruit Stand Team](https://fruitstand.dev/about.md) · November 3, 2026 · Guides*

To compare a fund's returns with its benchmark in SQL, join the fund's
trailing returns to the benchmark's for the same as-of date and subtract.
With ready-made 1- to 10-year returns, such as Fruit Stand's Fund Returns
listing on Snowflake, it's one query. Against SPY, Fidelity Contrafund was
ahead by 2.5 points a year over 10 years.

Below is the query, real results for three well-known active funds, and the
things that make a comparison fair or misleading.

## The query

Don't have fund returns in Snowflake yet? [Where to Get Trailing Returns for
Every US Fund and ETF in Bulk](https://fruitstand.dev/blog/trailing-returns-us-funds-etfs-bulk.md)
compares the options, from licensed feeds to calculating them yourself.

Trailing returns are one row per fund per as-of date, so the comparison
joins the fund's row to the benchmark's row for the same date:

```sql theme={null}
with latest as (
    select max(as_of_date) as as_of_date
    from ext_fund_trailing_returns_vw
    where code = 'SPY'
),
fund as (
    select r.*
    from ext_fund_trailing_returns_vw r
    join latest l on r.as_of_date = l.as_of_date
    where r.code in ('FCNTX', 'DODGX', 'PRGFX')
),
bench as (
    select r.*
    from ext_fund_trailing_returns_vw r
    join latest l on r.as_of_date = l.as_of_date
    where r.code = 'SPY'
)
select
    f.code,
    f.as_of_date,
    round(100 * (f.return_1y - b.return_1y), 2) as excess_1y,
    round(100 * (f.return_3y - b.return_3y), 2) as excess_3y,
    round(100 * (f.return_5y - b.return_5y), 2) as excess_5y,
    round(100 * (f.return_10y - b.return_10y), 2) as excess_10y
from fund f
cross join bench b
order by excess_10y desc;
```

Periods of 2 years and longer are annualized, so the 5- and 10-year
differences are percentage points a year. Shorter periods are cumulative.

## Real results

Trailing returns as of October 30, 2026:

| Fund | 1 year | 3 years | 5 years | 10 years |
| - | -: | -: | -: | -: |
| SPY (S\&P 500 benchmark) | 12.36% | 23.54% | 11.96% | 15.32% |
| Fidelity Contrafund (FCNTX) | 12.08% | 27.15% | 13.29% | 17.82% |
| T. Rowe Price Growth Stock (PRGFX) | 11.08% | 28.85% | 13.05% | 21.92% |
| Dodge & Cox Stock (DODGX) | 14.05% | 17.91% | 9.52% | 12.82% |

And the difference from SPY, in percentage points:

| Fund | 1 year | 3 years | 5 years | 10 years |
| - | -: | -: | -: | -: |
| FCNTX | −0.28 | +3.61 | +1.33 | +2.49 |
| PRGFX | −1.28 | +5.31 | +1.09 | +6.60 |
| DODGX | +1.68 | −5.63 | −2.44 | −2.50 |

Two growth funds beat the S\&P 500 over 3, 5 and 10 years but trailed it over
the last year. Dodge & Cox, a value fund, did the opposite: ahead over 1
year, behind over longer periods. One number never tells the whole story,
which is why comparing every period side by side is worth the extra
columns.

These are returns calculated from adjusted prices, so they include
reinvested distributions and are after the fund's expenses. They aren't
after sales charges or taxes.

## Picking the right benchmark

* **Match the fund's style.** The S\&P 500 is fine for a large-cap blend
  fund. A growth fund is better compared with a growth index ETF such as
  IWF, and a value fund with IWD. Comparing Dodge & Cox to the S\&P 500 makes
  a value fund look worse in years growth stocks led.
* **Use an investable benchmark.** An index ETF like SPY has its own small
  expense ratio, which makes the comparison against a real alternative, not
  a theoretical index.
* **Use the same as-of date.** The query joins on `as_of_date` for this
  reason. Comparing a fund's month-end return with a benchmark's latest
  return mixes two different windows.
* **Check the fund's history.** A fund younger than the period has no
  return for it. Filter out nulls rather than comparing against zero.

## Comparing across calendar years

Trailing returns move every day. To see how often a fund beat its benchmark,
compare calendar years instead:

```sql theme={null}
select
    f.calendar_year,
    round(100 * f.calendar_year_return, 2) as fund_return,
    round(100 * b.calendar_year_return, 2) as spy_return,
    round(100 * (f.calendar_year_return - b.calendar_year_return), 2) as difference
from ext_fund_calendar_year_returns_vw f
join ext_fund_calendar_year_returns_vw b
    on b.calendar_year = f.calendar_year
   and b.code = 'SPY'
where f.code = 'FCNTX'
  and f.calendar_year between 2016 and 2025
order by f.calendar_year;
```

Count the positive rows and you have how many of the last ten years the fund
won. For Contrafund it's 6 of 10, from 14.15 points ahead in 2020 to 9.30
points behind in 2022, which is why a 10-year average alone hides how bumpy
the ride was.

## F.A.Q.

> [accordion] What's the difference between excess return and alpha?
>
> Excess return is the simple difference between the fund's return and the benchmark's. Alpha adjusts for how much market risk the fund took, usually with a regression of the fund's returns on the benchmark's. Excess return is the right first check; alpha needs a return series, not just trailing returns.

> [accordion] Do these returns include dividends and fees?
>
> They're calculated from adjusted prices, so they include reinvested dividends and capital gains distributions, and they're net of the fund's expense ratio. They don't deduct sales charges or taxes.

> [accordion] Can I compare a mutual fund with an ETF?
>
> Yes. Mutual funds and ETFs are in the same returns table, keyed by ticker, so a fund-versus-ETF comparison is the same query. ETF returns are based on market price; mutual fund returns on NAV.

> [accordion] How do I rank every fund against a benchmark?
>
> Drop the ticker filter in the fund CTE and sort by the excess return. Filter to funds with a non-null 10-year return so younger funds don't distort the ranking.

To compare any US fund or ETF with its benchmark, start a free 30-day trial of
the [Fund Returns listing on the Snowflake Marketplace](https://app.snowflake.com/marketplace/listing/GZTYZTG3LLO9).
It has 1-day to 20-year trailing returns and calendar-year returns for about
32,000 US mutual funds and ETFs. The schema is in the
[dataset docs](https://fruitstand.dev/datasets/fund-returns.md). For where else to get fund returns,
see [Where to Get Trailing Returns for Every US Fund and ETF in Bulk](https://fruitstand.dev/blog/trailing-returns-us-funds-etfs-bulk.md).
Questions? Reach out at [contact@fruitstand.dev](mailto:contact@fruitstand.dev).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.