Bank Stress Test Tracker Excel: CCAR 2026 Capital Plan Dashboard for Big Bank Investors

Bank stress test tracker Excel is the kind of tool that suddenly matters every late June, when the Federal Reserve releases its CCAR results and the largest U.S. banks line up to announce dividend hikes and buyback authorizations within hours. CCAR 2026 is the headline event for bank-stock investors this month, and the gap between a strong capital buffer and a marginal one is what often decides which bank gets to lead with capital returns. This guide walks through how to build a bank stress test tracker in Excel, how MarketXLS powers it with live data, and how to use the free template to monitor the 10 covered banks before, during, and after the late-June release.

Bank Stress Test Tracker Excel: What This Dashboard Covers

The 2026 CCAR cycle is set against an unusual backdrop. Net interest margins peaked, deposit costs are settling, commercial real estate exposure remains a swing factor, and Basel III Endgame finalization continues to shape how banks plan capital. Going into the late-June release window, investors want a single dashboard that ties together fundamentals, capital ratios, and post-results scenarios. That is exactly what this template is built for.

Snapshot - The 10 CCAR 2026 Banks Tracked

CCAR (Comprehensive Capital Analysis and Review) is the Fed's annual exercise that tests whether the largest banks can keep lending and meeting obligations under a hypothetical severe recession. Each bank receives a Stress Capital Buffer (SCB) based on projected losses, which sets the minimum Common Equity Tier 1 (CET1) ratio it has to hold. Anything above that minimum is what the bank can return to shareholders, and that gap is the core variable in the dashboard you are about to build.

What Happens Right After CCAR Results

In a normal cycle, the Fed posts results the last Friday of June. Banks then have a short window before they publish their preliminary SCB and indicative capital plans (usually the following Monday or Tuesday). Almost every covered bank then announces a dividend rate for the coming quarter and a refreshed buyback authorization, often the same week.

For investors, three things move quickly:

1. Dividend per share is reset. Banks that cleared with a wider buffer tend to telegraph a meaningful hike. Marginal banks announce a flat dividend.
2. Buyback authorization is refreshed or upsized. This is the biggest swing variable - large authorizations can be 5 percent or more of market cap.
3. Stock prices reprice the day after the announcement, with banks often outperforming the broader market when capital returns surprise positively.

The bank stress test tracker Excel template walks you through each of these in a structured way, so you are not scrambling to update a spreadsheet at 4:30 PM on a Friday in late June.

Building a Bank Stress Test Tracker Excel - The Approach

The simplest version of this dashboard has three layers:

• Layer 1: Bank fundamentals. Price, market cap, P/E, P/B, dividend yield, ROE, beta. These set the baseline for any capital return analysis.
• Layer 2: Capital ratios. Reported CET1 ratio, SCB, Fed floor, buffer above minimum. This is where the stress test results get translated into a number you can act on.
• Layer 3: Capital return scenarios. Three paths for dividend hikes and buybacks (bull, base, bear), parameterized so you can change the assumptions without rebuilding the model.

Because each layer relies on live fundamentals, the natural way to drive it is with MarketXLS formulas. The template version of the dashboard uses live formulas everywhere, while the sample version is pre-filled with illustrative June 2 values so you can see exactly what the output should look like.

This is educational analysis, not investment advice. Tickers and figures are used to show how the formulas work and how the dashboard fits together.

The MarketXLS Implementation

The whole point of running this in MarketXLS is that you do not have to re-pull data each time you change a ticker. Every cell that needs a price, a ratio, or a dividend value is a single function. Here is how the dashboard maps to MarketXLS formulas.

Pulling Live Bank Fundamentals

The Main Dashboard tab pulls these for each of the 10 banks:

=QM_Last("JPM")                  Current stock price
=MarketCapitalization("JPM")     Market cap in dollars (divided by 1e9 for $B)
=PERatio("JPM")                  Trailing P/E ratio
=PriceToBook("JPM")              Price-to-book ratio (key for banks)
=DividendYield("JPM")            Annual dividend yield
=ReturnOnEquity("JPM")           ROE for capital generation context
=Beta("JPM")                     Beta vs the broader market

