• Home
  • |
  • Blog
  • |
  • Microsoft Excel for Actuaries: Should We Move On?
Excel for Actuaries

Microsoft Excel is a core part of the day to day work for actuaries. For anyone interested in pursuing a career in actuarial science; the most basic skill required is the need to master and understand Excel. Excel for actuaries is the backbone of the majority of their work. Excel has both benefits and drawbacks however, even with the drawbacks of using Excel for actuarial work, the industry still relies on it.

Limitations of Excel

excel icon on laptop

Excel has a capacity of 1,048,576 rows and 16,384 columns. Although this is a high volume, actuarial work can involve massive sets of data. This limitation can result in data being cut off without anyone realising which would lead to inaccurate answers. Furthermore, when there is a large amount of data in an excel sheet, the programme can be extremely slow and prone to crashing. This in turn could slow down the process and can lead to time being wasted if Excel crashes and the file was not saved.

Another limitation within the Excel system that can impact on actuarial work is the fact that large numbers are rounded off; the number precision in excel is to 15 digits. For basic calculations this is more than sufficient, however for complex calculations using large numbers this can compromise the calculation accuracy which may subsequently lead to a company underfunding their reserves.

When an actuary needs to make quick decisions all the relevant information needs to be at hand to ensure decision making is well informed and accurate. If information is stored throughout various Excel sheets, this will not be possible as it may prove too time consuming to summarise and collate the required information accurately.

In addition to Excel running slower when there are high levels of data, it can also become slower when there are too many Excel windows open at once. These limitations further evidence that Excel can delay an actuary when it comes to making a time sensitive decision.

Additionally, information may be dispersed through many different spreadsheets. This can make it difficult for reports to be generated, and often results in the data having to be collated manually which can be unproductive of an actuary’s time and therefore impractical.

The work completed by an actuary generally needs to be tested to ensure it is working correctly and that there are no errors. However, Excel spreadsheets can prove rather difficult to test. In order to include all the relevant information in calculations, a spreadsheet may be linked to several other spreadsheets. Therefore in order to follow the logical sequence of these formulas, you may be required to have several spreadsheets open at once which can make it very difficult to follow and process.

Given the fact that Excel workbooks can be connected to many other work books, signifies that the chain of data integrity can be easily broken. For example, one cell in one workbook can be used in a calculation for another workbook, and if a new column or row is inserted the cell used in the calculation is moved. This can render the calculation useless  by producing a nonsensical answer, or it could produce an answer which seems sensible but is completely inaccurate. This impact of this could result in severe losses for a company or incorrect information being sent to customers.

In general, models built on Excel are not well documented, meaning that when people leave or join the company, the gap in knowledge can cause difficulties. The gap in knowledge can exacerbate problems because the new employee may not realise that the chain of data integrity is broken if there is no documentation stating what information is used in which calculations.

Another major consideration when using Excel for actuarial work is that it is susceptible to fraud and corruption. Although workbooks can be password protected, many employees within the company will know the password to the spreadsheet and can easily change formulas or values. If they were only to alter them slightly, it is possible that no one would question the values being produced by the spreadsheet. Even if any changes were identified it is almost impossible to track down the individual who made the changes in the first place. This could result in huge losses or scandal for a company.

Advantages of Excel

excel for actuaries report statistics

While we have discussed the many disadvantages of Excel; it is important to also be cognisant of the advantages of Excel for actuaries.

One of the main advantages of Excel is that actuaries are very skilled and well trained in excel. This means that if a model made in Excel is being handed over to another colleague and it is documented correctly, it will be easy for the next person to seamlessly follow the model as everyone within the industry is very well versed with it.

For actuaries Excel is an excellent place to build simple models as it has a user-friendly interface that does not require any knowledge of different programming languages. It is quick when there are small amounts of data and requires a skillset which actuaries already possess. Excel greatly increases the speed at which simple calculations can be done and the models built in it can greatly lessen the workload of an actuary.

As well as having so much functionality, there are many add-ins available for excel that can provide a variety of functions from statistical analysis to monte carlo simulation. Excel also includes the programming language VBA. This can aid the work of actuaries, in particular by automating repetitive tasks or to programme additional functions which are not already included in Excel.

Furthermore, it is very easy to produce visualisations of data in Excel, which can be used to spot trends or anomalies in data.

