Free insurance client tracking spreadsheet
One row per policy, with renewal dates, premiums and commission in the same place. Works in Excel and Google Sheets. No email address, no sign-up, no follow-up sequence.
Most brokerages start here, and plenty run a healthy book this way for years. The template is a real working sheet, not a teaser for something else.
What is in it
One row per policy. A client with three policies gets three rows, which is what makes renewal tracking work: renewals happen per policy, not per client.
Columns you need
| Column | What goes in it |
|---|---|
| client_first_name | First name. One of first or last name must be present. |
| client_last_name | Last name. |
| policy_number | Whatever the carrier calls it. Must be unique per policy. |
| carrier | The insurer on the policy. |
| effective_date | YYYY-MM-DD. When cover started. |
| renewal_date | YYYY-MM-DD. Must be after the effective date. |
Columns worth filling in
| Column | What goes in it |
|---|---|
| policy_type | auto, home, life, business, health or other. Anything else becomes other. |
| status | active, pending, expired or cancelled. |
| premium_amount | The amount billed each period. See the note below, this is the one people get wrong. |
| premium_frequency | monthly, quarterly or annual. |
| commission_rate | First-year commission as a percentage, 0 to 100. |
| client_email, client_phone | Contact details. |
| client_city, client_province | Used for filtering and reporting. |
| notes | Anything you want to remember about the account. |
The one column people get wrong
premium_amount is what is billed each period, matching whatever premium_frequency says. If a client pays $142.50 monthly, the row is 142.50 and monthly, not the annual total.
Put an annual figure in a monthly row and every premium total you calculate is twelve times too large. The three sample rows in the file show all three frequencies on purpose.
Getting the most out of it
- Delete the three sample rows once you have read them.
- Sort ascending on
renewal_dateso the next renewals sit at the top every time you open it. - Add conditional formatting on
renewal_dateto highlight anything inside 90 days. That is the window where a renewal is still worth working. - Add your own columns freely. Extra columns are kept, and the importer ignores ones it does not recognise.
When a spreadsheet stops being enough
The usual signs are familiar: two people editing different copies, a renewal noticed the week it expires, and no way to tell which clients are drifting until one leaves. A sheet holds the data well and does not chase anything.
The columns here are the CovioIQ importer's columns, so if you do move later the file loads without rework and renewals rebuild themselves from the expiry dates you have already typed. That is deliberate, and it is the only pitch on this page.
This sheet holds the book. If what you need is the renewal WORK tracked rather than the data stored, the 90-day renewal checklist is the companion file: one row per task, with an owner and a due date.
No hurry, and no requirement. Take the sheet.
Common questions
Is this really free, and do I have to give you an email address?
Free, and no. The download link is a direct file link with nothing behind it. Use it and never speak to us if you prefer.
Does it work in Google Sheets as well as Excel?
Yes. It is a CSV, so both open it directly. In Google Sheets use File then Import, and choose to replace the current sheet. Keep the header row exactly as it is if you plan to import into CovioIQ later.
What do I put in premium_amount?
The amount billed each period, matching whatever premium_frequency says. If the frequency is monthly and the client pays $142.50 a month, premium_amount is 142.50 and not the annual figure. Putting an annual number in a monthly row is the single most common import error, and it makes every premium total twelve times too big.
Can I add my own columns?
Yes. Extra columns are kept and are ignored by the importer, so add whatever your brokerage tracks. Do not rename the columns listed above if you want a clean import later.
Do I have to use CovioIQ with it?
No. It is a working spreadsheet on its own. The columns happen to match the CovioIQ importer, so if you ever do want to move, the file loads without rework.