Excel代写-SMM065

MSc in Actuarial Science

SMM065 – Contingencies

Coursework Assignment – Deadline 4pm on 29th March 2021

This coursework consists of 2 questions. This is an individual piece of work and so you
must work entirely on your own. The penalties for collusion are high – so please work on

The background to the problem

You are working for a life insurance company which issues 15-year term insurance policies
to insured lives aged 55.

For each policy, the sum insured, which is payable at the end of the year of death, is
£150,000.

The annual premium under the policy is £1,850 and it is payable at the beginning of each
year that the policy is in force.

The insurance company holds reserves at the end of each year, except the last, of 80% of

There is no benefit payable when a policyholder withdraws from the policy.

The insurance company uses the following basis:

Mortality (dependent rate): AM92 select life table

Withdrawal (dependent rate): 2% for years 1 to 10; 1% for years 11 to 15

Interest earned on cash flow and reserves: 5% per annum

Expenses:

The expenses associated with the policy, which are incurred at the time of the premium
payment, will be £1,100 in the first year; the renewal expenses will be £200 in the second
year and then increase with inflation at the rate of 4% per annum in subsequent years.

(a) The expected profit arising in each of the 15 years, provided that the policy is in force at
the beginning of the year.

(b) The expected present value of future profits arising from the policy using a risk discount
rate of 9% per annum.

(c) The profit margin arising from the policy using a risk discount rate of 9% per annum.

(d) The internal rate of return on the above assumptions.

(e) The premium that the insurance company should charge in order to achieve a profit

(i) 5%, and
(ii) 20%

You should use a risk discount rate of 9% per annum in both cases.

Question 1

Carry out the calculations requested by your boss in (a) to (e) above.

You should use Excel to perform the calculations but do not submit the Excel file that you
have used for your calculations. Instead, print and attach all the tables that you think are
necessary to justify your answers. Make sure that each column has a heading and that it is
clear how each column has been produced from the previous columns. In other words, your
tables should use the same format as, say, shown in Tables 1 and 2 of the notes.

[25 marks]
Question 2

The marketing manager has seen the spreadsheets that you produced in carrying out the
calculations in Q1. They said that they were always terrible at maths at school but, as
marketing manager, they feel that it would be good for them to understand the calculations
that underlie the work you’ve done in (a) to (d).

Please write a memo for the marketing manager that explains how the calculations have
been performed in (a) to (d).

words you have used.

You need to communicate in a way which is appropriate for the marketing manager to
understand. Communications skills are prized by the Actuarial Profession!

[15 marks]