**PROFIT TEST **

**MODELLING IN LIFE **

**ASSURANCE USING **

**SPREADSHEETS **

**PART ONE **

### Erik Alm

### Peter Millington

**Profit test modelling in life assurance **
1. Introduction

The aim of this brief is to demonstrate the development of profit test models in life assurance using spreadsheets. We will work through several illustrative examples step-by-step and the degree of complexity will increase from one example to the next. We will start with a single policy excluding loadings and gradually work our way to an entire portfolio of policies where we also include investment return, expenses, loadings and maturity benefits. In part two we will build more advanced models where we will include surrenders, paid-ups and mortality risk.

It is very common in life assurance that the life office has high initial expenses when writing a new policy. This cost could be commission to the sales agent but could also be internal costs for underwriting or for IT systems. This leads to a negative cash flow or negative result for the life office at the inception of a life policy.

The premiums charged by a life office are calculated in such a way that the present value of the premiums should be equal to or exceed the present value of the future benefits and expenses. If not, the policy is written at an expected loss which, if it is done consistently, would threaten the solvency of the life office.

Letting the present value of the premiums being equal to the present value of the benefits and expenses is not enough. In order for the life office to be able to write new business, it needs to put up risk capital. For a proprietary company, this is done by shareholders who expect return on their share capital. For a mutual company this is done by the existing policyholders, who expect the surplus accumulated in the company not to be diluted by the writing of new policies that do not contribute to this surplus.

The life office usually sets internal rules determining the minimum profit to be emerging from a new life policy or new block of life policies written. One such rule could be that the present value of the premiums should exceed the present value of the benefits by a certain percentage. Another way of expressing profit requirements for a life policy is to state when the initial expenses are repaid at a stated internal discount rate. We will mainly work with this type of profit requirement in this brief. 2. Unit-linked assurance

2.1. A policy

Let us first start with a unit-linked policy as an example.

In a typical unit-linked assurance policy the financial risk is born by the policyholder. This differs from traditional assurance where the products typically provide guaranteed benefits at maturity, death or surrender. In unit-linked assurance, premiums are invested in a fund of the choice of the policyholder after deductions for expenses and mortality. The premium reserve will thus (in most cases) be defined retrospectively, using the investment return earned by the fund.

It should be noted that there are unit-linked policies sold which provide some guarantees. One typical guarantee is a guaranteed benefit of at least the sum of premium paid. In our discussion, we will assume that no guarantees are provided.

Let
*t*

*V*

= premium reserve at year t
*t*

*P*

= premium paid at the beginning of the year t
*t*

*i*

= investment return of the fund during the period
Assuming zero initial expenses and no mortality risk the premium reserve at time t is expressed as:

### )

### 1

### (

### *

### )

### (

*t*1

*t*

*t*

*t*

*V*

*P*

*i*

*V*

### =

_{−}

### +

### +

(1)Expression (1) shows the development of the fund in the time-discrete case. The keen student could here and in later examples construct the corresponding formula in the time-continuous case.

This type of policy could, just as well as a traditional policy, be studied analytically. We will however instead mainly study it in a more straightforward way in a spreadsheet environment. The main reason for this is that the assumptions used do often not lead to a nice analytical formula, like the Makeham formula. Another important advantage of this method is that the student could much easier see what happens during the lifetime of the policy rather than just seeing one figure being the result of a long formula. Also, it is easier in a spreadsheet to create ‘what if’ situations, i.e. to test the effect of changes in assumptions and to see how these changes affect the policy in different periods.

We will look at some principle problems and also give some practical tips on how this type of study is best done in a spreadsheet environment.

We will generally assume a level premium is paid, i.e.

*P*

*P*

_{t}### =

*, 0<t=d,*

*where d is the duration of the policy and t denotes time. *

We choose a policy where annual premiums of 100 units are paid for ten years and express this as in the following:

### 100

### =

*t*

*P*

*, 0<t=10*

*We will often use only P to denote a level premium. *

Let us look at a very simple table illustrating this in a table taken from an Excel (or Lotus or MoSeS) spreadsheet.

Policy duration 10 years Premium 100 per year

Year Premium 1 100 2 100 3 100 4 100 5 100 6 100 7 100 8 100 9 100 10 100

One important thing to note here is that all parameters should be stated explicitly in any Excel spreadsheet used for this type of calculations. It should always be very easy to change the parameters and study the effects of such change. If

### 50

### =

*t*

*P*

*for 0<t=5*we should then easily get:

Policy duration 5 years

Premium 50 per year

Year Premium 1 50 2 50 3 50 4 50 5 50 6 0 7 0 8 0 9 0 10 0

Let us assume that the investment return is zero. At the end of the policy a maturity benefit is paid, consisting of the sum of the premiums:

Policy duration 10 years Premium payment 10 years Premium 100 per year

