Merger Arbitrage Spread Tracker Excel: Live Deal Spread Dashboard (June 2026)

Merger arbitrage spread tracker excel - if you allocate to event-driven strategies, the spread between an announced deal price and the target's current trading price is the only thing you watch. This guide pairs a professional-grade Excel workbook with a live MarketXLS data layer so the deal table, annualized IRR, scenario P&L, and probability-weighted return refresh every time you open the file. The template is designed to look and behave like a paid dashboard, not a free spreadsheet handout.

Below you will find the deal table our workbook ships with, the formulas that power each metric, how to size positions, how to stress test break risk, and a 10-sheet walkthrough of the file you can download at the bottom of this post. The download is free for now and the design quality is meant to justify the email capture on its own.

Deal table at a glance (June 2026 snapshot)

The headline output of the workbook is a live deal table. Below is the static snapshot the sample workbook ships with. The downloadable template version replaces every "Current Target" value with a =QM_Last(ticker) call so the spreads and IRRs refresh on open.

These are illustrative values for the workbook. Verify every deal term against the most recent 8-K or proxy filing before you act on a spread.

What merger arbitrage actually is

Merger arbitrage is the practice of buying a target stock after a deal has been announced to capture the difference between the announced offer price and the current market price. That difference exists for one reason: investors who currently own the target want to lock in a price that is close to the deal value rather than wait the four to twelve months a typical deal takes to close. The arbitrageur takes the other side and is paid the spread for assuming three risks.

The first risk is deal break risk. If the deal falls apart, the target usually trades back toward its pre-announcement level. The drop can be as little as five percent or as much as forty depending on whether a credible second bidder is waiting. The second risk is time risk. Even when a deal does close, regulatory review can extend the timeline by ninety days or more, which collapses the annualized return without reducing the dollar spread. The third risk is terms risk: if a deal is repriced or restructured downward, the realized return is lower than the original headline.

A spread tracker is a working tool for managing all three. It records the spread, the days remaining, the annualized IRR, and a probability adjustment so that a portfolio of deals can be compared on the same footing. The workbook in this post takes that idea and adds a presentation-grade dashboard, a scenario engine, and a position-sizing layer.

Why a dashboard is the right format

A spreadsheet that lists deals row by row eventually becomes unreadable when you have more than five or six. The dashboard format collapses fifteen deals into a single screen that surfaces what matters: which deals have the widest spreads, which have the highest annualized IRR after adjusting for time, where capital is concentrated by sector, and what the worst-case dollar loss would be if a portion of the book broke at once. The Excel template ships with all of those views connected to a single set of input cells so you can rerun the whole portfolio under a different break probability or a different capital allocation without touching anything else.

This is also why the cover sheet, the KPI tile row, the heatmap, and the sector pie chart are not decoration. They are the parts of the workbook that make it usable as a daily tool rather than a monthly spreadsheet.

What's inside the template

The downloadable workbook is built around ten sheets, all linked to a single Inputs sheet that controls capital, risk tier, and break probability. Every other sheet recalculates the moment you change one of those inputs.

1. Cover - branded title page with the 2026 edition tag, "Data as of" line, table of contents, and the MarketXLS link. Gridlines hidden so it looks like a designed product on open.
2. How To Use - a numbered walkthrough that names every input cell and every formula. It is the first sheet anyone opening the file should read.
3. Dashboard - the headline sheet. Six KPI tiles across the top: active deals, average gross spread, average annualized IRR, capital allocated, portfolio probability-weighted IRR, and worst-case dollar loss. Below the tiles sits the live deal table with a 3-color heatmap on spread and a 3-color heatmap on annualized IRR. Two embedded charts - capital by sector and annualized IRR by deal - sit to the right.
4. Inputs & Controls - yellow input cells with a data validation dropdown for risk tier (Conservative / Base / Aggressive). Adjust total capital, allocated capital, target IRR, base break probability, extension days, friction cost, and fed funds rate. Risk tier behavior table shows how each tier maps to max deals, max percentage per deal, and a break probability multiplier.
5. Deal Tracker - the raw deal list. Target name, target ticker, acquirer, acquirer ticker, deal type, offer value, current target price, gross spread, days to close, annualized IRR, GICS sector, expected close date. The current target price is a live =QM_Last(ticker) call in the template. Spread is a formula, days to close is DATEVALUE - TODAY, and IRR is spread x (365 / days). Conditional formatting applies a red-amber-green color scale to both spread and IRR.
6. Scenario Analysis - per-deal P&L under three outcomes: deal closes, deal extends 60 days, or deal breaks. Expected P&L combines the three using base case probabilities. Portfolio total and worst-case loss roll up at the bottom. Data bars highlight the largest dollar gains and largest dollar losses.
7. Position Sizing - conviction score per deal equals annualized IRR times (1 - break probability). Portfolio weight equals conviction divided by total conviction. Dollar allocation per deal equals weight times capital allocated. Data bars on weight column make the largest positions visually obvious.
8. IRR Calculator - per-deal yellow override for break probability. The probability-weighted annualized IRR formula penalizes high IRR deals with high break risk. The cell that the Dashboard pulls its "Portfolio Weighted IRR" KPI from lives here.
9. Sector Breakdown - deal count per GICS sector and capital allocated per sector with data bars and a pie chart. Use this to avoid concentrating capital in any one sector that a regulator might block.
10. Methodology + Glossary & Disclaimer - explains every formula, every assumption, the data sources, and the limitations. Sets the file apart from a pure spreadsheet and lifts perceived value.

