ARR Bridge in Excel: SaaS Revenue Waterfall Guide (2026)

August 6, 2026 · Dezzmond Team
Formulas Financial Modeling Data Analysis

Two analysts, one billing export, two different ARR bridges. It happens constantly — and the reason is almost never the formulas. It's that nobody wrote down whether a customer who dropped from $40K to $0 in March and came back at $25K in May is churn then reactivation or contraction. An ARR bridge in Excel is only as trustworthy as the classification rules underneath it, and the good news is that once you get those rules right, the arithmetic ties by construction.

This guide builds the whole thing from a raw subscription export: a customer cube, a one-formula movement classifier, a bridge that reconciles to the penny, and net revenue retention derived from the same grid rather than calculated separately and hoped to agree. Every formula below is real and copy-ready.

What Is an ARR Bridge?

An ARR bridge (also called an ARR waterfall or recurring revenue rollforward) decomposes the change in annual recurring revenue between two dates into five movements: new, expansion, contraction, churn, and reactivation. It answers why ARR moved, not just that it moved, and it must reconcile exactly from beginning to ending balance.

The identity every bridge has to satisfy:

Beginning ARR
  + New ARR              (customers acquired this period)
  + Expansion ARR        (existing customers spending more)
  + Reactivation ARR     (previously churned customers returning)
  − Contraction ARR      (existing customers spending less)
  − Churned ARR          (customers who went to zero)
  = Ending ARR

ARR Bridge vs. ARR Waterfall vs. ARR Snowball

These get used interchangeably, mostly incorrectly. A bridge or waterfall is a single-period decomposition — one beginning balance, one ending balance, five bars between them. An ARR snowball is a cohort-stacked view showing each acquisition cohort's ARR contribution over time, which answers a different question (how do vintages age?) and is closer to a retention cohort heatmap than to a bridge.

ℹ️ Note: Everything here works identically for MRR. Divide the ARR values by 12 or run the cube on monthly amounts — the classification logic and tie-out check don't change.

Why Do ARR Bridges Fail to Tie Out?

Most bridges break for one of three reasons: movement amounts are computed independently of the customer-level balances, classification rules aren't mutually exclusive, or a customer changes categories mid-period and gets counted twice. The fix is structural — derive every movement from the same period-over-period delta so the total is an identity, not a coincidence.

That's the central design decision in this build. Look at the amount booked for each movement type:

Movement Prior ARR Current ARR Amount booked In NRR? Common mistake
New 0 > 0 Current No Counting a returning logo as new
Reactivation 0 > 0 Current No Burying it inside New
Expansion > 0 > Prior Current − Prior Yes Booking full current ARR
Contraction > 0 0 < Cur < Prior Current − Prior Yes Calling a downgrade "churn"
Churn > 0 0 −Prior Yes Booking the delta net of a same-month upsell
Flat > 0 = Prior 0 Yes Excluding these rows from the grid

Every single amount in that column is Current − Prior. New is Current − 0. Churn is 0 − Prior. Flat is zero. That means if you compute one delta grid and then merely label each cell, the sum of the five buckets equals the total change in ARR by algebra. There is nothing left to reconcile.

💡 Pro Tip: If your current bridge computes new ARR from the CRM, churn from the cancellation report, and expansion from a pricing tracker, it will never tie. Three sources, three cut-off conventions. One cube, one delta, one label.

How Do You Build an ARR Bridge in Excel?

Build it in four grids: a customer cube of ARR by customer by month, a delta grid, a label grid, and a bridge rollup driven by SUMIFS. Each grid is one formula filled across and down, which keeps the model auditable and lets a reviewer trace any bridge number back to the customers behind it.

graph LR
    A[Raw billing<br/>export] --> B[Customer cube<br/>ARR by cust x month]
    B --> C[Delta grid<br/>Curr - Prior]
    B --> D[Label grid<br/>New/Exp/Con/Churn/React]
    C --> E[Bridge rollup<br/>SUMIFS by label]
    D --> E
    E --> F[Tie-out check<br/>+ NRR / GRR]

Step 1: Build the Customer Cube

The cube is a matrix: one row per customer, one column per month, ARR in the cells. Put unique customer IDs in A5 with a spill formula so new logos appear automatically:

