Insurance Policy Tracking: 21 Columns and a 120-Day Renewal Plan

Insurance policy tracking means keeping one record per policy, and per layer of any excess tower, with the carrier, policy number, limits, expiration date, broker contact and a named internal owner. Work back from each expiration date: start the renewal 120 days out, because some states let an insurer send a nonrenewal notice only 30 days before expiry.

Sound familiar? Your policies live in the broker’s portal, the finance inbox and a PDF on someone’s desktop, so nobody can say what renews next or which customer holds a certificate showing last year’s dates. Here is a tracker you can build this week, a renewal timeline, a clean broker handoff and the rules that govern certificates.

Key takeaways

• One row per policy, and one per layer. An umbrella and two excess layers are three policies with three carriers and three expiration dates.
• Start each renewal 120 days before expiry. Ohio commercial property and casualty insurers, and New York excess liability insurers, can send nonrenewal notice as late as 30 days out.
• Calculate, don’t type. `=I2-TODAY()` gives days left and `=I2-120` gives the date to start the renewal.
• Name an owner and a backup for every policy. “The broker has it” is not an owner.
• A certificate of insurance is not cover. Texas and New York law both say a certificate cannot amend, extend or alter the policy it refers to.
• Keep a list of every certificate you issued. When a policy renews, each holder needs a new one.
• Review the register for 20 minutes every week. A spreadsheet only works if someone opens it.

One record per policy and per layer, so a layer renewing out of step is visible months before it lapses.

Why do businesses lose track of insurance policies?

Businesses lose track because policies are bought by different people, on different dates, through different channels. The data is scattered, not missing. Five patterns cause most of the trouble:

• Each line was bought by a different team. Finance buys property, operations buys auto, the CEO signed the D&O (directors and officers) renewal.
• The broker holds the only complete list. That works until the broker changes, or until you need an answer at 6 p.m. on a Friday.
• Several legal entities, one spreadsheet each. Nobody can say which company is covered for what.
• Layers renew out of step. An excess policy bought mid-year renews on its own date, separate from the umbrella beneath it.
• Certificates go out and are forgotten. Landlords, lenders and customers hold certificates with old dates, and nobody knows who they are.

Fix the data shape first. If you renew policies for clients rather than for your own business, our page on insurance renewal tracking for brokers covers the portfolio view.

What should an insurance policy tracking spreadsheet include?

An insurance policy tracking spreadsheet needs one row per policy or layer, the facts from the declarations page, two people, and four formula columns that tell you what to do. Copy this layout into Excel or Google Sheets with headers in row 1 and the first policy in row 2.

Format H, I and R as dates and Q as a number. The tower check compares every row in the same program and flags any layer whose expiration date differs. COUNTIFS works the same way in Excel, Google Sheets and LibreOffice.

Add two conditional formatting rules to the data range: a red fill for `=$Q2<=30` and an amber fill for `=AND($Q2>30,$Q2<=120)`. Then sort by column Q, smallest first.

Worked example (illustrative dates). A business buys its umbrella and first excess layer in the same program. The umbrella expires Jan 15, 2027, but the excess layer was placed later and expires Feb 1, 2027. On Nov 2, 2026 the umbrella row shows 74 days left, a start date of Sep 17, 2026 and the status “In renewal”. Both rows show “Dates differ”, which tells you to ask the broker whether to co-term the layers at this renewal.

Prefer a ready-made file? Our free insurance policy and COI tracker template has an “Our policies” sheet with renew-by dates and status colors, plus a sheet for certificates you collect from vendors. Add the Program, Layer and Tower check columns above if you buy layered cover.

How far ahead should you start tracking a policy renewal?

Start 120 days before the expiration date. That is our working rule, not a legal requirement, and it is set by the shortest notice you might receive. State law sets minimum nonrenewal notice periods for many commercial lines, and they vary: in the five states on our state-by-state nonrenewal notice table, they run from 30 days (Ohio, and New York excess liability policies) to 60 days (New York primary commercial policies and Washington). Starting at 120 days means you are already in the market before any notice can arrive.

Florida adds a condition worth tracking. Its 45-day notice duty applies “only if the insured has furnished all of the necessary information so as to enable the insurer to develop the renewal premium prior to the expiration date.” Late underwriting data weakens your own protection. Our commercial insurance renewal checklist breaks the 120 days into weekly tasks with email templates.

Who hands what to whom: the tracker owner starts the chain at 120 days and closes it by reissuing certificates.

How do you hand a renewal to your broker without losing anything?

Hand it over in writing, with a date, and keep the next step in your tracker rather than in the broker’s inbox. A clean handoff has five stages:

1. 120 days: send renewal data. Updated payroll, revenue, vehicle and property schedules, and any new locations or contracts. Ask for loss runs for each line.
2. 90 days: agree the strategy. Renew as is, market the account, change limits or deductibles. Record the decision and date in Notes.
3. 60 days: review terms. Compare quoted limits, deductibles, exclusions and premium with the expiring policy, line by line.
4. 30 days: approve and bind. Give written approval. Ask for the binder (a temporary evidence of cover until the policy is issued) and log its number.
5. After binding: update the row and reissue certificates. New policy number, dates and premium in the tracker, then a new certificate for every holder in column P.

Write the date each stage happened into the row. When a policy goes wrong, the question is always “who had it, and when”, and the row answers it.

What can a certificate of insurance prove, and what can’t it?

