Strip a , a or an down and you find promises of one shape: an amount of money, paid at a future date, if a named person is then alive, or then dead. Price the shape and you can price the products. The tool is the , EPV, and for one promise it is three numbers multiplied together:
EPV = amount × probability of payment ×
That line values the promise inside everything from a £10-a-month term policy to a multi-billion-pound . The only thing that changes is how many of those promises you add up.
The ingredients
From the Acttuary Standard Table (built in the first lesson of this module), for a life aged 60:
- Survivors: l₆₀ = 10,000 and l₆₅ = 9,654.8, so ₅p₆₀ = 0.96548.
- Deaths in each of the next five years: d₆₀ = 50.0, d₆₁ = 59.7, d₆₂ = 69.2, d₆₃ = 78.6, d₆₄ = 87.7.
And at 4% a year, where v = 1 ÷ 1.04:
| Years ahead k | 1 | 2 | 3 | 4 | 5 |
|---|---|---|---|---|---|
| v^k | 0.961538 | 0.924556 | 0.888996 | 0.854804 | 0.821927 |
Each factor answers: what is £1 then worth now, if money earns 4% in between? £1 due in five years is worth 82.2p today, because 82.2p invested at 4% grows back into £1.
The simplest promise: a pure endowment
A pays a fixed sum on a future date if the policyholder is then alive: no death benefit, nothing along the way. Take one: £10,000 at age 65, sold to a life now aged 60.
= 10,000 × 0.96548 × 0.821927 = £7,935.54, the fair price of that promise.
The price sits below £10,000 for two separate reasons, and it pays to keep them separate. The survival haircut removes 3.452%, because some policyholders will not live to collect. The haircut removes a further 17.8%, because money five years away is cheaper than money today.
Fair here has a precise meaning. Collect £7,935.54 from each of 10,000 60-year-olds: £79.36m. Invest it at 4% for five years and it grows to £96.55m. The table expects 9,654.8 survivors, each owed £10,000: £96.55m, so the fund empties to the last pound. Per policyholder: £7,935.54 × 1.04⁵ = £9,654.80, which is exactly £10,000 × 0.96548, the expected payout per life who signed up.
Five promises stapled together: term assurance
Now the classic protection product. A 5-year for £100,000 on our 60-year-old pays out if death occurs within the term, at the end of the year of death in the standard simplification. That is five promises: £100,000 at the end of year one if death comes in year one, at the end of year two if in year two, and so on. Each gets the same three-number treatment, using the probability of dying in that particular year, dx ÷ l₆₀:
| Year k | Dies aged | Probability dx ÷ l₆₀ | Discount v^k | of term (£) |
|---|---|---|---|---|
| 1 | 60-61 | 0.00500 | 0.961538 | 480.77 |
| 2 | 61-62 | 0.00597 | 0.924556 | 551.96 |
| 3 | 62-63 | 0.00692 | 0.888996 | 615.19 |
| 4 | 63-64 | 0.00786 | 0.854804 | 671.88 |
| 5 | 64-65 | 0.00877 | 0.821927 | 720.83 |
| Total | 0.03452 | 3,040.62 |
= £3,040.62, the fair single premium for £100,000 of five-year cover on this basis. It is not the price a customer would be quoted: real premiums add expenses, profit and the cost of holding capital on top, and the next lesson puts them there. Add the printed column and you get £3,040.63, a penny more, because five rows were each rounded to the nearest penny before being added. The total comes from the unrounded figures, £3,040.6209. Round at the end, not on the way, and say so when a checker asks why your total does not match their addition.
Two checks are worth building in. The probability column sums to 0.03452, which is exactly 1 minus 0.96548: dying within the term and surviving it are the only two exits, so the column must total the complement of ₅p₆₀. And 0.03452 × £100,000 = £3,452 is what the cover would cost with no discounting at all; the £411 gap is the interest the premium is assumed to earn between coming in and the claims going out.
Run it, then bend it
Both prices in one short loop. Press Run, then change i and run again: 0.03 first, then 0.05.
At 3% both prices rise: the to about £8,328, the to about £3,136. At 5% both fall. Nothing about the people changed; only the reward for waiting did. Every life insurance promise pays in the future, so all else equal a lower makes it dearer to fund.
There is no separate theory for each product. An is a row of , one per year of survival. A is a whose term never ends. Every a life actuary builds is amount × probability × , summed. Prove the scaling to yourself:
Work it out
A woman aged 75 is promised £3,000 at the end of each of the next three years, payable only if she is alive on the payment date. Her insurer's table shows l75 = 6,000, l76 = 5,820, l77 = 5,610 and l78 = 5,370, and pricing is at 6% a year, so the discount factors for the three dates are 0.943396, 0.889996 and 0.839619. What is the EPV of the three payments?
Build the pricing sheet
The table above is what a pricing actuary would put in a grid, and it is worth building once yourself: one row per year of the term, three columns for the three numbers, and two reconciliations at the bottom.
Spreadsheet: build it, don’t type it
Price the term assurance and the endowment
The basis sits in A2:B6 and the five dx figures in B9:B13, all from the Acttuary Standard Table. Build the pricing table and the read-outs. 1. C9:C13: the probability of dying in that year of the term, dx ÷ l60. The divisor is the same in all five rows, so lock the reference to B3 with dollar signs before you copy the formula down. 2. D9:D13: the discount factor for that year, 1 ÷ (1 + i) to the power k. The year number k is already sitting in column A, so no formula needs a typed exponent. 3. E9:E13: the EPV of that year's slice of cover, being sum assured × probability × discount factor. 4. C14 and E14: the two column totals. 5. H2: a check using IF and ROUND that returns OK when the probability column plus the five-year survival probability l65 ÷ l60 comes to exactly 1, and CHECK when it does not. 6. H3: the pure endowment, being the amount in B6, payable at 65 only if the life is then alive, rounded to the penny. 7. H4: what the term cover would cost with no discounting at all. H5: the interest the premium is assumed to earn between coming in and the claims going out, to the penny. H4 is not marked, but H5 leans on it.
Cells to fill: H2, H3, H5, D9, C14, E14
- Given data, locked
- Yours to fill
| Row | A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Basis | Value | Answer | Value | ||||||
| 2 | Interest rate i | 0.04 | Probability check | |||||||
| 3 | l60 | 10000 | Pure endowment EPV (£) | |||||||
| 4 | l65 | 9654.8 | Term cost with no discounting (£) | |||||||
| 5 | Term sum assured (£) | 100000 | Interest assumed earned (£) | |||||||
| 6 | Endowment amount (£) | 10000 | ||||||||
| 7 | ||||||||||
| 8 | Year k | dx | Probability dx/l60 | Discount v^k | EPV of term (£) | |||||
| 9 | 1 | 50 | ||||||||
| 10 | 2 | 59.7 | ||||||||
| 11 | 3 | 69.2 | ||||||||
| 12 | 4 | 78.6 | ||||||||
| 13 | 5 | 87.7 | ||||||||
| 14 | Total | |||||||||
| 15 | ||||||||||
| 16 | ||||||||||
| 17 |
How this grid works: typing, filling, references, layout
Moving and selecting
- Move a cell at a time
- : arrow keys
- Run to the end of a block
- : Ctrl+arrow
- Back to A1, or out to the last cell used
- : Ctrl+Home / Ctrl+End
- Select a range
- : Shift+arrow, or shift-click the far corner
- Select to the end of a block
- : Ctrl+Shift+arrow
- Take a whole row, or a whole column
- : Shift+Space / Ctrl+Space, or click its header
- Take several rows or columns
- : drag along the headers, or shift-click
- Take the lot
- : Ctrl+A
Entering and editing
- Start an entry
- : just type, or use the formula bar
- Commit it and move down, or up
- : Enter / Shift+Enter
- Commit it and move right
- : Tab
- Change your mind mid-entry
- : Escape
- Open what is already in the cell
- : F2, or double-click it
- Empty the selected cells
- : Delete
- Find a function, then its arguments
- : start typing the name; the open bracket lists the arguments in order, with the one you are writing picked out
Filling and copying
- Fill a formula down the column
- : Ctrl+D, or drag the small square at the corner of the selection
- Fill it right along the row
- : Ctrl+R
- Copy, or cut
- : Ctrl+C / Ctrl+X
- Paste it, references moving as they go
- : Ctrl+V
- Paste the numbers instead of the formulas
- : Ctrl+Shift+V
- Fill a whole block from one cell
- : copy it, select the block, paste
- The same four with a mouse
- : right-click a cell: the shortcuts are printed beside them
References
- Put a cell into a formula without typing its address
- : click it, or press an arrow after =, a bracket or an operator
- Grow that reference into a range
- : Shift+arrow, or drag across the cells
- Stop a reference shifting when the formula copies
- : F4, which adds the dollar signs
- Give the arrow keys back to the text
- : F2 swaps them between picking cells and moving the cursor
- See the range you picked before you commit it
- : each reference takes a colour and outlines the cells it points at; the same one twice keeps its colour
- See what a finished formula reads
- : select its cell: the cells it reads are outlined
Rows and columns
- Make a column wider, or a row taller
- : drag the line between two headers, or Alt+Shift+arrow
- Fit it back to what is in it
- : double-click that line, or Alt+Shift+0
- More room to work in
- : the blank rows and columns past the data, and the Add rows and Add columns buttons
Columns start as wide as what is in them, and nothing you do out in the blank space is marked.
The view
- Zoom in or out
- : Ctrl++ / Ctrl+-, the buttons above the grid, or Ctrl with the wheel
- Back to how it was drawn
- : Ctrl+0, or Reset view
- Find out what a shade means
- : the key above the grid, which lists only the shades this exercise uses
Zoom, widths and heights are how you are looking at the grid, not what is in it. None of it is marked.
Have a go and press Check. The worked solution opens up after your first real attempt.