> ## 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 Calculate Inflation-Adjusted Returns in SQL

> Calculate real, inflation-adjusted returns in SQL by joining FRED's CPI to adjusted prices: the formula, the query, real SPY results and a missing-month trap.

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

To calculate an inflation-adjusted (real) return in SQL, divide the growth in
an adjusted price by the growth in the Consumer Price Index over the same
months, then subtract 1. With CPI from FRED and prices in the same Snowflake
account, it's one join. Over the 10 years to September 2026, SPY returned
313%, or 196% after inflation.

Below are the formula, the query, real results by year, and one trap that
silently breaks most versions of this query: a missing CPI month.

## The formula

```text theme={null}
real_return = (end_price / start_price) / (end_cpi / start_cpi) - 1
```

Use adjusted prices, so dividends are included, and the CPI value for the
same months as the prices. The common shortcut, nominal return minus
inflation, is only close for small numbers: over 10 years it's off by tens of
percentage points.

## The data

* **Prices:** `adjusted_close` from the Historical Stock Prices listing, one
  row per security per day. SPY stands in for the S\&P 500 here.
* **CPI:** FRED's `CPIAUCSL`, the Consumer Price Index for All Urban
  Consumers, seasonally adjusted, monthly. Each observation is dated the
  first of the month it measures.

## The query

Don't have prices or CPI in Snowflake yet? [How to Get Historical Stock
Prices into Snowflake](https://fruitstand.dev/blog/historical-stock-prices-into-snowflake.md) and
[How to Get FRED Data into Snowflake (3 Ways)](https://fruitstand.dev/blog/fred-data-into-snowflake.md)
cover the ways to load each.

Take the last adjusted close of each month, join it to that month's CPI,
then compare each month with the same month a year earlier:

```sql theme={null}
with prices as (
    select date_trunc('month', date) as month,
           max_by(adjusted_close, date) as price
    from security_price_history_vw          -- Historical Stock Prices
    where code = 'SPY' and exchange_code = 'US'
    group by 1
),
cpi as (
    select date as month, value as cpi
    from fred.fred_series_observations_vw   -- FRED Series
    where series_id = 'CPIAUCSL'
),
monthly as (
    select p.month, p.price, c.cpi
    from prices p
    join cpi c on c.month = p.month
)
select
    e.month,
    round(100 * (e.price / b.price - 1), 2) as nominal_1y,
    round(100 * (e.cpi / b.cpi - 1), 2) as inflation_1y,
    round(100 * ((e.price / b.price) / (e.cpi / b.cpi) - 1), 2) as real_1y
from monthly e
join monthly b
    on b.month = dateadd('month', -12, e.month)
order by e.month desc;
```

The two listings are separate databases in your account, so qualify each
view with the database name you chose when you got the listing.

## Real results

SPY by calendar year, December to December, and the latest 12 months:

| 12 months to | Nominal return | Inflation | Real return |
| - | -: | -: | -: |
| December 2021 | 28.73% | 7.17% | 20.11% |
| December 2022 | −18.18% | 6.40% | −23.10% |
| December 2023 | 26.18% | 3.32% | 22.13% |
| December 2024 | 24.89% | 2.87% | 21.40% |
| December 2025 | 17.72% | 2.65% | 14.68% |
| September 2026 | 15.71% | 3.72% | 11.57% |

2022 is the year inflation mattered most: an 18% loss became a 23% loss in
real terms. Over the full 10 years from September 2016 to September 2026:

| | Cumulative | Per year |
| - | -: | -: |
| Nominal return | 312.9% | 15.23% |
| Inflation (CPI) | 39.4% | 3.38% |
| Real return | 196.1% | 11.47% |

$10,000 in SPY in September 2016 grew to about $41,300, worth about \$29,600
in September 2016 dollars.

The last 10 years were unusually strong. Over longer periods, the same query
gives about 8% a year after inflation:

| Period to September 2026 | Nominal per year | Inflation per year | Real per year |
| - | -: | -: | -: |
| 5 years | 13.70% | 4.19% | 9.13% |
| 10 years | 15.23% | 3.38% | 11.47% |
| 20 years | 11.11% | 2.56% | 8.34% |
| Since January 1993 | 10.81% | 2.58% | 8.03% |

SPY started trading in January 1993, so that's as far back as its prices go.
For earlier decades you need an index series instead of the ETF.

## The missing-month trap

FRED has no CPI observation for October 2025. The release was never
published because of the government shutdown that fall. Any query that
compares "12 rows back" with a window function, such as
`lag(price, 12) over (order by month)`, silently compares with the wrong
month once the joined series is missing a row. In our first version of the
query above, that turned SPY's 2025 return from 17.72% into 14.89%.

The fix is to join on the date itself, `b.month = dateadd('month', -12,
e.month)`, as the query does. A missing month then drops out instead of
shifting every comparison after it. Core CPI (`CPILFESL`) has the same gap; the
PCE price index (`PCEPI`), from a different agency, has an October 2025
value. The same fix applies to any monthly or quarterly FRED series with a
gap.

## Other things to get right

* **Same months on both sides.** CPI is monthly, so use month-end prices,
  not daily prices joined to the nearest CPI value.
* **CPI lags the calendar.** September's CPI is published in mid-October, so
  the latest month you can calculate trails today by about six weeks.
* **Pick the right index.** `CPIAUCSL` is the headline index. Core CPI,
  without food and energy, is `CPILFESL`, and the Fed's preferred PCE price
  index is `PCEPI`.
* **Revisions.** Seasonally adjusted CPI is revised each February. The FRED
  Series listing also has vintage observations if you need CPI as it was
  first published.

## F.A.Q.

> [accordion] What's the difference between real and nominal returns?
>
> A nominal return is the change in price, including dividends when you use adjusted prices. A real return removes inflation, so it measures the change in purchasing power.

> [accordion] What's the average S\&P 500 return after inflation?
>
> About 8% a year for SPY, from its launch in January 1993 to September 2026: 10.81% a year nominal, less 2.58% a year of inflation, compounded. The last 10 years were higher, at 11.47% a year real.

> [accordion] Can I just subtract inflation from the return?
>
> Only as a rough estimate over short periods. Over 10 years SPY's nominal return minus inflation is 273.5 points, while the true real return is 196.1%. Divide the growth factors instead.

> [accordion] Which FRED series should I use for inflation?
>
> `CPIAUCSL` (headline CPI, seasonally adjusted) is the standard choice. Use `CPILFESL` for core CPI or `PCEPI` for the PCE price index.

> [accordion] Can I do this for a mutual fund?
>
> Yes. Mutual funds are in the same price table with daily NAV and adjusted history, so replace SPY with the fund's ticker. For ready-made nominal returns, the Fund Returns listing has them for about 32,000 funds and ETFs.

This post uses two Fruit Stand listings on the Snowflake Marketplace:
[FRED Series](https://app.snowflake.com/marketplace/listing/GZTYZ40XYNE) for
CPI and [Historical Stock Prices](https://app.snowflake.com/marketplace/listing/GZTYZTG3LNFA)
for adjusted prices. Both have a free 30-day trial. The schemas are in the
[FRED docs](https://fruitstand.dev/datasets/fred-series.md) and the
[stock price docs](https://fruitstand.dev/datasets/historical-stock-prices.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.