Expert guide

Insurance Agency Renewal Tracker in Excel

Every policy on the work list at the right time, and retention you can see by producer

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: export policies from your agency management system into an Excel Table, add a lead-time table by line of business, calculate each policy's work-start date as expiration minus lead days, and filter a daily renewal queue by account manager. Track stage, remarketing, renewal premium and outcome, then report retention by producer and line. Add a cross-sell grid per client. For relationship tracking around it, see Excel CRM Lite.

An agency's book of business is mostly renewals. Losing a renewal because nobody started the conversation until the week before expiration is the most avoidable loss there is. Agency management systems hold the policies, but many agencies still run renewal work from spreadsheets because they need a simple queue, stage tracking and reports their system makes awkward.

Tracker Structure

ColumnNotes
Client, PolicyNo, LOBLine of business drives lead time and workflow
Carrier, Effective, ExpirationFrom the AMS export
CurrentPremiumAnnualised
Producer, AccountManagerAccountability
WorkStartCalculated from expiration and LOB lead days
StageNot started, Info requested, Marketing, Quoted, Presented, Bound, Lost
RemarketY/N with reason (rate increase, claims, coverage gap, client request)
RenewalPremium, Outcome, LostReasonResults

The Formulas

Lead days by line of business (tblLeadTimes: LOB, LeadDays):
=XLOOKUP([@LOB], tblLeadTimes[LOB], tblLeadTimes[LeadDays], 60)

Work start:
=[@Expiration] - [@LeadDays]

Days to expiration:
=[@Expiration] - TODAY()

Behind schedule (should have started, still not started):
=AND(TODAY() >= [@WorkStart], [@Stage] = "Not started")

Premium change at renewal:
=IF([@RenewalPremium] = "", "", [@RenewalPremium] / [@CurrentPremium] - 1)

The Daily Renewal Queue

=SORT(FILTER(tblPolicies[[Client]:[Stage]],
       (tblPolicies[AccountManager] = B1) *
       (TODAY() >= tblPolicies[WorkStart]) *
       ISNA(MATCH(tblPolicies[Stage], {"Bound","Lost"}, 0)),
       "No renewals to work"), 6, 1)

Sorted by expiration (6 is the Expiration column's position within the returned columns; adjust it to your layout), with Behind schedule rows highlighted in red. Add a column for large premiums so the biggest accounts are never missed.

Retention Reporting

Policies up for renewal in month (start in A2):
=COUNTIFS(tblPolicies[Expiration], ">=" & A2, tblPolicies[Expiration], "<" & EDATE(A2,1),
          tblPolicies[Outcome], "<>")

Retained (renewed or rewritten):
=COUNTIFS(tblPolicies[Expiration], ">=" & A2, tblPolicies[Expiration], "<" & EDATE(A2,1),
          tblPolicies[Outcome], "Renewed")
+COUNTIFS(tblPolicies[Expiration], ">=" & A2, tblPolicies[Expiration], "<" & EDATE(A2,1),
          tblPolicies[Outcome], "Rewritten")

Policy retention:  =Retained / UpForRenewal
Premium retention: retained renewal premium ÷ expiring premium of all policies up for renewal

Break both down by producer, line of business and carrier. Lost reasons, recorded every time, explain the numbers: price, service, coverage, client sold the business, moved to a direct writer.

Cross-Sell Grid

A client with a business owner's policy but no cyber, umbrella or workers' compensation cover is a conversation waiting to happen. Build a grid of clients against lines of business:

In a client × LOB grid, cell formula:
=IF(COUNTIFS(tblPolicies[Client], $A5, tblPolicies[LOB], B$4,
             tblPolicies[Outcome], "<>Lost") > 0, "✓", "")

Filter clients with three or more empty commercial lines and add those questions to their renewal review.

Renewal Review With AI

  • Pre-renewal summary

    From policy list, premium history and claims notes, a one-page summary for the account manager.

  • Quote comparison

    Compare expiring and renewal documents for changes in limits, deductibles, exclusions and endorsements, with page references for verification.

  • Client emails

    Renewal reminders and information requests drafted from the row data.

  • Cross-sell prompts

    Questions to ask based on the client's industry and the gaps in the grid.

Coverage interpretation stays with licensed staff, and client documents should go only to approved tools. The email workflow is in personalized emails from Excel with AI; document comparison follows the extraction approach in extracting PDF data with AI.

Keeping the Tracker in Sync

  • Refresh the policy export weekly with Power Query; keep stage, notes and outcome in a separate table keyed by policy number so refreshes do not wipe your work.

  • Never edit policy facts in the tracker; correct them in the AMS.

  • Archive bound and lost renewals to a history table each quarter for multi-year retention trends.

When several people update stages at once, a shared database such as our Access CRM avoids the conflicts of a shared workbook. For a management dashboard of retention, new business and premium by producer, see Excel dashboards and BI reporting.

FAQ

Insurance renewal tracker FAQ

Lead times, retention and AI reviews

Commercial policies are commonly worked 90 to 120 days ahead to allow time for remarketing; personal lines often 30 to 60 days. Set the lead time by line of business in the tracker so each policy appears on the work list at the right moment.

Client, policy number, line of business, carrier, effective and expiration dates, current premium, producer and account manager, renewal stage, remarketing flag, quoted renewal premium, outcome (renewed, rewritten, lost) and lost reason. Cross-sell opportunities per client are a valuable addition.

Divide policies renewed or rewritten with the agency by policies that came up for renewal in the period. Track it by count and premium, by producer, and by line of business. Premium retention can exceed 100 percent when rate increases outweigh lost accounts, so track both.

No. Most agencies should keep policies in their agency management system. An Excel or Access tracker is useful as a renewal work queue, for reporting the AMS does not provide, or for smaller agencies and MGAs that need structure before investing in more software.

AI can summarise a client's coverage and claims history before a renewal review, draft renewal reminder emails, compare renewal quote documents for changes in limits and exclusions, and suggest cross-sell questions. Coverage advice remains the licensed professional's responsibility.