A step-by-step guide to computing net revenue retention in Excel from a billing export: revenue per customer in two periods with SUMIFS, NRR over the customers who were active a year ago, gross revenue retention with MIN, expansion, contraction and churn per customer, NRR by start-year cohort, and the identity that proves the movements add up. Includes the exact formulas and the point at which the spreadsheet stops being enough.
Net revenue retention is this year's revenue from last year's customers, over last year's revenue from them. In Excel it is two SUMIFS columns and a handful of IFs. This guide gives the formulas, the cohort cut and the identity.
One sheet, Billing, one row per recurring invoice line:
| Column | Content |
|---|---|
| A | Customer |
| B | Invoice date |
| C | Recurring amount |
On Calc: prior period start and end in B1 and B2; current period start and end in C1 and C2.
Customer list in A5 with UNIQUE, or Remove Duplicates in older Excel. Prior revenue, B5:
=SUMIFS(Billing!C:C,Billing!A:A,A5,Billing!B:B,">="&$B$1,Billing!B:B,"<="&$B$2)
Current revenue, C5: the same with $C$1 and $C$2.
=SUMIFS(C5:C5000,B5:B5000,">0")/SUM(B5:B5000)
The criterion keeps only customers who had prior revenue. SUM of the prior column needs no filter because new customers are zero there.
Helper column D5:
=IF(B5>0,MIN(B5,C5),0)
Then:
=SUM(D5:D5000)/SUM(B5:B5000)
Gross retention can never exceed 100 percent. If yours does, the helper column is wrong.
Expansion, E5:
=IF(AND(B5>0,C5>B5),C5-B5,0)
Contraction, F5:
=IF(AND(B5>0,C5<B5,C5>0),B5-C5,0)
Churn, G5:
=IF(AND(B5>0,C5<=0),B5,0)
New, H5, shown and not used in NRR:
=IF(AND(B5<=0,C5>0),C5,0)
=SUM(B5:B5000)+SUM(E5:E5000)-SUM(F5:F5000)-SUM(G5:G5000)-SUMIFS(C5:C5000,B5:B5000,">0")
Zero. If not, a customer has negative revenue in one period, usually a credit note landing in the wrong year; find it with a filter on C5<0.
The movement rates follow: expansion over prior, contraction over prior, churn over prior. NRR equals one plus expansion rate minus contraction rate minus churn rate. Gross retention equals one minus contraction rate minus churn rate.
Add each customer's first invoice date, I5:
=MINIFS(Billing!B:B,Billing!A:A,A5)
Cohort year, J5: =YEAR(I5). NRR for the 2024 cohort:
=SUMIFS(C5:C5000,B5:B5000,">0",J5:J5000,2024)/SUMIFS(B5:B5000,J5:J5000,2024)
One row per cohort year. A healthy blended figure over a shrinking recent cohort is the commonest finding.
Customer in Rows, a Period field in Columns, Amount in Values gives the two totals. The retention figures still need the helper columns, so the pivot saves only Step 1.
New customers in the numerator. SUM(C:C)/SUM(B:B) is growth, not retention.
One-off revenue included. Last year's implementation fee becomes this year's contraction.
Renamed customers. One churn and one new logo, both false.
Periods of different length. Twelve months against nine, because the current year is not finished. Use trailing twelve months for both, or point-in-time recurring revenue.
Annual NRR for a board pack is a good spreadsheet job. Monthly, by cohort and segment, with expansion split into seats, products and price from line-level billing data, is where it strains; see the four signs a spreadsheet is no longer enough. For what the figure should be, see what is a good net revenue retention; for the same arithmetic by hand, NRR on ten customers. Covirage computes the same figures from the same export every month, with the identity checked.
Recurring revenue at two points in time if you have it: MRR or ARR per customer at the end of each period. If the billing export only has invoices, use twelve months of recurring invoice lines against the prior twelve, and exclude one-off items such as implementation fees, or they will show as churn the following year.
Because NRR measures what happened to revenue you already had. A customer with no prior revenue has nothing to retain. Including them turns the figure into a growth rate and hides churn. The SUMIFS criterion on the prior column being above zero is what excludes them.
With a mapping sheet, before anything else. A customer renamed mid-year shows as one churn and one new logo, which lowers NRR and gross retention falsely. Map both names to one parent and total by parent.