=SORT(UNIQUE(Raw[CustomerID]))

Month headers go in B4:M4 as real dates (2026-01-01, 2026-02-01, …), formatted mmm-yy. Then in B5, if your export is already one row per customer per month:

=SUMIFS(Raw[ARR], Raw[CustomerID], $A5, Raw[Month], B$4)

If instead you have a contract table with start and end dates — the more common case out of a CRM — the cube cell tests for overlap with the month:

=SUMIFS(Contracts[ARR],
        Contracts[CustomerID], $A5,
        Contracts[StartDate], "<="&EOMONTH(B$4,0),
        Contracts[EndDate],   ">="&B$4)

That sums every contract line active at any point during the month. For a stricter "active on the last day of the month" convention, change the second criterion to "<="&B$4 and the third to ">="&EOMONTH(B$4,0).

⚠️ Warning: Filter one-time fees, professional services, and usage overages out of Raw before they reach the cube. Non-recurring revenue in an ARR bridge produces phantom expansion in the month it lands and phantom contraction the month after — a pattern that looks exactly like real churn to anyone reading the deck.

Step 2: Compute the Delta Grid

On a sheet named Delta, mirror the cube's shape. The first month has no prior, so start in column C:

=Cube!C5-Cube!B5

Fill right and down. That's the whole grid. It is deliberately dumb — all of the intelligence lives in the labels.

Step 3: Classify Every Customer-Month With One Formula

On a Move sheet with the same shape, C5 holds a single LET-wrapped classifier:

=LET(
   prior,  Cube!B5,
   curr,   Cube!C5,
   hist,   SUM(TAKE(Cube!$B5:B5, , -12)),
   IFS(
     (prior=0)*(curr=0),          "-",
     (prior=0)*(curr>0)*(hist=0), "New",
     (prior=0)*(curr>0),          "Reactivation",
     curr=0,                      "Churn",
     curr>prior,                  "Expansion",
     curr<prior,                  "Contraction",
     TRUE,                        "Flat"
   )
 )

Three things are doing real work here:

  1. SUM(TAKE(Cube!$B5:B5, , -12)) is the reactivation test. Cube!$B5:B5 is an expanding range — in column C it covers January only, in column H it covers January through June. TAKE(…, , -12) grabs the last twelve columns of that range, so hist is the customer's trailing-12-month ARR history excluding the current month. Zero means never seen before within the window → New. Non-zero means they were here and left → Reactivation.
  2. Order matters. IFS returns on the first TRUE, so the New test has to precede the Reactivation test, and both have to precede the growth comparisons. Reorder these and you will silently label returning logos as expansion.
  3. (prior=0)*(curr=0) multiplies booleans instead of nesting AND(). Same result, and it survives being converted to an array formula later if you decide to compute the whole grid in one spill.

💡 Pro Tip: The 12-month reactivation window is the most common convention, but it is a policy choice, not a law. Change -12 to -6 or -24 to match your revenue recognition memo — and then write the number down somewhere a future analyst will find it.

Step 4: Roll Up the Bridge

Put the movement names in A12:A16 of a Bridge sheet, with beginning ARR in row 11 and ending ARR in row 17. Then a single SUMIFS, filled across all months and down all five movements:

=SUMIFS(Delta!C$5:C$500, Move!C$5:C$500, $A12)

Beginning ARR for the month:

=SUM(Cube!B$5:B$500)

Ending ARR:

=B11+SUM(B12:B16)

Note what that last formula does not do: it doesn't independently re-sum the cube. Which is exactly what lets the next step be a real check rather than a tautology.

Step 5: The Tie-Out Check

=ROUND(B17-SUM(Cube!C$5:C$500), 2)=0

B17 is the bridge's ending balance built from movements; SUM(Cube!C5:C500) is the cube's actual ending balance. If those disagree, something outside the delta grid is contributing ARR — usually a customer row added below your $500 boundary, or a cube formula overwritten with a hardcode.

Wrap it in conditional formatting so a break is visible without being hunted for:

=$B$18<>TRUE

