The S&P 500's Shiller CAPE ratio (price divided by ten years of average inflation-adjusted earnings) stood at about 37.85 in late May 2026, roughly 120% above its long-run average of 17.2 and around the 96th percentile of readings since 1881. This free Excel dashboard puts that reading in context: KPI tiles, a CAPE history chart back to 1881, a sector valuation heatmap, seven mean-reversion scenarios that translate CAPE into implied 10-year returns (about 4.2% nominal in the base case), and a 27-stock screener that compares each company with its sector's CAPE benchmark. Ticker-level cells are live MarketXLS formulas; the index and sector CAPE anchors are research-derived values documented in the workbook. The template is educational, not investment advice. Both files are linked at the end.
Try it in your own spreadsheet
Get the free MarketXLS sample workbook (.xlsx).
No account needed. MarketXLS live data requires a paid plan. Preview workbook
Explore more videos
The headline numbers (May 2026)
| Metric | Value | Reference |
|---|---|---|
| S&P 500 CAPE (May 2026) | 37.85 | Shiller online data |
| Long-run CAPE average (1881-2025) | 17.20 | Shiller online data |
| CAPE premium vs long-run average | +120% | Computed |
| Percentile rank vs 1881-2026 | ~96th | Computed |
| Post-1990 CAPE average | ~25 | Computed |
| Implied 10-year nominal return at base case | ~4.2% | Template Scenario Analysis |
| Implied 10-year real return at base case | ~1.2% | Template Scenario Analysis |
A premium of roughly 120 percent above the long-run average is among the highest readings in CAPE history. The periods that ranked higher were the dot-com peak in 1999-2000 and a small cluster in late 2021. Whether you call this expensive, structurally re-rated, or somewhere in between is the entire debate the template is built to support.
What is the CAPE ratio (Shiller PE)
The CAPE ratio - short for Cyclically Adjusted Price-to-Earnings - was popularized by Yale economist Robert Shiller and his collaborator John Y. Campbell in the late 1980s. It takes the real (inflation-adjusted) price of the S&P 500 and divides it by the average of ten years of real earnings.
CAPE = Real Price / Average of 10 Years of Real EPS
The trick is the ten-year denominator. A simple trailing PE ratio dances around with the business cycle because earnings spike during expansions and crash during recessions. By averaging earnings over a full decade, CAPE smooths cyclicality so that comparisons across decades are meaningful. A 1929 CAPE and a 2026 CAPE can be put on the same chart and read straight across.
CAPE is most useful at extremes. When CAPE has been below 10, the next decade's real returns have historically averaged well into the double digits. When CAPE has been above 30, the next decade's real returns have averaged below 3 percent. The relationship is not deterministic - the dispersion is wide - but it is the strongest single predictor of long-run equity returns in the literature.
It is also widely misused. CAPE is not a market timing signal. The market spent most of the 1995-2000 stretch above its 1929 CAPE peak and kept climbing. Anyone who shorted CAPE at 30 would have spent five years getting cooked. The template treats CAPE as a strategic positioning tool, not a tactical one.
Why a dashboard, not a chart
Most CAPE content online stops at "here is a 130-year line chart." That is useful for a glance but misses the practical questions you actually need to answer:
- What sectors are driving the headline CAPE higher? Is it tech alone or is the breadth wider?
- If CAPE mean-reverts halfway over the next decade, what does that imply for the S&P 500 price?
- How sensitive is that implied return to my assumed earnings growth?
- Among large-cap stocks, which ones trade at the deepest premium or discount to their sector's CAPE benchmark?
- How should defensive allocation shift as CAPE moves between regimes?
A dashboard answers those in one open. The template is structured around exactly these questions.
What's inside the template (10-sheet walkthrough)
The CAPE Ratio Dashboard is a 10-plus sheet premium workbook. Every sheet has a tab color, frozen panes, a header bar in MarketXLS navy, and a footer pointing back to marketxls.com. The dashboard view itself hides gridlines and is set up to print cleanly on one landscape page.
Sheet 1 - Cover
A branded title page with the template name, 2026 edition badge, table of contents, data-as-of stamp, and a centered MarketXLS link strip at the bottom. Gridlines off, navy background bar at the top. It is what someone sees on first open and sets the tone for the rest of the workbook.
Sheet 2 - How To Use
A step-by-step tutorial with 8 numbered steps. Open the file, refresh MarketXLS, set inputs, read the dashboard, run scenarios, drill into sectors, screen tickers, size positions. Below the steps is a comprehensive list of every MarketXLS formula used in the workbook with one-line descriptions of each. This sheet alone is a reference card for a CAPE-aware investor.
Sheet 3 - Dashboard
The headline view. The top of the sheet has a 6-tile KPI row: current CAPE, long-run average, CAPE premium percentage, S&P 500 level, mean-reversion target, and percentile rank. Below that is a line chart showing CAPE from 1990 through 2026 plotted against the long-run average so the gap is visible at a glance. Beneath the chart is the sector CAPE heatmap - a color-scaled table showing every GICS sector's current CAPE, historical average, premium percent, and regime label (Expensive, Above Average, Fair Value, Cheap). A second embedded bar chart compares current sector CAPE against history. Gridlines are hidden, print area is set, the whole sheet is designed to be the one view you keep open while working.
Sheet 4 - Inputs & Controls
Every yellow cell on this sheet drives downstream calculations. Pick your scenario from a dropdown (7 options ranging from Bull to Crisis Trough), enter your portfolio size, set sleeve weights for defensive equity, real assets, equity core, and cash. Choose your reference index (SPY, IVV, VOO, RSP, IWM, or QQQ) from a dropdown. Set your investment horizon in years and pick a risk tier. Three slots for custom tickers feed the Stock Screener sheet. Every input has a yellow fill, thick navy border, and a description column explaining what it controls.
Sheet 5 - Scenario Analysis
Seven named scenarios run side by side: Bull (No Reversion), Soft Landing, Base (Half Reversion), Mean Reversion, Stagflation, Bear (Deep Reversion), and Crisis Trough. Each row shows the terminal CAPE assumption, the 10-year EPS growth assumption, implied EPS in 10 years, S&P 500 price target, total return, annualized return, and real return after a 3 percent inflation deduction. Conditional formatting paints the return columns red to green and a 3-arrow icon set marks each annualized return as low, medium, or high.
Below the scenarios is a sensitivity matrix: terminal CAPE on the row axis (12 to 38) crossed with EPS growth on the column axis (2% to 8%). Every cell is the annualized return implied by that combination. A color scale paints the matrix red where returns turn negative and green where they reach 10 percent.
Sheet 6 - Strategy & Allocation
Sixteen rows mapping CAPE regime to suggested portfolio sleeves. When CAPE is above 35 (where we are now), the table suggests 40 percent quality compounders, 20 percent real assets (gold, TIPS, energy), 15 percent international, 20 percent cash and T-bills, 5 percent low-vol hedge. As CAPE falls into the 25-35 band, equity tilt rises and cash falls. Below 15, equity tilt maxes out near 85 percent. Each row includes a historical 10-year CAGR estimate and an estimated volatility figure for that sleeve. Conditional formatting paints the weight column with data bars and the CAGR column with a red-amber-green color scale.
Sheet 7 - Portfolio Sizing
A simple but useful sheet that translates the recommended sleeve weights into dollar allocations using your portfolio size from the Inputs sheet. Six rows (equity core, defensive, real assets, international, cash, hedge), one dollar figure each, sample tickers for each sleeve. An embedded pie chart shows the allocation visually with percent labels.
Sheet 8 - Sector Heatmap
A deeper version of the sector view from the Dashboard. Eleven GICS sectors ranked by current CAPE premium versus their 35-year average. Columns: current CAPE, historical average, premium percent, estimated Z-score (number of standard deviations from history), regime label. The Z-score column gets a 3-symbol icon set (down arrow, dash, up arrow) so outliers are obvious. The premium column gets the same red-green color scale used on the Dashboard. The current CAPE column gets data bars so the absolute magnitude is visible.
Sheet 9 - Stock Screener
Twenty-seven large-cap names representing every major GICS sector. Columns: ticker, company name, sector, current price, trailing PE, forward PE, price-to-book, price-to-sales, price-to-cash-flow, dividend yield, beta, and CAPE premium versus the company's sector benchmark. In the template version every value is a live MarketXLS formula - =QM_Last("AAPL"), =PERatio("AAPL"), =ForwardPE("AAPL"), =PriceToBook("AAPL"), and so on. The CAPE Premium column is calculated as (PERatio(ticker) - sector_cape) / sector_cape so it updates live. Color scales make it easy to spot which names trade at a steep premium to their sector and which trade at a discount.
Sheet 10 - Methodology
A one-page explainer covering the Shiller CAPE definition, data sources, the historical average derivation, sector CAPE computation method, scenario mechanics, the sensitivity matrix logic, and mean-reversion caveats. Also includes a section on the model's limitations - CAPE ignores accounting changes, payout structures, sector composition drift, and rate cycles. Reading this sheet before trusting the dashboard is worth five minutes.
Sheet 11 - Glossary & Disclaimer
Fourteen term definitions covering CAPE, Shiller PE, PE-10, mean reversion, earnings yield, ERP, quality compounder, defensive sector, real assets, TIPS, hedge sleeve, annualized return, total return, and Z-score. Below the terms is a clear educational-only disclaimer.
Reading the May 2026 dashboard
With the Dashboard sheet open in front of you, here is what the numbers say.
CAPE at 37.85 puts the S&P 500 in the 96th percentile of its 145-year history. Only the dot-com peak (around 44 in late 1999) and the very top of the 2021 melt-up reached higher levels. The market has rarely been more expensive on this metric.
The premium versus long-run average is 120 percent. That is the key number to internalize. A CAPE of 37.85 against a 145-year average of 17.20 means we are paying more than double what a long-run buyer would have paid historically.
Sector breadth is mixed but tech leads. Technology is at a sector CAPE around 42.6, against a long-run sector average closer to 28.4 - a 50 percent premium. Consumer Discretionary is similarly stretched at 38.2. At the other extreme, Energy sits near 14.8 (below its sector long-run of 18.6) and Financials at 16.4 (below their 17.8 average). The headline CAPE premium is not uniform; it is concentrated in growth-heavy sectors.
Mean-reversion math is sobering at the base case. Set the scenario selector to "Base (Half Reversion)" - terminal CAPE 28, EPS growth 5 percent. The template returns an implied 10-year S&P 500 price target near 6,800. Sounds positive until you compute annualized return: roughly 1.5 percent nominal, slightly negative in real terms after 3 percent inflation.
The bull case is plausible too. "Bull (No Reversion)" scenario - terminal CAPE 38, EPS growth 8 percent - returns an annualized 8 percent nominal. That outcome requires earnings to grow at the upper end of their post-WW2 range while multiples hold. It is not impossible, especially if AI productivity tailwinds materialize. The dashboard does not pick a side; it shows the range.
The MarketXLS implementation
Everything ticker-level in the template is built on standard MarketXLS functions. Here are the most important ones, all verified against the function docs:
=QM_Last("AAPL") → live price
=PERatio("AAPL") → trailing 12-month PE
=ForwardPE("AAPL") → forward PE on consensus EPS
=EarningsPerShare("AAPL") → trailing 12-month EPS
=DividendYield("AAPL") → dividend yield as decimal
=PriceToBook("AAPL") → price-to-book
=PriceToSales("AAPL") → price-to-sales
=PriceToCashFlow("AAPL") → price-to-cash-flow
=PEGRatio("AAPL") → PE divided by growth rate
=Beta("AAPL") → 5-year beta vs S&P 500
=ReturnOnEquity("AAPL") → ROE as decimal
=OperatingMargin("AAPL") → operating margin as decimal
=Sector("AAPL") → GICS sector classification
=MarketCapitalization("AAPL") → market cap in USD
=ChangePercentYTD("AAPL") → year-to-date return
=QuarterlyEarningsGrowthYoY("AAPL") → latest quarter YoY EPS growth
Formula documentation: QM_Last, PERatio, ForwardPE, EarningsPerShare, DividendYield, PriceToBook, PriceToSales, PriceToCashFlow, PEGRatio, Beta, ReturnOnEquity, OperatingMargin, Sector, MarketCapitalization, ChangePercentYTD, QuarterlyEarningsGrowthYoY
CAPE itself is a macro metric, not a per-ticker function - it requires 10 years of inflation-adjusted index earnings, which the template anchors to current research-derived values for the S&P 500 and each sector. The methodology sheet documents the source for each anchor. Every ticker-level cell, however, is fully live.
If you want to bolt a custom CAPE proxy onto your own watchlist, the simplest approximation is a 10-year average PE per ticker, which you can build off =PERatio("AAPL") and historical earnings via =GetHistory("AAPL","EPS",...). The template's Stock Screener uses current PE versus a sector CAPE benchmark - a faster proxy that still captures most of the signal.
Building this from scratch (if you prefer)
If you want to replicate the structure manually rather than start from the template, here is the workflow:
- Cover and How To Use sheets. Plain text and a TOC. The polish matters; users judge a template in the first five seconds.
- Build the CAPE history block. Either type in a year-by-year series from Shiller's website (online data) or pull a long-history index series via MarketXLS and compute the 10-year smoothing yourself.
- Set up the KPI tiles. Six merged-cell tiles across the top of the Dashboard sheet. Each tile gets a label row, big-number row, and delta row. Use white fill with thin borders and alternate the fill on every other tile.
- Build the sector heatmap. One row per GICS sector with current CAPE, historical average, and premium percent. Apply a 3-color scale to the premium column (green-yellow-red) and data bars to the current CAPE column.
- Add the scenario table. Each scenario is a row with terminal CAPE, EPS growth, computed price target, computed total return, computed annualized return. The math is fully transparent in the cells.
- Build the sensitivity matrix. Terminal CAPE on the row axis, EPS growth on the column axis. Each cell computes the annualized return implied. Apply a single color scale to the entire matrix.
- Set the inputs sheet. Yellow fill on every input cell with thick navy borders. Data validation dropdowns for the scenario name, risk tier, and reference index.
- Wire downstream sheets to the inputs sheet. Any cell that depends on portfolio size or scenario should reference the Inputs sheet, not hardcode a value.
Doing this from scratch will take several hours. The template gives you the same result in seconds.
Three practical use cases
Use case 1 - Long-horizon allocation review. You are a financial advisor sitting with a client who has a 10-year horizon. Open the Scenario Analysis sheet. Show the client three scenarios (Bull, Base, Bear) and walk through the implied returns. Use the Strategy & Allocation sheet to suggest a defensive tilt appropriate to the current CAPE regime. The dashboard turns an abstract macro discussion into a concrete portfolio conversation.
Use case 2 - Sector overweight/underweight decisions. You manage a tilted equity portfolio and want to lean into sectors trading at a discount to their CAPE history. Open the Sector Heatmap sheet. Energy and Financials currently show negative premium - meaning their CAPE is below their long-run average. That is not a buy signal on its own, but it is a starting point for further work.
Use case 3 - Single-stock context. You are evaluating Apple, Microsoft, or another large-cap. Open the Stock Screener sheet. Note that Apple's TTM PE is currently in the low 30s while Technology sector CAPE is near 42 - Apple looks relatively reasonable against its sector. NVIDIA, by contrast, trades at a TTM PE near 59, well above the sector benchmark. The CAPE Premium column quantifies this gap automatically.
Limitations and honest caveats
CAPE has been bearish for a long time. Anyone who hedged based on CAPE alone in 2014 (CAPE around 25) missed a substantial bull run. The metric is most useful when it disagrees with itself across decades - i.e., when current CAPE is well outside its post-1990 average, not just its 145-year average. The template includes both anchors.
CAPE does not account for changes in accounting standards. Post-2001 GAAP earnings include significant write-downs and impairments that pre-1990 earnings did not. Some quants argue this biases CAPE upward by 2-4 points. The template's mean-reversion scenarios let you stress-test this by using the post-1990 average (around 25) as the anchor instead of the 145-year average (17.20).
CAPE is not a timing signal. The dashboard is built to inform 10-year positioning, not 12-month tactical trades. Investors who treat CAPE as a sell-signal at any specific level tend to get whipsawed.
Sector CAPE estimates are less standardized than the headline figure. Different research providers produce different sector CAPE numbers depending on how they handle sector reclassifications, real earnings calculations, and index construction. The template anchors to a set of estimates documented in the Methodology sheet; treat them as a starting point, not gospel.
Download the templates
Download the templates:
- - Static dashboard with embedded formula comments on every data cell
- - Live-updating Stock Screener, fully wired MarketXLS formulas, dropdown-driven inputs
The static sample workbook is free to download. Refreshing MarketXLS formulas in the live workbook requires a paid MarketXLS subscription. The static version is faster to skim and gives you the dashboard at a glance. The MarketXLS version is what you want if you plan to actually use the template - its ticker-level formulas refresh when Excel recalculates.
FAQ
Q: What is the CAPE ratio in simple terms?
CAPE is the S&P 500's price divided by ten years of average real earnings. It is a longer-horizon version of the PE ratio, smoothed to remove the effects of the business cycle. A high CAPE suggests the market is expensive relative to its sustainable earning power; a low CAPE suggests it is cheap.
Q: Is the CAPE ratio a good indicator?
Over long horizons (about ten years), CAPE has historically been one of the stronger valuation predictors of equity returns. Over multi-year horizons, it is noisy. Over 12-month horizons, it has essentially no predictive power. Use it for strategic positioning, not tactical trading.
Q: What is a normal CAPE ratio?
The long-run average from 1881-2025 is roughly 17.20. The post-1990 average is closer to 25, reflecting structural changes in the market - lower rates, higher tech weight, more buybacks, accounting changes. Pick whichever anchor matches your view of how much of the rise is permanent.
Q: Why is the CAPE ratio so high in 2026?
Several factors: prolonged low real interest rates relative to the pre-2000 era, large-cap tech earnings growing faster than the broader market, sustained buyback activity supporting share prices, and AI optimism driving a multi-year re-rating of the Mag-7 and AI-exposed industrials. Whether these factors justify a permanent re-rating is the central debate.
Q: How do I calculate CAPE myself in Excel?
You need (1) the S&P 500 real (inflation-adjusted) price, (2) ten years of S&P 500 real EPS, (3) the average of those ten EPS values. CAPE equals (1) divided by (3). MarketXLS can pull historical S&P 500 prices via =GetHistory("^SPX","Close",...); the inflation adjustment uses CPI from BLS. For most users it is easier to use the template's pre-computed values and focus on the analysis.
Q: Can CAPE stay elevated forever?
In theory yes, in practice it has not. Every period of elevated CAPE in history has eventually been followed by a period of below-average returns. The reversion has happened sometimes via crash (1929, 2000, 2007) and sometimes via long sideways grinding (1966-1981, where the market went nowhere in nominal terms while inflation eroded real value). The template's scenarios cover both shapes.
The bottom line
CAPE at 37.85 is the second-highest reading in U.S. equity history. That does not mean the market is about to crash. It does mean that any investor planning to hold equities for the next ten years should run the math on what mean reversion (even partial) would imply for their returns. The CAPE Ratio Dashboard is built to make that math fast: pick a scenario, read the implied 10-year return, decide whether your current allocation is consistent with that expectation, and adjust if not.
For a deeper look at how this template fits into the broader MarketXLS workflow, see our Equity Risk Premium Dashboard (May 2026), the Magnificent 7 earnings dashboard, and the Sector Valuation Dashboard (May 2026). For background on the live functions powering the workbook, the Stock Market Data in Excel guide walks through the full MarketXLS toolkit.
Start with MarketXLS to power the live formulas, or book a demo to see how advisors are using dashboards like this with their clients.
