### Risk transfer...and transfer risk

#### (Dec 21, 2017)

The risk-transfer market for defined-benefit pensions in the UK has been  buoyant for many years.  There is considerable demand from pension schemes - to say nothing of their sponsoring employers - for solutions that transfer risks to insurers.  These risk transfers can be comprehensive, such as bulk annuities that take on investment, inflation and all demographic risks.  Or else they can be narrowly focused, such as the longevity swaps that only transfer a specific part of a scheme's overall risk.

Whatever the solution, something else needs to be transferred long before the risk can be: data.  To price a longevity swap or a bulk annuity, an insurer or reinsurer needs some very specific data on the lives covered. …

### Quantiles and percentiles

#### (Aug 20, 2014)

Quantiles are points taken at regular intervals from the cumulative distribution function of a random variable. They are generally described as q-quantiles, where q specifies the number of intervals which are separated by q−1 points. For example, the 2-quantile is the median, i.e. the point where values of a distribution are equally likely to be above or below this point.

A percentile is the name given to a 100-quantile.  In Solvency II work we most commonly look for the 99.5th percentile, i.e. the point at which the probability that a random event exceeds this value is 0.5%.  The simplest approach to estimating the 99.5th percentile might be to simulate 1,000 times and take the 995th or 996th largest…

Tags: quantile, percentile, Solvency II, Excel, R

### Excel's Limits

#### (Jun 27, 2014)

We have written in the past about some of the reasons why we don't use Excel to fit our models.  However, we do use Excel for validation purposes - fitting models using two entirely separate tools is a good way of checking production code.  That said, there are some important limits to Excel, especially when it comes to fitting projection models.  Some of these limits are rather subtle, so it is important that an analyst is aware of all of Excel's limitations.

The first issue is that Excel's standard Solver feature won't work with more than 200 variables, i.e. parameters which have to be optimised in order to fit the model.  This is a problem for a number of important stochastic projection models, as shown in Table 1. …

Tags: Excel, Lee-Carter, APC, CBD

### Why we don't fit models in Excel

#### (Oct 21, 2008)

Actuaries are very heavy users of spreadsheets, especially Microsoft® Excel.  One question we are occasionally asked is why we wrote our software in C++, instead of letting people have direct access to our code as a spreadsheet.

One answer is that there have been rather too many bugs for comfort in the basic arithmetic and mathematical functions in Excel.  Here are some examples:

1. In Excel 2007 the answer to 850 * 77.1 is given as 100000 instead of the correct answer of 65535.
2. We often need to do simulations, for which we need to generate uniform random numbers distributed between zero and 1.  In Excel 2003 the RAND() function returns negative numbers.
3. Excel will sometimes crash when trying to maximise certain log-likelihood…

Tags: spreadsheet, Excel, C++