Actuarial pricing, capital modelling and reserving

Pricing Squad

Issue 10 -- January 2017

Happy New Year from Pricing Squad!

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

Today's issue shows you how to fit amazing pricing models using only Excel.

Amazing models with Excel only

The Fox and the Cat

A Fox was boasting to a Cat of its clever devices for escaping its enemies. "I have a whole bag of tricks," he said, "which contains a hundred ways of escaping my enemies."

"I have only one," said the Cat; "but I can generally manage with that."

Just at that moment they heard the cry of a pack of hounds coming towards them, and the Cat immediately scampered up a tree and hid herself in the boughs. "This is my plan," said the Cat. "What are you going to do?" The Fox thought first of one way, then of another, and while he was debating the hounds came nearer and nearer, and at last the Fox in his confusion was caught up by the hounds and soon killed by the huntsmen. Miss Puss, who had been looking on, said:


First things first

The following models have been easily delivered using only Excel:
  • Price-matching a telematics product. We optimised 19 pricing parameters on a batch of 1,000 market quotes.
  • Simultaneously price-matching three household covers (building, content and items) separately from an all-cover-combined market data set. We matched a non-linear, non-multiplicative pricing model with 51 parameters using 25,000 data rows.
  • Price optimisation of a private motor account. 33 pricing parameters and 50,000 data rows.
  • Building a multiplicative elasticity model for private motor - 7 parameters and 50,000 data rows.
  • Modelling Winner's Curse by segment using 25 pricing parameters and 50,000 data rows.
  • Fitting a complex non-linear commercial fleet pricing model to loss cost data using 75 parameters.

How to build the workbook

A good Excel workbook needs to
  1. contain a big multivariate data set,
  2. present parameters by rating factor clearly,
  3. not use the vlookup() function.
This will ensure mathematical accuracy, clarity and speed.

Here is how you can do it:


... want to continue reading?

Please provide your email to log in or to subscribe to Pricing Squad: