SCR Calculator User Manual

Version 1.13.2.0 Last modified 2024-3-21

Annuity Cashflow Matching


Cashflow Optimisation for Long-Duration, Annuity Cashflow Matching

Long duration cashflow matching, such as for 20+ year annuity cashflows, follow the same process, but there are some additional conventions to follow, such as de-risking, if you wish for the procedure and results to be more useful.

Target cashflow constraints can be saved in a spreadsheet (downloadable example below) for convenient copy/paste into the 'optimised constraint' box of the calculator:

Note that actual annuity cashflows can be longer (e.g. 40+ years), but we are using liquid bond assets for demo, and their average duration is not that long (circa. 15 years). So we are only trying to match 20 years worth of cashflows in this case. For longer duration cashflows, a longer duration asset space is needed.


Step 1: Probability of Default data generation

To perform proper matching-adjustment-compatible annuity cashflow optimisation, we need to de-risk cashflows with probability of defaults from EIOPA or the PRA. These data are stored in the SCR Calculator database and readily available for use - below we describe the procedures.

We first prepare the current portfolio and the investible universe of bonds, in "Current" and "New" worksheets, respectively.

Assuming we are doing portfolio construction from scratch, we only need to place one nominal bond in the "Current" worksheet, such as in the downloadable example spreadsheet, to keep the optimiser happy. If it were for portfolio rebalancing, we can place the complete actual holdings in the "Current" worksheet.

Upload the example spreadsheet into the "Portfolio SCR" form (select the Bloomberg input mode) twice, select "Current" and "New" worksheets in turn, and click the "MA Potent" checkbox in "SII" tab in each case. A number of MA-related columns will be calibrated and added at the beginning of the data table, such as shown below.

Export the data table; and copy/paste those MA-related columns into the inputsheet, at the end of the corresponding tabs, such as shown below (revised downloadable example below).

The definitions of the added grey columns were explained in:

  • Chapter III Solvency II Matching Adjustment calculations
  • Chapter IV Portfolio SCR Calculation, Section 6 The SCR tabs
The most important columns are the "DurXMA" column (objective of maximisation) and "PDprob", which means "Probability of Default", and contains the PD values for each year of the remaining life of the bond, in comma separated format. This is the column that will be auto-recognised and used by the FI Portfolio Optimiser for de-risking the cashflows.

Note that these columns are generated by the Portfolio SCR Form only for those MA-compatible assets. If you wish to de-risk all fixed income cashflows, you can manually edit at this stage.


Step 2: Generate Cashflows for Optimisation

Upload the revised inputsheet into the Fixed Income Portfolio Optimiser. Then, in both the 'current' and 'new' tabs, click the "Add CFs" button to add 20 years' cashflows (the "Y1", "Y2", ..., "Y20" columns): It will ask you to confirm whether to use the "PDprob" column to de-risk cashflows. Select "Yes". (If this column wasn't present, it will ask for your confirmation that no de-risking is needed.)

Note that the cashflow projection in the Fixed Income Portfolio Optimiser is assumed tobe semi-annual coupon throughout. You will observe this if you change the projection frequency to "Quarterly" or "Monthly". This is a simplification, and different from the Portfolio SCR form, where daily cashflow modelling is assumed.


Step 3: Optimisation Settings

Enter the following in the "Settings" tab.

Note that:

  • Objective is set to maximise the Duration Times Matching Adjustment (the "DurXMA" column).
  • We ask the optimiser to search for a minimum portfolio size between 60% and 120%, that can generate these cashflows.
  • 'Common Constraints' area are set to very loose, because our first objective is to achieve cashflow matching
  • Cashflow constraints were copy/pasted from the input spreadsheet directly.
  • Unused constraint sections - gain/loss crystallisation, liquidity constraint, ESG score - are turned off.

In practice, the user should loosen all constraints to start with, then gradually tighten, so as to ensure there is always a possible optimised portfolio. The user can also set the 'Upper Bound's in 'Current' and 'New' tabs to a higher level, to ensure they do not become the bottleneck.


Final Step: Optimisation

Go to the 'Run' tab and click 'Run Optimisation', you will see an optimised portfolio of 44 bonds. Click the 'Show CFs' button and it will display a nicely fitted cashflow series.

The composition of the cashflows displayed is determined by the selection in the Combo Box under the "Draw RHS chart of a non-numeric column" label.

And you can observe the optimised Matching Adjustment using the numeric column display facility (the 'weighted average'):