Insurance Agent Commission & Renewal Tracker Spreadsheet
This spreadsheet works out what the policies you have already written will pay you month by month over the next year, and how much of what you have been paid could still be taken back.
A pipeline tells you what you might write and a commission statement tells you what you did write. Neither one tells you what November looks like on the book you already own, or how much first-year commission is still sitting inside a chargeback window and is not yet yours to keep.
What is in it
- Six linked tabs, from Start Here through to a one-screen Overview.
- Setup — one date. Every months-in-force figure, every next-renewal date and the whole twelve-month outlook is measured from it.
- Product Lines — 20 rows: your first-year rate, your renewal rate and your chargeback window, per kind of policy.
- Policies — 400 rows: seven columns you type, seven that work themselves out.
- Renewals Due — the next twelve months, month by month, with the policy count and the commission due in each.
- Overview — the whole book on one screen, including what is still at risk of being clawed back.
Who it is for
Independent agents and producers with a personal book across property, casualty and life, who are paid on first-year and renewal rates and carry the chargeback risk themselves.
The worked example
The worked book holds 116 policies across six lines, $293,790.00 of premium and 103 still active. It booked $74,717.70 of first-year commission, and the policies already on the books are due to pay $24,855.50 of renewal commission over the next twelve months — 33.3% of what year one paid, set out in the month it is due.
Line by line the difference is stark: nineteen term life policies paid $20,221.50 in year one, and next year the same nineteen will pay $772.20 between them, because term life renews at 3.8% while homeowners renews at 78.1%. Meanwhile $25,895.53 is still exposed — $23,717.39 of commission not yet earned, which is 31.7% of all first-year commission, plus $2,178.14 owed back on policies that have already ended.
How it works
Compatible with Microsoft Excel 2016 or newer on Windows or Mac, Google Sheets, and LibreOffice. No macros, no add-ons, no account to create, nothing to install.
The download has two workbooks: one with the example above already filled in, so you can see what every column expects, and the same file completely blank for your own figures. A plain-English guide comes as a PDF. WhatsApp’tan sipariş ver — $9
Orders are taken on WhatsApp: message me and I’ll send the payment details, then the files as soon as the payment is confirmed.
A look inside the file
Questions people actually ask
How do I work out what my existing book will pay me in renewal commission next year?
You enter each policy with its premium and product line, and the file applies that line's renewal rate to produce the next twelve months of renewal commission, month by month. In the worked book, 116 policies and $293,790.00 of premium booked $74,717.70 of first-year commission and are due $24,855.50 of renewal commission over the next twelve months — 33.3% of what year one paid. The monthly split matters: December is 16 policies and $4,121.10, August is 5 policies and $561.90.
Does it work if I keep only part of the commission, or have overrides?
Yes — put your own share in as the rate: if you keep 70% of a 15% commission, type 10.5%. Rates are set per product line, first year and renewal separately, across 20 lines and 400 policies, which is a substantial personal book in one file. The lines are whatever you write — the worked book runs from umbrella at a 91.7% renewal rate to term life at 3.8%.
My carrier claws back on a different schedule. Can I change it?
Yes — the chargeback window is set per product line on the Product Lines tab, and the model is pro rata across whatever number of months you put there. It is written out in plain English on the Setup tab so you can check it against your own agreement. In the worked book that produces $23,717.39 of unearned first-year commission plus $2,178.14 owed on ended policies — $25,895.53 of total exposure, $13,876.16 of it in whole life on a twenty-four month window.
Do I need Excel, or will Google Sheets and LibreOffice do?
All three work — the file is .xlsx, opening in Excel 2016 or newer on Windows and Mac, in Google Sheets through File then Import then Upload, and in LibreOffice. There are no macros, no add-ons, no account to create and no subscription. You download the file and it is yours.
Where do I record client names and policy numbers?
Nowhere, on purpose — there is no field for a date of birth, an address or a real policy number, because this file is about the commission, not the client. It is not a CRM, a pipeline or a lead tracker, and it does not track appointments. It connects to no carrier or agency management system, and it is not licensing, tax, legal or financial advice.
Need it built around your business?
This file assumes commission rates set per product line and a chargeback that unwinds pro rata across a window you choose. If your book is arranged differently — an override sitting above you on other producers’ business, group benefits billed per employee per month, or an agency where several producers share one book and one set of renewals — I can build the same arithmetic around the way you are actually paid.
All other templates are listed on the Excel & Google Sheets Templates page.