Old site

Excel as a swiss army knife

I’ve recently been involved in two surveys looking at what software actuaries use, and how they use it.

The first was organised by the GIRO working party on software use in general insurance, which I chaired. We got over 700 responses to our online survey, which was great. As we describe in the report, Excel is by far the most popular software, used by about 90% of the respondents.

The second survey was one that I ran myself, looking at software use in ICAs. There were 45 responses, from both life and non-life actuaries. Again, Excel was overwhelmingly popular: only one respondent doesn’t use it for preparing ICAs.

One theme that emerged from both surveys is the lack of informed choice. I really get the impression that many people use Excel just because it’s there, and because they can make it do what they need. However, it often takes a surprising amount of hard work. It’s a bit like using a swiss army knife to build a house; it probably can be done, but it’s not what you’d choose.

This was really brought home to me at the workshop we presented at the GIRO conference last week, on the results of the survey conducted by the working party. Presumably the people there were somewhat interested in software issues, and might be thought to be more informed than the run of the mill actuary. However, there were at least a couple who were very surprised when we said that we were disappointed at how few people used special purpose statistical software, given how poor the standard statistical functionality is in Excel.

Well, it’s definitely poor if you are operating in the tails of distributions. Microsoft admits that there are shortcomings in some of its algorithms, but basically says that it doesn’t matter much as everything is OK as long as you are not in the tails. But that’s exactly where most actuaries are operating. There are several useful descriptions of what the problems are. One that is especially annoying in some organisations is that the functions were changed in Excel 2003. If there are several different versions of Excel in the organisation, the results calculated by a spreadsheet can vary depending on who edited the spreadsheet most recently.

The random number generator in Excel isn’t brilliant, either. A new random number generator was implemented in Excel 2003, but in the first release some of the numbers it generated were negative, instead of in the [0, 1] range as advertised (this has since been fixed in a patch). The generator that was used in earlier versions of Excel was rather less random. If you are doing serious stochastic work, you should make sure that the generator you are using is sufficiently random. There are a number of add-ins available that provide more sophisticated algorithms than that used in the standard Excel one.