Year Premium Maturity benefit

1 100 2 100 3 100 4 100 5 100 6 100 7 100 8 100 9 100 10 100 -1000

We show the maturity benefit as negative, since it represents an outgo for the life office.

Let us also include the cash flow to the life office: Policy duration 10 years

Premium payment 10 years Premium 100 per year

Year Premium Maturity benefit Cash flow

1 100 100 2 100 100 3 100 100 4 100 100 5 100 100 6 100 100 7 100 100 8 100 100 9 100 100 10 100 -1000 -900

The premiums the life office receives years 1 to 10 must be reserved, since it will be needed to pay for the maturity benefit in year 10. The development of the premium reserve is given by (assuming zero investment return):

*t*
*t*
*t*

*V*

*P*

*V*

### =

_{−}1

### +

or*P*

*t*

*V*

*t*

### =

### ∗

The maturity benefit

*C*

*at time d is given by*

_{d}*P*

*d*

*V*

*C*

*d*

### =

*d*

### =

### ∗

### 0

### =

*t*

*C*

*for t ? d.*

Policy duration 10 years Premium payment 10 years Premium 100 per year

Year Premium Maturity benefit Cash flow Reserve

1 100 100 100 2 100 100 200 3 100 100 300 4 100 100 400 5 100 100 500 6 100 100 600 7 100 100 700 8 100 100 800 9 100 100 900 10 100 -1000 -900 0

We here use a minus sign for the maturity benefit, since it enters the cash flow as negative.

The reserve increases with the premiums paid and decreases with the maturity benefit paid out and we note that it is zero after the maturity of the policy just as we would expect.

For unit-linked business, the reserve (at least in simple cases) consists of savings belonging to the policyholder, where the policyholder bears the financial risks connected with the reserve. In such case, the reserve is often called the fund and the cash flow to and from the fund is not included when one studies the cash flow from the life office's point of view. The fund is like a bank account and is treated as such in US GAAP, the general accepted accounting principles in the US.

In our simplified example, the cash flow is given by

### 0

1### +

### −

### −

### =

### =

*t*

_{−}

*t*

*t*

*t*

*t*

*V*

*P*

*C*

*V*

*CF*

or in spreadsheet format:
Policy duration 10 years Premium payment 10 years Premium 100 per year

Year Premium Maturity benefit Fund Cash flow

1 100 100 0 2 100 200 0 3 100 300 0 4 100 400 0 5 100 500 0 6 100 600 0 7 100 700 0 8 100 800 0 9 100 900 0 10 100 -1000 0 0

In order to avoid having different formulae for year one and subsequent years, we include the value of the fund at the beginning of the year and the value of the fund at the end of the year. We have divided the table into two parts, one showing the development of the fund and one showing the cash flow to the life office.

Policy duration 10 years Premium payment 10 years Premium 100 per year

Year Fund in Premium Maturity benefit Fund out Cash flow

1 0 100 -100 0 2 100 100 -200 0 3 200 100 -300 0 4 300 100 -400 0 5 400 100 -500 0 6 500 100 -600 0 7 600 100 -700 0 8 700 100 -800 0 9 800 100 -900 0 10 900 100 -1000 0 0

For practical reasons, we use the convention that the fund out is shown with a minus sign (it is positive for the client but is a liability for the life office).

2.2. Investment return

We have up to now assumed that the money in the fund will earn no return. In real life, this money is invested in financial assets, in equities or bonds or both. The investment return includes dividends on shares and realised or unrealised gains on shares or bonds. We will assume that the investment return is fixed at a rate of

*i*

*per*

_{t}*time period t, even though the fund value might change continuously.*

The fund value and cash flows are given by:

### )

### 1

### (

### )

### (

_{t}_{1}

_{t}

_{t}*t*

*V*

*P*

*i*

*V*

### =

_{−}

### +

### ∗

### +

*t*

*t*

*t*

*t*

*t*

*t*

*V*

*P*

*i*

*C*

*V*

*CF*

### =

### (

_{−}

_{1}

### +

### )

### ∗

### (

### 1

### +

### )

### −

### −

or*t*

*t*

*t*

*t*

*t*

*t*

*t*

*t*

*V*

*P*

*V*

*P*

*i*

*C*

*V*

*CF*

### =

_{−}

_{1}

### +

### +

### (

_{−}

_{1}

### +

### )

### ∗

### −

### −

Assuming a level premium, the maturity benefit at maturity date d is expressed as

### ∑

= + ∗ = =*d*

*t*

*t*

*d*

*d*

*V*

*P*

*i*

