Learning Center

The ACTUARIAL TOOLBOX comes with the most standard actuarial functions used for life-contingent products. The full list of actuarial functions is presented below:

# Function Name Function Description

1

sActuarialTablesList

Returns all actuarial tables in the database.

2

sActuarialTableValues

Returns the values of the selected table.

3

sActuarialTableName

Returns the name of the table for the specified table number.

4

sActuarialqxVector

Returns the mortality rates, morbidity rates or scale of selected table.

5

sActuarialqxValue

Returns the mortality rate, morbidity rate or scale in selected table at a given age.

6

sActuarialpxValue

Returns the survivorship probability in selected table at a given age.

7

sActuarialpxVector

Returns the survivorship probability of selected table.

8

sActuarialnpxValue

Returns the probability that a life age x will survive n years.

9

sActuarialnpxVector

Returns a vector with the probabilities of an individual aged x to be alive at ages 0-130.

10

sActuarialqxnpxVector

Returns a vector with the probabilities of an individual aged x dying on the exact age of 0-130.

11

sActuarialexValue

Returns the remaining life expectancy of a person aged x.

12

sActuarialOmegaValue

Returns the age that can not be achieved by any person of the initial population.

13

sActuariallxVector

Returns a vector with the number of living people at ages 0-130.

14

sActuariallxValue

Returns the number of persons who attain age x.

15

sActuarialdxVector

Returns a vector with the number of people who die at ages 0-130.

16

sActuarialdxValue

Returns the number of persons who die between ages x and x+1.

17

sActuarialTable

Returns the complete life table for selected table.

18

sActuarialRANDqxVector

Returns a random vector representing ages 0-130 of an individual aged x with the states 0 for living or healthy and 1 for dead or disabled.

19

sActuarialRANDqxValue

Returns a random age of death or disability of an individual aged x.

20

sActuarialRANDqxVectorPopulation

Returns a random vector with the number of people who died or became disabled at ages 0-130.

21

sActuarialRAND3qxVector

Returns the respective random states at ages 0-130 of an individual aged x: 1 = is disabled, 1 = dead of a healthy person, 1 = dead of a disabled person.

22

sActuarialRAND3qxValue

Returns a random vector with ages in which the events of becoming disabled, death of a healthy person and death of a disabled person occurred.

23

sActuarialRAND3qxVectorPopulation

Returns a random vector with the numbers:  alive and healthy people, alive and disabled people, deaths of healthy persons and deaths of disabled persons.

24

sActuarialGenerationalTableqxMatrix

Returns a collection of mortality rates projected by year.

25

sActuarialGenerational2dTableqxMatrix

Returns a collection of mortality rates projected by year.

26

sActuarialGenerationalTableqxVector

Returns the appropriate mortality rates vector for an individual aged x using a generational mortality table.

27

sActuarialGenerational2dTableqxVector

Returns the appropriate mortality rates vector for an individual aged x using a generational mortality table.

28

sActuarialnpxnpixVector

Returns a matrix with the probabilities of an individual aged x to be alive and healthy (column 1) and the probabilities of him to be alive and disabled (column 2) at ages 0-130.

29

sActuarialTableMultipleDecreases

Returns multiple decrement life table.

30

sActuarialFlatRateVector

Returns a vector of size 131 with the specified yield rate.

31

sActuarialnpxnpixVectorMultipleDecreases

Returns a matrix with the probabilities of an individual aged x to be alive and healthy (column 1) and the probabilities of him to be alive and disabled (column 2) at ages 0-130.

32

sActuarialnqxnqixVector

Returns the cumulative probability of an individual aged x dying by any decrement (death of a healthy person or death of a disabled person) at ages 0-130.

33

sActuarialnpxForecastVector

Returns a vector with the probabilities of an individual aged x to be alive at years 0-130.

34

sActuarialqxnpxForecastVector

Returns a vector with the probabilities of an individual aged x dying on the exact years of 0-130.

35

sActuarialnpxnpixForecastVector

Returns a matrix with the probabilities of an individual aged x to be alive and healthy (column 1) and the probabilities of him to be alive and disabled (column 2) at years 0-130.

36

sActuarialnpxnpixMultipleDecreasesForecastVector

Returns a matrix with the probabilities of an individual aged x to be alive and healthy (column 1) and the probabilities of him to be alive and disabled (column 2) at years 0-130.

