Actuarial Science RUN OFF Triangles Or Delay Triangle

Run-off Triangles (also known as Delay Triangle) is a technique used to calculate the reserve in both Property & Casualty and Health Insurance.

Different methods that we use to calculate reserves are

  1. Basic Chain Ladder Method.
  2. Inflation-adjusted chain ladder method.
  3. Average cost Per Claim Method.
  4. Bornhutter-Ferguson Method.

In the new curriculum, the Run-offs Triangle has been included in CM2 and consists of an additional assessment in MS Excel. So, through this article, we have made an attempt to show you guys how you can use Excel to solve these problems.

Before discussing the problem-solving part, let’s have a look at the functions and shortcuts you’ll be using.

  1. Sum Function: To find the sum of a range of cells
  2. Product Function: To find the product for a range of cells
  3. Average Function: To find the average for a range of cells
  4. Ctrl+D: For dragging the formula to all cells
  5. F4: For freezing a cell

Basic Chain Ladder Method

The primary underlying assumption of the chain-ladder method is that historical loss development patterns are indicative of future loss development patterns. This technique is only accurate when patterns of loss development in the past can be assumed to continue in the future. When there are changes to an insurer’s operations, such as a change in claims settlement times, changes in claims staffing, or changes to case reserve practices, the chain-ladder method will not produce an accurate estimate without adjustments.

The chain-ladder method is also very responsive to changes in experience, and as a result, it may be unsuitable for very volatile lines of business. You can even download the excel sheet used in this blog.

Question:- Find the total reserve and reserve for each Accident Year using Basic Chain Ladder Method?


The Formulas that I have applied to calculate Cumulative claim table, Developing factor and the lower Triangle is shown in below figure.To cumulate the claims you do not need to use sum() function again and again for each cell. Just apply the formula in the first row and the just the entire column up till the blank cell and press CTRL+D(this will apply the same function to all the selected cells).

Formulas used to calculate Reserves are shown below.

Total Reserve Required will be=4865.70954

Inflation-Adjusted Chain Ladder Method

The Basic Chain Ladder method assumes that past inflation is representative of future inflation. As the methodology assumes an average development based on past years, the inflation assumption will similarly be based on a weighted average of past claims inflation. If for any reason, future inflation is expected to be different than the past, the Chain Ladder assumption may fail. The inflation-adjusted Chain Ladder methodology incorporates an explicit allowance for past and future inflation.

Let’s discuss how to solve problems involving only past inflation in Excel.

Question: Suppose that the annual claim payments and inflation rates over 12 months up to the middle of the given years given. Find the reserve required.

We already have the incremental claim amounts for years 2008-2012 (Figure 1) and the inflation rates (Figure 2).

Figure 1

Figure 2

On adjusting for inflation, by multiplying the upper triangle with inflation per 1-unit values we get the values adjusted for inflation. The product functions are applied across each diagonal according to the accident year and development year (figure 3).

Figure 3

Now that the upper triangle has been adjusted for inflation, we can apply the chain ladder method to the upper triangle.

Step1: Calculating the cumulative claims table (figure 4) and the development factors (figure 5 & 6).

Figure 4

Figure 5

Figure 6

Step2: Calculating the lower triangle (Figure 7)

Figure 7

Now that we have the lower triangle, we can calculate the total reserve required which is the difference between Total claims and settled claims. This part has been summarised in figures 8-10.

Figure 8

Figure 9

Figure 10

Thus, the reserve required is 4403.72.

Average Cost Per Claim Method
This method considers separately the two key elements of total claim amounts, i.e., the number of claims and the average amounts of the claims.
In this method, you can solve the problem in 2 ways, i.e., by developing factors and by grossing up factor.

Question: Find the total project loss of given problem?


Now, Let’s see what functions we can use to solve this problem using Excel.

To calculate the average cost per claim table you do not need to divide every cell individually. Just apply the division procedure in the first row and then drag each column separately and press CTRL+D (same formula is applied to each cell of the column with their respective value).

Total Project Loss=3603.44721

Now, let’s solve the same problem using developing factor.

Functions used to solve the problems are shown in below figures.

Total Projected Loss=3603.511897

Total Project Loss from both the method come out to be the same. So, You can use any method to solve the problem.

Bornhutter-Ferguson Method
This method is most useful when actual reported losses for an accident year are a poor indicator of future incurred but not reported (IBNR) losses for the same accident year. This is often the case when there is a low frequency of loss but a very high potential severity. In this method amount for emerging liability is added to actually reported liability to obtain the estimated ultimate loss for a given accident year.

Question: Find the Ultimate loss for the given problem.
We have been given the claims paid, loss ratio and the earned premium. First, we calculate the development factors (figure 1).

Figure 1

Then we calculate the cumulative factors (figure 2) as,

D11=E11*D10 and so on for remaining years

Figure 2

Also, b=1-(1/f) (Figure 3) is calculated for each year.

Figure 3

Here, the earned premium has been given in the question.
Also, loss ratio=83%. You can save this value in a cell, say J14 and freeze it using F4.
The initial ultimate liability (Figure 4) is Earned premium * b

C16=C16*$J$14 and so on for other years.

Figure 4

The emerging liability can be calculated by multiplying the IUL and b.

The reported liability consists of the values of the last diagonal for each year. This can be done by referencing the corresponding cell (by using =) in the upper triangle for each year.

The ultimate liability is the sum of the emerging and the reported liability.

The ultimate loss is the sum of all ultimate liabilities.

Figure 5

Ultimate loss=33280.07
Run-off Triangles was considered to be the easiest topic in CT6. By now you would have come to terms that Run-off Triangles is an easy topic to be done in Excel as well! That’s all from our side if you have any queries or doubt you can comment below.

Author: Anshul Parashar & Sampada Kelkar

Read more about Actuarial Science New Curriculum Click

You might also like More from author