EXAMPLE 1 ........................................................................................................................................................

A. Endowment .................................................................................................................................... 1

B. Pure endowment and Term insurance ............................................................................................ 4

C. Reserves ......................................................................................................................................... 7

D. Brutto premium and reserves ....................................................................................................... 10

EXAMPLE 2 ..................................................................................................................................................... 14

A. Whole life ..................................................................................................................................... 14

B. Reserves of Whole life .................................................................................................................. 16

C. Brutto Whole life .......................................................................................................................... 17

EXAMPLE 3 ..................................................................................................................................................... 18

A .Pure endowment .......................................................................................................................... 18

B. Deferred annuity .......................................................................................................................... 18

C. Fixed premium annuity ................................................................................................................. 20

D. Reserves ....................................................................................................................................... 22

EXAMPLE 4 – UNIVERSAL TRADITIONAL APPROACH ................................................................. 25

EXAMPLE 5 – FLEXIBLE PRODUCT ....................................................................................................... 27

A. Capital value at the end of policy .................................................................................................. 27

B. Minimizing premium ..................................................................................................................... 27

C. Endowment .................................................................................................................................. 28

EXAMPLE 6 – CASH-FLOW MODEL ....................................................................................................... 29

A. Profitability test ............................................................................................................................ 29

B. Liability Adequacy Test ................................................................................................................. 31

ACTUARIAL FORMULAS AND MS EXCEL FUNCTIONS .................................................................. 32

Nomenclature ................................................................................................................................... 32

Mortality tables and Commutation tables ........................................................................................ 32

 Actuarial functions............................................................................................................................ 34

Reserves ........................................................................................................................................... 36

Regular netto premium ..................................................................................................................... 37

TUTORIAL OF MS EXCEL APPLICATION ............................................................................................. 39

Example 1
A client, man 25 years old, wants to make an insurance contract for 25 years.

A. In case of death he wants to secure his family with 200 000. In case he survives all 25 years
he also wants to receive 200 000. How much would this contract cost him?
Derive results of single and regular netto premium.
B. The client wants to see how much of the premium he pays for insurance to secure his family
in case of death and how much for 200 000 in case he survives.
Derive results of single and regular premium.
C. You, as an insurance company, should be able to cover most of your contracts. For that
purpose you should calculate reserves for all considered contracts. Calculate reserve in year
7.
D. Apply charges and calculate brutto premium and reserves of contracts mentioned in section
A and B.

*note: assume traditional approach with interest rate 0.025 p.a.

A. Endowment
Gender Male
Age 25
Policy period 25
Death benefit (K) 200 000
Survival benefit (D) 200 000
Interest rate (i) 0.025
Type of insurance contract Endowment

Premium: Endowment
Single 108 888,99
Regular annual 5 829,87
Regular monthly 491,46

Single premium
Using Excel function

Using Mortality tables

dx * v + dx+1 * v2 +...+ dx+n-1 * vn lx+n * vn
xn = K * + D *
lx lx

2626,83 95039,86 * 0,53939

108 888,99 = 200 000 * + 200 000 *
98982,34 98982,34

1
Using probability

xn = K * qx*v+1|qx*v2+…+ n-1|qx * vn + D * npx * vn

108 888,99 = 200 000 * 0,02654 + 200 000 * 0,96017 * 0,539391

Using Commutation numbers

Cx + Cx+1 + Cx+2 +...+ Cx+n-1 Dx+n
xn = K * + D *
Dx Dx

1416,89 27651,11
108 888,99 = 200 000 * + 200 000 *
53390,15 53390,15

Mx - Mx+n Dx+n
xn = K * + D *
Dx Dx

1416,89 27651,11
108 888,99 = 200 000 * + 200 000 *
53390,15 53390,15

Regular premium – annual

Using Excel function

Using Actuarial formulas

K * A1xn + D * n Ex
K * nP x =
ӓxn˥

200 000 * 0,02654 + 200 000 * 0,51791

5 829,87 =
18,68
Using probability
K * qx * v +1|qx * v2 +…+ n-1|qx * vn + D * npx * v
n
K * nP x =
1 + 1px * v + …+ n-1px * vn-1

200 000 * 0,02654 + 200 000 * 0,51791

5 829,87 =
18,68

2
Using Commutation numbers
K * Mx - Mx+n + D * Dx+n
K * nP x =
Nx - Nx+n

200 000 * 1416,89 + 200 000 * 27651,11