⚠️ Warning: Set the cube's row range generously ($5:$5000) and let the SORT/UNIQUE spill sit inside it. Hardcoding the last customer row is the single most common cause of a bridge that tied in June and quietly stopped tying in July.

How Do You Calculate Net Revenue Retention From the Bridge?

Net revenue retention is beginning ARR plus expansion, contraction, and churn — all movements from customers who existed at period start — divided by beginning ARR. New and reactivation are excluded because those customers weren't in the starting cohort. Gross revenue retention uses the same numerator without expansion and is capped at 100%.

With the bridge laid out as above:

=(B11+B13+B14+B15)/B11

where B13:B15 are Expansion, Contraction, and Churn (the last two already negative). GRR drops expansion:

=(B11+B14+B15)/B11

Monthly NRR vs. Trailing-Twelve-Month NRR

Monthly NRR compounds misleadingly — a 1.5% monthly gain is not a 118% annual figure once churn timing is accounted for. Investors ask for TTM NRR: take the customers who were active twelve months ago, and compare what they pay today against what they paid then.

That's a cohort question, and it comes straight off the cube with SUMPRODUCT. If column B is the month twelve periods back and column N is the current month:

=SUMPRODUCT((Cube!$B$5:$B$5000>0)*Cube!$N$5:$N$5000)
 /SUMIF(Cube!$B$5:$B$5000, ">0")

The numerator sums today's ARR only for customers who had ARR twelve months ago, including any who churned to zero (they contribute nothing, which is the point) and excluding every logo acquired since. The denominator is that same cohort's starting ARR. See our SUMPRODUCT guide for why this pattern beats a helper column here.

Metric Window Includes expansion? Includes new logos? Typical B2B benchmark
Monthly NRR 1 month Yes No 100.5% – 101.5%
TTM NRR 12 months Yes No 105% – 120%
Monthly GRR 1 month No No 98.5% – 99.5%
TTM GRR 12 months No No 85% – 95%
Logo retention 12 months No (counts customers) No 80% – 90%
ARR growth Any Yes Yes Varies

ℹ️ Note: Best-in-class enterprise SaaS runs TTM NRR above 120%; SMB-focused products often sit near 100% and win on volume and acquisition efficiency. A bridge that shows 130% NRR alongside 78% GRR is telling you a small number of accounts are expanding hard while the long tail leaks — a very different business than one at 110%/95%.

Which Edge Cases Break Real ARR Bridges?

The five-bucket model handles roughly 90% of customer-months cleanly. The remaining 10% is where bridges get argued about in board meetings, so decide these in advance and document them.

graph TD
    A[Customer-month] --> B{Prior ARR = 0?}
    B -->|Yes| C{ARR in prior<br/>12 months?}
    C -->|No| D[New]
    C -->|Yes| E[Reactivation]
    B -->|No| F{Current ARR = 0?}
    F -->|Yes| G[Churn]
    F -->|No| H{Current vs Prior}
    H -->|Higher| I[Expansion]
    H -->|Lower| J[Contraction]
    H -->|Equal| K[Flat]

Same-Month Downgrade and Upsell

A customer drops a $30K module and adds a $50K one in the same month. The cube nets it to +$20K and labels it Expansion. That is correct at the customer level and wrong at the product level. If your board asks for product-level movement, run a second cube keyed on customer and product, classify at that grain, then roll up — the customer-level bridge stays the summary, the product cube becomes the explanation.

Mid-Month Starts and Stops

ARR is a point-in-time run rate, not a period total, so a contract starting on the 20th is either fully in the month or fully out depending on your overlap convention (Step 1). Don't prorate ARR — prorating turns a run-rate metric into a revenue metric and guarantees your bridge stops agreeing with the ARR number on the board slide.

Acquired Customer Bases

Logos arriving through an acquisition are not new business. Give them their own bucket — M&A ARR — as a sixth row in the bridge. Because the rollup is a SUMIFS on a label, adding a category means adding a row and a label, not rewriting anything. Tag the acquired customers in a lookup column and extend the classifier:

=LET(
   prior, Cube!B5,
   curr,  Cube!C5,
   acq,   XLOOKUP($A5, Acq[CustomerID], Acq[CloseMonth], 0),
   IF((prior=0)*(curr>0)*(acq=C$4), "M&A", <the IFS block from Step 3>)
 )

