Choosing the Right Tool

Introduction

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:

• The possibility of challenging the scope
• The importance of the choice of technology
• The benefits of engaging APR on a consultancy basis

Background

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:

• Understand what customer outcomes might look like under a range of different scenarios
• Consider the impact of a variety of interventions, e.g. limiting any premium increase to x%, on both the customers’ outcomes and the value of the book
• Ensure that the value of the chosen approach can be captured accurately in the regular valuation process

APR’s Role

The client already had:

• a valuation model that could value policies assuming that they lapse at the next review date
• an Excel pricing model that could project cashflows and solve for the revised premium or sum assured at the next review date using a goal-seek function (but only for the current product version)
• a productionised tool to calculate the revised premium or sum assured at the next review date (for all product versions)

APR’s role was therefore to:

• Update the valuation model so that the revised premiums and sums assured could be input and each policy could be valued up to its expected lifetime, allowing for the expected post-review premiums and claim amounts
• Update the Excel model so that, for all product variants, it would automatically loop through and process each of the policies and each of the potential interventions
• Test the models against results from the productionised tool
• Use the updated models to produce the projected premiums and sums assured and then demonstrate the overall impact of each of the interventions

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.

Outcome

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