Excel spreadsheets are flexible and can be easily altered. If there are new regulations which require changes to rates used in calculations, it is easy to change these without having to change any other formulas.

Overuse of Excel

excel for actuaries analyzing finance

While there are multiple drawbacks when it comes to Microsoft Excel, it is evident that for actuaries Excel still has advantages and that there continues to be a place within the industry for it. However, the overdependence on Excel for actuaries needs to be addressed. Excel does not need to be used as a one stop shop and it would be highly beneficial for actuaries if they were to incorporate other tools alongside it.

Databases such as SQL server and Microsoft Access are a great alternative to store data. They are designed to hold large amounts of data and enable quick and easy extract of the required data.

Programming languages such as R and Python are great tools which can be used to overcome the limitations associated with excel and should be considered as an alternative to excel when working with large amounts of data or when building complex calculators or models.

Benefits of Programming

actuary coding a program

Programming languages have powerful data manipulation tools. It is much easier to edit data sets using languages such as R than it is within excel. In Excel this is a time consuming and slow process but for people who are familiar with programming languages, this is a quick and easy task. These programmes can also handle large data sets much better than excel. In comparison where excel slows down with high levels of data, programming languages can work with large data sets quickly and do not crash so there is less chance of work being lost.

Automation is easier and faster with programming languages. In excel, if there is a task that needs to be completed daily this would involve opening a different file every day and running it manually. However, with programming languages this can be done automatically by writing a script that imports the data and runs the task each day.

Errors are easier to find and fix in programming languages. In Excel you will get an error notification, but you will not get detailed information about it or be pointed to where the problem begins. In programming languages you get a message that explains what the computer pinpoints as the problem.

Scripts are usually well documented as you can add comments on each line of code to explain what it is. This is useful for fixing mistakes but can also be useful for other colleagues who are trying to follow or check your work.

Programming languages like R and Python have more advanced statistical analysis than excel, as they also allow for the creation of machine learning models. They have more advanced visualisation capabilities than excel, which can make understanding the data easier.

Once you have mastered one programming language, it is easier to follow the logic of other languages and use these skills to either learn new languages or to be able to check colleagues’ scripts in different languages even if you cannot write the language. Additionally, the help functions within a lot of these programmes are impressive and there are many free online resources which you can use to learn or develop your skills.

Actuary v IT

actuary vs IT professional

Following a one to one discussion with Bryon Robidoux in April 2022 (Author of the article “Excel is not the culprit”); he highlighted a communication barrier between the actuarial department and the IT department. He suggested that it’s almost impossible to be completely up to date with the newest technology in IT and the new legislations/regulations in the actuarial industry.

It would appear that within the actuarial industry there is a resistance to change. The traditional skillset of an actuary does not involve programming languages, they are very skilled in excel and in general would prefer their work to remain on Excel rather than progressing to use a new programming language. This combined with the communication barrier between actuarial and IT, is going to lead to slow advances in technology within the actuarial industry, resulting in actuaries lagging behind in IT while they’re trying to keep up with the new regulations.

If actuarial and IT were to combine their skills and work together, the actuarial industry may move forward in terms of having up to date processes which are highly automated.

Excel is a good place for actuarial work to begin, as a template for a model that could be progressed much further using a programming language.

Conclusion

To conclude, I believe that there is a firm place within the actuarial industry for Excel and that there will always be. 

However, actuaries need to work more closely and build a strong relationship with IT so that they can improve the processes which they use on a daily basis.

There should be a push within the industry for actuaries to become more familiar with programming languages to enable them to complete their job in the most time efficient manner possible.

Excel is still key for simple models and calculations, but it is time that actuaries progress to using  more innovative databases and are not solely reliant  on excel as their main means for completing their work.

“This is a guest article written by Orla Crawley. Orla can be found on LinkedIn here.”

Related Posts

Why Actuaries Need Better Soft Actuarial Skills

Why Actuaries Need Better Soft Actuarial Skills

The Decline in Actuarial Exam Pass Rates and Potential Impacts on Student Travel Time

The Decline in Actuarial Exam Pass Rates and Potential Impacts on Student Travel Time

The Enterprise Risk Management (ERM) Actuary

The Enterprise Risk Management (ERM) Actuary

Actuarial Exams: The Ultimate (Non-conventional) Guide to Passing

Actuarial Exams: The Ultimate (Non-conventional) Guide to Passing

Guest Post


Mark Farrell

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}