Matching on the acquisition close month rather than a simple flag matters: acquired logos are M&A ARR in the month they land and ordinary customers in every month after, so a permanent flag would keep re-labelling their later upsells.

Currency

Run the cube at constant rates — one budget rate per currency for the whole year — and put FX movement in its own bucket computed as the difference between constant-rate and actual-rate ARR. Mixing spot rates into the cube spreads FX noise across all five movement types, and expansion is then indistinguishable from a weaker dollar.

Contract Renewals at a Different Price

A renewal is not a movement. Only the price change is. If a $100K contract renews at $115K, that's $15K of Expansion — the other $100K never moved. Bridges that book full renewal value as "renewal ARR" have a sixth bar that double-counts the base and then require a plug to close.

How Do You Chart an ARR Bridge in Excel?

Excel's built-in waterfall chart (Insert → Charts → Waterfall) plots the bridge directly from the six-row rollup, provided you mark Beginning and Ending as totals: click each column, then check Set as Total in the format pane. Negative movements color automatically.

Two presentation habits separate a bridge people trust from one they squint at:

  1. Order the bars by narrative, not by sign. Beginning → New → Reactivation → Expansion → Contraction → Churn → Ending reads as a story: what we won, then what we lost.
  2. Label the bars with the customer count as well as the dollars. "Churn −$420K" invites a shrug; "Churn −$420K / 3 logos" starts a conversation about which three.

Customer counts come off the same label grid:

=COUNTIFS(Move!C$5:C$500, $A15)

If you need more control than the native chart offers — a connector line, a subtotal column, custom base offsets — the stacked-column technique in our Excel waterfall chart guide applies unchanged to ARR movements.

Frequently Asked Questions

What is a good ARR bridge net revenue retention rate?

For enterprise B2B SaaS, TTM NRR above 120% is considered best-in-class, 105–115% is healthy, and below 100% means the existing base is shrinking and all growth is being bought through new acquisition. SMB and prosumer products typically run 95–105% because expansion paths are narrower. Always read NRR alongside GRR — high NRR masking low GRR signals concentration risk.

What is the difference between contraction and churn in an ARR bridge?

Contraction is a partial reduction from a customer who remains active — a downgrade, seat reduction, or module removal. Churn is a full drop to zero recurring revenue. The distinction matters because contraction is usually recoverable through customer success intervention, while churn requires a full re-acquisition motion. Merging them into one "lost ARR" bar hides which problem you actually have.

How do you treat a customer who churns and returns in an ARR bridge?

Book churn when ARR goes to zero and reactivation when it returns, using a defined lookback window — 12 months is the most common convention. Within that window the returning customer is reactivation; beyond it, they count as a new logo. Never net the two events against each other, because that understates both gross churn and gross new business in the periods where they occurred.

Can you build an ARR bridge without a customer-level data export?

No, not reliably. Movement classification requires comparing each customer's prior and current ARR, which is impossible from an aggregate revenue total. If customer-level data isn't available, the most you can produce is net ARR change plus whatever the CRM reports for new bookings — and those two rarely reconcile because they use different cut-off dates.

Should ARR bridges use bookings or recognized revenue?

Neither. ARR is a contracted run rate at a point in time, distinct from bookings (which include one-time fees and multi-year totals) and from recognized revenue (which follows ASC 606 timing). Building the cube from recognized revenue introduces ramp and deferral effects that will not match the ARR figure leadership reports.

Wrapping Up

The reason ARR bridges are hard has almost nothing to do with Excel. Build the cube once, derive every movement from the same period-over-period delta, and the reconciliation stops being a task — it becomes an identity you can prove in one cell. What remains is the genuinely hard part: deciding, in writing, what counts as reactivation, how acquired logos enter, and whether a renewal uplift is expansion.

If you're rebuilding this cube every month across segments, products, and currencies, Dezzmond can generate the classification and rollup formulas from a description of your export layout and flag the tie-out break before it reaches the board deck. Once your bridge reconciles, run it at the product grain next — the customer-level view tells you how much moved, and the product cube is usually the only thing that explains why.