What Do Entry-Level Actuaries Need to Know About Excel?

Actuarial exams aren’t the only thing that employers look at when they’re considering hiring a new entry-level actuary. Excel skills are important too!

If you want to be successful on the job, you will need to know how to use Excel functions such as if-then statements, vlookups, summations and averages. Being able to use pivot tables, graphing features, named ranges, sorting capabilities, and VBA will all come in useful as well.

But before we jump into the details about what you need to know, it will be helpful for you to understand what actuaries use Excel for and why it is so important.

What do actuaries use Excel for?

Excel is an extremely powerful tool for actuaries. It is a powerful tool used for hundreds of different types of tasks:

Calculators

One very common use of Excel is to create complex calculators. As you already may know, actuaries perform tons of calculations but it is impractical to repeat the same calculations (with different numbers) over and over again.

So, in order to avoid doing that, an actuary may set up one or more functions in Excel that allow the same calculation to be done using various different inputs.

For example, let’s look at this very simple calculator that takes “Number of Family Members” as an input (can be changed by the actuary) and then it outputs the “Total Annual Premium” that should be charged to the family for their insurance coverage.

The “Total Annual Premium” is just a calculation that takes the number of family members (input) multiplies it by 80 and then adds 73 to the total.

Once the actuary has set up this calculator, it could be easily passed onto someone else (like an underwriter) so that the actuary no longer had to be involved in the premium calculation process.

In the real actuarial world, the calculators would be much more complex, and require many more inputs. They may be used to calculate reserves, premiums, expenses, or many other things.

Reporting

Actuaries deal with tons of numerical data all day long, so it’s very common to need that data summarized into reports. Reports make the data easy for actuaries, other employees, and management to interpret.

For a valuation actuary, one of the most critical reports is called the Earnings By Source (EBS). This type of report summarizes all the company’s reserves and highlights areas where the company earned or lost money. If you’re interested in learning more about reserves, you’ll definitely want to check out this post that goes into tons of detail about them and valuation actuaries.

For example, a pricing actuary may need to create a sensitivity testing report when a new insurance product is being created. This report would summarize the change in premium caused by changing different assumptions used in the premium calculation (interest rate, for example).

Analyzing data

Often times large amounts of data are provided to actuaries in Excel. This data could come from another company, other departments within the company, or some other source.

For example, this could be policyholder information such as name, date of birth, gender, salary, etc. Or it could be information on financial assets that the company owns such as bonds, stocks and mortgages.

When this data comes to an actuary, it often needs to be analysed in some way so that it can be better understood. Actuaries rarely look at individual pieces of data. Instead they want to look at the big picture. They want to look for trends in the data and understand it as a whole.

What should an entry-level candidate know about Excel?

As an entry-level candidate having experience using Excel, it is absolutely necessary in order to be a high-quality candidate.

Excel has many different functionalities: many of them you will use on a daily basis, and while a few, you may never use.

Common Excel Functions for Actuaries

Here is a list of some of the most common Excel functions that I used in my first actuarial job. Sometime “nested functions” (which is the term used for using one function within another) are necessary to achieve the desired calculation.

=SUM()
=SUMIF()
=AVERAGE()
=AVERAGEIF()
=VLOOKUP()
=HLOOKUP()
=INDEX()
=MATCH()
=INDIRECT()
=LEFT()
=RIGHT()
=MID()
=IF()
=COUNT()
=COUNTIF()
=SUMPRODUCT()
=OFFSET()
=ROUND()
=DATE()
=DAY()
=MONTH()
=YEAR()

You should try learning what each of these functions does and practice using them. But you don’t need to memorize them all (although you probably will eventually after using the numerous times).

Instead of memorizing, just practicing using these functions. It will allow you to learn the capabilities that Excel has so that when you run into certain situations on the job you’ll know what Excel functions can do. Then, if you don’t recall the exact function, you can use Google to recall which function to use in that situation.

Other Excel Capabilities

There are also many other capabilities that you should know how to do as an entry-level actuary. Understanding how to do these things will ensure that you can navigate Excel and perform the tasks that you need to easily.

Here are some of the most common ones:

Deleting and inserting cells, rows and columns.
Copying, pasting, pasting transpose, and pasting values (keyboard shortcuts come in very useful).
Setting up and using pivot tables.
Absolute cell references in formulas (when and how to use them).
Merging cells.
Cell formatting (borders, cell colors, text colors, etc.).
Conditional formatting.
Cell validation.
Sorting cells and removing cell duplicates.
Setting up and using named ranges.
Freezing rows or columns.
Find and replace.
Print page formatting.
Filtering columns.
Commenting.
Data graphing.
Change column width and row height.

What is VBA in Excel and what do actuaries use it for?

