Plenty of good insurance products began life as a workbook. An actuary builds the rater in Excel, underwriters quote from it, and it does the job. Spreadsheets are quick to build, quick to change and everyone already knows how to use them.
The trouble starts when the workbook keeps being asked to do more. This post covers how to tell a spreadsheet rater has reached its limit, what it costs to keep it going, and how to move to a rating engine so that every quote prices the same afterwards, unless you decide it shouldn't.
Five signs it's time
- Someone wants a price over an API. A broker, partner or comparison site needs live quotes, and a workbook can't answer a request.
- There's more than one copy. Different teams or brokers have their own versions, and nobody is quite sure which is current.
- Every rate change waits for one person. Only one person really understands the workbook, and checking the effect of a change takes days.
- Audit questions take a week. It's hard to say which version priced a given quote, or exactly what changed between two versions.
- It has grown in the dark. Hidden sheets, macros and links to other files have piled up, and small edits have effects nobody predicted.
What staying in Excel really costs
Spreadsheets aren't the problem in themselves. The risk comes from running one as a production system:
- Mistakes hide well. A lookup that points one row out, or a range that doesn't stretch to cover a new band, can go unnoticed for months.
- Testing is informal. Most raters are checked by trying a handful of quotes, which misses the subtle errors.
- History is a folder of renamed files. Versions get copied with a date in the name, and the record of what changed is patchy.
- Anyone with the file can change a rate. There's no real control over who edits what.
For a regulated product, each of those makes it harder to show how prices are set and that they behave as intended.
A six-step migration
The aim is simple: after the move, every quote gives the same price as before unless you've chosen otherwise. The best way to prove that is with the spreadsheet itself.
Step 1: Pick the reference version and freeze it
Agree which copy of the workbook is the reference and stop changing it while you migrate. Any rate change in the meantime should either wait or be made in both places.
Step 2: Use the spreadsheet to write your tests
Run a large set of inputs through the workbook and record what it returns. Cover:
- the worked examples the actuary used when setting the rates;
- values just either side of every band boundary;
- every decline and refer rule;
- a sample of real quotes from the last few months.
Each of these, a full set of inputs paired with the spreadsheet's answer, becomes a test for the new model.
Step 3: Rebuild the logic, not the formulas
Pull out the rate tables and work out the calculation order: which factors apply, in what sequence, and how rounding, minimum premiums and fees are handled. In Swallow you can upload the workbook and let the AI draft a first model from it, or build it step by step on the canvas. Rate tables are plain rows that you can upload and download as CSV. Then someone who knows the spreadsheet checks each step against it. You end up with a readable model rather than a copy of cell formulas, like the example in our developer guide.
Step 4: Fix every failing test
A failing test points to a difference in logic. The quote trace for that test shows every input, lookup and formula with its values, so you can line it up against the workbook row by row. The usual causes are rounding at a different point, a band edge that is inclusive in one and exclusive in the other, and adjustments buried in the workbook that nobody wrote down.
Sometimes the investigation turns up a mistake in the spreadsheet. If it does, note it and agree with the pricing team whether to keep the current behaviour for now or fix it as a separate change with its own sign-off.
Step 5: Run both side by side
Before switching, price live quotes through both the workbook and the new model for a while and compare them. Every difference should have an explanation before you go further.
Step 6: Switch over and lock the workbook away
When the parallel run is clean, make the new model the only source of prices. Archive the spreadsheet somewhere it can't be used to quote by accident.
Life after the spreadsheet
Once the rater is a tested model, a rate change becomes short and controlled. In Swallow you start a new draft from the live version, make the change, run the tests, review what moved, then approve and publish it. Every version is kept, every step is logged with who did it and when, and every quote is saved with the version that priced it. Our posts on testing insurance pricing models and the insurance rate change process go into more detail.
The same model can then serve quotes through the API, embedded forms and AI clients, so the product is no longer limited to whoever has the file open. And if someone still wants to see it in a spreadsheet, a model can be exported back to Excel.
Getting started
Send us your spreadsheet rater and the worked examples behind it, and we'll talk you through getting it live on Swallow. Get in touch.
