Why we don't fit models in Excel

Actuaries are very heavy users of spreadsheets, especially Microsoft® Excel.  One question we are occasionally asked is why we wrote our software in C++, instead of letting people have direct access to our code as a spreadsheet. 

One answer is that there have been rather too many bugs for comfort in the basic arithmetic and mathematical functions in Excel.  Here are some examples:

1. In Excel 2007 the answer to 850 * 77.1 is given as 100000 instead of the correct answer of 65535.
2. We often need to do simulations, for which we need to generate uniform random numbers distributed between zero and 1.  In Excel 2003 the RAND() function returns negative numbers.
3. Excel will sometimes crash when trying to maximise certain log-likelihood functions.  This may well be due to arithmetic underflow.  For example, LN(1-NORMSDIST(8.5)) will not evaluate because NORMSDIST(8.5) is deemed by Excel to equal 1 instead of a number very slightly less than this.

Bugs do get reported and eventually fixed, of course, but better arithmetic isn't the only reason we don't use spreadsheets:

• Data limits.  Our our software has no hard-coded limits, whereas even Excel 2007 is limited to around one million rows of data.  The largest data file we have processed had 8.9 million records.
• Speed.  Our compiled C++ code is much faster than VBScript in Excel.  We can also take advantage of parallel processing, which spreadsheets cannot.

We could go on, but it is not the aim of this post to knock Excel.  After all, it is an exceptionally handy tool.  However, as the above lists demonstrate, a spreadsheet is a tool for business productivity, rather than one for scientific calculations.