---
title: "How to calculate NRR in Excel: formulas and sample table"
url: https://renewalfix.com/guides/how-to-calculate-nrr-in-excel/
description: "How to calculate NRR in Excel or Google Sheets: a sample table, the exact formulas, the mistakes that inflate it, and monthly and annual reporting."
updated: 2026-11-03
---

Guide

# How to calculate NRR in Excel (and Google Sheets)

To calculate net revenue retention (NRR) in Excel, list the customers you had at the start of the period with their ARR then and now, divide the total now by the total then, and leave out anyone who signed during the period. The sample below gets to 85% in 7 formulas, with GRR next to it so you can see what expansion is hiding.

By [John Beckett](https://renewalfix.com/about/#john-beckett) · Updated November 3, 2026

## 1. Lay out the cohort

One row per customer that had [ARR](https://renewalfix.com/glossary/annual-recurring-revenue/) 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](https://renewalfix.com/glossary/net-revenue-retention/) | `=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](https://renewalfix.com/glossary/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](https://renewalfix.com/tools/grr-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](https://renewalfix.com/glossary/monthly-recurring-revenue/) 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](https://renewalfix.com/glossary/gross-revenue-retention/) 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](https://renewalfix.com/guides/board-retention-report/) 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](https://renewalfix.com/product/retention-reporting/).

More on this topic: [revenue retention, definitions, guides and tools](https://renewalfix.com/topics/revenue-retention/).

---

Horizon is renewal risk software for Customer Success teams. Book a call: https://calendar.chilipiper.com/book/me/serban-goanta
