Actuarial Tables: Creating Tables, Part 2

Share on:

Overview

Introduction

In Actuarial Table: Creating Tables, Part 1, I have examined the topic of creating one-dimensional tables. In this article, I will focus on two-dimensional tables and a composite form of table namely select and ultimate table.

The tables to be discussed in this article include the following:

  1. Issue age by policy year table

Issue age by policy year table is a two-dimensional table in which the lookup keys are issue age and policy year. The most common use of issue age by policty year table is cash value table. In a typical cash value table, the cash value rate depends not only on issue age but also on policy year.

  1. Attained age by birth year table

Attained age by birth year table, or may be called generaltional table, is a two-dimensional table in which the lookup keys are attained age and year of birht. Some mortality tables are presented in an attained age by birth year table format.

  1. Select and ultimate table

A select and ultimate table internally consists of two tables: a select table and an ultimate table. The select table is an issue age by policy year table, and the ultimate table is an attained age table. The lookup keys of a select and ultimate table are issue age and policy year. Modern insurance mortality tables are usually presented as select and ultimate tables. Such tables can reflect the mortality experience differences as a result of underwriting.

The three table classes and their constructors described in this article are summarized below:

Class Description Constructor
Table.IAPY Issue age by policy year table Table.IAPY(...)
Table.AABY Attained age by birth year table Table.AABY(...)
Table.SU Select and ultimate table Table.SU(...)

All the code snippets and other supporting files in this article can be found in the sample project SampleTables. You can download it from GitHub. If you haven't done so, click here for details.

Launch RStudio and open SampleTables project. After the project is opened, attach Rgogo package from RStudio console:

1library(Rgogo)

Creating Issue Age by Policy Year Table

Issue age by policy year table is a two-dimensional table in which policy values are looked up by issue age and policy year. This type of table is implemented by Table.IAPY class in Rgogo.

In the example below, I am going to create a cash value table of a 20-year endowment insurance plan. The table values are stored in an Excel file. It contains cash value rates per thousand face amount for each issue age and policy year.

I will follow the steps below:

  1. Using constructor Table.IAPY to create an empty issue age by policy year table.

  2. Reading table values from an Excel worksheet using ImportTableValuesFromExcel function. The Excel file named Sample Cash Value Table.xlsx is located under the project data-raw folder.

  3. Saving the table object as an R data file.

The following codes will carry out the above steps:

 1tbl <- Table.IAPY(
 2    minAge = 18L, 
 3    maxAge = 50L, 
 4    maxPolYear = 20L,
 5    tBase = 1000, 
 6    source = "Sample Cash Value Table.xlsx", 
 7    createdBy = "Edward Kuo",
 8    id = "CV.E20", 
 9    descrip = "20-Year Endowment Cash Value Table"
10)
11
12tbl <- ImportTableValuesFromExcel(
13    tbl, "data-raw/Sample Cash Value Table.xlsx", "Sheet1", "B3:U35"
14)
15
16SaveAsRda(tbl, overwrite = TRUE)

The constructor Table.IAPY takes the following arguments:

Argument Data Type Description
minAge Non-negative integer The minimum issue age at which the table values are available.
maxAge Non-negative integer The maximum issue age at which the table values are available.
maxPolYear Positive integer The maximum policy year for which the table values are available.
tValue Numeric Table value data. tValue must be able to be coerced into a numeric matrix with (maxAge - minAge + 1) rows and maxPolYear columns.
tBase Positive numeric scalar Unit base of table values.
source character Information regarding the source of the table.
createdBy character Name of the creator of the table object.
id character Table identifier.
descrip character Description of the table.

When importing table values for an issue age by policy year table from Excel, ImportTableValuesFromExcel function assumes that the table values are stored in a worksheet with issue ages on rows and policy years on columns. However, if the data are stored differently with policy years on rows and issue ages on columns, you can add an argument transposeData and set the value to TRUE. The code becomes:

1tbl <- ImportTableValuesFromExcel(tbl, "data-raw/Sample Cash Value Table.xlsx", "Sheet1", "B3:U35", transposeData = TRUE)

Creating Attained Age by Birth Year Table

An attained age by birth year table can be created using constructor Table.AABY. The process is very similar to that of creating an issue age by policy year table.

For example, assume that you want to create a mortality table which contains mortality rates per thousand at each attained age between 0 and 110 and for each generation born between 1900 and 2000. The codes would look like below:

 1tbl <- Table.AABY(
 2    minAge = 0L, 
 3    maxAge = 119L,
 4    minBirthYear = 1900L,
 5    maxBirthYear = 2007L, 
 6    tBase = 1, 
 7    source = "US Social Security Administration", 
 8    createdBy = "Edward Kuo",
 9    id = "Mort.SSA.M", 
10    descrip = "Social Security Administration Mortality Rates 1900-2007, Male"
11)
12
13tbl <- ImportTableValuesFromExcel(
14    tbl, "data-raw/SSA Mortality Rates 1900-2007", "Male", "B2:DE121"
15)
16
17SaveAsRda(tbl, overwrite = TRUE)

