The engine prices one way: sum insured × base rate × the product of the factors that apply, with a floor at the minimum premium. Most tariffs can be written in that form exactly. This article shows the three shapes we see most and how each becomes rows on the Rates sheet.
Shape 1: a base rate with loadings and discounts
A tariff that says '0.8% of the sum insured, +25% for heavy manual work, +20% for ages 55 to 64, −10% for a 3-year term' becomes one base line and three question, age and duration factors. Additive percentages become multipliers: +25% is 1.25, −10% is 0.90. Check one thing: in the original, did loadings add or multiply? If they added (+25% and +20% = +45%), a risk with both will now be rated 1.25 × 1.20 = 1.50 instead of 1.45. Either accept the small difference or re-express the combined cases as their own factor lines.
- type = base
- rate 0.8
- type = question, question = occupation_class, values = Heavy manual
- rate 1.25
- type = age, from 55 to 64, derive = age
- rate 1.20
- type = duration, from 36 to 36
- rate 0.90
Shape 2: a matrix (ages down, something across)
A grid with age bands down the side and, say, occupation classes across the top is a base rate for one reference cell, an age factor per row and a question factor per column, as long as the grid is proportional (every row is the same multiple of the reference row). Check that first: divide each cell by the reference cell. If the ratios are constant down each column, the matrix is two factor tables. If they are not, the tariff is not multiplicative; in that case keep the matrix as question factors on a combined question, or ask for the underlying rating model.
Shape 3: fixed prices per tier
'Basic 2,500 for 60 a year, Plus 5,000 for 110, Max 10,000 for 200' is a set of packages. Give each package its sum insured and choose the base rate so that sum insured × base rate reproduces the price; where the prices are not proportional, add a question factor keyed to the package choice to correct each tier. The customer sees the price, not the arithmetic.
Always check back
- 1Pick ten real quotes from the old tariff, covering the corners: youngest and oldest, lowest and highest sum insured, the heaviest loading, the longest term.
- 2Upload the workbook, open the Rating tab and rate each one in the live calculator.
- 3Compare to the old premium. Differences are almost always additive-versus-multiplicative or a rounding rule; fix the rows, upload again.
Questions
- Our tariff has a different minimum premium per package. Is that possible?
- The engine has one minimum premium per product. Model a higher floor for a specific package by choosing that package's sum insured and base so the lowest possible premium already clears the floor, or split into two products.
- Should the rate table include insurance premium tax?
- No. Keep the rate table net: it describes the risk premium. Tax and fees are a matter for the invoice, not for rating, and mixing them in makes every loading wrong by the tax rate.