37

sActuarialdummyForecastVector

Returns a vector to indicate in the years 0-130 the occurrence of a permanent event at age 'xevent' of an individual with age 'xnow'.

38

sActuarialdummyVector

Returns a vector to indicate in the ages 0-130 the occurrence of a permanent event at age 'xevent' of an individual with age 'xnow'.

39

sx

Returns a vector with the ages 0-130.

40

sActuarialxForecastVector

Returns a vector with the projected ages of an individual aged x in the years 0-130.

41

sActuarialvalueForecastVector

Returns a vector for the years 0-130 with the specified value at age 'xevent' of an individual with age 'xnow'.

42

sActuarialAccruedInterestForecastVector

Returns the accumulated yield factor for the years 0-130.

43

sActuarialAnnuityCertainPresentValue

Returns the present value of an annuity-certain per annum for n years.

44

sActuarialAnnuityCertainFutureValue

Returns the accumulated amount of an annuity-certain per annum for n years.

45

sActuarialAnnuityLiability

Returns the present value of an annuity to continue during the person's life, but not after the year n.

46

sActuarialAnnuityCashFlows

Returns the cash flows of an annuity to continue during the person's life, but not after the year n. First column = raw cash flow, second column = actuarially projected cash flows, third column = financially and actuarially discounted cash flows.

47

sActuarialAssuranceLiability

Returns the present value of an assurance valid for n years and payable at the end of the year of the person's death.

48

sActuarialAssuranceCashFlows

Returns the cash flows of an assurance valid for n years and payable at the end of the year of the person's death. First column = raw cash flows, second column = actuarially projected cash flows, third column = financially and actuarially discounted cash flows.

49

sActuarialSalaryPurchasingPowerFactor

Returns a factor to adjust the effect of monthly inflation on real annual income.

50

sActuarialSocialSecurityConstantGrowthForecastVector

Returns the Social Security benefit projection for years 0-130.

51

sActuarialSocialSecurityVectorGrowthForecastVector

Returns the Social Security benefit projection for years 0-130.

52

sActuarialSalaryGrossForecastVector

Returns the salary projection for years 0-130.

53

sActuarialSalaryNetForecastVector

Returns the portion of the salary that exceeds the Social Security benefit.

54

sActuarialContributionsForecastVector

Returns the projected contributions of employees (column 1) and retirees (column 2) for years 0-130.

55

sActuarialnpxDependentForecastVector

Returns a vector with the probabilities that at least one valid dependent will survive at the end of years 0-130.

56

sActuarialSpotInterestRatesToDiscountFactorForecastVector

Converts a vector of spot interest rates to a vector of discount factors.

57

sActuarialForwardInterestRatesToSpotInterestRatesForecastVector

Converts a vector of forward interest rates to a vector of spot interest rates.

58

sActuarialSpotInterestRatesToForwardInterestRatesForecastVector

Converts a vector of spot interest rates to a vector of forward interest rates.

59

sActuarialForwardInterestRatesToDiscountFactorForecastVector

Converts a vector of forward interest rates to a vector of discount factors.

60

sActuarialEndSalaryAssuranceCashFlows

Returns the cash flows of an end salary assurance payable at the end of the year of the person's death. First column = actuarially projected cash flows, second column = financially and actuarially discounted cash flows.

61

sActuarialCashFlows

Returns a complete report of the cash flows of a complex pension scheme. Insert function via ACTUARIAL TOOLBOX tab to recover headers.

62

sActuarialCashFlowsMultipleDecreases

Returns a complete report of the cash flows of a complex pension scheme. Insert function via ACTUARIAL TOOLBOX tab to recover headers.

63

sActuarialRANDqxDependentForecastVector

Returns a random binary matrix indicating pension situation. First column, 1 = Employee alive and healthy; second column, 1 = Retired alive and healthy; third column =  Retired disabled; forth column = person is dead with living dependents.

64

sActuarialRANDCashFlows

Returns a complete report of the random cash flows of a complex pension scheme. Insert function via ACTUARIAL TOOLBOX tab to recover headers.

65

sActuarialBenefitsLiability

Returns the present value of a complex pension scheme.

66

sActuarialBenefitsLiabilityMultipleDecreases

Returns the present value of a complex pension scheme.

