How to Analyze New Business vs Renewal Mix in Excel

Learn how to compare new business and renewal premiums in Excel to understand your agency’s production mix. In this tutorial, you’ll see how to visualize the balance between new policies and renewals, uncover trends that impact acquisition costs, and measure customer loyalty over time.

Download the Excel file used in this tutorial:

Download

How to Analyze New Business vs Renewal Mix in Excel

This tutorial shows how to analyze your mix between New Business and Renewals using written premium data. Understanding this mix helps you measure customer retention, satisfaction, and how renewals help lower your overall acquisition cost.

1. Identify Unique Values for Business Type

  • Your business type (New Business or Renewal) is stored in Column I.
  • Use the UNIQUE() function to list distinct values:
     =UNIQUE(ColumnI)
  • If you want the list to update automatically as new data is added, add a period (.) inside the formula.
  • Copy the results and paste values using Ctrl + Shift + V to remove the formula.
  • You’ll now have a clean two-item list: New Business and Renewal.

2. Calculate Written Premium by Business Type

  • Written Premium is in Column N.
  • Use the SUMIFS() function to calculate total premium for each type:
     =SUMIFS(WrittenPremiumRange, BusinessTypeRange, “New Business”)
     =SUMIFS(WrittenPremiumRange, BusinessTypeRange, “Renewal”)
  • If you want to add more criteria (for example, by month), the same formula works with additional conditions.
  • Format the totals as currency using Ctrl + Shift + 4.

3. Lock Cell References for Accuracy

  • When dragging formulas, use F4 to lock references to columns that should remain fixed.
    • For example, always lock the column containing Written Premium and Business Type.
    • Leave the cell with “New Business” or “Renewal” unlocked so it adjusts when copied down.
  • This ensures formulas remain accurate when copied across cells.

4. Create a Donut Chart

  • Highlight your summary table (New Business, Renewal, and totals).
  • Go to Insert → Charts → Donut Chart.
  • Adjust the hole size or colors under Format Chart Area.
  • To display percentages, click the chart, select Add Data Labels → More Options → Percentage.
  • You can increase the font size or reposition labels for better readability.

5. Customize the Chart Design

  • Add a title such as New Business vs Renewal Mix.
  • Center it below the chart or reposition for clarity.
  • If desired, increase the donut’s size or adjust slice spacing to make percentages easier to read.

6. Optional: Add Counts or Averages

  • To analyze volume instead of dollar totals, replace SUMIFS() with COUNTIFS() to count how many policies fall into each category.
  • To find the average premium per policy, divide total written premium by the count of policies for each type.

7. Interpret the Results

  • A higher Renewal percentage suggests stronger customer retention and lower acquisition costs.
  • A high New Business share might indicate growth but higher acquisition expense.
  • Tracking this ratio helps you balance retention and new client growth.

Result

You now have a visual dashboard showing how much of your written premium comes from New Business vs Renewals. This metric helps evaluate long-term profitability, client loyalty, and marketing efficiency, all from a simple Excel model.

New Business vs Renewal Mix in Excel Dashboards

Q1. What does “new business vs renewal mix” mean in insurance analytics?
 This comparison shows how much of your written premium comes from new customers versus existing policy renewals. A healthy mix with strong renewals indicates customer retention and lower acquisition costs.

Q2. Why is analyzing renewals important?
 Renewals are typically more profitable because they reduce marketing and onboarding expenses. Tracking your renewal premium helps measure client satisfaction and long-term retention, two critical drivers of agency growth.

Q3. How can I compare new business and renewals in Excel?
 You can organize your policy data by type (new business or renewal) and summarize total written premium for each category. Then, use a donut or pie chart in Excel to visualize the share of each group, making it easy to see your production mix at a glance.

Q4. What insights can I gain from this analysis?
 By tracking the ratio between new business and renewals, you can identify whether your agency is focusing too heavily on acquiring new clients or maintaining existing ones. Balanced growth typically combines both strong acquisition and renewal retention.

Q5. Can I apply this method to other insurance KPIs?
 Yes. The same Excel approach works for comparing other categories, such as direct vs referred business, personal vs commercial lines, or carrier-specific performance.

Q6. How often should I review my new business vs renewal mix?
 Monthly or quarterly reviews are ideal. Regular monitoring helps you adjust marketing efforts, forecast renewal income, and evaluate the effectiveness of client retention strategies.

Get more lessons like this
by joining our newsletter

Receive weekly Excel tutorials, tips, and KPIs straight to your inbox

Join Now

Menu

Databoards

Analysis & Development

© 2025 All Rights Reserved.

Facebook Twitter Youtube