Price-to-book is the headline valuation metric for banks because tangible book value is the real anchor for what a bank is worth. Combining P/B with ROE gives a clean view of which banks the market is paying for capital generation versus which it is discounting.

Deeper Fundamentals on the Watchlist Tab

The Bank Watchlist tab digs into earnings power and payout structure:

=EarningsPerShare("JPM")         TTM earnings per share
=BookValuePerShare("JPM")        Per-share book value
=DividendPerShare("JPM")         Annual dividend per share
=PayoutRatio("JPM")              Dividend payout ratio
=ReturnOnAssets("JPM")           ROA, useful for cross-bank comparison
=FiftyTwoWeekHigh("JPM")         52-week high
=FiftyTwoWeekLow("JPM")          52-week low

The payout ratio is one of the most direct signals for stress test outcomes. Banks running a low payout ratio with a high CET1 ratio have two levers available: meaningful dividend hikes and large buyback authorizations. Banks running a high payout ratio and a thinner CET1 buffer typically lean on buybacks instead, because they are easier to scale down if conditions change.

Sizing the Capital Return Scenarios

The Capital Return Scenarios tab models three paths per bank. The buyback math uses MarketCapitalization to scale authorization size:

Bull Buyback ($B) = MarketCapitalization("JPM") / 1e9 * Bull%
Base Buyback ($B) = MarketCapitalization("JPM") / 1e9 * Base%
Bear Buyback ($B) = MarketCapitalization("JPM") / 1e9 * Bear%

And dividend scenarios use DividendPerShare with a configurable hike percentage:

Bull New Div = DividendPerShare("JPM") * (1 + BullHike%)
Base New Div = DividendPerShare("JPM") * (1 + BaseHike%)
Bear New Div = DividendPerShare("JPM") * (1 + BearHike%)

Because every variable is in a yellow input cell, you can change a single assumption (say, lowering bull-case dividend hike from 12 percent to 8 percent) and every cell across all 10 banks updates instantly.

Translating Buffers Into a Stress Rating

On the Capital Ratios tab, the dashboard maps each bank into a four-step rating:

• Strong Buffer: more than 5 percentage points above the minimum CET1 requirement
• Adequate: 2 to 5 percentage points above
• Marginal: 0 to 2 percentage points above
• Below Min: at or below required CET1

The rating formula is a nested IF that reads off the buffer column. It gives you a quick traffic light without having to scan the whole table.

What Is in the Template

The bank stress test tracker Excel template includes seven sheets:

How To Use

A walkthrough of what CCAR is, what the workbook does, what each sheet contains, and the MarketXLS functions used throughout. Includes branded links to the MarketXLS site and demo page.

Main Dashboard

Yellow input cells for portfolio size, stress severity, minimum CET1 threshold, max position size, and toggles for dividend or buyback focus. Below the inputs, the 10-bank watchlist runs live formulas for price, market cap, P/E, P/B, dividend yield, ROE, beta, and a Watchlist Score that combines yield, ROE, valuation, and capital buffer signal into a single rank. The score column uses conditional color scaling so the top names stand out.

Capital Ratios

Editable inputs for the Severely Adverse macro path (GDP, unemployment, housing, equity), plus the Fed CET1 floor. Each bank row contains user-editable CET1 and SCB inputs, a derived minimum CET1, the buffer above minimum, an estimated capital return capacity in dollars, and a stress rating. Buffer and capacity columns use color scales so weaker banks pop visually.

Capital Return Scenarios

Editable bull, base, and bear assumptions for dividend hikes and buyback authorizations as a percent of market cap. Each row shows the current dividend per share, the three scenario dividends, and the dollar buyback size in each path. Configured for a 1-year holding period by default; you can change to whatever horizon you model.

Bank Watchlist

