‘Practising Ruin’ Working Party – Excel Model Documentation

20 September 2014

Background

The purpose of this document is to give an overview of the functionality of the Excel Model that was prepared by the ‘Practicing Ruin’ Working Party and initially presented at the 2014 GIRO convention on Friday 26 September 2014. This document will straddle elements of a detailed user guide as well as technical documentation and discuss in detail the main assumptions, methodologies and limitations of the implementation of the underlying theoretical model. This document is intended to be read alongside the associated suite of documents produced by the Working Party.

This document will not cover the details of all the formulae used, but rather give the user a coherent understanding of the mechanics and main features of the Excel Model to assist with use, investigations and error-handling.

Although the Excel model has undergone testing (in terms of its mechanics as well as the results produced), users are strongly advised to perform their own testing before using model.

The Excel Model can be perceived as a calculation kernel and will assume a number of inputs. The Working Party and the Excel Model have not focussed on the parameterisation of these inputs, but rather the manipulation and use of these inputs.

The Working Party welcomes any questions, concerns and feedback relating to any of our work. In the first instance these can be sent toKapil Radia and Joseph Lo, then any of the other members of the Working Party.

Kapil

Joseph

Ronnie

Andres

Corina Constantinescu

Yuriy

For completeness, the corresponding Excel Model has been embedded below.

Technical Requirements and Overview

The embedded file above is version 1.0 of the Excel model which was developed in Microsoft Excel 2010. This model was briefly tested in 2003 and found to operate successfully, although thorough testing is still required in this prior version (and other relevant prior versions). However, the functions and tools utilised are believed to be version invariant. Indeed, the Excel Model has only been developed and tested in in the Windows operating system.

Having numerous files open at the same time as this Excel Model will not necessarily lead to it failing or producing incorrect results, but may significantly reduce performance. This will be discussed in more detail later on in this document.

In addition to standard Excel formulae, some calculations are performed using macros, developed within Visual Basic for Applications (“VBA”).

When the Excel Model is opened, the user may be presented with a ‘Security Warning’ (as illustrated in Figure 1 below), asking whether the content should be enabled. In order for the model to work correctly, macros must be enabled. The specific purposes of the VBA macros will be discussed later in this document.

Figure 1: Security Warning

The user will then be presented with a message box outlining the following message: “Please read the documentation and perform your own testing before using this model.” This is the same as the text that appears on the main worksheet at the top-left corner of the screen. These are clear warnings to remind user to read this document and perform their own testing (technical, regression, user acceptance etc.) before using this model.

The Excel Model makes extensive use of named ranges which are either explicitly defined or created implicitly in the Name Manager as illustrated in Figure 2 below. Those that are implicitly defined make use of Excel’s OFFSET function to define dynamic arrays that vary in size with other inputs, such as the number of exponential mixtures.

Figure 2: Name Manager

All named ranges are consistently named. Ranges that relate to particular inputs or variables have underscores at the beginning and end such as _Sigma_, _Q_ etc. Ranges that are used to define starting points for implicitly defined named ranges (using the OFFSET function) do not have underscores at the beginning and end of their namesbut contain ‘Start’ at the beginning such as Start_Lows, Start_Mus etc.

The purpose of using implicitly defined named ranges is to enable the model to avoid errors with empty or redundant inputs that are often encountered with inputting arrays or vectors of data.

The Excel Model has not been protected in any way for the benefit of the users. This extends to the VBA macros that automate some of the more detailed and repetitive calculations. This should help users gain a better understanding of the implementation and assist with understanding the calculations, any potential errors or strange results.

However this does present the obvious pitfall that the user may make accidental changes that could lead the Excel Model to fail or produce incorrect results. For example, the names of the worksheets must not be changed, formulae must not be changed etc. Error-handling and checking will be described later on in this document.

Throughout the model, consistent formatting has been used for simplicity and completeness which are highlighted below.

xxx / Heading
xxx / User input
xxx / Subjective input which should not need changing
xxx / Calculation
xxx / Error check
Ignore / empty range
xxx / Hyperlink to another range
xxx / Total / sub-total for calculations or illustration

In particular, the error check will appear as either “OK” or “ERROR…” with a suitable description where appropriate.

Where possible and practicable, numerous section names and formulae have been included (such as those illustrated in Figure 3 below) in order to illustrate the calculations being performed where simple titles were not possible. The headers also explain the contents of and calculations within the surrounding cells.

Figure 3: Section Names and Formulae