Visual Basic for Applications (VBA) is the coding language (or programming language) that can be used to automate tasks in Excel (as well as other Microsoft Office programs). As an actuary, there are times when repetitive tasks in Excel can be done much more quickly and accurately by automating the task rather than doing it manually.

For example, let’s say we had a list of 1000 families that we wanted to calculate the premium for using the premium calculator in the example mentioned above. Inputting the number of family members into the calculator manually for 1000 families and recording the results would take several hours!

Instead, we could use VBA to set up a simple automation that does this for us, and it would take less than a minute to complete.

Real World VBA Use by Actuaries

As I mentioned above, it is common for actuaries to receive large sets of data in Excel. It could be data relating to 1000’s of policyholders, or it could be data related to the financial instruments (bonds, stocks, etc.) that the company owns.

Before using the data, it is critical to review and validate it to make sure that it seems accurate and reasonable.

For example, a policyholder’s birthday should always be before the date that they became disabled. Or, as another example, their age should not exceed age 65 if the policy matures (ie. ends) at age 65.

Surprisingly, inconsistencies and errors in data is fairly common since it is often input into company systems manually.

If this type of validation has to be done on a regular basis (daily, weekly, monthly, etc.) then it would be much simpler to set up a VBA macro to do the validation each period, rather than doing it manually each time. It will save time, and will probably be better at catching errors than a human.

If you’re interested in learning more about VBA and how much programming actuaries do on the job, you should take some time to read this post. It goes into much more detail about what actuaries use programming for and other coding languages that you may be interested in learning.

Where can entry-level actuarial candidates learn Excel and VBA?

There are a few places you can go to learn Excel and VBA.

Etched Actuarial offers an all-in-one Excel & VBA course for future actuaries where you can learn Excel from beginner to advanced, and then learn VBA at a beginner level (which is all you need for most actuarial jobs).

The benefit of taking that course is that you get taught Excel in an actuarial context, and you get several practice projects that you can add to your resume.

If you’re not ready to make a financial investment into your Excel skills yet, then you can learn a lot on YouTube for free. It will likely not be taught in an actuarial context though, which would be the ideal situation for someone wanting to get into the field.

Remember, courses are great! But if you ever have the opportunity to use these skills on the job, that would be excellent experience to add to your resume.

Always be on the lookout for ways that you could use Excel and VBA at work too. Even if you have to stay late (unpaid, on your own time) to complete additional tasks in Excel or VBA that could be later added to your resume, it would be worth your time!

At the very least, make it a habit to use Excel and/or VBA at home too. You could use it to create and track a monthly budget, schedule your time, or even just to do simple calculations. The more you use Excel, the more familiar and comfortable you’ll get with it.

Excel proficiency tests during actuarial interviews

It is fairly common for actuarial employers to test your Excel proficiency (not usually VBA) before they hire you. The primary goal of this is:

To verify that you’re able to use the program at the level they need you to perform at.
To verify that you perform at the level you claimed on your resume and in the interview.

Not every employer will test you, but if they are going to they will usually let you know beforehand so that you can prepare if you need to.

Excel proficiency test setup

It is impossible to say for sure how the test will be set up for your interview. Each company has a slightly different way of testing the candidates.

Most commonly, they’ll give you a dataset and ask you to

Answer some questions about the data; and/or
Give you a problem solving exercise; and/or
Perform simple tasks (like find and replace).

Some companies will require that you know all the necessary formulas, while others will allow you to use Google and/or the Excel documentation to look up the information you need.

Sometimes you’re allowed to ask the interviewer clarifying questions and other times you can’t do this. If you are allowed to ask questions, they may be trying to get a sense of the quality of your questions and how well you can problem solve by yourself.

How to prepare for Excel proficiency tests

So, when you’re preparing for your Excel test, the best thing you can do is to learn when each of the functions and capabilities mentioned above should be used.

Once you learn that, it is important that you’re actually able to use the functions and capabilities too. You should practice these skills as much as possible so that you’re completely comfortable with them all and don’t have any hesitations.

(Note that above I’ve included some of the most common functions and capabilities but there may be others that the company wants to test.)

During the test, remember that you don’t have to be perfect in order to be considered for the job. Sometimes, however, just by showing your problem solving and logical thinking is enough to keep perspective employers interested in you, even if you are not completely proficient with Excel.

Other ways employers may test your Excel knowledge

Not every employer will require you to do an Excel proficiency test as I talked about above. Some may ask you a few questions during your interview (phone or in-person) in order to get a quick sense of your knowledge.

For example, the interviewer may ask you to:

Assess your skill level (no knowledge, beginner, proficient, advanced).
Name Excel functions that you’ve used in the past (and possibly why).
Explain what a certain function does and when you’d use it (vlookup, for example).