Calculate Retention Rate by Agent and Carrier in Excel

How to Calculate Retention Rate by Agent and Carrier in Excel

This tutorial shows how to calculate Retention Rate by Agent Name and Carrier in Excel, helping you identify which agents or carriers drive strong renewals and which may be impacting your book of business.

1. Create a Unique List of Carriers

• Use the UNIQUE() function on the Carrier column to remove duplicates.
  
• Copy and paste values using Ctrl + Shift + V to remove the formula.
  
• Delete the zero that appears at the top of the list.
  
• Sort alphabetically (Data → Sort A–Z) for easier reference.
  

2. Create a Unique List of Agents

• Use UNIQUE() again on the Agent Name column (e.g., Column K).
  
• Copy and paste as values, remove zeros, and delete the header row (e.g., “Agent Name”).
  
• Sort alphabetically.
  
• This clean list of agents will be used for dropdowns later.
  

3. Build an Agent Dropdown List

• Select a cell where you want your dropdown (for example, cell B3).
  
• Go to Data → Data Validation → List.
  
• To create a comma-separated list of names:
  
  • Use the TEXTJOIN() function with a comma as the delimiter to join all agent names into one string.
    

     Example:
     =TEXTJOIN(“,”, TRUE, Range)

• Copy and paste the result as values.
  
• In the Data Validation source box, paste the comma-separated list.
  
• Now you have an alphabetical dropdown list of agents that autofills as you type.
  

4. Count Renewed Policies (Renewal Flag = “Yes”)

• Use the COUNTIFS() function to count how many policies were renewed for a specific Agent and Carrier.
  

    Example:
     =COUNTIFS(RenewalFlagRange, “Yes”, CarrierRange, SelectedCarrier, AgentRange, SelectedAgent)

• Lock the cell reference for the selected agent (press F4) so it doesn’t move when you drag the formula down.
• This formula returns the number of renewed policies for each Carrier–Agent combination.
  

5. Count Total Policies

• Create another COUNTIFS() formula to count all policies (regardless of renewal).
  

     Example:

     =COUNTIFS(CarrierRange, SelectedCarrier, AgentRange, SelectedAgent)

• Lock your cell references again.
• This gives you the total number of policies written per Carrier–Agent combination.
  

6. Calculate Retention Rate

Divide the count of renewed policies by the total count:
     =Renewed ÷ Total

• Format as a percentage with Ctrl + Shift + 5.
• This gives the Retention Rate per Carrier–Agent pair.
  

If you prefer a single formula, nest one COUNTIFS() inside another for a compact version:

     =COUNTIFS(RenewalFlagRange,”Yes”,CarrierRange,SelectedCarrier,AgentRange,SelectedAgent) /

 COUNTIFS(CarrierRange,SelectedCarrier,AgentRange,SelectedAgent)

7. Calculate the Average Retention Rate

• To find the overall average retention (all agents and carriers):
• Count all “Yes” values in the Renewal Flag column:

      =COUNTIFS(RenewalFlagRange,”Yes”)

• Count all rows in the column (excluding headers):

     =COUNTA(RenewalFlagRange) – 1

• Divide the two results:

     =COUNTIFS(RenewalFlagRange,”Yes”) / (COUNTA(RenewalFlagRange)-1)

• Format as a percentage.
  

8. Build the Retention Chart

• Highlight your table (Carriers, Retention Rates, and Average).
  
• Go to Insert → Recommended Charts → Combo Chart.
  
• Use:
  
  • Bars for individual retention rates
    
  • Line for the average rate (orange line)
    
• The average line helps visualize which Carrier–Agent pairs are above or below average.
  

9. Format and Interpret Results

• Apply dark or branded chart colors under Chart Design.
  
• Add data labels for quick visibility.
  
• Use the chart to identify patterns, such as:
  
  • Agents or carriers with low retention rates (below the orange line)
    
  • Top performers with consistently high retention
    

If, for example, James with Allstate shows a 47% retention rate while the average is 58%, you’ll know where to focus performance discussions.

10. Optional Enhancements

• Add dropdowns to filter by carrier or agent dynamically.
  
• Apply conditional formatting to highlight poor performers.
  
• Extend the model with time-based tracking (monthly or quarterly retention).
  

Result

You now have a clear, dynamic Excel report that shows Retention Rate by Agent and Carrier, complete with an average benchmark line for quick comparison. This model helps pinpoint which agents and carriers contribute to strong renewals and where your retention strategy can improve.