How to Measure Maintenance Plan Renewal Rate %
and Spot Customer Churn Early

How to Calculate Maintenance Plan Renewal Rate % in Excel

1. Calculate the End-of-Month Date

Start by creating an end-of-month column based on the service date. This gives you a clean month value to group renewals consistently.

• Use the EOMONTH function to convert each service date into its corresponding month-end date
• Fill the formula down through the full dataset
• Confirm that each service date now rolls into the correct month-end value

This month field becomes the foundation for the rest of the analysis.

2. Create a Unique List of Months

Next, build a clean reporting list of months from the end-of-month column.

• Use the UNIQUE function to pull each month only once
• Wrap it with the SORT function so the months appear in the correct chronological order
• Check that the sequence is properly sorted before building the summary table

This gives you the month labels that will drive your chart and monthly KPI view.

3. Extract the Year for Annual Summaries

To create a yearly summary later in the video, strip the year out of the month field.

• Use the TEXT function to return the year from each month value
• Place the year in a separate helper column
• Keep this field available for annual rollups below the monthly chart

This makes it easy to summarize renewal activity at both the monthly and yearly level.

4. Calculate How Many Plans Were Up for Renewal

Once the data is structured, count how many maintenance plans were due for renewal each month.

• Use SUMIFS on the binary renewal-due field
• Match the month in your summary table to the month field in the dataset
• Copy the calculation down for every month in your report

Because the renewal-due field is binary, summing the ones gives you the total number of plans that were up for renewal.

5. Calculate How Many Plans Were Renewed

Now calculate how many of those due plans actually renewed.

• Use COUNTIFS to count records where the renewed field equals one
• Add another condition so the renewal-due field also equals one
• Add the month condition so the count is tied to the correct reporting month
• Fill the calculation down across all months

This gives you the monthly count of successfully renewed plans.

6. Calculate Cancelled Renewals

To build the stacked column chart, create a separate value for plans that did not renew.

• Subtract renewed plans from total renewals due
• Use this as the cancelled-renewal value
• Fill the calculation down for each month

This step makes it possible to show renewed and cancelled plans together in one stacked visual.

7. Calculate the Renewal Rate

With renewals due and renewals completed in place, calculate the renewal rate for each month.

• Divide renewed plans by renewals due
• Format the results as a percentage
• Copy the calculation down across all months

This creates the percentage line that will sit on top of the column chart.

8. Build the Combo Chart

Now create the chart that combines counts and percentages in one view.

• Highlight the month column, renewed count, cancelled-renewal count, and renewal rate
• Go to Insert and choose a Combo Chart
• Change the renewed and cancelled series to Stacked Columns
• Change the renewal-rate series to a Line
• Put the renewal-rate line on the Secondary Axis

This gives you the final layout shown in the video:

• renewed plans as one part of the stacked column
• cancelled renewals as the other part
• renewal rate as a line across the top

9. Adjust the Chart for Readability

Once the combo chart is built, clean it up so the key patterns stand out.

• Update the chart title
• Review whether you want data labels on everything or only on the line
• Add labels only to selected high or low points if a fully labeled chart feels too noisy
• Format those selected labels to make them easier to read

This keeps the chart focused and makes the monthly trend easier to interpret.

10. Build the Annual Summary

To see the KPI at the yearly level, create a second summary section using the year field.

• List the years you want to report on
• Use SUMIFS again to total renewals due by year
• Reuse the same structure for renewed plans and cancelled renewals
• Calculate the annual renewal rate from those totals

This gives you a higher-level view of whether renewal performance is improving or declining year over year.

11. Lock References Correctly Before Dragging Formulas

When building the annual section, make sure your formulas copy across properly.

• Lock the necessary columns or rows before dragging formulas horizontally
• Leave the year reference flexible where needed so it updates as you move across
• Double-check that the referenced columns are not shifting incorrectly

This helps you avoid broken totals and keeps the annual summary accurate.

12. Finalize the Report

At this point, you have a complete renewal tracking setup.

• monthly renewals due
• monthly renewed plans
• monthly cancelled renewals
• monthly renewal rate
• annual summary by year
• combo chart with stacked columns and a line

Once the data is structured correctly, the rest of the analysis becomes straightforward and easy to maintain.