Detailed per-bank fundamentals: price, EPS, book value per share, dividend per share, payout ratio, ROE, ROA, and 52-week high and low. Useful as the deep-dive layer when one bank stands out on the dashboard.

Portfolio Allocation

Translates a portfolio size and tier weighting into actual position sizing. Tier 1 banks (JPM, BAC, WFC, C in the default) get a higher allocation share; Tier 2 covers the regionals and others. Each row calculates allocation dollars, share count from live price, and projected annual dividend dollars. A totals row at the bottom sums allocation percent, allocation dollars, and dividend income.

Comparison Matrix

A side-by-side row-per-metric, column-per-bank view across the 10 names for price, P/E, P/B, dividend yield, ROE, beta, and EPS. Each row uses a color scale so winners and laggards on each metric are visually obvious. A Key Insights box highlights signals to look for: ROE plus capital buffer combinations, low payout plus high buffer for buybacks, P/B below 1.0 with rising ROE for revaluation candidates.

Every sheet has a MarketXLS Functions Used box at the bottom listing the exact formulas powering that sheet so users can reuse them in their own workbooks.

Download the templates:

• - Pre-filled values as of June 2, 2026
• - Live formulas that update every time you open the workbook

How to Use the Bank Stress Test Tracker Excel

A practical workflow if you are watching CCAR 2026:

1. Before the release - open the Capital Ratios tab and key in the latest reported CET1 from each bank's most recent 10-Q. Update SCB inputs if any preliminary signals have been published. The dashboard immediately shows you which banks have the widest buffers heading in.
2. The day results drop - update the SCB column with what the Fed published. The buffer column and stress rating recalculate live. Compare the new buffer to your pre-release expectations.
3. The week after - as banks announce their dividend hikes and buyback authorizations, update the Capital Return Scenarios tab with the actual figures. Tweak the bull, base, and bear inputs to reflect what management actually committed to.
4. Portfolio sizing - move to Portfolio Allocation. Set your portfolio size and tier weights to model the income impact. The annual dividend total at the bottom tells you the income line of the basket. Combine that with the buyback dollars from the Scenarios tab for total capital return.
5. Ongoing - the Watchlist and Main Dashboard tabs use live MarketXLS formulas, so prices, dividend yields, and valuations refresh whenever the workbook opens.

This sequence works whether you are an advisor preparing for client conversations after the CCAR results, a self-directed investor tracking your bank basket, or a research analyst sketching a CCAR scorecard.

Stress Test Layer - Going a Step Deeper

If you want to push the model further, the CCAR scenario inputs on the Capital Ratios tab let you sensitize stress assumptions. The 2026 Severely Adverse scenario defaults follow the Fed's typical macro shock pattern: a sharp GDP decline, unemployment north of 10 percent, large house price declines, and a deep equity drawdown. You can edit any of those to model your own scenario.

The dashboard does not attempt to replicate the Fed's full stress model. That would require bank-level loan books, trading exposures, and operational risk assumptions that are not public. Instead, the tracker takes the Fed's published SCB as the input, lets you adjust it, and translates the implied buffer into capital return capacity. This keeps the model focused on what is actionable for an investor: how much room each bank has to return capital, and which names look stronger or weaker relative to peers.

For a deeper dive on portfolio stress testing more broadly, see the portfolio stress test Excel guide. For net interest margin tracking that pairs with this dashboard, see the net interest margin tracker for Q1 2026.

Why P/B and ROE Matter More Than P/E for Banks

If you look across the watchlist, you will notice that bank P/E ratios cluster in a tight range. That is because earnings can swing meaningfully on credit costs, trading revenue, and the rate cycle. P/B and ROE tend to be more durable signals because they tie to capital generation.

A simple framework that the comparison matrix supports:

• High ROE + Low P/B: Implied market doubt about durability. If CCAR shows a strong capital buffer, that doubt can compress.
• High ROE + High P/B: Market already pays for capital generation. A strong CCAR result tends to validate the valuation without unlocking new upside.
• Low ROE + Low P/B: Restructuring story. Slower repricing path, harder to predict CCAR-driven moves.
• Low ROE + High P/B: Valuation may be vulnerable if the CCAR result is weak.

