How To …How To …\How to define a mortality table parameter

How to define a mortality table parameter

Many functions in EAC PV Tools have a mortality table parameter. This parameter is entered as a text string, which is the table name, plus several options that allow the user to define additional attributes such as set back/forward, projected (static or generational), weighted, combined, blended.

 

Mortality parameter definition

The most basic way to define a mortality table is to simply provide the table name, for example if you want to use the 1983 Group Annuity Mortality (male) then you enter "GAM83M". The add-in’s library contains many tables from a variety of sources.

Here is a list of the available attributes of the mortality table with examples:

Attribute

Format

Example

Resulting Table

Comments

None

 

GAM83M

1983 Group Annuity Mortality (male)

Table name must be provided as a test string

Set back age adjustment

tablename/SByears

GAM83M/SB3

1983 Group Annuity Mortality (male), set back 3 years

negative years is a set forward

Set forward age adjustment

tablename/SFyears

GAM83M/SF3

1983 Group Annuity Mortality (male), set forward 3 years

 

Apply a factor to the table (multiply every 𝑞𝑥 in the table by the factor)

tablename[factor]

GAM83M[.9]

1983 Group Annuity Mortality (male) where 𝑞𝑥 at every age is multiplied by .9

 

Blend two tables using a weighting factor for each table – e.g., create a unisex table

tablename1^tablename2

GAM83M[.6]^GAM83F[.4]

Blended table: 1983 Group Annuity Mortality (male) (table factor = 0.6) and 1983 Group Annuity Mortality (female) (table factor = 0.4)

This is the easy way to define a unisex table.

Combine employee/retiree tables where an “employee” table applies up to a given age, and a “retiree” table applies thereafter

tablename-Cage

 

where age is the age at
which the “retiree table”
begins

RP2014-E-F-c65

RP 2014 Total Dataset Employee (female) (age 18 to 80), combined using employee rates before age 65 and retiree rates thereafter

This only works for tables that have an “employee” table and an associated “annuitant’ table, such as RP-2000, RP-2006, RP-2014, RPH-2006, RPH-2014.

Pre-retirement and post-retirement tables where one table applies during the deferral period, and another table applies during the payment period

tablename

with “‑E” (employee) removed,
note this is not an actual
table, but a shortcut table
name for the add-in

RP2014-F

RP 2014 Total Dataset Employee (pre-ret), Healthy Annuitant (post-ret) (female)

tablename1,tablename2

RP2014-E-F,RP2014-HA-F

RP 2014 Total Dataset Employee (pre-ret), Healthy Annuitant (post-ret) (female)

No pre-retirement mortality

zero,tablename

zero,RP2014-HA-F

Pre-retirement: No mortality; Post-retirement: RP 2014 Total Dataset Healthy Annuitant (female) (age 50 to 120)

This is simply the combination of two tables, with “zero” being just special table name

Static projection to a given year using a 1-D mortality improvement scale

tablename/scalename@year

where year is the valuation year

GAM83M/AAM@2018

1983 Group Annuity Mortality (male), projected to 2018 with scale Scale AA (male)

In this example “AA” is the same as “AAM” because “M” is assumed given the male mortality table

Generational projection using a 2-D mortality improvement scale based on age and year

tablename/scalename
@year/gen

where year is the valuation year

GAM83M/AAM@2018/gen

1983 Group Annuity Mortality (male), at valuation year 2018 with generational improvement using Scale AA (male)

If “@year” is not provided, the valuation year is assumed to be the base year of the mortality table

Generational projection stopping at a given year using a 2-D mortality improvement scale based on age and year

tablename/scalename
@year/genstopyear

where year is the valuation
year, and stopyear is the year
at which the projection stops

RP2014-F/MP2020
@2022/gen2030

RP 2014 Total Dataset Employee (pre-ret), Healthy Annuitant (post-ret) (female), at valuation year 2022 with generational improvement using Full 2D Mortality Improvement Rates Scale MP-2020 (female) (stop at year 2030)

Apply a factor to the projection scale (multiply every factor𝑥 in the table by the factor)

tablename/scalename
@year{factor}

where year is the valuation
year

gam83m/AAM@2018{.5}

1983 Group Annuity Mortality (male), projected to 2018 with scale Scale AA (male) (projection scale factor = 0.5)

 

 

Mortality table wizard

To assist the user in setting the various options for a mortality table definition, there is a Mortality Table Wizard. Clicking on the button in the Excel’s main menu pastes the wizard in the active worksheet. From there, all of the settings are available, and the resulting table name is produced along with a long description of the table.

Graphical user interface, application, table

Description automatically generated

 

Mortality table library

You can view the entire library of mortality tables by clicking on the 𝑞𝑥 button in Excel’s ribbon menu.

 

Mortality improvement scale library

You can view the entire library of mortality improvement projection scales by clicking on the 𝑠𝑐𝑥 button in Excel’s ribbon menu.