The Excel Model consists of two worksheets named Analytical and Automation. The document will focus on the Analytical worksheet as this is where all the calculations and operations are performed for a single set of inputs (that is, a single basis). The Automation worksheet contains the inputs (and subsequent results) relating to a number of sets of inputs / bases which simply feeds the inputs of the Analytical worksheet one basis at a time and then outputs the results.

Analytical Worksheet – Inputs and Results

In the top-left hand corner, under the Audit / Legend section, there is a description of the version of the model a succinct legend which coincides with the descriptions above.

Directly beneath these are a set of instructions to help the user complete the required inputs. The corresponding named ranges are given as well as individual hyperlinks that direct the user to the underlying named ranges.There are also a number of basic checks to ensure that the inputs are reasonable. If the conditions are met, then “OK” is displayed, otherwise an error message is displayed which briefly describes the nature of the error. If all checks are met then the ‘Overall Check’ box displays “OK”. All of these items are illustrated in Figure 4 below.

Figure 4: Inputs

_X0_Initial Capital – the amount of capital at time 0. The check ensures that this value is positive.

_C_Constant Premium – the amount of constant premium income. The check ensures that this value is positive. It is advised to use sensible values here that are commensurate with the other inputs.

_Sigma_Brownian Motion Sigma – the sigma parameter for the Brownian motion. The check ensures that the value is not negative.

_Lambda_Frequency – the annual frequency of claims. The check ensures that the value is positive.

_Q_Force of Interest – the instantaneous force of interest. The check ensures that the value is positive.

_A_Lower Barrier – the lower barrier for the dividend strategy. The check ensures that the value is greater than or equal to zero and less than or equal to the initial capital.

_B_Upper Barrier – the upper barrier for the dividend strategy. The check ensures that the value is greater than or equal to the initial capital.

These inputs are fixed in size (they are all single item variables), however the following two are vectors that can hold up to ten entries. Indeed, the length of both must be the same. These vectors are arranged horizontally, so the entries should run from left to right.

_Input_Ps_Pi– the weightings for the mixed exponential distributions. The checks ensure that all weights are positive and less than or equal to 1 and that they all sum up to 1.

_Input_Means_Meani – the mean values for each mixed exponential distribution corresponding to the weights above. These are not the exponential parameters. These must be distinct and not repeated, otherwise the repeating entries could be summarised into one entry with a larger corresponding weight above. The checks ensure that the values are positive and that the number of inputs are consistent.

All of these inputs are located at the top of the worksheet and are partly illustrated in Figure 3 above. The number of mixed exponential distributions is therefore limited toten. This number was subjectively determinedas it was deemed to be sufficient for most extreme cases.Indeed, there could be significant parameter error for distributions characterised by ten mixed exponential distributions. However, parameterisation of these inputs is outside of the scope of this Working Party. In the rare case that more than ten parameters are required, extending the model should not be too difficult for experienced Excel and VBA users.

The exponential parameters (weights and corresponding means) can be input in any order but will be consistently reordered by the underlying exponential parameter (that is, the reciprocal of the mean) from smallest to largest. This is to facilitate subsequent calculations.

The input list / instructions also refers to three other inputs which relate to specific parts of the calculation which are described below for completeness but will be explained in more detail later on in this document. These are not expected to be changed frequently, but may need to be adjusted based on the input parameters and in order to achieve sensible results.

_Small_Small value – a very small (positive) value is needed as the lower bound for the first root / Theta of the characteristic (Psi) equation.

_Theta_Tolerance_The absolute tolerance level for determining the values of the roots / Thetas of the characteristic (Psi) equation.

_Bisection_Iterations_The maximum number of iterations used in the bisection algorithm in order to find all roots / Thetas of the characteristic (Psi) equation.

Based on these inputs, the Excel Model calculates who additional variables which make subsequent calculations easier:

_n_Inputted Mixtures – The number of inputted mixed exponential distributions. This is calculated by determining the length of the inputted vector of exponential mixtures.

_NN_Roots – the number of roots (Thetas) of the characteristic equation (Psi). If there are n exponential mixtures and the Brownian Motion Sigma parameter is zero, the number of roots will be ( n+1 ). However, if the Brownian Motion Sigma parameter is positive, the number of roots will be ( n+2 ).

Moving to the results, there are two key results to be calculated.

Results_Only_Upper_BThe expected present value of dividends given only an upper barrier _B_ for the divided strategy. The check ensures that this value is positive.

Results_Both_A_And_BThe expected present value of dividends given only both an upper _B_ and lower _A_barrier for the divided strategy. The check ensures that this value is positive.