5 829,87 =
997208,20

K * dx * v + dx+1 * v2 +…+ dx+n-1 * vn + D * lx+n * vn

K * nP x =
lx + lx+1 * v + lx+2 * v2 +…+ lx+n-1*vn-1

200 000 * 2626,83 + 200 000 * 51263,61

5 829,87 =
1848768,26

Regular premium – monthly

Frequency of premium (m) monthly m = 12
Regular netto premium
K* (m)
nPx = (m-1)* (Dx - Dx+n)
m*(1 - )
2m * (Nx - Nx+n)

5 829,87
491,46 =
11,86

3
B. Pure endowment and Term insurance
Gender Male
Age 25
Policy period 25
Death benefit (K) 200 000
Survival benefit (D) 200 000
Interest rate (i) 0.025
Type of insurance contract Endowment / Term insurance

Premium: Endowment Pure endowment Term insurance

Single 108 888,99 103 581,31 5 307,67
Regular annual 5 828,87 5 545,70 284,17
Regular monthly 491,46 467,51 23,96

Pure endowment – single premium

Using Excel function

Using Mortality tables

lx+n * vn
xn = D *
lx

95039,86 * 0,53939
103 581,31 = 200 000 *
98982,34

Using probability

xn = D * npx * vn

103 581,31 = 200 000 * 0,96017 * 0,53939

Using Commutation numbers

Dx+n
xn = D *
Dx

27651,11
103 581,31 = 200 000 *
53390,15

4
Pure endowment – Regular premium – annually
Using Excel function

Using Actuarial formulas

n Ex
K * nP x = D *
ӓxn˥

0,51791
5 545,70 = 200 000 *
18,68

Using Commutation numbers

Dx+n
K *nPx = D *
Nx - Nx+n

27651,11
5 545,70 = 200 000 *
997208,20

Using Mortality tables

lx+n * vn
K *nPx = D *
lx + lx+1 * v + lx+2 * v2 +…+ lx+n-1 * vn-1

51263,61
5 545,70 = 200 000 *
1848768,26

Using probabilities
n
npx * v
K *nPx = D *
1 + 1px * v + …+ n-1px * vn-1

0,51791
5 545,70 = 200 000 *
18,68

5
Pure endowment – Regular premium –monthly
Frequency of premium (m) monthly m = 12
Regular netto premium
K* (m)
nPx = (m-1)* (Dx - Dx+n)
m*(1 - )
2m * (Nx - Nx+n)

5 545,70
467,51 =
11,86

Term insurance – Single premium

Using Excel function:

Using Mortality tables

dx * v + dx+1 * v2 +…dx+n-1 * vn
xn = K *
lx

2626,83
5 307,67 = 200 000 *
98982,34
Using probabilities

xn = K * qx*v+1|qx*v2+…+ n-1|qx * vn

5 307,67 = 200 000 * 0,02654

Using Commutation numbers

Cx + Cx+1 + Cx+2 …+ Cx+n-1 Mx- Mx+n
xn = K * =
Dx Dx

1416,89 1416,89
5 307,67 = 200 000 * =
53390,15 53390,15

6
Term insurance – Regular premium – annually
Using Excel function

Using Actuarial formulas

A1xn
nP x = K *
ӓxn˥

0,02654
284,17 = 200 000 *
18,68

Using Commutation numbers

Mx - Mx+n Cx + Cx+1 + Cx+2 …+ Cx+n-1

nP x = K * =
Nx - Nx+n Nx - Nx+n

1416,89 1416,89
284,17 = 200 000 * =
997208,20 997208,20

Using Mortality tables

dx * v + dx+1 * v2 +…dx+n-1 * vn
nP x = K *
lx + lx+1 * v + lx+2 * v2 +…+ lx+n-1*vn-1

2626,83
284,17 = 200 000 *
1848768,26

Using probabilities
qx*v+1|qx*v2+…+ n-1|qx * vn
nP x = K *
1 + 1px * v + …+ n-1px * vn-1

0,02654
284,17 = 200 000 *
18,68

Term insurance – Regular premium - monthly

Frequency of premium (m) Anually m= 1
Regular netto premium
K* (m)
nPx = (m-1)* (Dx - Dx+n)
m*(1 - )
2m * (Nx - Nx+n)

284,17
23,96 =
11,86

