81 Chancery Lane
01235 821 160
View map


1 Lochrin Square
92 Fountainbridge
01235 821 160
View map


24A Baggot Street Upper
D04 V970
01235 821 160
View map

Administrative Office

BH Office
Church Street
OX12 8QA
01235 821 160
View map

Send us a message

Contact Us

Choosing the Right Tool


On a recent project, APR built a cashflow model, performed some analysis and summarised the conclusions in a report. That could be the summary of many APR projects – but there were some interesting features of this one which highlighted the following:


Beginning in the 1990s, the client sold a life assurance product, which was common throughout the UK life insurance industry at that time. The product gives the policyholder the option, at specified dates: to continue the policy with a potentially higher premium; to continue the policy with a potentially lower sum assured, or; to lapse the policy. The revised premium or sum assured would be set on cost neutral terms – i.e. the value to the client does not depend on which option the policyholder chooses. Thus, each policy could be valued easily, based on the assumption that it lapses at the next review date. However, revised premiums for older customers could, in some circumstances, become very large.

Project Aims

As a mutual, dedicated to ensuring good value for customers, the client began a project in order to:

APR’s Role

The client already had:

APR’s role was therefore to:

How APR provided the solution

It is important to note that APR’s role was defined in such a way that part of the solution was already stipulated: the discounted value would be calculated in the valuation model, and the goal-seek for the appropriate premium or sums assured would be done in Excel. It is true that actuarial valuation models are not usually suited to embedding goal-seek functionality and it is true that the Excel models often lack the controls that auditors and risk reviewer demand, but dividing the task in this way had one important implication: it would be slow.

The Excel model was quite complicated, with several look-up tables, individual underwriting adjustments, joint life complications and historic changes to the product terms, all of which slowed down the Excel goal-seek function. Furthermore, the calculated values became very sensitive at the extreme ages, making it harder for the goal-seek function to find a solution. While this Excel workbook was ideal for investigations and pricing, it was not suited to batch processing. Some rough calculations showed that to loop through tens of thousands of policies for each of the 14 interventions on a single PC, would take about 100 days.

One of the benefits of engaging APR is being able to tap into the knowledge across the company. In particular, the Technology Team looks at issues like this all the time, and so the team investigated some alternative solutions. The first step was to pick apart the model to understand the nature of the issues at higher ages as well as all of the product variations and modelling quirks. With that knowledge, we set about simplifying the model, ensuring that it was easier to follow and arranging the calculations so that the goal-seek function (rewritten in VBA) would run as quickly as possible. With that simplified model, we tested a few different tools to see which would be quickest. An indicative performance comparison of model run-times is shown below:

Note that the original Excel workbook was ten times slower than the optimised version and it had to be removed from the chart in order to make the other bars visible.


Working with clients on a consultancy basis involves regular communication, to ensure that client needs are being met in the most efficient way. Having established that dialogue with the client, it was relatively easy to agree to move from Excel to the R (with C++ goal-seek) solution, noting that although this was not the fastest solution, the client was more familiar with R than some of the alternatives, having already implemented some R tools within the actuarial department.

The project was not always smooth: testing uncovered many aspects that required further investigation and moving the cashflow model from Excel to R was not in the original plan. However, all project requirements were met, and the client was grateful that we had solved a thorny problem.

Phil Creswell

March 2021