Every sheet has frozen panes, tab colors set to match the palette, a footer line with the MarketXLS book-a-demo link, and a "MarketXLS Functions Used" reference block. The Cover and Dashboard sheets hide gridlines.

How the formulas connect (and which MarketXLS functions are doing the work)

Every dollar value, every percentage, and every count in the workbook is a formula. Below are the MarketXLS calls and the cross-sheet references the workbook ships with.

Live data calls (Deal Tracker sheet, current target price column):

=QM_Last("HES")
=QM_Last("X")
=QM_Last("CPRI")
=QM_Last("PXD")
...

The sample file pre-fills the current price with a static value and attaches a cell comment showing the formula. The template file replaces the static value with the formula itself so the spread updates the moment QuoteMedia refreshes.

Sector classification (Deal Tracker sheet, sector column):

=IFERROR(Sector("HES"), "Energy")
=IFERROR(Sector("X"), "Materials")

Sector returns the GICS sector for each ticker. The IFERROR wrap shows a sensible fallback if the ticker is delisted between deal announcement and close, which happens with smaller targets.

Spread formula (Deal Tracker, column H):

=IFERROR((G8-L8)/L8, 0)

G is the offer value, L is the current target price. This produces a percentage spread.

Days to close (Deal Tracker, column I):

=MAX(1, DATEVALUE(N8)-TODAY())

N is the expected close date stored as a text date. MAX(1, ...) prevents division by zero when a deal closes today or is overdue.

Annualized IRR (Deal Tracker, column J):

=IFERROR(H8 * (365/I8), 0)

This is the standard merger arbitrage annualization. A 4.4% spread that closes in 80 days produces a 4.4 * (365/80) = 20.1% annualized return.

Probability-weighted annualized IRR (IRR Calculator sheet, column H):

=F8*(1-G8) - G8*0.12*(365/D8)

F is the unadjusted annualized IRR, G is the per-deal break probability (yellow input), D is days to close. The second term penalizes deals where a break costs the typical 12% drop, scaled to an annualized rate.

Conviction-weighted position sizing (Position Sizing sheet):

Conviction:   =D8 * (1 - E8)
Weight:       =IFERROR(F8/SUM($F$8:$F$22), 0)
Allocation $: =G8 * 'Inputs & Controls'!C7

This produces a portfolio that overweights high-IRR, low-break-risk deals automatically.

Scenario expected P&L (Scenario Analysis sheet):

=(1-'Inputs & Controls'!C13-0.08)*E8 + 0.08*F8 + 'Inputs & Controls'!C13*G8

E is close P&L, F is extend P&L, G is break P&L. The probability is split between close (most weight), extend (8%), and break (the base case input).

Every cross-sheet reference resolves the moment you change a single input cell. Reset the Inputs sheet capital from 100,000 to 250,000 and every position size, every scenario P&L, every KPI tile updates.

Where the workbook is helpful (and where it is not)

The workbook is helpful when you are running a moderate book - five to fifteen active deals - and need a single screen that shows where your spread risk and your IRR concentration sit. It is helpful when you need to defend an allocation in a partner meeting because the methodology sheet writes out every assumption. And it is helpful when you want to stress test what happens if the base case break probability is wrong by adjusting one cell.

The workbook is not a replacement for primary research. It does not read 8-K filings. It does not know whether the FTC is preparing a Section 7 complaint against a deal. It does not capture contingent value rights or earn-outs. It also does not hedge the short leg of a stock deal automatically. Treat it as a monitoring and screening tool. The judgment calls on which deals to take and which to pass on are still yours.

How to use the workbook in an advisor workflow

A practical workflow for a financial advisor or RIA running a merger arbitrage sleeve looks like this.

Step 1: Populate the Deal Tracker with active deals. Replace the seed list with your own. The workbook is built to handle fifteen rows but you can extend it. Each row needs target name, target ticker, acquirer, acquirer ticker, deal type, offer value, expected close date, and sector. The current price column is a live formula.

Step 2: Set capital and risk tier on the Inputs sheet. Pick the total capital you intend to allocate to merger arbitrage and the tier that matches the client mandate. Conservative limits you to eight deals and 12% per deal. Aggressive allows fifteen deals and 8% per deal.

Step 3: Review the Dashboard. Confirm the average annualized IRR meets your target. Confirm capital allocated does not exceed the mandate. Confirm worst-case loss is acceptable.

