Actuarial pricing, capital modelling and reserving

Pricing Squad


Issue 21 -- January 2018

Welcome back in 2018!

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

Today's issue is about benchmarking Excel.


Benchmarks for summing Excel files

Last year, I worked with a marine portfolio which had to be analysed in Excel. This involved averaging exposure and claims across multiple segments in a loop until the correct maths converged into a smooth non-linear model.

Problem

Suppose you have a long list of policies. Each policy has a number, a producing country and premium.

Say, you need total premium by country. Of the different formulae available in Excel, which one is best?

Stats

Below are benchmarks using four types of Excel syntax and three types of ranges. The sum is across 250,000 policies with two conditions. I measured time per calculation in miliseconds using Excel 2013.

Conclusion

SUMIFS trumps array functions and SUMPRODUCT. If you need more flexibility in your conditions, go for array functions but avoid explicit if() statements inside. Use exact ranges instead of more generic ranges. This way you can speed up your spreadsheet up to 12 times.

That is all for today. Thank you for reading!


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