程序代写代做代考 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 OSFI’s 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 we’ll 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!)
