Saturday, 26 April 2014

Mortality Manager v003


I have now completed the development of support for Mortality Projections. I have decided to produce an updated version which includes this additional functionality in a similar way to that made available in the post Mortality Manager with FCell 2.1.
This post describes what is provided and instructions for use and also covers the changes made to the UDFs.

Workbook Customisation

One option for obtaining Mortality Manager v003 is as a standalone workbook. I have created two versions for 32 bit and 64 bit Excel. These are available from bitbucket at:
 Once you download the appropriate version, you can simply open in Excel:

Simply follow the instructions in the GettingStarted sheet to be able to replicate the examples contained in the workbook.

Excel Customisation

The other option for obtaining Mortality Manager v003 is as an .XLL file. This provides the Ribbon, Custom Task Pane and UDF additions included in Mortality Manager using a single file.

I have created two versions of the XLL for 32 bit and 64 bit Excel. These are also available from bitbucket at:
Once you have downloaded these, they can simply be opened using Excel or to install permanently use File > Options > Add-ins and then use the Go.. button to "Manage the Excel Add-ins" and then Browse to add the downloaded XLL.

(N.B. If you get a message that the XLL is in an invalid format then either you are trying to use the wrong version or your computer is missing the Visual C++ runtime for Visual Studio 2012, which is required by these XLLs. For the latter issue please use the installer available from Microsoft at

Once loaded, you should see the Mort Ribbon and additions in the function wizard, e.g. a new category MortMgrUdf.Mort.

To get started, I suggest using the following steps:
  1. On the Mort Ribbon select "Settings". Specify local folders to store local versions of tables and to store Transitory data when importing from the SoA site, e.g. "C:\Mort\Local" and "C:\Mort\SoA". N.B. Create these folders first and then browse to select them using the "..." button.
  2. Restart Excel to get these settings reloaded. Re-open the Settings dialog and confirm that the folders are still correctly set.
  3. Using the SoA toggle button open the SoA Task Pane. Use the "Reload Index from Website" button - this may take some time! Confirm that the file "soaindx.json" has been create in the SoA folder, e.g. in C:\Mort\SoA
  4. Import some sample Select and Ultimate tables. For example, in the SoA Task Pane select a Nation of "United Kingdom". Select the checkboxes for the tables "ELT No. 16 (2000-02) - Female, ANB", "ELT No. 16 (2000-02) - Male, ANB", "AFC00" and "AMC00". Import using the "Import" button. Confirm that new json files appear in the local tables folder, e.g.  in C:\Mort\Local.
  5. In the CMI 2012 Task Pane, select Get Sample Results and then Save Rates to create a default set of Projected Rates.
You should then be able to use the Task Panes included to view the tables, compare tables and calculate various factors. You should also be able to get results from the tables using the UDFs provided.

UDFs with complex table parameters and results

I have converted all the supplied UDFs to use a facility included in FCell 2.1 to use complex parameters and return complex results. The UDFs that return mortality results now all work with the mortality table as one of the parameters. It is assumed that another UDF has been used to pull in the mortality table reference to an Excel cell.

This is best illustrated by an example provided with the customised workbook:

Please note the get UDF references in cell F3, with this formula:
=get("ELT No. 16 (2000-02) – Female, ANB")
This simple gets this mortality table and holds a reference to it in the cell. You can then use this in another UDF, as illustrated by the function wizard for cell F7:

The code for this UDF is simply:

/// qx for ultimate table
[<XlFunctionHelp("Get q from Ultimate table")>]
let qx tbl x = 
    let rts = tbl.MortRates
    match rts with
    | Ultimate(r) -> r.[x]
    | _ -> failwith "Table is not an Ultimate table"

Note the use of the XlFunctionHelp attribute to add documentation in the function wizard in Excel.

Monday, 21 April 2014

Mortality Projection Functions


I have now completed the Ribbon and Custom Task Panes relating to Mortality Projections within Mortality Manager. This includes functionality to:
  • Generate Mortality Projection using the CMI 2012 model 
  • Save these Generated projected rates
  • View and Edit Saved Projected Rates (This also allows you to create these by hand or sourced from other data)
  • Compare two sets of Projected Rates
  • Generate Single Life and Joint Life Mortality Functions (qx, dx, lx, ex) from a set of Projected Rates (depending on a specified age and calendar year)
  • Generate Single Life and Joint Life Commutation Functions (Dx, Nx, Sx, Cx, Mx, Rx) from a set of Projected Rates (depending on a specified age and calendar year)
  • Generate Single Life and Joint Life Present Value Functions (ax, Ax, Px) from a set of Projected Rates (depending on a specified age and calendar year)
The updated code is available on bitbucket. Mortality Manager now looks like this:

Persisting using Records and Json

In a very similar way to the Mortality tables, I have used a combination of F# Record Types and Json.NET to support saving these projected rates. The Record used is defined as:
type Qxt = 
    {   BaseName : string
        BaseRates : float []
        BaseDate : System.DateTime
        ProjName : string
        StartYear : int
        EndYear : int
        QxtName : string
        QxtRates : float [,] }

This includes details of the Base Mortality rates used (their name, the rates  and the assumed date these rates apply), then the name of the Projection used and then details of the projected rates (start and end year, the name used to identify these rates and the rates as a 2D array).

These can be simple saved to Json using this code:
/// save Qxt to json
let save (ptbl : Qxt) jsonfil = 
    let jsontxt = JsonConvert.SerializeObject(ptbl)
    File.WriteAllText(jsonfil, jsontxt)

The saved sets of rates can be viewed using the View/Create option for Projections in Mortality Manager:

Re-using the Mortality Code 

To generate the functionality to calculate Mortality Functions, Commutation Functions and Present
Value functions, the simplest approach was to re-use the code already written for Mortality Tables. To do this, it was simple to generate an intermediate Mortality Table Record Type and then pass this to the existing functions.

To create this intermediate type, this code was used:

// get mort table given age and year and proj table
let getmtbl iage iyr qxts = 
    //get qs from this age and year forwards
    let pj_qxs = pj_qx iage iyr qxts
    //zero fill for prior ages
    let rts = [|0..120|]|> (fun a -> if a <iage then 0.0 else pj_qxs.[a-iage])
    //return mortality table record
    { Name = (qxts.QxtName + " at age " + iage.ToString() + " and year_" + iyr.ToString())
      MortInfo = dummy_mi
      MortRates = Ultimate(rts) }

Please note the need to supply a specific age and year to allow a single array of rates to be extracted from the Projected Rates. Also, note that the rates have been zero filled for ages below the specified age. The function just returns a dummy Mortality Table Record with a dummy name and info, just holding the projected values as a set of Ultimate rates.  The results using this code therefore apply to someone who is the specified age in the specified year and allows for mortality improvements from that date onwards. 

The next blog will complete the functionality for Mortality Projections by adding related User Defined Functions and the existing UDFs will be revisited allowing for the extra options available under FCell 2.1.

Monday, 7 April 2014

Smith Wilson and the F# Community


Last month I wrote a post on Smith Wilson and Deedle. Smith-Wilson is a method of curve fitting, which was the preferred method under QIS5 for interpolating and extrapolating interest rate curves.

In that post, I first used R with a CRAN package created by my friend and ex-colleague Phil Joubert. I then used Deedle and the R Type Provider to call this functionality from F#.

I promised to revisit this topic and generate a purely F# approach.

The F# Community

I had initially intended to generate the F# code needed, but through the F# Community I found a much better alternative. I was able to contact Russell Politzky in South Africa. Russell tells me that he is "a developer and aspiring Quant/Developer". He had generated some excellent code for Smith-Wilson and he has very kindly let me refer to this in this post. His code can be found in his GitHub repository at:
I was given more help by Adam Mlocek of StatFactory - see comments in the next section.

Math Libraries

Being able to quickly generate solutions for these types of modelling challenges is critically dependent on being able to utilise good supporting libraries. F# programmers are very lucky to have a large number of excellent options of Math libraries. These are documented on the F# Foundation site at:
Russell's code uses the open source Math.NET Numerics library. It also uses the supporting Intel Math Kernel Library (MKL) to increase the performance of the code.

Adam also provided me with a version of this code converted to use StatFactory's FCore library. I believe Adam is planning to also blog on this topic and I will add a link here when he publishes the relevant post.

EIOPA Example

In the earlier post, I replicated the results from one of the EIOPA QIS5 examples. Russell's repository already includes scripts to replicate these examples, see:
I have created a simplified version of the former to replicate the results from the earlier post:
#r "System.Windows.Forms"
#r "System.Windows.Forms.DataVisualization"
#r @"..\packages\MathNet.Numerics.2.6.2\lib\net40\MathNet.Numerics.dll"
#r @"..\packages\MathNet.Numerics.FSharp.2.6.0\lib\net40\MathNet.Numerics.FSharp.dll"
#r @"..\packages\FSharp.Charting.0.90.5\lib\net40\FSharp.Charting.dll"
#load "Rates.fs"
#load "SmithWilson.fs"
#load "Plot.fs"

open System
open FSharp.Charting
open FSharp.Charting.ChartTypes
open System.Drawing
open Rates
open SmithWilson
open Plot

// This is the first of the EIOPA examples from the paper at the following URL
let a = 0.1 // Mean reversion parameter controls rate at which curve reverts to UFR.
let UFR = Math.Log(1.0 + 0.042) // Ultimate Forward Rate expressed in NACC (converted from 0.042 NACA)
// Market data.
let m = [| 1.0; 1.0; 1.0; 1.0 |] // market prices
// Cash flow dates in year fractions.
// This is the union of all of the cash flow dates
// from all instruments.  It's expressed in years
// here.
let cashFlowDates = [| 1.0; 2.0; 3.0; 4.0; 5.0 |]

let C = // Cash flow matrix
    [ [ 1.01; 0.00; 0.00; 0.00; 0.00 ] // instrument 1
      [ 0.02; 1.02; 0.0; 0.0; 0.0 ] // instrument 2
      [ 0.026; 0.026; 1.026; 0.0; 0.0 ] // instrument 3
      [ 0.034; 0.034; 0.034; 0.034; 1.034 ] // instrument 4

let time = [| 0.0..50.0 |]
let timeDfs = PtSmithWilson a UFR m C cashFlowDates time
let dfs = timeDfs.Column(1)
let rateNACA = getNacaRates time dfs

plot (rateNACA |> time) 100.0 "EIOPA Sample : NACA Rates" "Rate     [% NACA]"

let ans = dfs.[4]

In lines 1 to 16, supporting code and libraries are referenced. In addition to Math.NET Numerics the code also uses the F# Charting library to support charting directly from F# Interactive.

The code from lines 18 to 38 sets up the input data. The main function PtSmithWilson is then called in line 39, which returns a Dense Matrix. In F# Interactive you get:
val timeDfs : MathNet.Numerics.LinearAlgebra.Double.DenseMatrix =
  DenseMatrix 51x2-Double
           0            1
           1     0.990099
           2     0.960978
           3     0.925216
           4     0.885004
           5     0.843439
           6     0.803782
         ...          ...
          50     0.122813
The discounts rates are then put into a Vector in line 40 and then converted to a List of spot rates with annual compounding in line 41.

These are then plotted to produce the following chart (which corresponds to the same chart produced by R in the earlier post):

Finally, we confirm that the code generates the test value of 0.885 in line 45. In F# Interactive we get:
val ans : float = 0.8850041337

Tuesday, 1 April 2014

Mortality Projections


As mentioned in earlier posts, one key element missing from Mortality Manager is handling of future mortality improvements.

This post will start to address this limitation, by adding a facility to generate mortality projections in line with the model provided by the Continuous Mortality Investigation (CMI) under CMI Working paper 63 in the spreadsheet Cmi2012model.xls. This is referred to as the CMI_2012 model and can be downloaded at:

(Note that there is an updated CMI_2013 model, but this has not been made publicly available.)

The Model

Although the spreadsheet is complicated, the model itself is relatively simple. It depends heavily on a large amount of data stored in the spreadsheet. These can be adjusted, if the model is used in Advanced mode.

It is much simpler just to use the model in Core mode. In this mode, the model depends only on three factors entered in the Inputs sheet:
  1. Gender - Male or Female
  2. Long-Term Rates of Mortality Improvement - for example, 0.1%
  3. Constant Additional Rate of Mortality Improvement - for example, 0.01%
 These inputs can be held within F# using a Discriminated Union Type and a Record Type:
type gender = 
    | Male
    | Female
type cmiInputs = 
    { Gender : gender
      LongTermRate : float
      ConstantAddition : float }

The data from the spreadsheets used to drive the model can be stored as 2D arrays in F#. For example, the data stored in sheet CoreParameters, Columns K:AC, Rows 145:245 is stored in variable initRatesF as a float[,].

Note that to be consistent with the other parts of Mortality Manager, the results are calculated from age 0 to age 120. The CMI model uses ages 20 to 150. Note that I have used the (unrealistic) assumption that all ages 20 and below are modelled identically.

Initial Results

In the first update to Mortality Manager I have replicated the creation of the main projection results under Core mode. I have replicated the results generated in sheet:
  • Proj Mort Imps -  Projection of Aggregate Annual Rates of Mortality Improvement
The results are calculated by projecting separately an age/period effect and a cohort effect. Both of these are calculated using a cubic convergence function to smooth results for past periods included in the spreadsheet with the assumed long term effects. The F# code mimics the spreadsheet structure. For example, the following code replicates the results in sheet Convergence Fn by Age:

/// gets the convergence function by age: Sheet "Convergence Fn by Age", Columns G:BE
let convAge (inps : cmiInputs) = 
    ((perAgePerElems inps), convParams)
    ||> Array.map2 (fun p (a, b, c) -> 
            [| 0..50 |] |> (fun cl -> 
                                if cl > p then 0.0
                                else a * (float (cl) / float (p)) ** 3.0 + b * (float (cl) / float (p)) ** 2.0 + c * (float (cl) / float (p)) + 1.0))
    |> array2D

This function takes as input the record type holding the assumptions. On line 3, it uses other functions that produce arrays by age (0-120) for the periods for interpolation and the factors a, b and c used by the cubic function. On line 4, these two arrays are double piped (||>) to a map2 higher order function, which then applies the cubic function for projected years 0-50, but set to zero if above the limit for that age. On line 8 the result of an array of arrays is converted to a 2D array.

The main result is created using the following function:

/// gets the projected mortality improvements: Sheet "Proj Mort Imps", Columns D:BK
let projImps (inps : cmiInputs) = 
    let irs = initRates inps
    let pa = projAge inps
    let pc = projCoh inps
    let iyr = 2009
    [| 0..120 |]
    |> (fun i -> 
            [| 1991..2059 |] |> (fun y -> 
                                    if y <= iyr then irs.[i, y - 1991]
                                    else pa.[i, y - iyr] + pc.[(max 0 (i - y + iyr)), y - iyr] + inps.ConstantAddition))
    |> array2D

Lines 3 to 5 pull in results from other functions that generate the initial rates on mortality improvement, the projected improvements by age/period and the projected improvements by cohort. In lines 9 to 11, the initial rates are used up to 2009 and in later years the sum of the projected rate by age/period and by cohort and the assumed constant addition is used.

The complete code is held in module CmiProj in the file CmiProj.fs, which is stored on bitbucket.

Custom Task Pane

To start using this code I have created a Mortality Projections Custom Task Pane in Mortality Manager. In this first version, it simply allows specifying the three Core assumptions. On pressing the Generate Projection button, one sheet is created giving the projected mortality improvements. This sheets is given a suitable name depending on the input chosen.

This is what the Mortality Manager looks like using this functionality:

In later posts, I will add more functionality to replicate the samples provided in the CMI spreadsheet, add functionality to be able to use the projections with any mortality tables held in mortality manager and to calculate mortality, commutation and present value functions allowing for mortality improvements.