7
C. Reserves
Gender Male
Age 25
Policy period 25
Death benefit (K) 200 000
Survival benefit (D) 200 000
Interest rate (i) 0.025
Reserve year (t) 7
Type of insurance contract Endowment / Term insurance / Pure endowment

Premium: Reserve of regular premium

Endowment 0,22055*200 000 =44 110
Pure endowment 0,21517*200 000 =43 034
Term insurance 0,00540*200 000 =1 080

Endowment
Using Excel function

Using Actuarial formulas

tVx = Ax+t,n-t˥ - nP x * ӓx+t,n-t˥

0,22055 = 0,64492 - 0,02915 * 14,56

Using Commutation numbers

Dx Nx+t - Nx+n
tVx = 1 - *
Dx+t Nx - Nx+n

53390,15 650482,57
0,22055 = 1 - *
44680,94 997208,20

Netto reserves
250 000 Kč
200 000 Kč
150 000 Kč
100 000 Kč
50 000 Kč
0 Kč
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

8
Pure endowment
Using Excel function

Using Actuarial formulas

tVx = n-tEx+t - nP x * ӓx+t,n-t˥

0,21517 = 0,61886 - 0,02773 * 14,56

Using Commutation numbers

Dx+n Nx - Nx+t
tVx = *
Dx+t Nx - Nx+n

27651,11 346725,63
0,21517 = *
44680,94 997208,20

Netto reserves
250 000 Kč

200 000 Kč

150 000 Kč

100 000 Kč

50 000 Kč

0 Kč
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

9
Term insurance
Using Excel function:

Using Actuarial formulas

tVx = A1x+t,n- t˥ - nPx * ӓx+t,n-t˥

0,0054 = 0,0261 - 0,0014 * 14,5584

Using Commutation numbers

Mx+t - Mx+n Mx - Mx+n Nx+t - Nx+n
tVx = - *
Dx+t Dx+t Nx - Nx+n

1164,4039 1416,8875 650482,5696

0,0054 = - *
44680,9390 44680,9390 997208,2031

Netto reserves
1 200 000 Kč
1 000 000 Kč
800 000 Kč
600 000 Kč
400 000 Kč
200 000 Kč
0 Kč
0 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40 42 44 46 48 50 52 54

D. Brutto premium and reserves

Gender Male
Age 25
Policy period 25
Death benefit (K) 200 000
Survival benefit (D) 200 000
Interest rate (i) 0.025
Reserve year (t) 7
Type of insurance contract Endowment / Term insurance / Pure endowment

10
 Premium: Regular brutto Brutto reserve
Endowment 5 965,93 42 546,92
Pure endowment 5 680,87 41 471,95
Term insurance 402,92 -487,07
*Note: There are no Excel functions to calculate brutto premium

Endowment

Endowment brutto premium

K * Axn + K + fix + ӓxn * fix + K + fix + K)
Bxn =
ӓxn * (1 - Bxn -Bxn) - Bxn

108888,99 + 2004,00 + 18,68 * 10,20

5 965,93 =
18,68 * 0,99690 - 0,00020

Endowment brutto reserve

(K + fix + Bxn) * ӓx+t,n-t
K*Vxbrutto = K*tVxnetto -
ӓx,n

2005,19 * 14,56
42 546,92 = 44109,87 -
18,68

Brutto reserves K*tVxNetto K*tVxBrutto

250 000 Kč

200 000 Kč

150 000 Kč

100 000 Kč

50 000 Kč

0 Kč

-50 000 Kč
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

11
Pure endowment

Pure endowment brutto premium

D * nEx K +fix + ӓxn fix +K + fix +K)

+ *
K * Bxnbrutto =
ӓxn (1 - Bxn -Bxn) - Bxn
*

103581,31 2004,00 + 18,68 10,20

5 680,87 = + *
18,68 * 0,99690 - 0,00020

Pure endowment netto reserve

(K + fix + Bxn) * ӓx+t,n-t
K*tVxbrutto = K*tVxnetto -
ӓx,n

2005,14 * 14,56
41 471,95 = 43 034,85 -
18,68

Brutto reserves K*tVxNetto K*tVxBrutto

250 000 Kč

200 000 Kč

150 000 Kč

100 000 Kč

50 000 Kč

0 Kč

-50 000 Kč
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

12
Term insurance

Term insurance brutto

premium
K * A1xn + K + fix + ӓxn fix + K + fix + K)
*
Bxn =
ӓxn * (1 - Bxn -Bxn) - Bxn