A certificate of insurance summarizes a policy for someone who is not a party to it. It does not change the policy. Partners often ask for wording a certificate cannot carry, so track the policy, then issue certificates from it.

In one multiyear review of construction vendors’ certificates published by IRMI (International Risk Management Institute), David Dybdahl reported “a material misrepresentation rate of more than 90 percent” between what contracts required and what certificates showed. That was certificates received, not issued, but it shows why the policy, not the certificate, belongs at the center of your tracker. Collecting certificates from vendors is a separate job; see certificate of insurance tracking.

Do this now: a 90-minute insurance policy audit

You can build a complete register in one sitting without buying anything. Time estimates are ours, for a business with 10 to 20 policies.

1. Collect declarations pages (20 minutes). Ask your broker for a schedule of insurance and the current declarations page for every policy, including excess and specialty lines.
2. Build the sheet (15 minutes). Copy the 21 columns above and paste in the formulas.
3. Enter every policy (30 minutes). One row per policy and per layer. Leave nothing in Notes that belongs in its own column.
4. Assign owners (10 minutes). Every row gets an owner and a backup. If nobody will take one, that is your first finding.
5. List certificate holders (10 minutes). Search sent mail for “certificate of insurance” and list each holder against the policy.
6. Book the reviews (5 minutes). A 20-minute weekly review and a renewal start date for every row showing “In renewal”.

How can you make insurance tracking more useful than a list of dates?

Use the register to make better renewal decisions, not just on-time ones.

• Co-term where you can. Ask the broker to align layers and lines to one date. Fewer dates means fewer misses and one renewal meeting.
• Track premium history. Keep last year’s premium beside this year’s so rate changes are visible at the 60-day review.
• Record limits against contract requirements. Leases, loan agreements and customer contracts often set minimum limits. Note the strictest one per line.
• Keep a claims column. Open claims affect renewal terms; the broker should hear about them from you first.
• Review once a year with the broker. Use the register as the agenda: gaps, overlaps, limits and which policies could be combined.

Spreadsheet or software for insurance policy tracking?

A spreadsheet is enough for a few dozen policies, one owner and a weekly review that actually happens. It struggles when several people own policies, when you run several entities or layered programs, or when the person who built it leaves, because nothing reminds anyone unless the file is open. Formula errors are the other risk: Raymond Panko of the University of Hawai’i compiled audits of real business spreadsheets and reported that “these studies found errors in 94% of the spreadsheets studied.”

ExpiryEdge is one way to make the move: each policy or layer becomes a record with an owner, a backup, files and reminders at 90, 60, 30 and 7 days by email, SMS, Slack or Teams. For the policies your own business buys, see insurance renewal tracking software. Running wrap-up programs on construction projects? Read OCIP wrap-up insurance explained.

Frequently asked questions

What is insurance policy tracking?

Insurance policy tracking is keeping a register of every policy a business holds, with the carrier, policy number, limits, deductible, premium, expiration date and a named owner, and acting on each date before it passes. Layered programs need one record per layer, because each layer has its own carrier and expiration date.

What fields should an insurance policy tracker have?

At minimum: named insured, coverage line, layer, carrier, policy number, effective and expiration dates, limits, deductible, premium, broker contact, internal owner, backup owner and certificate holders. Add formula columns for days left (=I2-TODAY()), a start-renewal-by date (=I2-120) and a status.

How early should a business start an insurance renewal?

We suggest 120 days before expiration. That is advice, not law. State minimum nonrenewal notice periods differ, and in Ohio a commercial property or casualty insurer can mail notice 30 days before expiration, so starting early keeps your options open.

Does a certificate of insurance extend coverage?

No. Texas Insurance Code §1811.152 says a certificate “does not amend, extend, or alter the coverage afforded by the referenced insurance policy,” and New York Insurance Law §502(c) says the same. Coverage comes from the policy and its endorsements.

How do you track umbrella and excess layers?

Give each layer its own row with its carrier, limit, attachment point and expiration date, and group the layers with a Program column. A COUNTIFS check such as =IF(COUNTIFS(C:C,C2,I:I,"<>"&I2)>0,"Dates differ","OK") flags any layer that renews on a different date.

Can I track insurance policies in a spreadsheet?

Yes, for a few dozen policies with one owner who opens the file every week. A spreadsheet sends no reminders, and audits compiled by Raymond Panko found errors in 94% of business spreadsheets studied. With several owners, entities or layered programs, software with reminders and an audit trail is the safer control.

Sources

• Texas Legislature, Insurance Code Chapter 1811, Certificates of Property and Casualty Insurance (§§1811.152, 1811.153, 1811.155; accessed 12 Oct 2026).
• Texas Department of Insurance, Certificates of Insurance: Frequently Asked Questions (last updated 31 Oct 2022; accessed 12 Oct 2026).
• New York State Senate, Insurance Law §502 (accessed 12 Oct 2026).
• Ohio Revised Code, Section 3937.26 (effective 13 Sep 2004; accessed 12 Oct 2026).
• Florida Legislature, 2026 Florida Statutes §627.4133 (accessed 12 Oct 2026).
• eCFR, 44 CFR Part 61, Insurance Coverage and Rates (Appendix A, Section E; accessed 12 Oct 2026).
• IRMI, David Dybdahl, Avoiding Common Insurance Certificate Errors (17 Jul 2015).
• Raymond R. Panko, What We Don’t Know About Spreadsheet Errors Today, arXiv:1602.02601.
• Last reviewed: 12 October 2026