67

sActuarialEndSalaryAssuranceLiability

Returns the present value of an end salary assurance payable at the end of the year of the person's death.

68

sActuarialRANDBenefitsLiability

Returns a random present value of a complex pension scheme.

69

sActuarialCashFlowsVector

Extracts the actuarial projection of cash flows calculated using the function "sActuarialCashFlows", "sActuarialCashFlowsMultipleDecreases" or "sActuarialRANDCashFlows".

70

sActuarialBenefitsLiabilitiesVector

Extracts the actuarial projection financially discounted of cash flows calculated using the function "sActuarialCashFlows", "sActuarialCashFlowsMultipleDecreases" or "sActuarialRANDCashFlows".


The usage of actuarial tables in actuarial functions is highly flexible and it can be done by one of the following methods (see examples below):

  1. Using the number of a table registered in the database (this is the default option);
  2. Using the name of a table registered in the database;
  3. Using the table values inserted in an Excel range; and
  4. Entering the qx vector directly as a function argument.

A

B

C

D

1

Life expectancy at age 60 (table UP 1994 female)

 

 

 

2

         Using table number

24.96938061

=sActuarialexValue(6,60)

 

3

         Using table name

24.96938061

=sActuarialexValue("UP1994F",60)

 

4

         Using table range

24.96938061

=sActuarialexValue(B10:B140,60)

 

5

         Entering the qx vector

24.96938061

=sActuarialexValue(sActuarialqxVector("UP1994F"),60)

 

6

        

 

7

 

 

 

 

8

Table UP 1994 female

 

 

 

9

Age

Mortality rates (qx)

 

 

10

0

0.00000

=sActuarialqxVector("UP1994F")

Note: after entering this formula click on the "Multiple Values Formula" button.

11

1

0.00057

 

 

 

 

139

129

1.00000

 

 

140

130

1.00000

 

 

Besides the option of calling the function directly in Excel, there is also the option to insert a template of usage of the function choosing one of the options in the tools group.

For instance, if you want to calculate the actuarial liability of an individual in a pension fund the following result can be immediately achieved by clicking on the “Products/Pension (liability)” option:

 

A

B

C

1

Actuarial liability (Defined Benefit)

 

 

2

 

 

 

3

Retired contribution

0.05

 

4

Worker contribution rate over gross salary

0

 

5

Worker contribution rate over net salary (gross - social security)

0.1

 

6

Administrative costs

0.1

 

7

Current gross salary (annual)

100000

 

8

Current age

30

 

9

Retirement age

65

 

10

Growth (real) rate of worker salary

0.03

 

11

Growth (real) rate of retired pension

0

 

12

Disability entrance table

80

 

13

Life table normal life

6

 

14

Life table disabled life

3

 

15

Current Social Security reference benefit (annual)

30000

 

16

Growth (real) rate of Social Security reference benefit

0.01

 

17

Is normal

TRUE

 

18

Forward (real) interest rate curve

0.05

 

19

Life table dependent

1

 

20

Expected inflation rate

0.05

 

21

Life insurance benefit (% over salary)

0.083333333

 

22

Age at wedding

27

 

23

Children maximum age to receive benefits

20

 

24

Partner age

28

 

25

First child age

1

 

26

Second child age

-2

 

27

Third child age

131

 

28

Fourth child age

131

 

29

Fifth child age

131

 

30

Extra worker contribution

0

 

31

Limiting age to become disabled

130

 

32

Retirement salary conversion rate

1

 

33

Dependent benefit conversion rate

1

 

34

Actuarial liability

                             $ 613,131.82

=-sActuarialBenefitsLiability($B$3,$B$4,$B$5,$B$6,$B$7,$B$8,$B$9,sActuarialFlatRateVector($B$10),

sActuarialFlatRateVector($B$11),sMxElemMult(sActuarialqxVector($B$12),sActuarialdummyVector($B$31,1)),

$B$13,$B$14,$B$15,sActuarialFlatRateVector($B$16),$B$17,sActuarialFlatRateVector($B$18),$B$19,$B$20,

$B$21,$B$22,$B$23,$B$24,$B$25,$B$26,$B$27,$B$28,$B$29,$B$30,$B$32,$B$33)

35

 

 

 

© 2016 Safe Quantitative Technologies, ltd. All rights reserved.