5307,67 + 2004,00 18,68 10,20

402,92 = + *
18,68 * 0,99690 - 0,00020

Term insurance brutto reserve

brutto netto
(K + fix + Bxn) * ӓx+t,n-t
tVx = tVx -
ӓx,n

2004,08 * 14,56
-487,07 = 1075,01 -
18,68

Brutto reserves K*tVxNetto K*tVxBrutto

3 000 Kč

2 000 Kč

1 000 Kč

0 Kč

-1 000 Kč

-2 000 Kč

-3 000 Kč
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

13
Example 2
50 years old female wants to be insured for one million in case of death.

A. Compare single and regular premium for whole life.

B. Calculate also reserve at age 75.
C. Calculate whole life premium and reserve including charges.

A. Whole life
Gender Female
Age 50
Policy period 
Death benefit (K) 1 000 000
Type of insurance contract Whole life

Premium: Single premium Regular premium

Whole life 453 687,44 20 254,98

Whole life – single premium

Using Excel function:

Using Mortality tables

dx * v + dx+1 * v2 + …
x = K *
lx

44291,15
453 687,44 = 1 000 000 *
97624,80
Using probability

x = K * qx*v+1|qx*v2+2|qx*v3 + …

453 687,44 = 1 000 000 * 0,45369

Using commutation numbers

Cx + Cx+1 + Cx+2 + ... Mx
x = K * =
Dx Dx

12886,16 12886,16
453 687,44 = 1 000 000 * =
28403,17 28403,17

14
Whole life –Regular premium
Using Excel function:

Using Actuarial formulas

Ax
K * nPx = K *
ӓxn˥

0,45369
20 254,98 = 1 000 000 *
22,39881

Using Mortality tables

dx * v + dx+1 * v2 +…
K * nPx = K *
lx + lx+1 * v + lx+2 * v2 +…+ lx+n-1*vn-1

44291,15
20 254,98 = 1 000 000 *
2186679,82

Using probabilities
qx * v + 1|qx * v2 +…
K * nPx = K *
1 + 1px * v + …+ n-1px * vn-1

0,45
20 254,98 = 1 000 000 *
22,40

Using Commutation numbers

Mx Cx + Cx+1 + Cx+2 +…
K * nPx = K * =
Nx Nx

12886,16 12886,16
20 254,98 = 1 000 000 * =
636197,45 636197,45

15
B. Reserves of Whole life
Gender Female
Age 50
Policy period 
Death benefit (K) 1 000 000
Survival benefit (D) 0
Reserve (t) 25
Type of insurance contract Whole life

Premium: Reserve
Whole life 0,53529*1M = 535 287,41

Whole life – Reserve

Using Excel function:

Using Actuarial formulas

tVx = A1x+t - Px * ӓx+t

0,53529 = 0,74612 - 0,02025 * 10,40901

Using Commutation numbers

Dx Nx+t
tVx = 1 - *
Dx+t Nx

28403,17 127882,26
0,53529 = 1 - *
12285,73 636197,45

Netto reserves
1 200 000 Kč

1 000 000 Kč

800 000 Kč

600 000 Kč

400 000 Kč

200 000 Kč

0 Kč
0 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40 42 44 46 48 50 52 54

16
C. Brutto Whole life
Gender Female
Age 50
Policy period 
Death benefit (K) 1 000 000
Reserve (t) 25
Type of insurance contract Whole life

Premium: Regular brutto premium Brutto reserve

Whole life 20 443,72 534 346,79

Whole life – Regular brutto premium

Whole life brutto premium

K * A1x + K + fix + ӓx * fix + K + fix + K)
Bxn =
ӓx * (1 - Bxn -Bxn) - Bxn

453687,44 + 2020,00 + 22,40 * 35,00

20 443,72 =
22,40 * 0,99690 - 0,00020

Whole life – Brutto reserve

Whole life brutto reserve
(K + fix + Bxn) * ӓx+t
K * tVxbrutto = K* tVx
netto
-
ӓx

2024,09 * 10,41
534 346,79 = 535287,41 -
22,40

1 200 000 Kč
Brutto reserves K*tVxNetto

1 000 000 Kč

800 000 Kč

600 000 Kč

400 000 Kč

200 000 Kč

0 Kč

-200 000 Kč
0 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40 42 44 46 48 50 52 54

17
Example 3
Young man of age 30 wants to be secured when he reaches his 60 by certain amount of money.

