Daily Archives: 7 Aug 2018

How to import the Solvency 2 RFR

Published by:

UPDATE: The code mentioned in this blog has been moved to https://github.com/wjwillemse/solvency2-data

If you want to make insurance calculations you often need an recent interest rate term structure. In this example we show how to import the Solvency 2 Risk Free Rate from the EIOPA website in a convenient Pandas Data Frame, ready to be used for future calculations.

The code can be found on https://github.com/wjwillemse/insurlib.

First we import insurlib.solvency2, the Python package that contains functions to generate the names of the files, import the zip-file from the EIOPA website, extract it to an Excel file (both stored on disk) and read the Excel file in a proper Pandas Data Frame.

In [1]: import pandas as pd
        from datetime import datetime
        from insurlib import solvency2

We have now all the functions we need.

The function that does all this is solvency2.rfr.read, it returns a Python dictionary with all information about the RFR of a certain reference date.

If you do not add a input datetime, i.e. solvency2.rfr.read(), then the function with use today() and you will receive the most recent published RFR.

In [2]: d = solvency2.rfr.read(datetime(2018,1,1))

What information is stored in the dictionary?

In [3]: d.keys()
Out[3]: dict_keys(['input_date', 
           'reference_date', 
           'url', 
           'path_zipfile', 
           'name_zipfile', 
           'path_excelfile', 
           'name_excelfile', 
           'metadata', 
           'RFR_spot_no_VA', 
           'RFR_spot_with_VA'])

Let’s take a look at the individual elements of the dictionary.

The original date by which the function was called is stored in the dictionary as input_date

In [4]: d['input_date']
Out[4]: datetime.datetime(2018, 1, 1, 0, 0)

You can call the function with any date and the function will generate a proper reference date from it. The reference date is the most recent end of the month prior to the input date. So if for example the input is datetime(2018, 1, 1) then the reference date is '20171231', because this the most recent end of the month prior to the input date. The reference date is a string because it is used in the name of the files to be downloaded from the EIOPA-website.

In [5]: d['reference_date']
Out[5]: '20171231'

Furthermore the url, location and filenames are stored in the dictionary.

In [6]: print(d['url'])
        print(d['name_zipfile'])
        print(d['name_excelfile'])
Out[6]: https://eiopa.europa.eu/Publications/Standards/
        EIOPA_RFR_20171231.zip
        EIOPA_RFR_20171231_Term_Structures.xlsx

Now, let’s take a look at the available RFR’s.

In [7]: d['metadata'].columns
Out[7]: Index(['Euro', 'Austria', 'Belgium', 'Bulgaria', 'Croatia', 'Cyprus', 'Czech Republic', 'Denmark', 'Estonia', 'Finland', 'France', 'Germany', 'Greece', 'Hungary', 'Iceland', 'Ireland', 'Italy', 'Latvia', 'Liechtenstein', 'Lithuania', 'Luxembourg', 'Malta', 'Netherlands', 'Norway', 'Poland', 'Portugal', 'Romania', 'Russia', 'Slovakia', 'Slovenia', 'Spain', 'Sweden', 'Switzerland', 'United Kingdom', 'Australia', 'Brazil', 'Canada', 'Chile', 'China', 'Colombia', 'Hong Kong', 'India', 'Japan', 'Malaysia', 'Mexico', 'New Zealand', 'Singapore', 'South Africa', 'South Korea', 'Taiwan', 'Thailand', 'Turkey', 'United States'],    dtype='object')

To get all the metadata of the French RFR we select metadata from the dictionary.

In [8]: d['metadata'].loc[:,'France']
Out[8]: Info              FR_31_12_2017_SWP_LLP_20_EXT_40_UFR_4.2
Coupon_freq                                             1
LLP                                                    20
Convergence                                            40
UFR                                                   4.2
alpha                                            0.126759
CRA                                                    10
VA                                                      4
reference date                                   20171231
Name: France, dtype: object

That is: the Coupon frequency is one year, the Last Liquid Point is 20 years, the Convergence period is 40 years, the Ultimate Forward Rate is 4.2 (this rate changed at the beginning of 2018 to 4.05), the alpha parameter of the Smith-Wilson algorithm is 0.126759, the Credit Rate Adjustment is 10 basis points and because we have the curve without the Volatility adjustment the VA is 4 basis points. It is identical to the Euro curve.

To get one single item from the metadata we can use the following line (note that this is the UFR at the end of 2017).

In [9]: d['metadata'].loc["UFR",'Germany']
Out[9]: 4.2

To get the euro RFR without Volatility Adjustment (the first ten durations) we use

In [10]: d['RFR_spot_no_VA']['Euro'].head(10)
Out[10]: Duration
1    -0.00358
2     -0.0025
3    -0.00088
4     0.00069
5     0.00209
6     0.00347
7     0.00469
8     0.00585
9     0.00695
10    0.00802
Name: Euro, dtype: object

Now suppose that we want to store the RFR of six consecutive months into one Data Frame. This is how we can do that.

First we define ref_dates with the reference dates we want to acquire.

In [11]: ref_dates = pd.date_range(start='2018-01-01', periods = 6,freq = 'MS')
         ref_dates
Out[11]: DatetimeIndex(['2018-01-01', '2018-02-01', '2018-03-01', '2018-04-01', '2018-05-01', '2018-06-01'], dtype='datetime64[ns]', freq='MS')

Then we use a Python list comprehension to obtain the RFR’s of the reference dates and we show the Data Frame with the first ten durations.

In [12]: rfr = [solvency2.rfr.read(ref_date)['RFR_spot_no_VA']['Euro'] for ref_date in ref_dates]
         df_euro = pd.DataFrame(data = rfr, index = ref_dates).T
         print(df_euro.head(10))

A list comprehension can also be used for the metadata. The following code obtains the metadata of the UK RFR.

In [13]: rfr = [solvency2.rfr.read(ref_date)['metadata']['United Kingdom'] for ref_date in ref_dates]
         print(pd.DataFrame(data = rfr, index = ref_dates))
Out[13]:           2018-01-01  2018-02-01  2018-03-01  2018-04-01  2018-05-01  \
Duration                                                               
1           -0.00358    -0.00363    -0.00352    -0.00362    -0.00358   
2           -0.00250    -0.00225    -0.00220    -0.00258    -0.00244   
3           -0.00088    -0.00020    -0.00022    -0.00083    -0.00065   
4            0.00069     0.00190     0.00178     0.00104     0.00120   
5            0.00209     0.00380     0.00361     0.00285     0.00286   
6            0.00347     0.00537     0.00521     0.00418     0.00441   
7            0.00469     0.00670     0.00666     0.00556     0.00577   
8            0.00585     0.00791     0.00793     0.00672     0.00698   
9            0.00695     0.00899     0.00906     0.00783     0.00809   
10           0.00802     0.00987     0.01007     0.00884     0.00911   

          2018-06-01  
Duration              
1           -0.00331  
2           -0.00236  
3           -0.00098  
4            0.00057  
5            0.00213  
6            0.00356  
7            0.00490  
8            0.00613  
9            0.00725  
10           0.00824