Step 4: Override break probability on the IRR Calculator. Deals with clear regulatory risk (energy mega-mergers, tech mega-deals) should have higher break probability than deals without it. Use yellow cells on the IRR Calculator sheet to tune.

Step 5: Read the Sector Breakdown. If a regulator is signaling concern about one sector, you want to know your exposure before the news hits.

Step 6: Refresh weekly. Live data refreshes automatically on open if you have MarketXLS installed. Update the expected close date column on the Deal Tracker whenever a deal extends its timeline.

Practical guardrails

A few rules the workbook does not enforce but that experienced merger arbitrage allocators tend to follow.

The first is to be skeptical of any deal with an annualized IRR above forty percent. The spread is wide for a reason - usually a credible regulatory challenge or a financing risk - and the workbook's break drop assumption of 12% understates how badly the target can fall in those cases.

The second is to limit the position size on any single deal to no more than the maximum your worst-case scenario can absorb without breaching client mandates. If a 12% drop on the largest position would create a problem, the position is too large.

The third is to stay diversified across deal type. Cash deals trade differently from stock deals. Stock deals require a short hedge that the workbook does not automate but that should be sized into your position from the outset.

The fourth is to update break probabilities aggressively as news flows. The base-case 7% break rate is an average. A specific deal with the FTC threatening to sue is not an average deal.

A note on how this workbook was built

The dashboard uses the openpyxl charting and conditional formatting libraries to embed live charts, color scales, and data bars without requiring any VBA. The MarketXLS functions used throughout are documented in the function reference at the bottom of each sheet so users who want to extend the workbook can copy the syntax directly.

The visual palette is consistent across every sheet: MarketXLS blue (#0066CC) for headers, deep navy (#003366) for the cover, yellow (#FFD60A) for editable input cells, and a red-amber-green heatmap for ranking columns. Number formats are tuned per cell: dollars use $#,##0.00, percentages use 0.00%, counts use #,##0.

Download the templates

Download the templates:

• - pre-filled with current data and MarketXLS formula comments on every data cell
• - live formulas that refresh on open

Both files are free downloads designed to look and behave like a paid dashboard.

FAQ

What is merger arbitrage and how do I track it in Excel?

Merger arbitrage is the strategy of buying a target stock after a deal is announced to capture the spread between the announced offer price and the current market price. Tracking it in Excel requires a live data source for the target prices, formulas for spread and annualized IRR, and a probability adjustment for break risk. The downloadable workbook in this post wires those together using MarketXLS as the data layer.

How is annualized IRR calculated for a merger arb deal?

Annualized IRR equals the gross spread multiplied by 365 divided by days to close. A 4% spread that closes in 90 days annualizes to 4% x (365/90) = 16.2%. The workbook performs this calculation on the Deal Tracker sheet and rolls it into a probability-weighted version on the IRR Calculator sheet.

What break probability should I use for merger arbitrage modeling?

Academic research on long-run merger arbitrage suggests an average break rate of five to ten percent across all announced deals, with significantly higher rates for deals facing antitrust review. The workbook ships with a base case of seven percent on the Inputs sheet and allows per-deal overrides on the IRR Calculator sheet.

Can the workbook handle stock-for-stock deals?

Yes. Stock deals are entered with the cash-equivalent offer value at announcement. In production, the long target leg should be paired with a short acquirer leg sized to the exchange ratio. The workbook does not automate the short leg, but it correctly records the offer value, spread, days to close, and annualized IRR for stock and mixed-consideration deals.

How often does the data refresh?

Current target prices refresh whenever you open the workbook with MarketXLS installed and connected. Deal terms (offer value, type, expected close date, sector) are entered manually based on company filings. Update the expected close date column whenever a deal extends its timeline.

Does the workbook give buy or sell recommendations?

No. The workbook is a screening and monitoring tool. It surfaces deals with the highest annualized IRR, the widest spreads, and the heaviest sector concentration. The judgment call on which deals to enter, which to pass on, and how to size each position is yours. Read the methodology sheet for every assumption the model makes.

What if QM_Last() returns nothing for a ticker?

QuoteMedia data feeds occasionally lapse for thinly traded names or for tickers that have been suspended pending a deal close. If QM_Last returns an error, the workbook's IFERROR wrappers default to a sensible value so the dashboard does not display #N/A across the table. Reauthenticate the QuoteMedia session if you see persistent errors.

The Bottom Line

A merger arbitrage spread tracker excel workbook only earns its keep when it is fast to update, transparent in its math, and visually clear enough to use as a daily monitoring tool. The download in this post is built around those three requirements: live MarketXLS data feeds, a documented methodology sheet, and a dashboard format that compresses fifteen deals onto a single screen. Use it as a starting point for your own active deal book and tune the inputs against your mandate.

To explore more live financial data in Excel, see what MarketXLS offers at marketxls.com and marketxls.com/book-demo. For other dashboard-style templates - covering bank stress tests, sector rotation, dividend kings, and more - browse the MarketXLS blog.