How to create an Excel Add-In to build Actuarial Functions

Introduction

Hello Readers!
This article is about creating the excel add-in to build various actuarial functions like calculating the expected present value of benefit payable under Assurance and Annuity contracts. These are the very basic calculations that every insurance company do to calculate the expected claim amount that need to be paid to the policyholders.

This article is based on one of the CAST (Certificate in Actuarial Software Techniques) project. The project can be viewed from the link below:

Please download the mortality data file from the project page given above or by clicking here.

Being an actuarial aspirant, I found the excel add-in very useful and a quick way to perform such calculations given the required inputs. Here I am using AM92 mortality table as a mortality table input. Let’s proceed and see how to create this add-in.

Creating an Excel Add-in

In this tutorial, we learn to create an excel add-in to calculate EPV of benefits under whole life assurance contract. We can do this in three parts which are:
1) Write the code to calculate EPV of benefits in a module.
2) Save as an excel add-in.
3) Calculate the EPV.

Part 1: Write the code to calculate EPV in a module

Here are some steps to start writing the code for excel add-in:

1. Open an excel workbook and press Alt + F11 to open VB editor window.
2. On the left side of window, right click on Microsoft excel Objects. Go to insert option and click on ‘Module’. You will see a module inserted under the same tab.
3. Double click on the module. This is the space where we write code to make an excel add-in.

4. Now, if we want to make a function to calculate the EPV, it is necessary to know the formula before we start to write the code. So the formula is:

5. We now define the name of the function which is “Ax” here and mention the required input with their respective data types as shown in the figure below.

6. Now, we need to declare various variables that are needed to perform the required calculations using the dim (dimension) keyword. I have declared several variables here to use as dummy variables in the loop to make the process elaborate. The same function can be built with less variables, by combining calculations.

7. We can now initialise the epv as 0 and also calculate the present value factor v @ input interest rate i. We have used a lim variable to run the loop for required number of iterations based on the mortality. Here, we have used the AM92 mortality which has the maximum age of 120. You can set the lim variable as per the maximum age of the mortality used in your program.

8. We have to save the mortality data in one of the sheet in the workbook, where the add-in is used. Saving the mortality data in the worksheet where the add-in is used allows you to use custom mortality as per your requirement. I have the mortality data saved in a worksheet called as mortality. The column names are Age and qx.

9. To extract the required probability from the excel worksheet, we use the vlookup function and use this to calculate kpx and then kbarqx. Here, kpx variable is used to calculate _kp_x and qx variable is used to calculate q_x+k components of _k|q_x in the EPV formula. We have used 2 loops and dummy variables a1 and a2 to perform this calculation. a1 is used as age variable to find the required kpx in the inner loop. a2 is used as an age variable to calculate qx in the outer loop. pvf is used to calculate v^k+1 . The values of pvf and kbarqx are used to find epv for each age (iteration) and then added back to epv to calculate the summation. Finally, we use the Round function outside the loop to display the epv after rounding to the required number of decimal places d. The final code is given below. You can copy and modify it as per your requirements.

Function Ax(x As Integer, i As Double, d As Double)
Dim a1 As Integer, a2 As Integer, lim As Integer
Dim k As Integer, n As Integer, pvf As Double
Dim kpx As Double, kbarqx As Double, epv As Double
Dim qx As Double, v As Double
lim = 120 - x
v = 1 / (1 + i)
epv = 0
For k = 0 To lim
    a2 = x + k
    pvf = (Exp((k + 1) * (Log(v))))
    kpx = 1
    For n = 0 To k - 1
        a1 = x + n
        qx = Application.WorksheetFunction.VLookup(a1, Sheets("mortality").Range("A2:B105"), 2, False)
        kpx = kpx * (1 - qx)
    Next n
    qx = Application.WorksheetFunction.VLookup(a2, Sheets("mortality").Range("A2:B105"), 2, False)
    kbarqx = kpx * qx
    epv = epv + (pvf * kbarqx)
Next k
Ax = Round(epv, d)
End Function

Hurray! We have finished our first step of writing the code.

Part 2: Save as an Excel add-in

• Please go back to the worksheet, click on File menu and then Save As.
• In the Save As dialog box, change the File Format to .xlam and save with the name you want to keep of your add-in. Here, I have named it as Epvcal.
• Now, go to the excel file where you want to use this function.
• To activate the add-in Epvcal in Excel, you need to use the add-in option. This could be located under Developer menu OR File menu OR Tools menu based on the Excel version that you are using. Once the add-in is added to your Excel environment, please ensure that it is ticked.

Part 3: Calculate the EPV

We can now calculate the EPV of the Whole Life Assurance by using Ax function available in the add-in. Use =Ax in any cell and inputs values to calculate the EPV of Whole Life Assurance.

You can verify the calculated values from the Formulae and Table Book of IFoA.

Conclusion

In this article, we have covered the process to build an excel add-in for the whole life assurance contract. In the same way, we can build the actuarial functions for other type of assurance and annuity contracts. Also, using the assurance functions, we can calculate annuity contracts using the relationship between assurance and annuity contracts.
Thanks!