Request Demo

How to Calculate Customer Retention Rate in Excel [Analysis Template]

Blog by Ian Luck
March 10, 2023

At CustomerGauge, we've built the software and playbook for sales and CX teams to tackle customer churn at the root cause.

From experience feedback collection through to revenue retention tools, our technology is specifically designed to improve your growth rate.

A critical step toward improving customer retention is knowing you have a problem in the first place. Our research shows that 32% of companies don't measure retention at all. 🤯

This is the very first step to improving your retention rate. So let's learn how to calculate customer retention rate in a simple Excel sheet we've put together.

DOWNLOAD THE EXCEL SHEET HERE.

Benchmark Report CTA

Step 1: Make a Copy of the "How to Calculate Retention" Excel Sheet [Tab 1]

First things first, please make a copy of the spreadsheet. I often find people miss this step and request edit access, but we can't give that because it would edit the sheet for everyone—so please follow the below instructions to copy your very own template.

If you make a copy (as shown in the screenshots below), you'll have your own copy that's completely invisible to others.

How to calculate customer retention rates in excel

Step 2: Read Through the Customer Retention Rate Formula in the Excel Sheet [Tab 2]

There are three methods of calculating your customer retention rate (actually there's five, we dive deeper into them in our article customer retention rate formula here).

In this simple retention rate calculation spreadsheet, we cover three: CRR, RRR and Net RRR. Each of which will show slightly different variations on your customer retention stats.

For example, CRR shows the number of customers retained, whereas RRR shows the amount of revenue you retain (a much more important number for the health of your business going forward.

CRR Formula in Excel

Step 3: Review the Calculation Examples [Tab 3]

Review tab three for a few retention calculation examples.

In example one, we look at the customer and revenue retention rate between period one and two.

Retention is always measured between two time periods, whether thats one month, one year or whatever suits your product type.

What you're measuring is the revenue or number of customers that still remain in the second period.

Calculate Customer Retention

Example 1 (the left table in the image above) automatically calculates both CRR and RRR. The key difference between the two is that CRR only looks at the change in volume of customers, whereas RRR looks at the change in amount of revenue.

It's useful to have both because if you lose one very large customer it'll more noticeable in the RRR calculation, but if you're churning multiple small customers that'll be easier to notice in the CRR calculation.

Example 2 (the table on the right) automatically calculates Net RRR. What's the difference? NRRR includes upsell and downsell in the calculation. So if an existing customer buys more or reduce their account size—it reflects in your customer churn dashboard.

The Net NRR calculation goes: (Revenue Period 2 from Same Accounts in Period 1 + Any Upsell or Downsell) / Revenue in Period 1

Step 4: Prepare Your Inputs for the Customer Retention Rate Calculator

To calculate your retention rates using our Excel Sheet, you'll need three datasets.

1. A list of customers

2. Each of their revenue in the last period

3. Each of their revenue in the following period

Make sure you're given the most up-to-date revenue data, including any upsell or downsell. Using these three, you should be able to calculate everything.

Step 5: Insert Your Customer Data & Ensure the Calculation Cell Covers Your Additions

In tab 4, we've combined all the calculations into one simple-to-use table.

Retention Calculator in Google Sheets

If you take your customer list, or segment you're analyzing now, and insert the revenue from those customers for Q1 2022. At the end of Q2 2022 you can do the same, in the period 2 column.

In column four, add any upsell or downsell that was seen in those accounts (you can calculate this by subtracting period 2 revenue from period 1 to see the change). Column five will automatically make a total for you.

Finally: you'll see all your retention rate statistics—ready to report.

Why Using A Dedicated Software Delivers Better Results

It's all very well making this simple calculation yourself in an Excel sheet. But you're missing out on a whole wealth of insight that only a dedicated customer technology can deliver.

Your next steps after calculating retention is to (1) understand why and (2) tackle churn at the root cause.

That's where it's best to use a B2B-focused revenue retention software like CustomerGauge. With our software you'll know before a customer is about to churn so your account managers can remedy the situation before it happens.

We've built the revenue-retention playbook (Account Experience). Request a meeting with our team to learn more.

Frequently Asked Questions
A customer retention dashboard is your visual homebase for all your customer experience and retention efforts. It needs to track key performance metrics around customer retention—like retention rate; churn rate; and Net Promoter Scores for each account. However, in a good customer retention dashboard, we also suggest that you include detailed insights on leading factors of customer loyalty as well, things like closed-loop rate; customer engagement signals and the drivers of customer loyalty this month.
To answer this question properly, I’ve roped in an expert on customer retention—our very own Ian Luck, VP of Global Marketing at CustomerGauge. (He’s been driving flagship customer retention campaigns for over a decade and was key to product development at CustomerGauge.) Here’s Ian’s answer to why customer retention is important: “Ever wonder how companies develop inhuman growth rates year over year? Turns out, they're masters at maximizing customer retention rates. In fact, these companies usually have triple digit net retention rates and beyond. Customer retention is an often overlooked way to maximize growth. But there are benefits beyond growth. With stellar retention comes brand ambassadors that will talk about your brand every chance they get—driving more and more net new revenue opportunities.” There you have it: strong retention = strong revenue growth + more referral opportunities.

About the Author

Author Icon
Ian Luck
Ian has been in the CX market for over a decade evangelizing best-practices and strategies for increasing the ROI of customer programs. He loves a loud guitar, a thick non-fiction book, and a beach day with his family. You can catch him around the north shore of Boston, MA.
Subscribe now to get the latest from the CustomerGauge Blog
See our Privacy Policy and GDPR terms.
More On This Topic
Loading Symbol