Skip to content
Acttuary

Inside Pensions / The valuation cycle

Valuing a promise

A scheme is a stack of promises: so much a year, to this member, from that date, for as long as they live. There is no pot with anyone's name on it. One collective fund backs the whole list, and the payments stretch decades into the future. Before you can ask whether the fund is big enough, you have to answer a harder question first: what is a promise to pay money in ten or thirty years' time worth today?

The tool for that question is , and nearly everything else in this course is an application of it.

One payment

Start with the simplest promise a scheme could hold: £5,000, payable in exactly ten years. You do not need £5,000 today, because money invested in the meantime grows. If you can earn 5% a year, ten years of growth multiplies your money by 1.05^10, about 1.6289. Run that backwards and you need only:

£5,000 ÷ 1.05^10 = £3,069.57

Check it forwards: £3,069.57 in the fund today, earning 5% a year, grows to the full £5,000 just as the payment falls due.

That £3,069.57 is the of the promise, and a present value is what a pension is. When a scheme reports liabilities of £500m, nobody owes £500m this afternoon. The figure is thousands of future payments, each discounted back like this, added up.

The rate is the whole game

Rerun the same sum at 3%:

£5,000 ÷ 1.03^10 = £3,720.47

Nothing about the promise changed: same member, same £5,000, same date. Yet the liability rose by about 21%, purely because the fell two percentage points. That is the single most important mechanism in UK pensions: when fall, liabilities rise. The next lesson covers what happened when this mechanism ran one way for twenty years and then reversed.

Where does the rate come from? For funding purposes it is normally anchored to yields, the return on UK government . The logic: a pension promise is about as certain as a payment gets, so you price it off the most certain investments available. Funding are influenced by market interest rates and the scheme’s investment strategy. If market yields rise, the used to value the liabilities will often rise too, reducing their .

Streams and the annuity factor

Real pensions are streams, not single payments. Say a scheme owes a member £1,000 at the end of each of the next five years, at 4%. You could discount all five payments separately and add them up. The shortcut the profession runs on is the :

(1 - 1.04^-5) ÷ 0.04 = 4.4518

£1,000 × 4.4518 = £4,451.82

Read the factor as a price tag: £1 a year for five years costs £4.45 today. A real pension is the same object stretched over decades, with one refinement: each payment is also weighted by the probability the member is alive to collect it, which bakes a (the assumed death rates at each age) into the factor.

Prudent or best estimate?

A promise does not have one price, because assumptions are choices. Two terms to know:

  • : assumptions intended to be realistic, with no deliberate margin for caution or optimism.
  • Prudent: assumptions deliberately tilted towards caution, such as a lower or longer lifetimes. Prudence pushes the liability number up, and where that opens a shortfall it asks more money of the employer sooner, which buys members security.

Statutory funding valuations are required to be prudent; transfer values (the sum a member can move to another scheme instead of the promise) are calculated on . Same promises, different prices. The split is deliberate: a transfer value is paid out of the collective fund, so a leaver paid on the prudent basis would take out more than the promise is expected to cost, at the expense of the members who stay.

Rate and term together

The rate is only half of it. The other half is how far away the payment is, because compounds. Here is the same £5,000 promise priced at four rates, first ten years out and then forty, the far end of a real scheme's payment stream:

Due in 10 yearsDue in 40 years
5%£3,069.57£710.23
4%£3,377.82£1,041.45
3%£3,720.47£1,532.78
2%£4,101.74£2,264.45

Read down the columns, not across the rows. At ten years, taking the rate from 5% to 2% raises the liability by about a third. At forty years it more than triples it. The same yield fall does far more damage to a promise that is decades away.

These numbers leave out mortality and inflation. Real valuations layer both back in, but the core never changes: every liability is a discounted payment, or a sum of them.

Layer mortality back in

A valuation model puts one row per projected payment on a sheet, with the visible in its own column, so a reviewer can trace any number back to an input. Below is that sheet, for a pensioner aged 60 drawing £1,000 a year for five years, the same stream as the above, with one column added for the probability the member is alive to collect each payment, built from each year's mortality rate, written q on the sheet.

Spreadsheet: build it, don’t type it

Discount the projected cashflows, twice

Three blocks: the assumptions at the top, one row per projected payment in the middle, the two answers at the bottom. Nothing you fill in may contain a typed-in number: every cell reads the row or the input it depends on. WORKINGS. D7 to D11: the probability the member survives from today to each payment date. D7 is one minus the first year's mortality rate; every row below it is the row above times one minus that year's rate. Build it as a running product down the column, not as five separate chains typed out. E7 to E11: the discount factor, 1 divided by (1 + the rate in B2) raised to the power of the year in column A. Build the factors explicitly. F7 to F11: payment times discount factor, which is what the payment is worth today if it is certainly made. G7 to G11: payment times survival probability times discount factor. F12 and G12: the two column totals. RESULTS. B15: the annuity factor from the formula in the lesson, (1 minus (1 plus the rate) to the power of minus the term) divided by the rate, reading both the rate and the term from B2 and B3. B16: the certain stream priced with that factor instead. B17: how much of the certain value mortality removes, as a percentage of F12. CHECKS. E15, E16 and E17, each returning OK or CHECK: E15: the row-by-row total in F12 agrees with the annuity-factor route in B16. E16: F12 also agrees with NPV over the payments in B7 to B11. NPV discounts its first cashflow by one year, which is exactly what row 7 does. E17: the survival probabilities stay between 0 and 1 and fall as the term lengthens. Marked cells: D7, D11, E11, F12, G12, B15, B17, E15, E16, E17.

Cells to fill: D7, D11, E11, F12, G12, B15, E15, E16, B17, E17

100%
  • Given data, locked
  • Yours to fill
RowABCDEFGHI
1INPUTS
2Discount rate0.04
3Term (years)5
4
5WORKINGS: the projected payments, one row each
6YearPayment (£)Mortality rate qSurvival probabilityDiscount factorValue today (£)Expected value today (£)
7110000.008
8210000.009
9310000.01
10410000.011
11510000.012
12Total
13
14RESULTSCHECKSStatus
15Annuity factor from the formulaRow-by-row total agrees with the annuity factor
16Certain stream via the factor (£)Row-by-row total agrees with NPV
17Cost of mortality (%)Survival probabilities fall and stay between 0 and 1
18
19
20
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.

Now do one by hand:

Work it out

A scheme must pay £8,000 in exactly 15 years. At a discount rate of 4% pa, what is the present value of the promise?

£