A. How much he has to pay every year to get two million when he turns 60.
B. How much he would have to pay to get 5 000 every year from 60 until the rest of his life.
Compare this premium with premium for 5 000 every year from his 60 until his 80.
C. He is sure that now he can pay max 2000 per year. What annuity he can expect when he
turns 60 until his death and until his 80?
D. Make the reserves of these two contracts at year 12 and 45.

A .Pure endowment
Using Actuarial formulas

n Ex
nP x = D *
ӓxn˥

0,42621
40 680,47 = 2 000 000 *
20,95

B. Deferred annuity
Gender Male
Age (x) 30
Policy period (N) 
Deferred (k) 
Annuity (D) 5 000
Interest rate (i) 0,025
Type of insurance contract Deferred / Whole life / Temporary annuity

Premium: Single premium Regular premium

Whole life annuity 30 576,34 1 459,22
Temporary annuity 26 639,88 1 271,36

Whole life annuity – Single premium

Using Excel function

Using Mortality tables

lx+k+1 * vk+1 + lx+k+2 * vk+2 +...
x = D *
lx

603057,53
30 576,34 = 5 000 *
98615,07

18
 Using probabilities

x = D * k+1px * v
k+1 +
k+2px * vk+2 +….

30 576,34 = 5 000 * 6,12

Using Commutation numbers

Dx+k+1 + Dx+k+2 ... Nx+k+1
x = D * =
Dx Dx

2766364,06 287503,27
30 576,34 = 5 000 * =
47014,01 47014,01

Whole life – Regular premium

Using Excel function

Regular premium
D * k+1|ӓx
nPx =
ӓxk˥

30576,34
1 459,22 =
20,95

Temporary annuity – Single premium

Using Excel function

Using Mortality tables

lx+k+1 * vk+1 + lx+k+2 * vk+2 +…+ lx+n* vn
xn = D *
lx

525418,84
26 639,88 = 5 000 *
98615,07

19
 Using probabilities

xn = D * k+1px * vk+1 + k+2px * vk+2 +…+ npx * vn

26 639,88 = 5 000 * 5,33

Using Commutation numbers

Dx+k+1 + Dx+k+2 +…+ Dx+n Nx+k+1 - Nx+n+1
xn = D * =
Dx Dx

250489,59 250489,59
26 639,88 = 5 000 * =
47014,01 47014,01

Temporary annuity – Regular premium

Using Excel function

Regular premium
D * k|axn˥
nPx =
ӓxk˥

26639,88
1 271,36 =
20,95

C. Fixed premium annuity

Whole life
P  axk  Dk | a x

P  axk
D
k| a x

2000  20,95
D  6852,95
6,11

Temporary annuity
P  axk  Dk | a xn

20
 P  axk
D
k | a xn

2000  20,95
D  7865,58
5,32

xk
Excel function for a

Excel function for k| ax

Excel function for k | a xn

21
 D. Reserves
Gender Male
Age (x) 30
Policy period (N) 
Deferred (k) 
Annuity (D) 5 000
Interest rate (i) 0,025
Type of insurance contract Deferred / Whole life / Temporary annuity

Premium: Reserve t = 12 Reserve t = 45

Whole life annuity 4,16*5 000 = 20 800 7,93*5 000 = 39 650
Temporary annuity 3,63* 5 000 = 18 150 3,98*5 000 = 19 900

Whole life annuity

Netto reserves
80 000 Kč
70 000 Kč
60 000 Kč
50 000 Kč
40 000 Kč
30 000 Kč
20 000 Kč
10 000 Kč
0 Kč
0 3 6 9 12 15 18 21 24 27 30 33 36 39 42 45 48 51 54 57 60 63 66 69 72 75

Using Excel function:

Using actuarial symbols:

K=12, tVx for k > t

tVx = k-t|ax+t - kPx * ax+t,k-t

4,16 = 8,34 - 0,29 * 14,30

22
 K=45, tVx k ≤ t

tVx = ax+t

7,93 = 7,93

Temporary annuity

Netto reserves
70 000 Kč
60 000 Kč
50 000 Kč
40 000 Kč
30 000 Kč
20 000 Kč
10 000 Kč
0 Kč
0 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40 42 44 46 48 50

Using Excel function:

Using actuarial symbols:

K=12, tVx for k > t

tVx = k-t|ax+t,n-t - k|nPx * ax+t,k-t

3,63 = 7,26 - 0,25 * 14,30

