Actuarial pricing, capital modelling and reserving

Pricing Squad

Issue 33 -- March 2020

Welcome back to Pricing Squad

Pricing Squad is the newsletter for fellow pricing practitioners and actuaries in general insurance.

Today's issue is about Excel Solver.

How reliable is Solver

Here are a few pricing jobs we recently delivered using Excel Solver:

  • Fitting entire Energy rating structure to historical data
  • Fitting telematics parameters for Private Motor insurance
  • Simultaneously fitting frequency parameters and ILF curves to Inland Marine

If you also use Excel Solver for predictive modelling, you might wonder how reliable Solver is.

Solver engine relies on a Generalized Reduced Gradient method which in turn uses an iterative Lagrangian algorithm.

What does this means in practice?

First, Solver is 100% accurate when the function being minimised is smooth and has no local minimums like in the picture below. This is the case for fitting any linear models, and most maximum likelihood models. In those cases Solver should always find the global minimum or maximum regardless of the starting point.

The only limitation in this case is scaling of parameters. If you have one variable expressed in $ and another expressed in $000,000,000 then just divide the second one by 1e9 before using it in the Solver. Otherwise, the function being minimised (or maximized) might falsely appear to Solver as relatively very insensitive to the first parameter.

Solver is also able to find a minimum when no single variable in isolation improves the target cell. A naive tool would get stuck in a scenario depicted below where changing no single parameter improves model fit. There is a way to improve this model but one needs to travel diagonally along two parameters.

This is often the case for non-linear rates where one needs to simultaneously change base rate and one other parameter to improve model it.

But there are limitations.

If there are multiple local minimums Solver will find the nearest minimum and stop. You can't blame Solver for that if the space of all possible values of all parameters and the possible number of local minimums can theoretically be infinite.

The minimum found be Solver depends on the starting point.

A pragmatic solution in this case is to try a few different starting points and be satisfied with a decent local minimum. For instance, if the starting point represents current rates (or multiple versions of rates) then everyone will be pleased if significantly better rates than those can be found.

10 Downing Street Home Insurance Price Index

From 2020 we are sharing with you the "10 Downing Street Home Insurance Index". This is the simplest insurance price index in the world, and provides the average of the top three aggregator quotes for a single property. Have fun!

This month's winner is Coop. Price index is 4% up from last month.

Copyright © 2020 Jan Iwanik, All rights reserved. You are receiving this email because you subscribed to updates from We publish data and analysis for informational and educational purposes only. You can unsubscribe from this list by emailing us.