Best-Estimate User Guide

Contents

1Introduction

1.1System Requirements

1.2Disclaimer

2Run-Off Triangle

3Usage

3.1Basics

3.2Further Options

3.3Notes on Segmentation

4Output of the Results

4.1Prediction

4.2Residual Plots

4.3Prediction Error (Long-Term View)

4.4Prediction Error of the Claims Development Result (Short-Term View)

4.5Premium Risk

4.6Tail Estimation

4.7Prediction Error Including Tail

4.8Run-Off Time

4.9Discounting

4.10p%-Quantile

- 1 -

Best-Estimate User Guide

1Introduction

Solvency II envisages a principle-based economic valuation of technical provisions, where the best estimate, defined as the expected present value of future cash flows, constitutes the main building block: It is about the estimation of the present value of future cash flows where this relates to claims provisions in the field of non-life insurance. The Excel macro presented is intended to assist participants in the valuation of the best estimate. This document describes the usage of this macro.

This Excel macro has been designed as a simple tool with a view to assist those insurers participating in the QIS which, up to now, have not used actuarial techniques or software as their primary means for setting provisions in non-life insurance. Still, it is essential that the user is capable of assessing the adequacy and quality of the given input data. Furthermore, the results need to be checked for accuracy. For this purpose the paper “Methoden zur Schätzung von Schaden- und Prämienrückstellungen“ (GDV circular 1215/2008 of03.07.2008) provides assistance.

To make the access to this tool as easy as possible the Excel workbook contains a spreadsheet called Übersicht. On the one hand there is a survey (see section 2 and section 3.1) of the basic usage of this tool. There is an overview of all spreadsheets contained in this workbook as well as their purpose. On the other hand the user can change the language here. From the list in cell B3 the user can choose his preferred language. Right now German and English are available. After choosing from the list the translation is activated by clicking on the orange speech balloon.

Figure 1: Choosing a Language

1.1System Requirements

The valuation tool has been developed using Excel 2003. In case other versions of Excel (higher or lower, or with another language installed) are used, some menu items mentioned in this documentation or shown in screenshots may have changed.

1.2Disclaimer

This is a cost-free product provided by CEIOPS which is based on an Excel tool developed by the German insurance industry association (GDV) for the use in Solvency II quantitative impact studies. It has been validated by the competent staff members and bodies to the best of their knowledge and belief. Nevertheless, the tool may include technical or other mistakes, inaccuracies or typographical errors. Any feedback to this effect would be welcomed by CEIOPS. CEIOPS assumes no responsibility for errors or omissions in the tool, which is provided 'as is' without warranty of any kind, either express or implied.

- 1 -

Best-Estimate User Guide

2Run-Off Triangle

Figure2: Example of a run-off triangle with cumulative data

It is necessary to provide a run-off triangle with information on the paid claims – either cumulative or incremental - in individual accident and development years. A run-off triangle (see Figure 2) is a two-dimensional data structure consisting of columns and rows determined to store information on the process of claims development. At this the rows represent the accident years and the columns represent the development years.Accident years declare the year in which a claim is incurred, whereas the development years declare the elapsed time since the occurrence of the claim.A run-off triangle isfilled with data by determining the appropriate accident year and development year for every claim payment. This claim payment is then added to the value in the corresponding cell of the run-off triangle at the intersection of this particular accident year and development year.In this manner a run-off triangle with incremental data is derived. In order to get a run-off triangle with cumulative data one has to add up the contents of every previous cell in the same row.

Note:This Excel tool requires (paid) claim amounts as input data. If incurred claims are entered, the tool will produce incorrect results.

Instead of cumulative data as shown in Figure 2 incremental data can also be used.

- 1 -

Best-Estimate User Guide

3Usage

3.1Basics

In the following the functionality of the tool will be explained. As shown in Figure 2, the input data are entered in the range B2:AE31 of the worksheet Eingabe in the form of a run-off triangle (or a trapezium). Note that the lower left hand corner of the run-off triangle must be placed in the cell B31.A run-off triangle from another Excel workbook may be entered by transferring it using the copy and paste function of Excel. It is recommended to paste the values only. Otherwise the format of the input range will be changed unintentionally.

Note:The lower left hand corner of the run-off triangle must be entered in cell B31.

All commands to run the macro and all the options to influence the results of the macro can be found exclusively on the worksheet Eingabe. The other worksheets in the spreadsheet of the tool are just designed to show the results of the calculations.In order to run the macro one has to enter 2 accident years and 2 development years at least. Moreover, no more than 30 accident years and development years may be entered.It is possible to evaluate a run-off trapezium, that is a data structure that contains more accident years than development years. However, it is not possible to evaluate a data structure that contains more development years than accident years. Alongside with evaluable input data the user must input whether the data is incremental or cumulative (cell M34). If the user provides incorrect information about the input data, the results will inevitably be wrong, too.In addition, the unit of the input data can be declared as well as a unit for the output (cells I34 and I35).For each option there are three possibilities: euro, thousands of euros and millions of euros.The units “thousands of euros” or “millions of euros” can be used in case of large claim payments.

