$10,000 in the S&P 500 tracker: price only vs dividends reinvested, year-end 2006 to July 2026

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           argMax(toFloat64(close), window_start) AS close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2005-12-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-07-31')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY d
),
base AS (
    SELECT argMax(close, d) AS start_px
    FROM daily
    WHERE d <= toDate('2005-12-31')
),
year_end AS (
    SELECT toYear(d) AS year,
           argMax(close, d) AS close,
           max(d) AS last_day
    FROM daily
    WHERE d >= toDate('2006-01-01')
    GROUP BY year
),
reinvest AS (
    SELECT dv.ex_dividend_date AS d,
           log(1 + toFloat64(dv.cash_amount) / dl.close) AS log_growth
    FROM global_markets.stocks_dividends AS dv
    INNER JOIN daily AS dl ON dl.d = dv.ex_dividend_date
    WHERE dv.ticker = 'SPY'
      AND dv.cash_amount > 0
      AND dv.ex_dividend_date >= toDate('2006-01-01')
      AND dv.ex_dividend_date <= toDate('2026-07-31')
)
SELECT ye.year AS year,
       round(10000 * ye.close / any(b.start_px)) AS price_only_usd,
       round(10000 * ye.close / any(b.start_px) * exp(sum(ri.log_growth))) AS reinvested_usd,
       round(100 * (1 - exp(-sum(ri.log_growth))), 1) AS dividend_share_pct
FROM year_end AS ye, reinvest AS ri, base AS b
WHERE ri.d <= ye.last_day
GROUP BY ye.year, ye.close
ORDER BY year