Net Customer Retention Rate (NRR): Track Customer Growth in Excel

How to Calculate Net Customer Retention Rate % in Excel

1. Extract the Month and Year from the Snapshot Date

• Start with the Snapshot Month column in your dataset.
• Use the TEXT function to pull out the month name from the snapshot date.
• Use the TEXT function again to pull out the year from the same date.
• Fill both formulas down so every row has a month and year value.

This gives you the date fields needed to identify the starting customer base and summarize the data by reporting period.

2. Create a Unique List of Snapshot Dates

• Use the UNIQUE function on the snapshot date column to generate a list of distinct reporting dates.
• Format the results as dates so the list is easier to read.
• Remove dates from the prior year if you only want to chart one year of data.
• Keep only the monthly dates you want to include in the final analysis.

This creates the monthly timeline that will drive the summary table and chart.

3. Flag the Existing Customer Base

• Create a helper column to identify which records belong to the existing customer group from the prior period.
• Use the COUNTIFS function to check:
  • the employee or customer ID
  • the month
  • the year
• Set the logic so the row returns a value that identifies whether that record belongs to the base period you are tracking.

In the video, this step is used to identify the customers that existed in December 2025, so the calculations for 2026 are based only on that prior group.

4. Set Up the Summary Table

• Create a summary section with the monthly dates across the reporting period.
• Add column headings for:
  • Beginning Revenue
  • Expansion Revenue
  • Contraction Revenue
  • Churn Revenue
  • Ending Revenue
  • Net Customer Retention Rate %
  • Target
• Copy the headings into place so the structure is ready before adding formulas.

This becomes the table used for both calculation and charting.

5. Calculate Beginning Revenue

• Use the SUMIFS function to total the beginning revenue.
• Set the criteria so the sum only includes:
  • rows flagged as part of the existing base
  • rows that match the selected snapshot month
• Fill the formula across the summary table for each reporting month.

This gives you the baseline revenue for the retained group at the start of each period.

6. Calculate Expansion, Contraction, and Churn Revenue

• Copy the same SUMIFS structure used for beginning revenue.
• Change only the revenue column being summed each time.
• Use the same criteria:
  • existing customer flag
  • matching snapshot month
• Repeat for:
  • Expansion Revenue
  • Contraction Revenue
  • Churn Revenue
• Fill the formulas across all months.

This saves time because the structure stays the same and only the source revenue field changes.

7. Calculate Ending Revenue

• Create the ending revenue column using the values already calculated in the summary table.
• Add the positive revenue movements.
• Subtract the negative revenue movements.
• Fill the calculation across the row for all reporting months.

This gives you the ending value for each month based on the starting revenue and monthly changes.

8. Calculate Net Customer Retention Rate %

• Create the Net Customer Retention Rate % column using the ending revenue and beginning revenue values.
• Format the result as a percentage.
• Fill the formula across the row to calculate the monthly retention result.

This gives you the monthly trend needed for charting and performance tracking.

9. Add the Target Row

• Create a target row in the summary table.
• Use a fixed target percentage for each month.
• Fill that value across the full reporting period.

This gives you a benchmark line to compare against the monthly retention trend.

10. Create the Chart

• Highlight the month column and the two rows you want to plot:
  • Net Customer Retention Rate %
  • Target
• Hold Ctrl if needed to select non-adjacent ranges.
• Go to Insert and choose Recommended Charts.
• Select a Line Chart to compare actual performance against target.

This creates a clean monthly view of retention performance over time.

11. Refine the Chart Layout

• Update the chart title to match the KPI.
• Adjust the column widths in the worksheet if needed so the summary table is easier to read.
• If the line variation looks too flat, adjust the chart axis scale to show more movement.
• Add Data Labels to the main line if you want to display the monthly values.
• If labels are hard to read, add a background or adjust the font size for clarity.

These formatting changes make the chart easier to present and interpret.

12. Review the Underlying Row-Level Logic

• Check individual employee or customer records in sequence to confirm the monthly flow is working correctly.
• Review how the prior month’s ending value becomes the next month’s starting point.
• Confirm whether revenue movements are being classified correctly as:
  • expansion
  • contraction
  • churn
• Use a single record example to validate that the monthly transitions make sense.

This helps confirm the summary table is accurate before using it in a dashboard or report.