[SOLVED] Excel Stats 3H03 Winter 2017

Stats 3H03 Winter 2017

Introduction to Actuarial Mathematics

BONUS Assignment

BACKGROUND

OSFI (Office of the Superintendent of Financial Institutions Canada) has introduced new standards

with respect to Capital Adequacy Testing. They are due to go live January 1, 2018.

A key component is the requirement to create cash flows under a variety of scenarios.

If you are interested in reading more details you could read section 6.2 and 6.6 of OSFIs Life

Insurance Capital Adequacy Test (LICAT). A copy will be posted under the bonus section of A2L.

GOAL

We will test the impact of one traditional product Whole Life

The Whole Life product is subject to 3 risks:

a) mortality risk

b) lapse risk (too complex for this exercise)

c) expense risk

We will want to calculate the expected cash flows under the following scenarios for the whole life

insurance product and determine the APV of these cash flows.

a) mortality risk level risk

b) mortality risk trend risk

c) mortality risk catastrophic risk

d) expense risk level and trend combined risk

The objective will be to create an excel/libre file with a minimum of 5 tabs that will contain the future

cash flows under the best estimate assumption and 4 risk scenarios described above PLUS the present

value of the expected cash flows.

The best estimate assumption is the starting set of assumptions.

POLICY DETAILS

Product sold = Whole Life

Face amount = 100,000

Status = Single life

Life

Sex Male

Smoker status Non Smoker

Issue age 45

Premiums = payable for 20 years

Cash Values = see cash value table

Participating/Non participating = Participating

Dividends = see dividend scale table

Policy Year Cash Value per $1000 Dividend per $1000

1 0 0

2 0 0

3 0 0

4 15 0

5 26 1.1

6 39 1.1

7 53 1.1

8 66 1.1

9 80 1.1

10 95 1.2

11 110 1.2

12 125 1.2

13 140 1.2

14 155 1.2

15 170 1.5

16 190 1.5

17 210 1.5

18 220 1.5

19 240 1.5

20 260 1.6

21 280 1.6

22 320 1.6

23 330 1.6

24 335 1.6

25 340 1.8

26 350 1.8

27 355 1.8

28 365 1.8

29 370 1.8

30 380 2

31 390 2

32 400 2

33 410 2

34 415 2

35 425 2.2

36 435 2.2

37 445 2.2

38 455 2.2

39 465 2.2

40 475 2.4

41 490 2.4

42 500 2.4

43 510 2.4

44 520 2.4

45 530 2.6

46 540 2.6

47 555 2.6

48 565 2.6

49 580 2.6

50 590 2.8

51 600 2.8

52 620 2.8

53 640 2.8

54 680 2.8

55 720 3

56 780 3

57 840 3

58 900 3

59 950 3

60 1000 3

Step 1. Premiums are calculated based on the best estimate assumptions. You will calculate the gross

premium, payable at the beginning of the year, for 20 years.

Step 2. Calculate the best estimate cash flows:

a) expected death benefits

b) expected surrender benefits

c) expected dividend payments

d) expected premium payments

e) expected expenses

f) the net cash flows (outflows inflows)

Step 3. Calculate cash flows under each of the shock scenarios requested.

Step 4. Calculate the actuarial present values of each component of the cash flows as well as the net

cash flows.

STARTING ASSUMPTIONS

Mortality = Stats Canada table

Mortality improvement assumptions = CIA recommended mortality improvement factors, see attached

document.Use the improvement factors in the same way that the CIA recommends, namely for 25

years and using 50% of the rates, starting at the valuation date. We will assume the valuation date and

issue date are the same, thus well apply mortality improvement from issue. See page 4 point 1 for CIA

instructions and page 5 & 6 for an example of the application of the improvement rates.

Lapse/Surrender = 5% for 1st 10 years, 4% for next 10 years, 3% for next 20 years, 2% for next 10

years, and 1% for the remainder of years

Per policy expense = 50 per year, payable at BOY, for the life of the contract

Percent of premium expense for commissions = 12% per year, payable at BOY

Percent of premium expense for premium tax = 2% per year, payable at BOY

Interest rate = 10%

We will assume UDD in the independent tables.

Scenario 1 mortality risk level risk

The mortality rate is reduced by 15% for each age and duration. (We are assuming the policy is death

supported as defined by OSFI).

Scenario 2 mortality risk trend risk

The mortality improvement is reduced by 75% at all policy durations. Again we are assuming the

policy is death supported as defined by OSFI.

Scenario 3 mortality risk catastrophic risk

The mortality assumptions suffers an absolute increase in the number of deaths per thousand lives

insured. For Canada this absolute increase is 1.0 per thousand lives.

Scenario 4 expense risk all types

The shock is an increase of 20% in the first year followed by a permanent increase of 10% in all

subsequent years. Premium taxes are excluded from this increase.

DESIGN OF EXCEL/LIBRE FILE

Points will be given for a design that will easily facilitate changes to assumptions especially the

starting assumptions. Thus the shock assumption should not be hard-coded in the calculation but rather

retrieved from a visible field to allow changes to be made.

My recommendation would be to create future cash flows for each type of cash flow, sum them

together to get the total net cash flow and as a last step calculate the APV.

RULES OF PARTICIPATION

1. you can work in teams with a maximum of 5 people

2. identify the team members

3. you can complete fewer scenarios but points will be assigned on a pro-rata basis

4. the due date is April 13th

POINTS ASSIGNED

1. creating the best estimate cash flows/APV will be worth 7/12

2. creating the mortality level scenario will be worth 1/12

3. creating the mortality trend scenario will be worth 2/12

4. creating the mortality catastrophic risk scenario will be worth 1/12

5. creating the expense (all types) risk scenario will be worth 1/12

Ask questions if you need clarification.

This exercise will use skills you learned in chapter 3 (mortality improvement), chapter 4 (life

insurance), chapter 5&6 (premium calculation), chapter 8 (multiple decrements) and of course improve

your excel skills (which are in demand in the marketplace!)