The key arguments of the constructor Table.AABY include:

Argument Data Type Description
minAge Non-negative integer The minimum attained age for which the table values are available.
maxAge Non-negative integer The maximum attained age for which the table values are available.
minBirthYear Positive integer The minimum calendar year of birth. The value must be between 1800 and 9999.
maxBirthYear Positive integer The maximum calendar year of birth. The value must be between 1800 and 9999.

Similar to an issue age by policy year table, when importing table values for an attained age by birth year table from Excel, ImportTableValuesFromExcel function assumes that the table values are stored in a worksheet with attained ages on rows and birth years on columns. However, if the data are stored differently with birth years on rows and attained ages on columns, you can add an argument transposeData and set the value to TRUE.

Creating Select and Ultimate Table

A select and ultimate table internally consists of two tables: a select table and an ultimate table. The select table is an issue age by policy year table, and the ultimate table is an attained age table. The number of policy years in the select table is called select period. When looking up a select and ultimate table, the required lookup keys are issue age and policy year. If the policy year is within the select period, the corresponding value in the select table is returned; otherwise, the value corresponding to the attained age in the ultimate table is returned.

The constructor for creating a select and ultimate table is Table.SU. Let's use the sample file 2017 Loaded CSO Female Nonsmoker ALB.xlsx located in the sample project data-raw folder as an example to illustrate how to create a select and ultimate table.

In the Excel file, it is a 2017 Loaded CSO age-last-birthday mortality table for a female nonsmoker. The rates are on a per thousand basis. The CSO table is a select and ultimate table and has a select period of twenty five years. The minimum issue age of the table is 18, and the maximum issue age is 95. The maximum attained age for which the rates are available is 120.

The codes for creating this select and ultimate table are as follows:

 1tbl <- Table.SU(
 2    minSelAge = 18L,
 3    maxSelAge = 95L,
 4    selPeriod = 25L,
 5    maxAttAge = 120L,
 6    tBase = 1000, 
 7    source = "Society of Actuaries", 
 8    createdBy = "Edward Kuo",
 9    id = "Mort.CSO2017LL.FN", 
10    descrip = "2017 Loaded CSO Female Nonsmoker ALB Mortality Rates per 1000"
11)
12
13tbl <- ImportTableValuesFromExcel.SU(
14    tbl, "data-raw/2017 Loaded CSO Female Nonsmoker ALB.xlsx", 
15    "2017 FNS ALB", "B4:Z81", "AA4:AA81"
16)
17
18SaveAsRda(tbl, overwrite = TRUE)

The arguments specific to Table.SU constructor are:

Argument Data Type Description
minSelAge Non-negative integer The minimum select age (or issue age) of the table.
maxSelAge Non-negative integer The maximum select age (or issue age) of the table.
selPeriod Positive integer The select period of the table.
maxAttAge Non-negative integer The maximum attained age for which the table values are available.

The function for importing table values is different from previous examples. We use ImportTableValuesFromExcel.SU to import table values for a select and ultimate table. The function takes five arguments. The first three arguments are the table object, the path of the Excel file and the name of the sheet in the Excel file. The fourth argument is the range string of the select table, and the fifth argument is the range string of the ultimate table. Note that the function assumes that the data for select table and ultimate table are in the same worksheet.

ImportTableValuesFromExcel.SU also assumes that the source data of the select rates in the Excel is a table with issue ages for the rows and durations (policy years) for the columns. If your select rate table is arranged differently with durations for the row and issue ages for the columns, you can add transposeData = TRUE as the last argument. With this additional argument, the function can automatically transpose the table values for you.

Summary

In this article, I have demonstrated how to create a two-dimensional table and a select and ultimate table. The two-dimensional table classes implemented in Rgogo Framework include Table.IAPY (issue age by policy year table) and Table.AABY (attained age by birth year table).

A select and ultimate table is implement by Table.SU class. Table.SU internally consists of two tables: a select table and an ultimate table. The select table is an issue age by policy year table, and the ultimate table is an attained age table.

Each table class has its own constructor. The name of the constructor is the same as the class name, but the arguments are somewhat different.

We can either specify table values in the constructor or import the values from an Excel file. In case of importing table values from an Excel file, the same function ImportTableValuesFromExcel used for one-dimensional tables is also used for importing values for two-dimensional table classes Table.IAPY and Table.AABY. However, a different function ImportTableValuesFromExcel.SU shall be used for importing values of a select and ultimate table class Table.SU.

After a table object is created, we use SaveAsRda function to save the object in R data format.

Previous article: Actuarial Table: Creating Tables, Part 1

Next article: Actuarial Tables: Using Tables