1. Lay out the cohort
One row per customer that had ARR on the first day of the period, with their ARR on that day and on the last day. Use the same two dates for everyone. (Example numbers.)
| Row | A: Account | B: Start ARR | C: End ARR | What happened |
|---|---|---|---|---|
| 2 | Acme | $60,000 | $72,000 | Expanded by $12,000 |
| 3 | Birch | $48,000 | $48,000 | Renewed flat |
| 4 | Cobalt | $36,000 | $0 | Churned |
| 5 | Delta | $30,000 | $24,000 | Downgraded by $6,000 |
| 6 | Ember | $26,000 | $26,000 | Renewed flat |
| 7 | Fjord | $0 | $20,000 | New logo, signed in month 4. Stays out of the calculation |
Fjord is on the list only to show the most common mistake. Its start ARR is 0, and every formula below uses "start ARR above 0" as the test for who counts.
2. The formulas
Put the labels in A9:A15 and the formulas in B9:B15. They work the same in Excel and Google Sheets. Format B11 and B15 as percentages.
| Cell | What it is | Formula | Result |
|---|---|---|---|
| B9 | Starting ARR | =SUMIF(B2:B7,">0",B2:B7) | $200,000 |
| B10 | Ending ARR from the same customers | =SUMIF(B2:B7,">0",C2:C7) | $170,000 |
| B11 | NRR | =B10/B9 | 85% |
| B12 | Expansion | =SUMPRODUCT((B2:B7>0)*(C2:C7>B2:B7)*(C2:C7-B2:B7)) | $12,000 |
| B13 | Churn | =SUMPRODUCT((B2:B7>0)*(C2:C7=0)*B2:B7) | $36,000 |
| B14 | Contraction | =SUMPRODUCT((B2:B7>0)*(C2:C7>0)*(C2:C7<B2:B7)*(B2:B7-C2:C7)) | $6,000 |
| B15 | GRR | =(B9-B13-B14)/B9 | 79% |
The parts reconcile with the total: $200,000 − $36,000 − $6,000 + $12,000 = $170,000, which is B10. If your version doesn't reconcile, a customer is counted twice or a row is in the wrong column. B12 to B14 are also the three numbers the GRR and NRR calculator asks for.
To try it, copy this block and paste it into cell A1 of a new sheet. The columns split and the formulas calculate.
Account Start ARR End ARR Acme 60000 72000 Birch 48000 48000 Cobalt 36000 0 Delta 30000 24000 Ember 26000 26000 Fjord (signed in month 4) 0 20000 Starting ARR =SUMIF(B2:B7,">0",B2:B7) Ending ARR, same customers =SUMIF(B2:B7,">0",C2:C7) NRR =B10/B9 Expansion =SUMPRODUCT((B2:B7>0)*(C2:C7>B2:B7)*(C2:C7-B2:B7)) Churn =SUMPRODUCT((B2:B7>0)*(C2:C7=0)*B2:B7) Contraction =SUMPRODUCT((B2:B7>0)*(C2:C7>0)*(C2:C7<B2:B7)*(B2:B7-C2:C7)) GRR =(B9-B13-B14)/B9
3. Mistakes that change the number
- New logos in the cohort. Count Fjord's $20,000 in the ending ARR and the total becomes $190,000, so "NRR" reads 95%, 10 points too high. NRR measures what existing customers did. New business has its own line.
- Mixing periods. Start-of-year ARR in one column and end-of-quarter ARR in the other, or MRR on one side and ARR on the other. Both columns use the same two dates and the same unit.
- One-off fees. Implementation, training and overage charges are revenue, but they aren't recurring. Leave them out of both columns.
- Counting a downgrade and a churn for the same customer. If Delta had downgraded in March and left in September, its end ARR is 0 and the whole $30,000 is churn. The formulas above handle this because contraction only counts customers whose end ARR is above 0.
- A number finance doesn't recognize. Use finance's ARR definition, their customer list and their dates. The board meeting shouldn't be about whose 85% is right.
4. Monthly and annual reporting
Monthly. The cohort is everyone with MRR on the first of the month; the two columns are MRR on the first and on the last day. The same formulas apply. Monthly NRR sits close to 100%, so small differences matter: 98.5% a month compounds to 83.4% over 12 months (0.985 to the power of 12), and 99% a month compounds to 88.6%. Don't multiply a monthly loss by 12.
Annual. The cohort is everyone with ARR on the first day of the year, measured again on the last day. For a line the board can follow between year ends, report a trailing 12-month NRR each month: the cohort from 12 months ago, measured today.
Whichever you report, say which it is ("NRR for the 12 months to 30 September, existing customers only") and show it next to GRR and logo retention. In the sample, 85% NRR and 79% GRR tell different stories, and the 79% is the one that needs explaining. The board retention report guide covers how to present both.
Horizon reports GRR, NRR and logo retention and checks them against your finance figures before showing them, then splits every lost dollar into reasons with an owning team. See retention reporting.
More on this topic: revenue retention, definitions, guides and tools.
Updated November 3, 2026