*C*1 ) 1 (

Important to remember is that the investment return varies over time, depending on the development of the assets in the fund. Let us now assume that the fund will earn 5% annual return (after taxes and after internal fund expenses).

Policy duration 10 years Premium payment 10 years Expected increase in unit value 5% annually

Premium 100 per year

Year Fund in Premium Interest Maturity benefit Fund out Cash flow

1 0 100 5 0 -105 0 2 105 100 10 0 -215 0 3 215 100 16 0 -331 0 4 331 100 22 0 -453 0 5 453 100 28 0 -580 0 6 580 100 34 0 -714 0 7 714 100 41 0 -855 0 8 855 100 48 0 -1 003 0 9 1 003 100 55 0 -1 158 0 10 1 158 100 63 -1 321 0 0

(In unit–linked business, the value of the fund is often expressed as a number of units, multiplied by the value of a unit. When the underlying assets increase in value, the number of units remains constant while the value of a unit increases. When new premium is added to the fund, the number of unit increases.)

*Problem: What interest rate is needed in order to provide a maturity benefit of 2000? *
*Answer: 12.3% *

This problem could be solved by analytical methods, but a more practical and faster way is to use the problem solving methods of Excel: Goal Seeker or Solver. (Tools, Goal Seeker).

2.3. Initial commission

Up to now, the cash flow for the life office has been zero. We shall now start to look at this cash flow.

The most important cost for the life office in writing business is the initial expenses, and especially the commission paid to the sales agents, being ties agents or brokers. This commission is often paid up-front (i.e. directly after a sale is made) and is often calculated as a percentage (or per mille) of the total premium volume of the contract. We call the percentage a. The initial commission is thus given by

*P*

*d*

*I*

_{1}

### =

*α*

### ∗

### ∗

,*I*

_{t}### =

### 0

### ,

*t*

### ≠

### 1

This initial commission enters as negative cash flow year one. The cash flow formula is now given by:

*t*
*t*
*t*
*t*
*t*
*t*
*t*
*t*
*t*

*V*

*P*

*V*

*P*

*i*

*C*

*I*

*V*

*CF*

### =

_{−}

_{1}

### +

### +

### (

_{−}

_{1}

### +

### )

### ∗

### −

### −

### −

The above expression consists of two parts. The first part is inflow and outflow of the premium reserve. This part does in reality not affect the life office as such but rather the client fund. The second part is the initial commission. Looking at cash flow that affects the life office separately, we have:

*t*
*t*

*I*

*CF*

### =

### −

Let us assume that the commission is 40 per mille of the total premium. For our contract, the total premium is 10*100 = 1000 and the commission is thus 40.

Policy duration 10 years

Premium payment 10 years

Expected increase in unit value 5% annually

Premium 100 per year

Initial commission 4 % of total premium Year Fund in Premium Interest Maturity

benefit

Fund out Com-mission Cash flow 1 0 100 5 0 -105 -40 -40 2 105 100 10 0 -215 0 0 3 215 100 16 0 -331 0 0 4 331 100 22 0 -453 0 0 5 453 100 28 0 -580 0 0 6 580 100 34 0 -714 0 0 7 714 100 41 0 -855 0 0 8 855 100 48 0 -1 003 0 0 9 1 003 100 55 0 -1 158 0 0 10 1 158 100 63 -1 321 0 0 0

In our tables, we show commission and other expenses as negative, since they mean outflow for the life office.

2.4. Premium charges

We have an outflow from the life office in the form of the commission. The life office will need to cover these expenses and this is done by introducing some charges that the policyholder has to pay. One way of doing this is to charge a percentage of each premium paid to the life office. Let us introduce such a charge and let that charge ? be the same as the commission, i.e. 4%.

Introducing premium charges, the development of the premium reserve is given by:
*t*
*t*
*t*
*t*
*t*
*t*
*t*

*C*

*i*

*P*

*V*

*P*

*P*

*V*

*C*

*i*

*P*

*V*

*V*

### −

### ∗

### ∗

### +

### +

### ∗

### −

### +

### =

### −

### +

### ∗

### −

### ∗

### +

### =

_{−}

### )

### (

### )

### 1

### (

### ))

### 1

### (

### (

_{1}

*γ*

*γ*

*γ*

The maturity benefit is

### ∑

= + ∗ − ∗ = =*d*

*t*

*t*

*d*

*d*

*V*

*P*

*i*

*C*1 ) 1 ( ) 1 (

*γ*

The cash flow to the life office is
*t*

*t*

*P*

*I*

*CF*

### =

### ∗

*γ*

### −

Policy duration 10 years

Premium payment 10 years

Expected increase in unit value 5% annually

Premium 100 per year

Initial commission 4 % of total premium Premium charge 4 % of each premium

Year Fund in Prem-ium Charge Inte-rest Matu-rity benefit Fund out Charg e Commi ssion Cash flow 1 0 100 -4 5 0 -101 4 -40 -36 2 101 100 -4 10 0 -207 4 0 4 3 207 100 -4 15 0 -318 4 0 4 4 318 100 -4 21 0 -435 4 0 4 5 435 100 -4 27 0 -558 4 0 4 6 558 100 -4 33 0 -687 4 0 4 7 687 100 -4 39 0 -822 4 0 4 8 822 100 -4 46 0 -964 4 0 4 9 964 100 -4 53 0 -1 114 4 0 4 10 1 114 100 -4 61 -1 270 0 4 0 4

A profit testing study in a spreadsheet environment is normally done vertically the way we have done it up to now. We will however do it horizontally for the remainder of this brief.

Policy duration 10 years

Premium payment 10 years

Expected increase in unit value 5% annually

Premium 100 per year

Initial commission 4 % of total premium Premium charge 4 % of each premium

Year 1 2 3 4 5 6 7 8 9 10 Fund in 0 101 207 318 435 558 687 822 964 1 114 Premium 100 100 100 100 100 100 100 100 100 100 Charge -4 -4 -4 -4 -4 -4 -4 -4 -4 -4 Interest 5 10 15 21 27 33 39 46 53 61 Maturity 0 0 0 0 0 0 0 0 0 -1 270 Fund out -101 -207 -318 -435 -558 -687 -822 -964 -1 114 0 Charge 4 4 4 4 4 4 4 4 4 4 Comm -40 0 0 0 0 0 0 0 0 0 Cash flow -36 4 4 4 4 4 4 4 4 4

The premium charge is shown twice, as an expense for the policyholder and as an income for the life office.

Let us also look at the accumulated cash flow at time t where t <= d. This is given by:

### ∑

= =*t*

*x*

*x*

*t*

*CF*

*AccCF*1 Year 1 2 3 4 5 6 7 8 9 10 Fund in 0 101 207 318 435 558 687 822 964 1 114 Premium 100 100 100 100 100 100 100 100 100 100 Charge -4 -4 -4 -4 -4 -4 -4 -4 -4 -4 Interest 5 10 15 21 27 33 39 46 53 61 Maturity 0 0 0 0 0 0 0 0 0 -1 270 Fund out -101 -207 -318 -435 -558 -687 -822 -964 -1 114 0 Charge 4 4 4 4 4 4 4 4 4 4 Comm -40 0 0 0 0 0 0 0 0 0 Cash flow -36 4 4 4 4 4 4 4 4 4 Accumulated cash flow -36 -32 -28 -24 -20 -16 -12 -8 -4 0

2.5. Net present value

We note in the previous table that the accumulated cash flow amounts to zero at
*maturity date d, which seems to show that income and outgo for the life office are *
equal. The timing of the two is however not equal. The life office has an initial outgo
while the income comes later and the life office will need to borrow externally or use
internal funds to finance this outgo. These funds are not free and the life office must
therefore include the effect of this cost in its calculations. The most common way to
do this is to calculate present values (the future cash flows are discounted to the
present time.)

The general formula for calculation of Net Present Value as per the beginning of year 1 is

### ∑

### ∑

= − = + −_{=}

_{∗}∗ =

*d*

*k*

*d*

*k*

*k*

*k*

*k*

*k*

*n*

*X*

*v*

*X*

*v*

*X*

*X*

*NPV*1 1 0 1 1 0... ) ( where

*r*

*v*+ = 1 1

* is the discount factor and r is the discount rate. *

*k*

*X*

*= cash flow at time k (i.e. at beginning of year k)*One may use the NPV function of Excel to do this calculation.

One must decide on an appropriate discount interest rate. This discount rate should take into account the cost of money for the life office. If the commission is financed through new equity in the company, the cost of money is the return the shareholders want on this new equity (including tax). This might be 15%. If the life office has idle funds which would otherwise be invested, the discount rate should take into account the income which would have been received in such an alternative investment, where one should include the risk involved with investing funds into initial commissions. If the initial commission investment is funded through reinsurance, the cost of this reinsurance could be used for the discount rate.

*We will here assume a discount rate of 10%, giving us a discount factor v=0.90909. *

Policy duration 10 years Discount rate 10%

Premium payment 10 years NPV -13

Expected increase in unit value 5% annually

Premium 100 per year

Initial commission 4 % of total premium Premium charge 4 % of each premium

Year 1 2 3 4 5 6 7 8 9 10 Fund in 0 101 207 318 435 558 687 822 964 1 114 Premium 100 100 100 100 100 100 100 100 100 100 Charge -4 -4 -4 -4 -4 -4 -4 -4 -4 -4 Interest 5 10 15 21 27 33 39 46 53 61 Maturity 0 0 0 0 0 0 0 0 0 -1 270 Fund out -101 -207 -318 -435 -558 -687 -822 -964 -1 114 0 Charge 4 4 4 4 4 4 4 4 4 4 Comm -40 0 0 0 0 0 0 0 0 0 Cash flow -36 4 4 4 4 4 4 4 4 4 Accumulated cash flow -36 -32 -28 -24 -20 -16 -12 -8 -4 0 Discount factor 1 0.909 0.826 0.751 0.683 0.621 0.564 0.513 0.467 0.424 Discounted cash flow -36 4 3 3 3 2 2 2 2 2 Accumulated discounted cash flow -36 -32 -29 -26 -23 -21 -19 -17 -15 -13

We can see that the Accumulated discounted cash flow is equal to –13 at the maturity age. This is the NPV of the cash flow valued at the discount rate of 10%. We

define this as our profit and our profit goal is that the profit should be positive (or at least not negative).

The profit could also be calculated directly by using the NPV function in Excel. Please note that the
Excel formula assumes that all payments are made in arrears i.e.at the end of the period in question,
while we here assume that all payments (except the maturity) are made at the beginning of the period in
*question. The value calculated by Excel must therefore be multiplied by (1+r), in our case 110% in order *
to arrive at the right answer. Using the NPV formula in Excel gives the answer –12, which multiplied by
110% gives –13 as can be found in the lower right hand corner of the table above.

We see that we must use a premium charge greater than 4% in order to break-even,
i.e. a profit of zero. We can calculate the premium that is required for a break-even
situation by setting the NPV of future premium charges equal to the initial
commission.
This gives:
*P*
*d*
*I*
*v*
*P* *t*
*n*
*t*
*t* = = ∗ ∗
∗
∗

### ∑

− =*α*

*γ*1 0 (n=d).

*where n=10, d=10, i=5% and a=4%,*We get

*P*

*d*

*v*

*P*

*t*

*t*= ∗ ∗ ∗ ∗

### ∑

=*α*

*γ*9 0 or 4 . 0 9 0 = ∗ = ∗

### ∑

=*d*

*v*

*t*

*t*

*α*

*γ*

### 4

### .

### 0

### 1

### 1

10_{=}

### −

### −

### ∗

*v*

*v*

*γ*

4
.
0
76
.
6 =
∗
*γ*

The premium charge that will give break-even is

059
.
0
=
*γ*

The same answer could have been found by once again using the Goal Seek or Solver.

2.6. Portfolios, model points

We have up to now looked at a 10-year policy. Let us look at a 5-year policy, assuming an initial commission of 5.9% of the total premium.

Policy duration 5 years Discount rate 10%

Premium payment 5 years NPV 5

Expected increase in unit value 5% annually

Premium 100 per year

Initial commission 4 % of total premium Premium charge 5.9 % of each premium

Year 1 2 3 4 5 6 7 8 9 10 Fund in 0 99 203 312 427 0 0 0 0 0 Premium 100 100 100 100 100 0 0 0 0 0 Charge -6 -6 -6 -6 -6 0 0 0 0 0 Interest 5 10 15 21 26 0 0 0 0 0 Maturity 0 0 0 0 -548 0 0 0 0 0 Fund out -99 -203 -312 -427 0 0 0 0 0 0 Charge 6 6 6 6 6 0 0 0 0 0 Comm -20 0 0 0 0 0 0 0 0 0 Cash flow -14 6 6 6 6 0 0 0 0 0 Accumulated cash flow -14 -8 -2 4 10 10 10 10 10 10 Discount factor 1 0.909 0.826 0.751 0.683 0.621 0.564 0.513 0.467 0.424 Discounted cash flow -14 5 5 4 4 0 0 0 0 0 Accumulated discounted cash flow -14 -9 -4 1 5 5 5 5 5 5

The table above shows that the 5-year policy gives a profit of 5. If we instead calculate the profit of a 15-year policy, we would make a loss of 11. For a 20-year policy, we make a loss of 25. The initial commission formula gets more expensive for long term policies. Let us therefore assume that the agent gets commission for only the first 20 premiums, even if the policy duration is longer. This is a common way to construct sales commission scales. The initial commission is given by:

*P*

*d*

*I*

_{1}

### =

*α*

### ∗

### min(

### 20

### ;

### )

### ∗

Let us now assume that the maturity age is 65 years, x is the age of the assured, (i.e.

*d=65-x) and that the minimum initial age is 20. Let us also assume that the *

distribution of initial age will be even over the age band 20-64 years. We could then calculate the profitability of each initial age and sum the result over all ages as:

NPV =

### ∑ ∑

= − = −_{}

###

###

###

###

###

_{∗}

_{−}

_{∗}

_{−}

### ∗

64 20 65 1 1### )

### 65

### ;

### 20

### min(

### )

### (

*x*

*x*

*t*

*t*

*x*

*v*

*P*

*γ*

*α*

*One could in principle solve for ? from the above expression by setting the total to *
zero to get the break-even situation.

NPV =

### (

### )

### min(

### 20

### ;

### 65

### )

### 0

64 20 65 1 1_{}

### =

###

###

###

###

###

_{∗}

_{−}

_{∗}

_{−}

### ∑ ∑

= − = −*x*

*x*

*t*

*t*

*x*

*v*

*γ*

*α*

A rearrangement of the terms gives

### ∑

### ∑ ∑

= = − = −_{∗}= − ∗ 64 20 64 20 65 1 1 ) 65 ; 20 min(

*x*

*x*

*x*

*t*

*t*

*v*

*x*

*γ*

*α*and then

### ∑ ∑

### ∑

= − = − =### −

### ∗

### =

_{64}20 65 1 1 64 20

### )

### 65

### ;

### 20

### min(

*x*

*x*

*t*

*t*

*x*

*v*

*x*

*α*

*γ*

This could however be a bit complicated to handle. Another problem is that, by using this complex formula, one can not differentiate the profitable from the non-profitable policies. One gets a much better view of the situation by studying the different policies one by one. We study therefore the expression for calculating the profit for a cohort of policies NPV =

### ∑

− = −_{∗}

_{∗}

_{−}

_{∗}

_{∗}

_{−}

*x*

*t*

*t*

*x*

*P*

*P*

*v*65 1 1 ) 65 ; 20 min( ) (

*γ*

*α*

*for x = 20, 21,…,64.*

This is straightforward but could be cumbersome. One common way to simplify the calculations is to use model points. The profits of a 25-year and a 26-year policy are rather equal and the 25-year policy could represent both a 24-year and a 26-year policy. We therefore choose a number of model policies that will represent the rest. Using this principle and letting each 5-year age bands be represented by its middle point, we thus study

NPV =

### ∑

− = −_{−}

_{∗}

_{∗}

_{−}∗ ∗

*x*

*t*

*t*

*x*

*P*

*v*

*P*65 1 1 ) 65 ; 20 min( ) (

*α*

*γ*

*for x = 22, 27,…,62.*This gives:

Maturity age 65 years Discount rate 10%

Expected increase in unit value 5% annually

Premium 100 per year Max commission years 20

Initial commission 4 % of total premium max 80%

Premium charge 6 % of each premium Age Policy duration Profit 62 3 4 57 8 3 52 13 -5 47 18 -18 42 23 -21 37 28 -19 32 33 -17 27 38 -16 22 43 -15 Total -103

This calculation could be done by testing the policy durations one at a time. A quicker way is to use the Data Table function in Excel which gives all values at the same time. Please note that tables are dynamically updated if this function is not turned off (Tools, Calculation, Automatic except tables), why having large tables might lead to heavy update times.

NPV =

### 100

### 5

### .

### 9

### %

### (

### )

### 100

### min(

### 20

### ;

### 65

### )

### 103

62 ... 27 , 22 65 1 1_{}

### =

### −

###

###

###

###

###

_{∗}

_{∗}

_{−}

_{∗}

_{∗}

_{−}

### ∑

### ∑

= − = −*x*

*x*

*t*

*t*

*x*

*v*

*α*

(a)
The result is not good, but it is hard to see how bad it is. We want to know how much
we need to increase the premium charge in order to go break-even. We want to find
*a k such that the profit is equal to zero, i.e.: *

NPV =

### 100

### (

### 5

### .

### 9

### %

### )

### (

### )

### 100

### min(

### 20

### ;

### 65

### )

### 0

62 ... 27 , 22 65 1 1###

### =

###

###

###

###

###

_{∗}

_{+}

_{∗}

_{−}

_{∗}

_{∗}

_{−}

### ∑

### ∑

= − = −*x*

*x*

*t*

*t*

*x*

*v*

*k*

*α*

(b)
Inserting expression (a) in (b) gives

### 103

### )

### (

### %

### 9

### .

### 5

### 100

### )

### (

### )

### %

### 9

### .

### 5

### (

### 100

64 20 65 1 62 ... 27 , 22 65 1 1###

### =

###

###

###

###

###

_{∗}

_{∗}

### −

###

###

###

###

###

###

_{∗}

_{+}

_{∗}

_{∑}

_{∑}

### ∑

### ∑

= − = = − = −*x*

*x*

*t*

*t*

*x*

*x*

*t*

*t*

*v*

*v*

*k*

This then gives

### 103

### 100

### )

### (

### 100

62 ... 27 , 22 1 65 1 1 64 ... 20 65 1 1###

### =

###

###

###

###

###

_{∗}

_{∗}

### ≈

###

###

###

###

###

###

_{∗}

_{∗}

_{∑}

_{∑}

### ∑

### ∑

= − = − = − = −*x*

*t*

*t*

*x*

*x*

*t*

*t*

*v*

*k*

*v*

*k*

Further
### ∑

### ∑

### ∑

_{=}= − = −

### ≈

###

###

###

###

###

###

_{∗}

### ≈

_{64}20 64 20 65 1 1

_{(}

_{)}

### 103

### )

### (

### 100

### 103

*x*

*x*

*x*

*t*

*t*

_{NPV}

_{NPV}

_{P}

_{P}

*v*

*k*

We therefore also include the net present value of the premiums paid for each policy
in our table.
Age Policy
duration
Profit NPV of
premium
62 3 4 274
57 8 3 587
52 13 -5 781
47 18 -18 902
42 23 -21 977
37 28 -19 1 024
32 33 -17 1 053
27 38 -16 1 071
22 43 -15 1 082
Total -103 7 750
This gives
0133
.
0
7750
103
=
=
*k*

The loss is thus -1.33% of the NPV of the total premium. Let us therefore increase the premium charge with 1.4% to 7.4%:

Maturity age 65 years Discount rate 10% Expected increase in unit value 5% annually

Premium 100 per year Max commission years 20

Initial commission 4 % of total premium max 80%

Premium charge 7.4 % of each premium Age Policy duration Profit NPV of premium 62 3 8 274 57 8 11 587 52 13 6 781 47 18 -5 902 42 23 -8 977 37 28 -4 1 024 32 33 -2 1 053 27 38 -1 1 071 22 43 0 1 082 Total 5 7 750

We find that the portfolio has a break-even point with a premium charge of 7.4% Let us now assume that we expect to sell more of some policies and less of others. Most of our new clients are expected to be around 35 years and few are 20 or 60 years. We include this in our calculation by weighting the different policies by their expected sales figures:

NPV =

### ∑

### ∑

= − = −_{}

###

###

###

###

###

_{∗}

_{−}

_{∗}

_{−}

### ∗

64 20 65 1 1### )

### 65

### ;

### 20

### min(

### )

### (

*x*

*x*

*t*

*t*

*x*

*v*

*x*

*W*

*P*

*γ*

*α*

Assume that our portfolio has an average duration of 23 years and has an age distribution as shown in the table below:

Age Policy duration Number of policies 62 3 100 57 8 200 52 13 300 47 18 400 42 23 500 37 28 400 32 33 300 27 38 200 22 43 100 Total 3 000

This gives the following results: Policy duration Number of policies Profit per policy NPV premium per policy

Total profit Total NPV of premium 3 100 8 274 824 27 355 8 200 11 587 2 285 117 368 13 300 6 781 1 746 234 411 18 400 -5 902 -2 096 360 862 23 500 -8 977 -3 845 488 577 28 400 -4 1 024 -1 698 409 489 33 300 -2 1 053 -631 315 791 38 200 -1 1 071 -155 214 118 43 100 0 1 082 5 108 174 Total 3 000 -3 565 2 276 146

The figures in the column Profit per policy are rounded to the nearest integer. When calculating the total profit, non-rounded figures are used.

We have here more of the non-profitable policies and less of the profitable policies. The NPV of the loss is only 0.16% of the NPV of the total premium, why an increase of the premium charge of 0.2% should be enough to make the portfolio profitable. We increase the premium charge to 7.6%.

Maturity age 65 years Discount rate 10%

Expected increase in unit value 5% annually

Premium 100 per year Max commission years 20

Initial commission 4 % of total premium max 80%

Premium charge 7.6 % of each premium Policy duration Number of policies Profit per policy NPV premium per policy

Total profit Total NPV of premium 3 100 9 274 879 27 355 8 200 13 587 2 520 117 368 13 300 7 781 2 215 234 411 18 400 -3 902 -1 374 360 862 23 500 -6 977 -2 868 488 577 28 400 -2 1 024 -879 409 489 33 300 0 1 053 0 315 791 38 200 1 1 071 273 214 118 43 100 2 1 082 221 108 174 Total 3 000 987 2 276 146

As shown in the previous table, we have arrived at a small profit of 987.

In the real world, you might not know the actual age distribution of the portfolio. It is therefore often a good idea to test different reasonably realistic age distributions in the portfolio and choose the least favourable. In our case, we assume that we will sell either the evenly distributed portfolio or the one with the weight on duration 23 and we choose the latter one and thus the premium charge of 7.6%.

We discussed in section 2.5. the choice of discount rate. The result that we arrive at is dependent on the discount rate chosen.

*Answer: There will be a loss of 0.66% of NPV of total premiums. A high discount *
*makes it more expensive to have high initial costs. *

2.7. Fixed costs

Up to now, we have only included the commissions to the sales agents as expenses. These commissions are defined to be proportional to premium volume, why it does not matter if we have sold small or large policies.

Let us now assume that we have an initial fixed expense of 10 for each new policy. The introduction of this new expense leads to a need of increase in charges. One possibility could be to introduce a policy charge of the same amount as the policy expense. Another would be to increase the premium charge. We will choose the latter alternative. We therefore now want to determine how much we need to increase the premium charge to offset this expense.

With a fixed cost, large policies will be more profitable than small policies. We will investigate the effect on a portfolio of policies with different premium. The example below shows the case for one policy with a premium of 100.

Policy duration 10 years Discount rate 10%

Expected increase in unit value 5% annually NPV of profit -9

Premium 100 per year NPV of premium 68

Initial commission 4 % of total premium max 80%

Internal initial expenses 10 per policy Max commission years 20 Premium charge 7.6 % of each premium

Year 1 2 3 4 5 6 7 8 9 10 Fund in 0 97 199 306 418 536 660 790 926 1 070 Premium 100 100 100 100 100 100 100 100 100 100 Charge -8 -8 -8 -8 -8 -8 -8 -8 -8 -8 Interest 5 9 15 20 26 31 38 44 51 58 Maturity 0 0 0 0 0 0 0 0 0 -1 220 Fund out -97 -199 -306 -418 -536 -660 -790 -926 -1 070 0 Charge 8 8 8 8 8 8 8 8 8 8 Comm -40 0 0 0 0 0 0 0 0 0 Internal expenses -10 0 0 0 0 0 0 0 0 0 Cash flow -42 8 8 8 8 8 8 8 8 8 Accumulated cash flow -42 -35 -27 -20 -12 -4 3 11 18 26 Discount factor 1 0.909 0.826 0.751 0.683 0.621 0.564 0.513 0.467 0.424 Discounted cash flow -42 7 6 6 5 5 4 4 4 3 Accumulated discounted cash flow -42 -35 -29 -23 -18 -14 -9 -5 -2 1

If we do this calculation for different policy premiums and durations, we get:

Policy duration x years Discount rate 10%

Expected increase in unit value 5% annually NPV of profit -9

Premium 100 per year NPV of premium 68

Initial commission 4 % of total premium max 80%

Internal initial expenses y per policy Max commission years 20 Premium charge 7.6 % of each premium

Profit Annual premium Duration 10 40 100 250 1000 3 -9 -6 -1 12 78 8 -9 -5 3 21 116 13 -9 -7 -3 8 64 18 -10 -11 -13 -19 -44 23 -11 -12 -16 -24 -67 28 -10 -11 -12 -15 -32 33 -10 -10 -10 -10 -10 38 -10 -9 -9 -7 4 43 -10 -9 -8 -4 12

If all policy durations and premiums were evenly distributed, we could just sum up a total and get –156. Let us now however assume that the policies are expected to be distributed as follows: 10 40 100 250 1000 3 0.80% 1.60% 1.00% 0.40% 0.20% 8 1.60% 3.20% 2.00% 0.80% 0.40% 13 2.40% 4.80% 3.00% 1.20% 0.60% 18 3.20% 6.40% 4.00% 1.60% 0.80% 23 4.00% 8.00% 5.00% 2.00% 1.00% 28 3.20% 6.40% 4.00% 1.60% 0.80% 33 2.40% 4.80% 3.00% 1.20% 0.60% 38 1.60% 3.20% 2.00% 0.80% 0.40% 43 0.80% 1.60% 1.00% 0.40% 0.20%

As before, we multiply the result for each type of policy with the probability weight of that policy in order to arrive at the portfolio probability. We thus multiply the profit matrix with the distribution matrix and arrive at the following result.

Profit 10 40 100 250 1000 3 -0.07 -0.10 -0.01 0.05 0.16 8 -0.14 -0.16 0.05 0.17 0.46 13 -0.22 -0.34 -0.08 0.10 0.38 18 -0.33 -0.73 -0.54 -0.30 -0.35 23 -0.42 -0.98 -0.79 -0.49 -0.67 28 -0.33 -0.70 -0.49 -0.25 -0.26 33 -0.24 -0.48 -0.30 -0.12 -0.06 38 -0.16 -0.30 -0.17 -0.05 0.01 43 -0.08 -0.15 -0.08 -0.02 0.02 Total –9.53

Premium 10 40 100 250 1000 3 0.22 1.75 2.74 2.74 5.47 8 0.94 7.51 11.74 11.74 23.47 13 1.88 15.00 23.44 23.44 46.88 18 2.89 23.10 36.09 36.09 72.17 23 3.91 31.27 48.86 48.86 97.72 28 3.28 26.21 40.95 40.95 81.90 33 2.53 20.21 31.58 31.58 63.16 38 1.71 13.70 21.41 21.41 42.82 43 0.87 6.92 10.82 10.82 21.63 Total 1074

The profit in relation to the premium is -9.53/1074 = –0.9%. Let us try with a premium charge of 8.5%. We get a profit very close to zero as expected. We have thus found our break-even point.