## 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:

- Open an excel workbook and press Alt + F11 to open VB editor window.
- 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.
- Double click on the module. This is the space where we write code to make an excel add-in.

- 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:

- 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.

- 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.

- We can now initialise the
as 0 and also calculate the present value factor**epv**@ input interest rate**v**. We have used a**i**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.*lim*

- 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
. The column names are*mortality*and*Age*.*qx*

- To extract the required probability from the excel worksheet, we use the vlookup function and use this to calculate
and then*kpx*. Here,*kbarqx*variable is used to calculate*kpx*and_{k}p_{x}variable is used to calculate*qx***q**components of_{x+k}in the EPV formula. We have used 2 loops and dummy variables_{k|}q_{x}and*a1*to perform this calculation.*a2*is used as age variable to find the required*a1*in the inner loop.*kpx*is used as an age variable to calculate*a2*in the outer loop.*qx*is used to calculate*pvf**v*^{k+1}. The values ofand*pvf*are used to find*kbarqx*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*epv*. The final code is given below. You can copy and modify it as per your requirements.*d*

```
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
menu and then*File*.*Save As* - In the
dialog box, change the*Save As*to*File Format*and save with the name you want to keep of your add-in. Here, I have named it as*.xlam*.*Epvcal* - Now, go to the excel file where you want to use this function.
- To activate the add-in
in Excel, you need to use the add-in option. This could be located under*Epvcal*OR*Developer menu*OR*File 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.*Tools menu*

## 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

**in any cell and inputs values to calculate the EPV of Whole Life Assurance.**

*=Ax*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!

## Comments are welcome on LinkedIn

https://www.linkedin.com/feed/update/urn:li:activity:6737647779335696384