23
K = 45, tVx for k ≤ t

tVx = ax+t,n-t

3,98 = 3,98

24
Example 4 – Universal Traditional approach
30 years old man has very special demands about his insurance contract. When he turns 40, he wants
to be insured for 10 000 in case of death. When he turns 50, he wants to increase death benefit up to
20 000 and until his 55 he wants to receive 5 000 every year. In his 60 he wants to get annuity 20 000
and then until his 70 to be insured for 10 000 in case of death but doesn’t want to pay premium.

The charges for this contract assume following:

alfa fix 2000

alfa from premium (%) 2,0000%
alfa z K (%) 2,0000%
alfa z D (%) 2,0000%

fix from K or D from P

Beta 20,00 2,00000% 2,00000%
Gamma 30,00 3,00000% 3,00000%
Delta 35,00 3,50000%

Calculate netto and brutto premium and reserve in policy year 5 and 25 of this contract.

Gender Male
Age (x) 30
Policy period (N) 
Deferred (k) 
Reserve (t) 
From age 40 to 50: 10 000
Death benefit (K) From age 50 to 60: 20 000
From age 60 to 70: 10 000 without paying premium
From age 50 to 55: 5 000
Annuity (D)
In age 60: 20 000
Interest rate (i) 0,025
Type of insurance contract Deferred / Term insurance / Temporary annuity

Premium: Netto Brutto

Premium 1 222,46 2 035,55
Reserve year 5 6 593,45 5 079,80
Reserve year 25 16 921,97 16 538,53

25
 Reserves tVxNetto tVxBrutto

35 000 Kč

30 000 Kč

25 000 Kč

20 000 Kč

15 000 Kč

10 000 Kč

5 000 Kč

0 Kč
0 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38
-5 000 Kč

The input information of this policy can be seen on picture below.

26
Example 5 – Flexible product
Deal flexible contract for 35 years old male for 30 years.

A. What is the capital value at the end of policy if the premium is 2 000 per year, death benefit
is 100 000 and initial deposit of 1 000?
B. What is the minimal premium to keep zero reserve at the end of policy?
C. What is the premium for similar contract to endowment where the death benefit is 100 000
and survival benefit is 50 000?

A. Capital value at the end of policy

Policy characteristics Model point:
Claims: Year 2015
Death SA+CV Age at valuation date 35
Tech. Int. rate 2,5% Sex Male
Policy charges: SA 100 000
1st year 80,0% Premium (annual) 2 000
2+years 20,0% CV at val. date 1 000
Profit share 85,0% Policy year at val. date 1
Surrender fee 5,0% Policy_period 30

Year Age Policy Year Pshare CV EoY

… … … … …
2044 64 30 … 67 821

B. Minimizing premium1

Policy characteristics Modelpoint:

Claims: Year 2015
Death SA+CV Age at valuation date 35
Tech. Int. rate 2,5% Sex Male
Policy charges: SA 100 000
1st year 80,0% Premium (annual) 538
2+years 20,0% CV at val. date 1000
Profit share 85,0% Policy year at val. date 1
Surrender fee 5,0% Policy_period 30

1
*Note: This Solution requires to install solver.

27
C. Endowment
Policy charcteristics Modelpoint:
Claims: Year 2015
Death SA Age at valuation date 35
Tech. Int. rate 2,5% Sex Male
Policy charges: SA 100 000
1st year 80,0% Premium (annual) 1 616
2+years 20,0% CV at val. date 1 000
Profit share 85,0% Policy year at val. date 1
Surrender fee 5,0% Policy_period 30

Year Age Policy Year Pshare CV EoY

… … … … …
2044 64 30 … 50 000

28
Example 6 – Cash-Flow model
One big company wants to insure 1 000 its employees. In case of death, 100 000 will be paid and if
insured person survives 10 years the contract will be canceled and the person will obtain at least
20 000. All employees are all men in age 35.

A. Compare traditional and flexible approach and decide which of the two types of contract is
more profitable.
B. Make calculation of Liability Adequacy Test (LAT) for both approaches.

A. Profitability test
Flexible approach
Calculation of flexible product premium with SA = 100 000 and at the end CV = 20 000. By using
solver the minimal premium is 2 495.

Policy characteristics Modelpoint:

