Replacing a spreadsheet rater: when to move and how to do it safely
Signs that an Excel rating model has reached its limits, the risks of keeping it, and a step-by-step migration that uses the spreadsheet's own results as tests.
Many insurance products start life in a spreadsheet. A pricing actuary builds the rater in Excel, the underwriters use it to quote, and for a while it works well. Spreadsheets are quick to build, easy to change and familiar to everyone involved.
Over time the same spreadsheet often ends up doing far more than it was designed for. This article covers the signs that a spreadsheet rater has reached its limits, the risks of keeping it, and how to move to a rating engine without changing a single price by accident.
Signs it is time to move
A spreadsheet rater is usually due for replacement when one or more of these apply:
- You need real-time quotes. Brokers, partners or price comparison websites want a price through an API, and the spreadsheet cannot provide one.
- Several copies exist. Different teams or brokers have their own versions, and nobody is certain which one is current.
- Rate changes are slow and risky. Each change needs the one person who understands the workbook, and checking the effect takes days.
- Audit questions are hard to answer. It is difficult to say which version priced a given quote, or what changed between two versions.
- The workbook has grown. Hidden sheets, macros and links to other files have built up, and small edits can have effects nobody expects.
The risks of staying in Excel
Spreadsheets are not wrong in themselves. The risk comes from how they are used in production:
- Errors are easy to make and hard to find. A formula that points at the wrong row or a range that does not extend to a new band can go unnoticed for months.
- There is no built-in testing. Most spreadsheet raters are checked by trying a few quotes, which misses subtle errors.
- Version control is manual. Files are copied and renamed, and the history of changes is incomplete.
- Access is hard to control. Anyone with the file can change a rate.
For a regulated product, these gaps make it harder to show how prices are set and that the pricing behaves as intended.
How to migrate safely
The goal of a migration is that every quote prices the same after the move as before, unless you have decided otherwise. The spreadsheet itself is the best tool for proving this.
1. Freeze a reference version
Agree which version of the spreadsheet is the reference and stop changing it during the migration. Any rate changes in this period should wait or be made in both places.
2. Generate test cases from the spreadsheet
Run a large set of inputs through the spreadsheet and record the results. Include:
- the worked examples the actuary used when designing the rates;
- values on either side of every band boundary;
- every decline and referral rule;
- a sample of real quotes from recent months.
These become the test suite for the new model. Each test is a complete set of inputs and the spreadsheet's result.
3. Rebuild the logic as a model
Extract the rate tables and reconstruct the calculation order: which factors apply, in what sequence, and how rounding, minimum premiums and fees work. With Ptally, AI reads the workbook to extract tables and draft the calculation, and an engineer or pricing consultant checks every step against the spreadsheet. The result is a readable model rather than a copy of the spreadsheet's formulas, as shown on our model building page.
4. Run the tests until every one passes
Any test that fails points at a difference in logic. The debug trace for a failing quote shows every input, lookup and formula, so it can be compared line by line with the spreadsheet. Common causes include rounding at a different step, a band boundary that is inclusive in one version and exclusive in the other, and hidden adjustments in the workbook that were never documented.
Sometimes the investigation finds an error in the spreadsheet. When that happens, record it and decide with the pricing team whether to keep the existing behaviour for now or correct it as a separate, approved change.
5. Run in parallel
Before switching over, price live quotes through both the spreadsheet and the new model for a period and compare the results. Any difference should be explained before the old rater is retired.
6. Switch over and retire the spreadsheet
Once the parallel run is clean, make the new model the only source of prices. Archive the spreadsheet so it cannot be used for quoting by mistake.
After the move
Once the rater is a tested model, a rate change becomes a short, controlled process: make the change in a draft version, run the test suite, review the differences, then approve and publish. Every version is recorded, and every quote can be traced back to the version that priced it. Our article on regression testing for insurance pricing covers this process in more detail.
The same model can then serve quotes through an API, embedded forms and AI clients, so the product is no longer limited to the people who have the spreadsheet open.
Getting started
Send us your spreadsheet rater and the worked examples behind it. We will rebuild it as a tested model, show you every test passing against the spreadsheet's own results, and run it alongside your current process until you are ready to switch. There is no implementation fee and nothing to pay upfront. Talk to our team.