Insurance & Uninsured Loss Tracker Spreadsheet
This spreadsheet adds up what risk actually costs you — premiums, the losses, your own hours — and works out the value below which a claim costs more than it recovers.
The premium is not what risk costs you; it is the part of it you can see. Underneath sit the losses the insurer did not pay, the losses you never claimed for, and the afternoons spent on photographs, forms and loss adjusters. This file writes all four down and then judges every incident against its own policy’s break-even line.
What is in it
- Nine linked tabs, from Start Here through to a one-screen Overview.
- Losses — one row per incident, seven things typed, with a plain verdict written on the row.
- By Policy — what each policy costs and what it has paid back.
- Never Claimed — what you swallowed, and which of it you should have claimed for.
- Worth Claiming — the break-even line for each policy: the excess plus what one claim adds to the renewals.
- Setup — six numbers and two lists; change the loading and every verdict moves.
Who it is for
Anywhere you carry insurance and things go wrong: a haulier or anyone running a fleet, a manufacturer, a care home, a farm, a pub group, a contractor, a landlord.
The worked example
The worked example covers three years. Premiums came to $222,600, the incidents cost $403,422, and the insurers paid back $156,233 — leaving $247,189. Add 971 of your own hours worth $34,968 and the real cost of risk is $504,757, or 2.27 times the premium.
The break-even line on this business’s fleet policy is $10,716: $1,500 of excess plus $9,216 that one claim adds to three years of renewals. Of 103 claims, 79 were under their own policy’s line and 35 paid nothing at all because the loss came in under the excess. On the other side, 162 losses were swallowed for $157,763, and the file marks the thirteen that should have been claimed — $37,082 between them. The fleet policy paid back 29 cents in the dollar; buildings and contents paid back $1.79.
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. Order on WhatsApp — $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 know whether a loss is worth claiming on?
You compare it against that policy's own line, which is the excess plus the premium multiplied by the loading and the number of years it lasts. On the worked example's fleet policy that line is $10,716: $1,500 of excess and $9,216 of what one claim adds to three years of renewals. The file judges every incident against its own policy's line rather than one average for the business, and in the worked years 79 of 103 claims came in under it.
Is this only for fleets?
No, it works anywhere you carry insurance and things go wrong: a manufacturer, a care home, a farm, a pub group, a contractor, a landlord. It handles several policies side by side and gives each one its own line; in the worked example those lines run from $432 to $10,716. That is the point, because one average line for a whole business tells you nothing.
We do not know what a claim does to our premium, and we have never written our small losses down.
Ask your broker what a claim loads the premium by, because most will tell you, and if they will not, eight to ten per cent for three years is the usual working figure, changed in one cell. For the losses, start now and put in what you can remember for the last year. Almost nobody has ever written the small ones down, and even a rough list changes the renewal conversation.
Which spreadsheet programs does it run in?
Excel, Google Sheets and LibreOffice. It is built as .xlsx for Excel 2016 or newer on Windows or Mac, and goes into Google Sheets through File then Import then Upload. No macros, no add-ons, no account to create, nothing to install and no subscription.
Is this telling us not to claim, and does it hold personal data?
No, it is not telling you not to claim, because a large claim is exactly what the policy is for; it tells you where the line is before you pick up the phone, which is a different thing. It holds incidents, policies and amounts, with no names. It is not a policy management system or a claims portal and it will not tell you whether you are properly covered, only what the cover you already have has actually been worth.
Need it built around your business?
This file assumes commercial policies with an excess and a renewal loading you can estimate. If your cover is arranged differently — a captive or self-insured retention where the loading argument does not apply, professional indemnity claims that stay open for years rather than settling in weeks, a fleet on a fault-based rating where only at-fault claims move the premium, or a group programme where one site’s claims reprice everybody — I can build the same arithmetic around how your renewals actually respond.
All other templates are listed on the Excel & Google Sheets Templates page.