After entering these three essential pieces of information (run-off triangle, form of data and unit) the macro can be started by pushing the button “Start!“ (see Figure 3).Having calculated all results these are output on the three worksheets Ergebnisse, kum.Schadenzahlungand Barwerte.

Figure3: Start Button, Units of Input and Output, Form of Data

Once some input has been entered in the worksheet Eingabe it can be saved and managed for later utilisation (see Figure 4). Thus, several run-off triangles can be evaluated using different options within one spreadsheet.To save input data the user enters a number into cell D90identifying a column as labelled in row 94and pushes the button “save”. Thereafter, all input data is stored in the column underneath the given label.To retrieve data thus saved the user enters the corresponding digit from row 94 into cell D91 and pushes the button “load“.Additionally, stored input data can be conveniently deleted from its column by entering its column label from row 94 into cell D92 and then pushing the button “delete“. Lastly, all data entered in the worksheet Eingabe can be deleted by pushing the button “delete“ displayed over cell G90.Pushing that button restores the default values of the options, too (as described in section 3.2).Note that loading a saved data set only restores the input data and not the corresponding results on the other worksheets. Results need to be calculated again by pushing the button “Start!”.

Figure4: Saving and Managing Input Data

3.2Further Options

Beyond that the user has several options. These range from humble adjustments of layout of the output to settings regarding the estimation of a tail that have major impact on the calculated results.All options will be explained consecutively.Note that some settings of options supersede other options. In that case these redundant options will be marked gray shaded. Redundant input information is recognised and ignored by the macro automatically, so there is no need to delete redundant input. It should be pointed out that all options address features. To run the macro only the three essential pieces of information (triangle, form and unit) are necessary as described in paragraphs9 and 10.

3.2.1General

The options of the category General affect the given run-off triangle in general. These options can be found in the first row of the worksheet Eingabe:

Balance sheet year –Here, the user can specify the date of the current accident year.In the output the accident years will be labelled according to this input instead of numbering them from .The default value is 2008.
Line of Business –Here, the user can specify the line of business relating to the current run-off triangle.The line of business is important for the presetting of further information regarding predefined chain-ladderfactors and earned premiums.If no line of business is given, the macro uses “other line of business“ as a default. Six lines of business are available (see section3.3).
Commentary –The user can comment on the run-off triangle and the settings here. This memo can be useful especially if the input data is to be stored.

Figure5
: Optionsfrom Category “General”

3.2.2Units and Data

The options from the category Units and Data influence how the input data is dealt with. Both options are essential for running the macro and they need to be set correctly before pushing the start button. They have been addressed already in paragraph 9but they are also listed here for the sake of completeness (seeFigure 3).

Unit: Conversion – Here, the user specifies the input unit of the given data (cell I34). Moreover, the user can specify the output unit used for printing the results (cell I35). There are three possibilities:euro, thousands of eurosand millions of euros. The default value is euro.
Data: Form –Here, the user must specify whether the input data of the run-off triangle consists of cumulative or incrementalclaims amounts (M34). The default value is cumulative.
Data: Years of Loss Occurrence Used –Here, the user can specify how many years of loss occurrence are used to calculate the chain-ladderfactors (cell P34). Thereby, changed claims settlement can be taken into account.The subset of “years of loss occurrence”is only used for the calculation of the chain-ladderfactors (see paragraph 23). If the user does not specify this option, the whole run-off triangle will be used.

3.2.3Tail

The options from the category Tail can be found in the rows from 33 to 44. With these options different aspects of tail estimation can be controlled: fundamental questions, techniques of tail estimation and calculation of the standard error including tail.

Tail: General

Consideration of Tail? –Yes/ No: Should a tail be included? If the user chooses No, all other options from this category become redundant. The default value of this option is Yes.
Tail Factor –If the user already has an estimate of a tail factor for the given run-off triangle, he may use this factor directly instead of the estimation by means of the tail function.If the user supplies a tail factor, all options from the category “Tail: Estimation” become redundant. The use of a supplied tail factor has further impact on the calculations, see paragraph44.
Overall Length of Claims Settlement –How long does it take practically for all claims to be settled completely(seesection4.5, paragraph42)?Note that the overall length of claims settlement including the tail is demanded. Please enter the number of development years and not the label of the last development year. The default value is 50 years.If more than 50 years are entered, consider to predefine more interest rates for the yield curve.

