Formula-based rating: from the actuarial spreadsheet to production

At almost every carrier, rating starts life in a spreadsheet. It's the tool actuaries know best, it lets them iterate quickly and it exposes every step of the calculation. The trouble starts when that spreadsheet has to become the price a customer sees on a website, at a broker or in a partner's checkout.

Usually someone in IT receives the file, interprets it and rewrites the logic in code. From then on there are two ratings: the spreadsheet and production. Every adjustment means repeating the translation, and every translation is a chance for the two versions to drift apart.

Formulas that read like the spreadsheet

The alternative is to express the rating directly as formulas the platform executes, with syntax simple enough for an actuary to read and validate without depending on a developer. Something like:

sum_insured * base_rate * age_factor

Each coverage gets its formula, built from the product's variables. If the spreadsheet says the theft premium is the sum insured times a rate that depends on the zone, the formula says exactly that. There's no intermediate layer to interpret.

This has an important side effect: the rating becomes auditable. Anyone with access can see how each coverage is calculated, and a review no longer requires reading code.

Lookup tables instead of nested conditions

Much of a rating isn't formulas but tables: rates by zone, factors by age band, values by plan. In a spreadsheet they're cross-sheet lookups. Translated into code, they often end up as long chains of conditions that are hard to maintain.

It's better to keep them as tables and query them from the formula. In quore, a lookup function takes the table name, the variable that selects the row and the column you want to read:

sum_insured * lookup(zone_rates, zone, 'theft')

When a zone's rate changes, you update the table and the formula stays the same. If several product lines share a table, such as a zone list or a vehicle rate book, you can store it once in a shared library and use it from every product.

Test before you publish

A readable formula isn't necessarily a correct one. Before a rating reaches production, test it with concrete cases and compare the result against the spreadsheet.

That's what a simulator is for: you enter a sample customer's data and see the full breakdown, coverage by coverage, with taxes and installments. The practice that works best is to build a set of reference cases together with the actuarial team:

• Typical cases that represent most quotes.
• Cases at the edges of every range: minimum and maximum age, lowest and highest sum insured.
• One combination for each relevant row of the lookup tables.
• Cases that should be rejected, to confirm validations work.

If they all match the spreadsheet, the rating is ready. If one doesn't, you found the bug before a customer did.

Drafts and published versions

A live rating isn't edited in place. Changes go into a draft, get tested in the simulator and only reach production when someone publishes a new version. Until then, every channel keeps quoting on the current version.

This solves two problems at once. You can work on a rate change for days without affecting operations, and you get traceability: every issued policy is tied to the version it was quoted on, so you can always reconstruct why a customer paid what they paid.

Net premium and total premium are not the same

A common mistake when taking a rating to production is blurring concepts the spreadsheet keeps cleanly apart. Fix the vocabulary from day one:

• Net premium (prima in Spanish): the net technical price of the risk, the output of each coverage's formula.
• Total premium (premio in Spanish): what the customer actually pays, the net premium plus taxes, surcharges and other charges.

Ambiguous labels cause confusion because it's unclear whether taxes are included. If screens, documents and integrations always separate the two, customers understand what they pay and the technical team knows what it's measuring. That's also why taxes and surcharges are best defined separately, each with its own calculation base, rather than folded into a coverage formula.

Taking rating from Excel to production doesn't have to be a translation. If the formula is the rating, the actuary can read it, the simulator can test it and the published version is exactly what was approved.