Equity risk premium dashboard Excel - if that brought you here, you are trying to answer one of the oldest questions in markets: are stocks cheap or expensive compared with bonds right now? This guide walks through a working dashboard that tracks the equity risk premium (ERP) live in Excel, lets you stress test it under different earnings and rate scenarios, and turns the result into a concrete stock vs bond allocation. The data updates automatically through MarketXLS, so the spread, the regime label, and the suggested mix all refresh whenever Excel recalculates.
The dashboard ships in two files. The sample workbook has every cell pre-filled with values dated 2026-05-05 so you can study the layout and the math without any add-in installed. The template workbook is the same design with live MarketXLS formulas wired into the data cells, ready to update on your machine. Both are linked in the "Get the workbook" section near the end.
Equity risk premium dashboard Excel: snapshot for May 2026
The dashboard centers on the Fed Model spread - the SPX forward earnings yield minus the 10-year US Treasury yield - alongside Damodaran-style implied ERP context. Here is the snapshot the sample workbook displays for May 5, 2026.
| Metric | Value | What it tells you |
|---|---|---|
| SPX forward P/E | 21.4x | Price paid per dollar of next-year earnings |
| SPX forward earnings yield | 4.67% | The inverse of forward P/E |
| US 10-year Treasury yield | 4.30% | Risk-free comparison anchor |
| Fed Model spread (ERP) | 0.37% | Earnings yield minus 10-year |
| Damodaran historical ERP avg | 4.60% | Long-run implied ERP benchmark |
| AAA corporate bond yield | 5.05% | Investment-grade credit anchor |
| BAA corporate bond yield | 5.85% | Lower-grade credit anchor |
| VIX | 16.4 | Realized volatility regime |
| Regime label | RICH | Spread well below historical average |
| Suggested stock weight | 32.6% | Glide path output, bounded 30-90% |
A 0.37% Fed Model spread is unusually thin. For context, the dashboard's historical reference path puts the average closer to 3.0% and the long-run Damodaran implied ERP near 4.6%. The point of the dashboard is not to tell you what to do with that information but to make the comparison auditable, so you can decide whether the regime label and the glide path output match your own framework.
What is the equity risk premium and why dashboard it?
The equity risk premium is the extra return investors expect for owning stocks instead of risk-free Treasuries. There are several ways to estimate it, and reasonable people disagree on the right one. The dashboard tracks the two most common framings:
- Fed Model spread - SPX forward earnings yield minus the 10-year Treasury yield. Simple, transparent, easy to compute every day. The trade-off is that it ignores expected earnings growth, so it tends to look low when growth expectations are high.
- Implied / Damodaran ERP - the discount rate that, applied to projected SPX cash flows, equates them to the current index level. More theoretically grounded, but sensitive to your growth and payout assumptions.
The reason to dashboard either of them is that both move slowly enough to be useful for portfolio decisions, and quickly enough that a static PDF report goes stale within a week. A live Excel dashboard gives you the current reading in the morning, lets you answer "what if rates fall 50 bps?" without leaving the spreadsheet, and stores your assumptions so you can audit them later.
What is inside the workbook
The workbook ships with ten sheets, each playing a specific role.
| # | Sheet | Purpose |
|---|---|---|
| 1 | Cover | Title page, data-as-of date, color-coded regime tile |
| 2 | How To Use | Step-by-step guide to inputs, outputs, and refresh |
| 3 | Dashboard | KPI tile row, ERP gauge, sector heatmap chart |
| 4 | Inputs | Yellow-cell control panel with dropdowns and tolerances |
| 5 | Scenario Analysis | 7 by 7 grid of ERP outcomes across rate and earnings yield shocks |
| 6 | Stock vs Bond Mix | Glide path translating ERP into allocation |
| 7 | Sector ERP | Sector-by-sector earnings yield vs 10-year heatmap |
| 8 | Historical ERP | 15-period reference series with line chart |
| 9 | Methodology | Definitions, formula notes, data sources |
| 10 | Glossary & Disclaimer | Terms, caveats, no-advice notice |
Tabs are color-coded with the MarketXLS palette: navy for cover, blue for how-to, gold for dashboard and inputs, gray for analysis sheets, and dark gray for methodology and glossary.
Sheet 3: Dashboard - the page you will live on
The dashboard sheet opens with a row of seven KPI tiles in MarketXLS navy and gold:
- SPX forward earnings yield
- 10-year Treasury yield
- Fed Model spread
- AAA bond yield
- BAA bond yield
- VIX
- Regime label
Each tile carries a comment with the underlying MarketXLS formula, so you can copy it into your own work. Below the tile row sits an embedded bar chart comparing the SPX earnings yield against AAA and BAA bond yields, followed by a conditional-formatted screener listing 16 sector ETFs with a color scale across price, P/E, dividend yield, and one-year return columns. Cool reds flag low values, ambers mark the middle, and greens highlight the high end of each metric.
The screener uses real MarketXLS formulas that you can verify in the function documentation:
=QM_Last("SPY")for the latest price=PERatio("XLK")for trailing P/E=ForwardPE("XLF")for the forward P/E=DividendYield("XLU")for the trailing dividend yield=Beta("XLV")for five-year beta=StockReturnOneYear("XLE")for the one-year total return
For a primer on PERatio versus the forward variant, see our deep dive on the P/E ratio in Excel with MarketXLS.
Sheet 4: Inputs - the only place you should type
The inputs panel is the single source of truth for every assumption in the workbook. All editable cells are formatted in MarketXLS gold with dark text so you know exactly what you can change. Defaults match the May 2026 snapshot.
| Input | Default | Notes |
|---|---|---|
| Benchmark ticker | SPY | Drives the earnings yield input on the dashboard |
| 10-year Treasury override | (blank) | Leave blank to use TreasuryRate10Y live |
| Target ERP (long-run avg) | 4.60% | Damodaran-style benchmark |
| "Cheap" threshold | >= 4.50% | Triggers green regime tile |
| "Rich" threshold | ≤ 0.50% | Triggers red regime tile |
| Portfolio size | $250,000 | Used by the allocation sheet |
| Stock weight floor | 30% | Glide path lower bound |
| Stock weight ceiling | 90% | Glide path upper bound |
| Glide path slope | 8 | Sensitivity of stock weight to ERP gap |
Two data validation dropdowns keep the typing honest: the benchmark ticker is restricted to a curated list of broad ETFs, and the regime label is a derived dropdown rather than a free-text field. Frozen panes keep the column headers visible as you scroll, and gridlines on Cover, Dashboard, and Inputs are hidden so the layout reads more like a finished report.
Sheet 5: Scenario Analysis - stress testing in one screen
Scenario analysis is the part of the workbook that actually earns its keep when markets get strange. The sheet builds a 7 by 7 grid that recomputes the Fed Model spread for every combination of:
- 10-year Treasury yield from 3.30% to 5.30% in 0.33% steps
- SPX forward earnings yield from 3.67% to 5.67% in 0.33% steps
Every cell in the grid is a simple subtraction (earnings yield minus rate), but the magic is in the conditional formatting: a three-color scale paints rich regimes in red, neutral regimes in amber, and cheap regimes in green. You can see at a glance how much the ERP has to widen to flip back to long-run-average territory, and which combination of rate cuts and earnings rerates would do the work.
Underneath the grid, a small commentary block lists the three ways the spread typically widens:
- Earnings rerate higher (forward P/E compresses, earnings yield rises)
- Rates fall (10-year yields move down)
- Some combination of both
This sheet is where you "argue with the dashboard" - if you think bond yields are going to 3.5% and earnings hold, the grid tells you what that means for the regime label without rebuilding any formulas.
Sheet 6: Stock vs Bond Mix - the glide path
The allocation sheet translates the ERP signal into a stock vs bond mix using a transparent linear glide path. The default rule is:
StockWeight = TargetWeight + Slope * (CurrentERP - TargetERP)
StockWeight = MIN(Ceiling, MAX(Floor, StockWeight))
With the May 2026 inputs (Current ERP 0.37%, Target ERP 4.60%, Slope 8, Floor 30%, Ceiling 90%, Target weight 60%), the formula produces a stock weight near the floor. The sheet shows three columns side by side:
- Current allocation, given today's ERP and the inputs
- Long-run policy mix at the target ERP (60/40 by default)
- Difference, in dollars, on the portfolio size you supplied
A small embedded pie chart visualizes the current mix, and a comments block reminds you that the slope, floor, and ceiling are policy choices, not market data. If you prefer a flatter allocation rule, set the slope to 2 or 3 and watch the recommendation move closer to the long-run policy mix.
For more on building rules-based allocation logic in Excel, see Build a tactical asset allocation model in Excel and Glide path retirement modeling in Excel.
Sheet 7: Sector ERP - heatmap by sector
Macro ERP gets the headlines, but the equity risk premium is not uniform across sectors. The sector ERP sheet pulls the forward earnings yield for each of the 11 SPDR sector ETFs, subtracts the 10-year Treasury yield, and shades the result with a red-amber-green color scale.
The May 2026 snapshot shows wide dispersion: defensives like staples and utilities trade at thin or negative spreads, while energy and financials offer comfortably positive ERPs. Cyclical tech is the marginal case, with a near-zero spread depending on how aggressively you mark forward earnings.
The point is not to tell you what to buy. It is to make the comparison visible: when a single equal-weight number hides large sector-level differences, dollar-cost-averaging into the index buys you the average even if the average masks pockets of unusually rich or cheap pricing.
Sheet 8: Historical ERP - context for today's reading
A single number is hard to interpret. The historical ERP sheet contextualizes today's spread against a 15-period reference path covering 2010 through 2026, including:
- The decade of low rates and reasonable spreads (2010-2018)
- Pandemic-era earnings yields (2020-2021)
- The 2022 rate shock and ERP collapse
- The current environment, with mid-4% bond yields and high single-digit earnings yields
An embedded line chart plots the path so you can see the regime shifts visually, and the sheet's table doubles as a teaching tool: every row carries the underlying SPX P/E, the 10-year yield, and the resulting Fed Model spread, so you can audit the numbers and add your own historical observations.
Sheet 9: Methodology - the audit trail
Every assumption in the workbook is documented on the methodology sheet. Topics covered include:
- The Fed Model spread definition and its known weaknesses (no growth, no inflation adjustment)
- The Damodaran implied ERP framework and where the historical average comes from
- The MarketXLS formulas used and their parameters
- The data refresh cadence (intraday for prices, daily for fundamentals, monthly or quarterly for fundamentals depending on the issuer)
- The bounds, slope, and target choices in the glide path
If you want to read more about how MarketXLS sources Treasury rate and credit data, see Treasury yield curves in Excel and Corporate bond spreads in Excel.
Sheet 10: Glossary and Disclaimer
The final sheet defines every term used in the dashboard, so you can hand the workbook to a colleague without having to walk them through the language. Terms covered include earnings yield, forward earnings yield, Fed Model, implied ERP, glide path, regime, color scale, conditional formatting, and data validation. The disclaimer makes clear that nothing in the workbook is investment advice; ERP is a valuation framework, not a buy or sell signal, and the dashboard is built for educational and research purposes.
How the formulas connect
The dashboard is intentionally short on macros. Every value you see flows from a small set of MarketXLS calls and ordinary Excel arithmetic. Here are the building blocks the workbook uses, with each one verified against the MarketXLS function documentation.
=ForwardPE("SPY")
=1/ForwardPE("SPY")
=TreasuryRate10Y("AAPL")
=1/ForwardPE("SPY") - TreasuryRate10Y("AAPL")
=BondYieldAAA("AAPL")
=BondYieldBaa("AAPL")
=VIX("AAPL")
=DividendYield("XLU")
=Beta("XLV")
=StockReturnOneYear("XLE")
=PERatio("XLK")
=QM_Last("SPY")
The macro calls (TreasuryRate10Y, BondYieldAAA, BondYieldBaa, VIX) take a Symbol parameter for compatibility, but they return market-wide series. Using "AAPL" or "SPY" as the parameter is just a placeholder. The IFERROR wrapper on the screener handles the handful of sector ETFs that do not return a clean dividend or one-year return value, so the conditional formatting still paints cleanly.
Three ways advisors use the dashboard
1. Weekly investment committee meeting. Drop the workbook on a shared drive, refresh on Monday morning, and the regime label, ERP value, and suggested mix are all current for the meeting. The screener gives you a quick sector-by-sector read for the equity portion of the conversation.
2. Client review prep. When a client asks "are stocks expensive?", the workbook provides a defensible answer that does not depend on a single sound bite. You can show them the spread, the historical context, and the scenario grid, and they can see for themselves what would have to happen for the answer to flip.
3. Tactical tilts. Some advisors run a small tactical sleeve around a strategic core. The glide path on the Stock vs Bond Mix sheet gives a transparent rule for sizing that sleeve, with clear floors and ceilings so the rule cannot drag the portfolio into extreme positions.
Choosing the right ERP framework
The Fed Model and Damodaran-style implied ERP often disagree, especially when growth expectations are high. The workbook does not try to settle that debate. Instead, it shows both numbers side by side on the dashboard and lets you choose which one to anchor your regime label on by adjusting the cheap and rich thresholds on the inputs sheet.
If you lean toward a growth-aware framework, set the cheap threshold lower (say 3.5%) and the rich threshold higher (say 1.5%) so the regime label tracks the implied ERP more closely. If you prefer the Fed Model's parsimony, leave the thresholds at 4.5% and 0.5% and accept that the label will spend long periods at the extremes during easy- or tight-money cycles.
What the dashboard does not do
It is worth being explicit about scope. The workbook does not:
- Predict where stocks or bonds will trade over any horizon
- Tell you to buy or sell any specific security
- Account for taxes, transaction costs, or your individual circumstances
- Replace a written investment policy or risk-tolerance questionnaire
- Adjust for inflation expectations (the Fed Model is a nominal comparison)
What it does is make a slow-moving valuation signal visible, auditable, and rule-based, so you can layer your own judgment on top.
FAQ
What is the equity risk premium in Excel?
The equity risk premium in Excel is typically calculated as the SPX forward earnings yield minus the 10-year Treasury yield (the Fed Model spread), or as a Damodaran-style implied premium derived from index cash flows and growth assumptions. The dashboard in this template wires up the Fed Model version using =1/ForwardPE("SPY") - TreasuryRate10Y("AAPL") and benchmarks the result against the long-run Damodaran average.
How often does the dashboard refresh?
Prices refresh whenever Excel recalculates - typically intraday during market hours when MarketXLS auto-refresh is enabled. Fundamentals such as P/E and forward P/E update once a day after the close. Macro series such as the 10-year Treasury, AAA, and BAA bond yields update on the cadence published by their data providers (typically end-of-day for rate data).
Can I use a different benchmark than SPY?
Yes. The Inputs sheet has a data validation dropdown letting you switch between SPY, QQQ, DIA, IWM, and other broad-market ETFs. The earnings yield and forward earnings yield calculations on the dashboard pull from whichever ticker you select, so the entire ERP reading recalculates automatically.
Is the Fed Model spread the same as the equity risk premium?
Not exactly. The Fed Model spread is one specific way to approximate the equity risk premium - it compares earnings yield directly to the risk-free rate but ignores expected earnings growth. The Damodaran implied ERP is a more theoretically grounded estimate that bakes in growth and payout expectations. The dashboard tracks the Fed Model version because it is fully observable from market data, but the Methodology sheet explains the difference in detail.
What is a "good" equity risk premium?
There is no single number, but the long-run Damodaran historical average is about 4.6% and the Fed Model average is closer to 3.0%. Spreads above those levels generally suggest stocks are cheap relative to bonds; spreads well below them suggest the opposite. The workbook lets you set your own cheap and rich thresholds on the Inputs sheet so the regime label matches your framework.
Can I customize the glide path between stocks and bonds?
Yes - that is exactly what the Inputs sheet is for. You can set the target weight (default 60% stocks), the slope (default 8, meaning a 1 percentage point ERP gap moves the recommended stock weight by 8 percentage points), and the floor and ceiling. Setting the slope to 0 makes the recommendation static (always 60/40); setting it higher makes the recommendation more responsive to ERP swings.
Get the workbook
Both files are free to download. The sample workbook is a static study copy with values dated 2026-05-05 - useful for reviewing the layout and the math without a MarketXLS install. The template workbook contains live MarketXLS formulas and refreshes when you open it on a machine with the add-in installed.
- Sample (static):
- Template (live formulas):
If you do not yet have MarketXLS installed, you can read about the MarketXLS Excel add-in and the supported financial functions. The dashboard uses only documented functions, no scraped data or unofficial APIs.
Bottom line
The equity risk premium dashboard turns a slow-moving valuation signal into a daily, auditable read on stock vs bond pricing. The May 2026 snapshot shows a Fed Model spread well below the long-run average - a regime the dashboard labels as RICH and translates into a stock weight near the 30% policy floor. Whether you act on that read is your call. What the workbook gives you is the math, the assumptions, and the controls all on one screen.
Drop the template into your weekly review process for a quarter and see how it sharpens the conversation. If you want help wiring it into a broader research workflow, the team at MarketXLS does this every day.
Built with MarketXLS - real-time market data and financial functions for Excel. Visit marketxls.com to see the full add-in, or book a demo if you want a guided walkthrough.
