Actuarial pricing, capital modelling and reserving

Pricing Squad

Issue 5 -- August 2016

Welcome back to Pricing Squad!

Pricing Squad is a newsletter for fellow pricing practitioners and actuaries in general insurance. Enjoy, and let me know your comments and ideas for future issues.

Today's issue is about classifying rating factors, such as occupation, line of business, car make etc. These granular risk factors tend to have hundreds of levels which often need to be classified into a few robust groups.

Classify with class

Recently I was asked to finalise a loss-cost GLM model which had taken ages to get through underwriting approval. The bottleneck in the process was the inclusion of hundreds of occupation levels. They needed to be organised into a dozen or so neat groups.

Perhaps you've come across this problem too when classifying areas or vehicle makes in personal insurance, or lines of business in commercial insurance?

Triage logic

In the end, the following common-sense and time-tested triage logic saved everyone a lot of time, and eliminated subjectivity.

First, I tabularised model residuals by the factor being classified and by another independent factor, namely the underwriting year. I used GLM prediction divided by capped actual claims as residuals.

Based on the number of underwriting years available (five), I decided that we needed four out of five years' data behaving consistently to claim statistical significance, a figure based on a binomial distribution and a 95% confidence interval.

For each level of the factor being classified it was decided whether the model residuals were significantly higher than the total average, significantly lower than the total average, or not significantly different from the total average at all. This gave us an initial classification into three groups.

Next, we repeated the process recursively within each of the three groups a couple of times. The whole process took a few minutes. Done!

When back-testing other data on multiple occasions, I had found that two to five iterations of the triage logic had usually been enough to produce a classification which made sense to me and to the underwriters.

A free macro for you

If you like the triage method, you can use the free Triage Classification macro from my website. (Since the macro is free, please remember that I take no responsibility for how you use it and for the advice you provide to your client or employer based on your use of it.)
  1. Download the Excel file containing the macro from the Code Repository.
  2. Open the spreadsheet and enable the VBA macro.
  3. Paste your data residuals and exposure measure into the spreadsheet.
  4. Hold down Ctrl + t.
  5. When the dialog box pops up, specify the significance threshold, number of iterations and the spreadsheet ranges where residuals and exposures are stored. It is best to start with one iteration and gauge the result before progressing further.
  6. It is best to start with one iteration and gauge the result before progressing further.
  7. Hit "Go".
  8. Unique classification IDs for each factor level will appear under the "new group" column header.

Have fun!

And don't forget to check out our other time-saving pricing tools.

Can we help you?

If you are interested in new pricing ideas to radically simplify your current analytical procedures and deliver reduced loss ratio quickly, or if you are simply looking for an actuarial contractor, please be in touch.

Thank you for reading and have a great day,
Jan Iwanik, FIA PhD

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