Claims: Year 2015
Death SA Age at valuation date 35
Tech. Int. Rate 2,5% Sex M
Policy charges: SA 100 000
1st year 80,0% Premium (annual) 2 495
2+years 20,0% CV at val. date 0
Profit share 85,0% Policy year at val. date 1
Surrender fee 5,0% Policy_period 10

From Cash-Flow model the profitable criteria is Pcrit 1.

PVPL
Pcrit1 
PV Pr emium

Liability model
PV CF -29 388 850

PV PL -29 636 250

PV Premium 115 266 345

Net PL -24 830 982

Total Earnings -24 830 982

Pcrit 1 -25,7%
Pcrit 2 -118,8%

29
Traditional approach
The premium of traditional approach of endowment contract below needs to be firstly calculated.

Time (t) Age (x) In force Premium transfer Death benefit Survival benefit
1 35 1 1 100 000
2 36 1 1 100 000
3 37 1 1 100 000
4 38 1 1 100 000
5 39 1 1 100 000
6 40 1 1 100 000
7 41 1 1 100 000
8 42 1 1 100 000
9 43 1 1 100 000
10 44 1 1 100 000 20 000

Regular brutto premium 2 146,58

The profit criteria can be seen in output of Cash-flow model.

Liability model
PV CF -14 196 104

PV PL 5 468 347
PV Premium 99 171 010

Net PL 4 106 974

Total Earnings 4 907 146

Pcrit 1 5,5%
Pcrit 2 25,5%

Traditional approach seems to be more profitable for insurance company based on Pric1 and also for
insured person because of lower premium. The reason why traditional approach gives better results
is based on assumptions of no surrenders payoffs. If client cancels flexible policy, he receives his
capital value adjusted by surrender fee. In traditional approach we assume no payout when the
policy is canceled.

30
B. Liability Adequacy Test

Flexible approach Traditional approach

BE 29 388 850 BE 14 196 104

RM 8 976 977 RM 10 882 614

FV 38 365 827 FV 25 078 718

LAT 38 365 827 LAT 5 074 425

After pressing “Calculate LAT” Liability adequacy test will be automatically calculated.

31
Actuarial formulas and MS Excel functions

Nomenclature
X Age

N Policy period

i Interest rate

D Death benefit

K Survival benefit

t Time of reserve

k Deferred time

Mortality tables and Commutation tables

Probability of death qx
Excel function: qx (x)

l x  l x 1 d x
qx  
lx lx

Probability of survive px
Excel function: px (x)

px  1  qx

l x 1
px 
lx

Number of living lx
Excel function: lx (x)

l x  p x 1  l x 1  1  q x 1   l x 1

Number of death dx
Excel function: dx (x)

d x  l x  l x 1

Probability of surviving n years npx

Excel function: npx (x,n)

l xn
n px 
lx

32
 p x  i 0 p x i
n
n

Probability of death in n years nqx

Excel function: nqx (x,n)

l x  l xn
n qx 
lx

n q x  1 n p x

Probability of death in certain age x+n n|qx

Excel function: n_qx (x,n)

d xn
n| qx 
lx

Discounted number of living at age x

Excel function: DDx (x,i)

Dx  l x  v x

Discounted number of death at age x

Excel function: Cx (x,i)

C x  d x  v x 1

Commutation numbers of first order

Excel function: Mx (x,i)

 i 0 Dx i
x
N x  Dx
[ 2]

Excel function: Nx (x,i)

 i 0 C xi
x
M x  Cx
[ 2]

Commutation numbers of second order

Excel function: Sx (x,i)

 i 0 N xi
x
S x  Dx
[ 3]

Excel function: Rx (x,i)

 i 0 M xi
x
Rx  C x
[ 3]

33
Actuarial functions
Pure endowment
Excel function: nEx (x,n,i)

l xn  v n
n Ex 
lx

Dx  n
n Ex 
Dx

n Ex n px  v n

Whole life
Excel function: A1x (x,i)



d x i  v i 1
A 1
x  i 0

lx



C x i Mx
A1
x  i 0

Dx Dx

A1 x  i 0 q x i  v i 1


Temp insurance
Excel function: A1xn (x,n,i)


n 1
d x i  v i 1
A 1
xn  i 0

lx


n 1
C x i M x  M xn
1
A xn  i 0

Dx Dx

A1 xn  i 0 i| q x i  v i 1
n 1

Endowment
Excel function: Axn (x,n,I,D,K)

Axn  A1 xn  D / K  n E x


n 1
d x i  v i 1 l xn  v n
Axn  i 0
 D/K 