This is a heuristic, not a recommendation. The point of the dashboard is to make it easy to see which quadrant each bank sits in for your own analysis.

Tying It Back to Income

If you focus on dividend investing, the bank sector after CCAR is one of the most reliable seasonal moments for dividend hikes. The template's Portfolio Allocation tab projects annual dividend income for the full basket given your portfolio size and tier weights. If you change the dividend hike inputs in the Scenarios tab to the actual announced rates, the projected income flows through automatically.

To pair this with broader dividend strategy work, see the dividend growth screener walkthrough and the yield on cost dashboard for May 2026.

What This Dashboard Does Not Do

A practical note on scope:

• It does not predict CCAR outcomes. SCB values are inputs, not predictions.
• It does not optimize portfolio weights. Tier weights are user-set, not solved.
• It does not include systemically important non-bank financials. CCAR scope is the largest U.S. banks.
• It does not generate buy or sell recommendations. The Watchlist Score is a ranking tool for your own research.

These are intentional. The goal is a clear, transparent monitor that gives you a head start when results land, without pretending to be a full bank stress model.

FAQ

What is the bank stress test tracker Excel?

The bank stress test tracker Excel is a MarketXLS-powered template that monitors the 10 largest U.S. banks during the CCAR cycle. It pulls live fundamentals, lets you input each bank's CET1 and SCB, calculates capital return capacity, and models bull, base, and bear dividend and buyback scenarios.

When does CCAR 2026 release?

CCAR 2026 results are scheduled for late June 2026, following the Federal Reserve's standard release pattern. The exact Friday is set by the Fed each year. Banks usually announce their preliminary SCB and capital plans within a few business days of the release.

Which banks are in the dashboard?

The default watchlist covers the 10 largest U.S. banks: JPM, BAC, WFC, C, GS, MS, USB, PNC, TFC, and COF. You can add or remove tickers in the workbook - every row uses the same MarketXLS formulas, so changing a ticker updates the entire row.

What is a Stress Capital Buffer (SCB)?

The Stress Capital Buffer is a bank-specific add-on, set each year by the Fed based on projected losses under the Severely Adverse scenario in CCAR. It sits on top of the 4.5 percent Fed minimum CET1 requirement. A bank's minimum CET1 ratio under CCAR equals 4.5 plus its SCB.

What does CET1 buffer above minimum mean?

CET1 buffer above minimum is the difference between a bank's reported CET1 ratio and its CCAR minimum (Fed floor plus SCB). A wider buffer means more flexibility to raise dividends and authorize buybacks. The dashboard rates buffers as Strong, Adequate, Marginal, or Below Min based on this gap.

Can I model my own stress scenario?

Yes. The Capital Ratios tab includes editable yellow input cells for the Severely Adverse GDP, unemployment, housing, and equity assumptions, plus the Fed CET1 floor. You can replace any of these with your own assumptions to see how the resulting buffer and capital return capacity change.

Does the dashboard work for non-CCAR banks?

The watchlist defaults to CCAR-covered banks. You can swap in regional banks or other financials, and the live formulas still work for price, P/E, dividend yield, and so on. The Capital Ratios and Scenarios tabs assume CCAR-style stress inputs, so the framing is most accurate for CCAR banks.

The Bottom Line

CCAR 2026 is the single biggest scheduled event for big bank investors this June. The bank stress test tracker Excel template gives you a structured way to monitor the 10 largest U.S. banks, translate stress test results into capital return capacity, and size positions accordingly. With MarketXLS formulas powering every cell, the dashboard updates as prices, dividends, and fundamentals change, so the workbook stays current well past the release date.

If you want to see how MarketXLS handles fundamentals, options, screeners, and portfolio analytics across asset classes, visit https://marketxls.com or book a demo to see the full feature set in Excel.