Tail: Estimation

Start Regression from Development Year –For the internal estimation of the tail of the claims development, a development function is fitted to the chain-ladderfactors. The user can specify the development year from which the regression starts. All chain-ladderfactors of previous development years are ignored during the regression.Note that at least two chain-ladderfactors being larger than one are necessary to carry out the regression (seesection4.5, paragraph37).The default value is 5.
Use Predefined Chain-LadderFactors for Regression –Yes/No. If the user chooses No, only the chain-ladderfactors based on the observations in the run-off triangle are used for the regression, except for those being explicitly excluded. Otherwise, the chain-ladder factors of the run-off triangle are supplemented consecutively by adding the predefined chain-ladder factors (see section4.5, paragraph37).The default value is No.
Type of Curve for Development Function – Exponential/Weibull/Power/Sherman. Here, the type of curve for the development function can be chosen (see section4.5,Table 1).The default value is Exponential.
Constant c forShermanCurve– Linear Regression is used to determine the parameters of the curves fitting the chain-ladderfactors. Since the Sherman curve depends on three parameters,the user must specify the third parameter by himself so that the other two parameters can be estimated using linear regression (seesection4.5, paragraph38). If another type of curve has been selected, this option becomes redundant.

Tail: Standard Error

Preset by User? –Yes/ No. Does the user provide the two parameters and necessary for estimating the standarderror including tail? If the answer is No, the macro tries to estimate these two parameters (see section4.7).If the answer is Yes, it is redundant to specify a reference year. The default value is No.
se(f_ult) –Here, the user can specify the estimation error (standard deviation) of the tail factors. Appropriate only if option “Preset by User” has been set to Yes.
sigma_ult –Here, the user can specify the variance parameter for the tail. Appropriate only if option “Preset by User” has been set to Yes.Needs to be filled in the same unit as the output data (see paragraph 9).
Reference Year –The user can mark an accident year (using numbers from 0 to ) that is representative of the whole run-off triangle for reasons of typical claims settlement. This reference year is used as an “anchor point” to derive (see section4.7, paragraph46). The macro is capable of marking a reference year if the user does not supply a reference year.

Figure6: OptionsfromCategory“Tail”

- 1 -

Best-Estimate User Guide

3.2.4Further Settings

To this category “Further Settings” all other options and settings belong. These can be found starting from row 45.

p%-Quantileof the Overall Reserve Without Tail –The user can specify a number between 1and 100. Then the corresponding p%-quantile of the overall reserve without tail is calculated based on a log-normal distribution (see section4.10and Figure 7).The default value is 75.
p%-Quantile of the Overall Reserve Including Tail –The user can specify a number between 1 and 100. Then the corresponding p%-quantile of the overall reserve including tail is calculatedbased on a log-normal distribution. Of course this entry will only be evaluated if a tail is considered (see section4.10and Figure 7).The default value is 75.
Yield Curve –In these three rows yield curves can be provided which will be used for calculating the present value of the future cash flows and duration.Three variants can be given.The first row (row 50) shows the variant for interest rate reduction. The second row (row 51) shows the standard yield curve and the third row (row 52) shows the variant for interest rate increase (see section4.9,paragraph49 and Figure 8). If nothing is entered here, every interest rate is automatically interpreted as 0 %.
Average Interest Rate in % -In addition to the yield curves the user can provide an average interest rate for the calculation of the present values and duration. In doing so only one interest rate is used during this calculation instead of many (see section4.9, paragraph50and Figure 8).If nothing is entered here, the interest rate is automatically interpreted as 0 %.
Chain-LadderFactors –The valuation tool allows the usage of pre-defined chain-ladder factors, e.g. from market sources. The tool uses these pre-defined factors to supplement the factors derived on the basis of the insurer-specific input triangle. This may be helpful in cases where the available input triangle itself would be too short to determine an appropriate tail function. For up to six lines of business the user can provide pre-defined chain-ladderfactors. To use one set of these the user must select the corresponding line of business for the input triangle and the user must mark that pre-defined chain-ladderfactors should be used for estimating the tail function (see section4.5, paragraph35and Figure 9).
Earned Premiums –The user may supply earned premiums for the six lines of business to calculate ultimate loss ratios (see section4.5). To use one set of these the user must select the corresponding line of business for the input triangle (see Figure 9).If nothing is entered here, there will be no results regarding the ultimate loss ratios.
Overwriting Observed Chain-LadderFactors –For every development year the user can supply a chain-ladderfactor that will be used instead of the corresponding chain-ladderfactor calculated on the basis of the original run-off triangle. Particularly, one could use factors from market sources or smoothed factors(see paragraph39).

- 1 -

Best-Estimate User Guide