lx lx

34
 
n 1
C x i Dxn M x  M xn D
Axn  i 0
 D/K    D / K  xn
Dx Dx Dx Dx

Axn  i 0 i| q xi  v i 1  D / K n p x  v n
n 1

Whole life annuity

Excel function: Dax (x,i,in_arearrs,frequency,deferred)



l
i 1 x  k  i
 v k i
k| a x 
lx

a x  i 1 k i p x  v k i

k|



D x  k i N x  k 1
k| ax  i 1

Dx Dx

a x  ax  1



l
i 0 x  k i
 v k i
k| ax 
lx

ax  i 0 k i p x  v k i

k|



D x  k i N xk

k| a x  i 0

Dx Dx

Temp annuity
Excel function: Daxn (x,N,i,in_arearrs,frequency,deferred)

  v k i
n
l
i 1 x  k  i
k| a xn 
lx

a xn  i 1 k i p x  v k i
n
k|


n
D x  k i N x  k  n 1
k| a xn  i 1

Dx Dx

a xn  axn  1

k|
xn  k 1| a xn
a

35
 
n 1
l
i 0 x  k i
 v k i

k | a xn 
lx

axn  i 0 k i p x  v k i
n 1
k|


n 1
D x  k i N xk n
k| axn  i 0

Dx Dx

Reserves
Endowment reserve
Excel function: tVx_ Endowment (x,n,t)

t Vx  Ax t ,nt  Pzn  a
x t ,nt

Dx N x t  N x  n
Vx  1  
Dx t N x  N x  n
t

Whole life reserve

Excel function: tVx_ Whole_live (x,t)

Vx  A1 x t  Pz  a
t
x t

Dx N x t
t Vx  1  
Dx t N x

Temp insurance reserve

Excel function: tVx_ Temp_insurance (x,n,t)

Vx  A1 xt ,nt  Pzn  axt ,nt

t

M x t  M x  n M x  M x  n N x t  N x  n
Vx   
N x  N xn
t
Dx t Dx t

Pure endowment reserves

Excel function: tVx_ Pure_endowment (x,n,t)

Vx  nt E x t  Pzn  a
t
x t ,nt

Dx  n N x  N x t
Vx  
Dx t N x  N x  n
t

Deferred life anuity reserves

Excel function:

36
For t<k

V x  k t | a
t
x t  k Px a
x t ,k t

N x  k N x  N x t
Vx 
D x t N x  N x  k
t

For t>=k

V x  ax t
t

N x t
tVx 
Dx t

Regular netto premium

Pure endowment regular
Excel function: regular_Pure_endowment (x,n,i)

Ex
Pxn  n

axn

Dx  n
Pxn 
N x  N xn

l xn  v n
Pxn 

n 1
l
i 0 x i
 vi

p  vn
Pxn  n x


n 1
i 0 i
px  vi

Whole life regular

Excel function: regular_Whole_life (x,n,i)

Ax
Px 
ax



M C x i
Px  x  i 0

Nx Nx

 d

x i  v i 1
Px  i 0

 l

i 0 x i
 vi

37
 

q x  v i 1
Px  i  0 i|



i 0 i
px  vi

Temp insurance regular

Excel function: regular_netto_Temp_insurance (x,n,i)

A1 xn
Pxn 
axn

i 0 C xi
n 1
M  M xn
Pxn  
N x  N xn N x  N xn


n 1
d
i 0 x i
 v i 1
Pxn 

n 1
l
i 0 x i
 vi


n 1
q  v i 1
Px  i  0 i| x


n 1
i 0 i x
p  vi

Endowment regular
Excel function: regular_netto_Endowment (x,n,i,K,D)

Axn A1 xn  D / K  n E x
Pxn  
axn axn

M x  M x  n  D / K  Dx  n
Pxn 
N x  N xn


n 1
d x i  v i 1  D / K  l x  n  v n
Pxn  i 0


n 1
l
i 0 x i
 vi


n 1
q x  v i 1  D / K  n p x  v n
Pxn  i  0 i|


n 1
i 0 i
px  vi

38
Tutorial of MS Excel application

1 2

3
4

Input information:
Fill input information to yellow cells. See in part 1.

Output results:
Results are automatically calculated in green part 2.

Different ways of calculation:

See part 3, different approaches to obtain same result based on input information.

Detailed application of formulas:

Different approaches from part 3 are described in part 4 in detail.

39