Analytical Worksheet – Calculations

The Excel Model contains a large number of lengthy calculations. However they have been laid out in a logical manner that either directly relate to the underlying formulae or are reasonably easily identifiable.

The calculations generally relate to two-dimensional summations that span the number of exponential mixtures ( _n_ ) and the number of roots / Thetas (_NN_). Therefore each table is generally arranged so that the exponential mixture parameters span the columns (and are assigned the aesthetic variable i)andthe roots / Thetas span the rows (and are assigned the aesthetic variable j).

This is why the maximum number of columns is 10 (corresponding to the maximum number of exponential mixtures) and the maximum number of rows is 12 (corresponding to the case where the Brownian Motion Sigma parameter is non-zero, thus yielding 12 roots to the characteristic equation). This is briefly illustrated in Figure 5 below.

Figure 5: Calculation Array

In order to derive the two main results, the Excel Model needs to derive the roots (which have been denoted using the Greek letter Theta) of an equation which has been labelled the ‘characteristic equation’ (which has been denoted using the Greek letter Psi).

As explained before, the number of roots depends on the input parameters (namely, the number of exponential mixtures and whether or not the Brownian Motion Sigma is non-zero). The roots are calculated by clicking the ‘Calculate Thetas’ button which executes VBA code to automate the process.

In order to help find the roots, the Excel Model calculates analytical upper and lower bounds for each required root and then then uses the bisection method to iteratively determine each root / Theta between the defined lower and upper bounds. As the bisection method is generally widely understood, it has not been described in this document.

The first root is always positive and therefore requires a very small (positive) value for the lower bound – this is given by the _Small_Value_ range. This is defined at the top of the worksheet alongside the other main inputs. The current value in this range is 0.00000000001 (or 1e-11) which is likely to be sufficiently small for most cases. However in the extreme case that it is not, this can be manually changed to a lower number.

The bisection algorithm is repeated until the number in the _Bisection_Iterations_ range is hit, or the root / Theta is absolutely lower within the value in the_Theta_Tolerance_ range.

Figure 6 below illustrates the section of the worksheet that relates to the root-finding algorithm. Indeed, when the routine is run, the values in the ‘Check’ column (which evaluates the value of the characteristic / Psi function at the root / Theta value) should all be lower than the specified tolerance value. This is what the ‘Tolerance Check’ performs and this also feeds into the ‘Overall Check’ outlined above.

Figure 6: Roots / Thetas

In the peculiar case where the routine does not find any / all suitable roots, the user can adjust the three values. It is not recommended to change the Theta, Lower Bound or Upper Bound values below, but they have been highlighted in green (in a similar fashion to the three variables above) as the advanced user could manually adjust these to help the bisection routine. However if these are changed, they must be reverted to the original formulae. In either case, the Theta column below does not contain any formulae as this contains the values to be derived using the bisection algorithm.

The Excel Model also automatically plots 100 points of the characteristic / Psi equation between the upper and lower bound. The user simply has to select the value of the root around which they want to view the function, as illustrated in Figure 7 below.

Figure 7: Characteristic / Psi Equation Graph

Automation Worksheet – Overview

As explained before, the Analytical worksheet contains the inputs for one set of parameters (one basis) and runs the model to produce the two results for this basis. However, if the user desires to repeat this process a number of times (perhaps for stress, sensitivity and scenario testing), the user will have to input the parameters and re-runthe model for each basis in turn, which is a cumbersome task.

The Automation worksheet facilitates such a task by housing all of the input parameters relating to a number of bases and then feeding them into the Analytical worksheet in turn, and then collecting the results.

The user simply has to enter all of the parameters in a coherent array and make sure that the formula in the _Automation_Bases_ named range (adjacent to the ‘Bases’ cell) references the appropriate number of bases to be modelled. In particular, the parameter array spans from left to right so the array cannot be changed in this capacity. However the array can be expanded and contracted vertically (as required) as each row refers to a separate basis. This is illustrated in Figure 8 below.

Figure 8: Automation

It is important to clarify that this tool only specifies the main input parameters and not the subjective inputs. Although an advanced Excel and VBA user could implement this quite easily, if desired.

After the inputs have been defined, the user simply needs to click the ‘Do Runs’ button which then executes a VBA program to feed the Analytical worksheet with each set of parameters in turn and then export both sets of results in the adjacent cells (currently columns AD and AE). If for any reason the run is not successful (perhaps due to an absurd input value), the results cells for that basis will contain a generic error message.