Executive summary
From 1871 to 2023 the S&P 500 returned 6.9% a year after inflation, with dividends reinvested. That average hides the number that matters for planning: when stocks started cheap, the median return over the next ten years was 10.6%. When they started expensive, it was 4.0%, and three in ten of those decades lost money.
At the latest valuation in the data, history points to planning on returns closer to 4% after inflation than 7% for the next decade.
Historical analysis, not investment advice. Past returns don't predict future returns.
Business challenge
Return assumptions drive real decisions: how much a pension sponsor must contribute, what an FP&A team books for an investment line, how much a household saves for retirement. Yet the assumption is usually one unexamined number.
Three questions usually go unasked:
- Is the number before or after inflation?
- How often does it fail over the time horizon that actually matters?
- Does today's starting valuation change the odds?
Why it matters
The gap between a 7% and a 4% real return compounds. Over ten years, $1 million grows to about $1.97 million at 7% but only $1.48 million at 4%, both in today's dollars. A plan built on the wrong number can be underfunded by roughly half a million dollars per million invested, and nobody notices until it is too late to fix cheaply.
Data sources
| Source | What it provides | Coverage |
|---|---|---|
| Robert Shiller, Irrational Exuberance monthly data | S&P 500 price, dividends, earnings, CPI, 10-year rate | 1871 onward |
| US Bureau of Labor Statistics, CPI-U | Official monthly consumer price index | 1913 onward |
Both are public. No employer data is used anywhere in this project.
Technology stack
- PostgreSQL as the warehouse, with separate
staging,coreandmartschemas and role-based access (ETL account, read-only analyst role). - SQL for all modeling: CTEs, window functions, gaps-and-islands,
NTILEand percentiles. - Python for extract and load, with file checksums and a load audit table.
- Power BI: star-schema semantic model, DAX measures, three-page report saved as a text-based project so every change is versioned.
- Git and GitHub Actions: every change rebuilds the warehouse from scratch and runs the tests; data refreshes monthly.
Methodology
Layered pipeline
Raw files land in staging exactly as received. The core layer cleans and types them and joins the two inflation series. The mart layer holds the analysis tables the report reads: monthly returns, growth indexes, valuation, rolling returns and drawdown episodes.
Calculations in SQL
- Rolling returns over 1, 10, 20 and 30 years using
LAGandLEAD, compounded through running sums of log returns. - CAPE (price divided by ten-year average real earnings) computed from scratch and reconciled against Shiller's published series to within about 1%.
- Valuation buckets: every month ranked into fifths by CAPE with
NTILE(5), then compared on what happened next. - Drawdown episodes found with a gaps-and-islands pattern: each new all-time high starts a new episode.
Data quality as a gate
Seven automated tests run after every build, and any failure stops the export to Power BI. They caught two real problems: the source uses 0 to mean "not yet published", and BLS never published October 2025 CPI because of the government shutdown. Both are handled and documented in the data dictionary.
I built the project with AI-assisted development (Claude), the same way I use AI in my day job to move faster on analysis.
Analysis
The interactive report at the top of this page is a web version of my Power BI report, built from the same data model and the same pipeline output. Each page makes one point: the long-run return, how valuation changes it, and how time changes the risk.
See the original Power BI pages
Key insights
| Finding | Evidence |
|---|---|
| 6.9% | Real annual return, 1871 to 2023 (9.2% nominal, 2.1% inflation). |
| 31% to 0% | Share of periods that lost purchasing power: 31% over 1 year, 11% over 10 years, 1 in 1,589 over 20 years, none over 30 years. |
| 10.6% vs 4.0% | Median next-10-year real return from the cheapest vs the most expensive fifth of starting valuations. The cheapest fifth never lost money over the decade; 30% of the most expensive decades did. |
| 94th pct. | Latest CAPE in the data (29.8, June 2023) was higher than 94% of all months since 1880. |
| 153 months | Time to recover the 2000 to 2009 decline after inflation. The 1929 to 1932 fall was the deepest at 77%. |
Recommendations
For anyone setting a long-term equity return assumption:
- State it after inflation. A nominal number mixed with real spending goals overstates what a plan can afford.
- Set a range, not a point, and anchor it to valuation. Use something near 4% real as the base case while valuations sit in the top fifth, and treat 7% as the upside.
- Match the risk test to the horizon. Short horizons need a cash buffer; the data shows 10-year periods still lose money about one time in nine.
- Refresh it on a schedule. The pipeline updates monthly, so the assumption can be revisited with the data instead of by habit.
Business impact
The analysis turns "assume 7%" into a defensible range tied to observable conditions. For a $1 million portfolio over ten years, the difference between the two assumptions is about $490,000 in today's dollars. That is the planning risk this work makes visible before it becomes a shortfall.
The same pattern applies to any enterprise forecast: separate the long-run average from the conditions you are starting in, test how often the assumption fails, and automate the refresh so the answer stays current.
Lessons learned
- Profile the source before modeling. A zero that means "not published" would have silently dragged averages down.
- Make the report independent of the database. Committing the mart extracts means anyone can open the dashboard without installing PostgreSQL.
- Valid files aren't a working report. Opening the report in Power BI Desktop surfaced four issues schema checks missed, including a DAX reserved word.
Future improvements
- Extend earnings and dividend data past mid-2023 with a second public source, so valuation readings are current.
- Add bonds and cash to compare the equity premium over time.
- Model retirement withdrawals by resampling historical sequences, to show how the order of returns affects a plan.
- Publish an interactive version of the report.