Thursday, 28 May 2015

FCell Calculation Engine - modelling decrements

Introduction

In the earlier posts, we have set up the inputs to the projection model. In this post, we will be doing the first real calculations. We will project the expected progression on policy counts allowing for decrements, principally lapses and deaths.

If you look at the Excel only version in sheet Decrements, you will see that we first project forward the calendar months and years and how the age(s) and duration since the policy started increase over time. We then determine the mortality and lapse rates at each period. From this we can calculate how the number of policies changes allowing for decrements. 

A copy of the workbook created at this stage can be found here:
https://bitbucket.org/pb_bwfc/calceng/src/bf3f40dd49673c60275a27a7ea5cddbc9ec94249/SingleProj/Excel/SingleProj_v3.xlsm?at=default

Years, Ages and Durations

We first need to create FCell cells to hold these projected values. For these calculations, FCell includes a very useful feature - CellArray. This is described as:
  • "CellArray<'T> is a convenience type which wraps Cell<'T> array." 
This is described further in the FCell documentation.

The code to create the cells is shown below:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
[<XlInvisible>]
module Model = 
    //Decrements
    let sz = 361
    let toMonthly x = 1. - (1. - x) ** (1./12.)
    let elaps = Cell<int>.Create()
    let tend = Cell<int>.Create()
    let t = CellArray<int>.Create([|0..sz-1|])
    let calMonth = CellArray<int>.Create(sz)
    let calYear = CellArray<int>.Create(sz)
    let currAge1 = CellArray<int>.Create(sz)
    let currAge2 = CellArray<int>.Create(sz)
    let dur = CellArray<int>.Create(sz)

  • Line 4 creates a variable sz to hold the size of the arrays. The projection is monthly. The value chosen will allow a projection of 360 months, i.e. 30 years.
  • Line 5 is a utility function that can convert a yearly rate to a monthly rate.
  • Lines 6 and 7 create simple cells to hold the elapsed months at the start of the projection and the elapsed months at the end of the projection.
  • Lines 8 to 13 create cell arrays of size sz for time in months, calendar months, calendar years, attained ages and duration in months.
The code to calculate and display the values in Excel is:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
[<XlWorkbookOpen>]
let workbookOpen () = 
    //Decrements
    elaps.SetFormula(pol, startDate, fun p s -> 12 * (s.Year - p.EntryYear) + s.Month - p.EntryMonth + 1)
    tend.SetFormula(pol,elaps,fun p e -> p.Term * 12 - e)
    tend.AsObservable.Add(fun t -> ThisWorkbook.SetValue([0..t], "Decrements", 2, 1, t+2, 1))
    let getCalMonth (t : int) (te:int) (s : DateTime) = 
        if t>te then 0
        else (t+s.Month-1)%12+1
    calMonth.SetFormula(t, !!tend, !!startDate, getCalMonth)
    calMonth.AsObservable.Add(fun cm -> ThisWorkbook.SetValue(cm, "Decrements", 2, 2, cm.Length+1, 2))
    let getCalYear (t : int) (te:int) (s : DateTime) = 
        if t>te then 0
        else (t+s.Year*12+s.Month-1)/12
    calYear.SetFormula(t, !!tend, !!startDate, getCalYear)
    calYear.AsObservable.Add(fun cy -> ThisWorkbook.SetValue(cy, "Decrements", 2, 3, cy.Length + 1, 3))
    let getCurrAge (t : int) (te:int) (a : int) (e: int)= 
        if t>te then 0
        elif a = -1 then -1
        else a + (t+e)/12
    currAge1.SetFormula(t, !!tend, !!pol, !!elaps, fun t te p e -> getCurrAge t te (int (p.EntryAge1)) e)
    currAge1.AsObservable.Add(fun ca -> ThisWorkbook.SetValue(ca, "Decrements", 2, 4, ca.Length + 1, 4))
    currAge2.SetFormula(t, !!tend, !!pol, !!elaps, fun t te p e -> getCurrAge t te (int (p.EntryAge2)) e)
    currAge2.AsObservable.Add(fun ca -> ThisWorkbook.SetValue(ca, "Decrements", 2, 5, ca.Length + 1, 5))
    dur.SetFormula(t,!!tend, !!elaps, fun t te e -> if t>te then 0 else t+e)
    dur.AsObservable.Add(fun d -> ThisWorkbook.SetValue(d, "Decrements", 2, 6, d.Length + 1, 6))
  • Line 4 sets the value of elaps (the elapsed months), calculated simply from the dates in the Pol and Settings sheets.
  • Line 5 then calculates tend (the last month) using the elapsed months and the policy term.
  • Line 6 then updates column A of the sheet Decrements when tend changes.
  • Lines 7 to 9 define a function used to calculate the calendar month given t (elapsed months), te (the last month) and s (the start date).
  • Line 10 sets the formula for calMonth to use this function. Note the use of the !! operator that converts two of the inputs to be CellArray as required by SetFormula for a CellArray variable.
  • Line 11 updates column B of the sheet Decrements when calMonth changes.
  • Lines 12 to 14 define a function used to calculate the calendar year given t (elapsed months), te (the last month) and s (the start date).
  • Line 15 sets the formula for calYear to use this function.
  • Line 16 updates column C of the sheet Decrements when calYear changes.
  • Lines 17 to 20 define a function used to calculate the current age given t (elapsed months), te (the last month), a (entry age) and e (the initial elapsed months).
  • Line 21 sets the formula for currAge1 to use this function.
  • Line 22 updates column D of the sheet Decrements when currAge1 changes.
  • Line 23 sets the formula for currAge2 to use this function.
  • Line 24 updates column E of the sheet Decrements when currAge2 changes.
  • Line 25 sets a simple formula for dur which starts at elaps and increases by 1 each month.
  • Line 26 updates column F of the sheet Decrements when dur changes.
When you build this version and click the Calculate Now button, then the formulas in sheet Decrements for columns A to F get replaced by values generated by the Calculation Engine.
 

Rates and Numbers

We first need to create FCell cells to hold these projected values. The code to create the cells is shown below:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
[<XlInvisible>]
module Model = 
    //Decrements
    let rate1 = CellArray<float>.Create(sz)
    let rate2 = CellArray<float>.Create(sz)
    let rate1_2 = CellArray<float>.Create(sz)
    let numDeaths = CellArray<float>.Create(sz,0.0)
    let numLapses = CellArray<float>.Create(sz,0.0)
    let numMats = CellArray<float>.Create(sz,0.0)
    let numPols = CellArray<float>.Create(sz,1.0)

  • Lines 4 to 6 create cell arrays of size sz for mortality rates for first age, second age and a combined rate.
  • Lines 7 to 10 create cell arrays of size sz for numbers of deaths, lapses, maturities and overall polcies remaining. 
The code to calculate and display the values in Excel is:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
[<XlWorkbookOpen>]
let workbookOpen () = 
    //Decrements
    let getRate (t:int) (te:int) (cy:int) (ca:int) (mtb:mortTable) (itb:Imps) = 
        if t>te then 0.0 
        else 
            let mrt i = 
                match mtb with
                |Select(per, rates) -> failwith "TODO"
                |Ultimate(rates) -> rates.[i]
            let imp i = 
                if ca = -1 then 0.0 
                else(itb.ImpRates.[ca,cy-itb.StartYear] * (mrt ca))|>toMonthly
            t|>imp
    rate1.SetFormula(t,!!tend,calYear,currAge1,!!maleMort,!!maleMortImprov,getRate)
    rate1.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Decrements", 2, 7, r.Length + 1, 7))
    rate2.SetFormula(t,!!tend,calYear,currAge2,!!femaleMort,!!femaleMortImprov,getRate)
    rate2.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Decrements", 2, 8, r.Length + 1, 8))
    rate1_2.SetFormula(rate1,rate2,fun a b -> a + b - a*b)
    rate1_2.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Decrements", 2, 9, r.Length + 1, 9))
    numDeaths.[1..].SetFormula(t.[1..],!!tend,rate1_2.[1..],numPols,fun t te r p -> if t>te then 0.0 else p * r)
    numDeaths.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Decrements", 2, 10, r.Length + 1, 10))
    numLapses.[1..].SetFormula(t.[1..],!!tend,!!lapses,dur.[1..],numPols,numDeaths.[1..],fun t te (lp:float[]) d np nd -> if t>te then 0.0 else (1.0 - (1.0 - lp.[d]) ** (1.0/12.0)) * (np-nd))
    numLapses.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Decrements", 2, 11, r.Length + 1, 11))
    numMats.[1..].SetFormula(t.[1..],!!tend,numPols,numDeaths.[1..],numLapses.[1..],fun t te np nd nl -> if t=te then np-nd-nl else 0.0)
    numMats.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Decrements", 2, 12, r.Length + 1, 12))
    numPols.[1..].SetFormula(t.[1..],!!tend,numPols,numDeaths.[1..],numLapses.[1..],numMats.[1..],fun t te np nd nl nm -> if t>te then 0.0 else np-nd-nl-nm)
    numPols.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Decrements", 2, 13, r.Length + 1, 13))

  • Lines 4 to 14 define a function used to calculate the calendar month given t (elapsed months), te (the last month), cy (the calendar year), ca (current age), mtb (the mortality table) and itb (mortality improvements).
  • Line 15 sets the formula for rate1 to use this function.
  • Line 16 updates column G of the sheet Decrements when rate1 changes.
  • Line 17 sets the formula for rate2 to use this function.
  • Line 18 updates column H of the sheet Decrements when rate2 changes.
  • Line 19 sets a simple formula for rate1_2 based on rate1 and rate2.
  • Line 20 updates column I of the sheet Decrements when rate1_2 changes.
  • Line 21 sets a simple formula for numDeaths, which just applies a rate to the numPols from the previous period. Note the use of [1..] to use variables from the previous month.
  • Line 22 updates column J of the sheet Decrements when numDeaths changes.
  • Line 23 sets a simple similar formula for numLapses, which just applies a rate to the numPols from the previous period less the numDeaths.
  • Line 24 updates column K of the sheet Decrements when numLapses changes.
  • Line 25 sets a simple formula for numMats, which just sets it to remove all policies at tend.
  • Line 26 updates column L of the sheet Decrements when numMats changes.
  • Line 27 sets a simple formula for numPols, which just sets it to the previous period value less the decrements. Note the use of numPols within the formula to make use of the previous month's value.
  • Line 28 updates column M of the sheet Decrements, when numPols changes.
When you build this version and click the Calculate Now button, then the formulas in sheet Decrements for columns G to M get replaced by values generated by the Calculation Engine.

To test that this is functioning correctly, you can try changing the lapse rates from 0.05 to 0.1. If you now click the Calculate Now button, then the values suitably update, e.g the value of BEL in cell J2 of sheet Values changes from -70.3 to -67.1.

Wednesday, 27 May 2015

FCell Calculation Engine - loading Mortality

Introduction

In this post, we will be loading Mortality rates created using Mortality Manager (see post http://fsharpactuary.blogspot.co.uk/2015/03/fcell-30-and-updates.html).

If you look at the Excel only version, you will see that there are mortality improvement factors stored in sheets MaleMortImprov and FemaleMortImprov. There are then base factors stored in sheets MaleMort and FemaleMort.

For the former, I will use a simple approach of treating these in the same way as the other inputs and just use them as data read from Excel. For the latter, I will read these from files held in JSON format created by Mortality Manager.

A copy of the workbook created at this stage can be found here:
https://bitbucket.org/pb_bwfc/calceng/src/fd38bc66998c224d7e682ec6aaf9f1cda5717a72/SingleProj/Excel/SingleProj_v2.xlsm?at=default

Mortality Improvements

The improvement factors were created by Mortality Manager, but then were simply pasted into the spreadsheet. The simplest option is to just load the rates into a 2D array of floats. It is also necessary to load in the range of calendar years for which the rates apply. The type Imps used is defined as follows:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
//define type for improvements
[<XlInvisible>]
type Imps = 
    { StartYear : int
      EndYear : int
      ImpRates : float [,] }
    //define conversion from Excel data to improvements data type
    static member op_Explicit (v : XlData) : Imps = 
        match v with
        | XlArray2D(arr) -> 
            let lastcol = (arr |> Array2D.length2) - 1
            let rts = arr.[1..121, 1..lastcol] |> Array2D.map (!!)
            { StartYear = (!!arr.[0, 1] : float) |> int
              EndYear = (!!arr.[0, lastcol] : float) |> int
              ImpRates = rts }
        | _ -> raise (new ArgumentException("Improvements data invalid"))

  • Line 2 ensures that this code is not treated as a source for Excel UDFs by FCell
  • Lines 4 to 6 define the fields of the Imps Record type and each is given a suitable type.
  • Lines 8 to 16 handle conversion from the FCell XlData type to the Imps type.
  • Line 8 uses op_Explicit to define casting from XlData.
  • Lines 10 to 15 handle the valid case of being provided with a range of the right shape. The code uses the !! operator provided with FCell, which converts an Excel cell to a float or string automatically. In two of the lines, we then need to convert to the int type.
  • Line 16 handles any errors.
The data is then loaded using this code:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
[<XlInvisible>]
module Model = 
    //Mortality
    let maleMortImprov = Cell<Imps>.Create()
    let femaleMortImprov = Cell<Imps>.Create()

    [<XlWorkbookOpen>]
    let workbookOpen () = 
        //Mortality
        ThisWorkbook.SubscribeCell(maleMortImprov, "MaleMortImprov", "A1:EC122") |> ignore
        ThisWorkbook.SubscribeCell(femaleMortImprov, "FemaleMortImprov", "A1:EC122") |> ignore
        //temporary code to confirm imps loads
        maleMortImprov.AsObservable.Add(fun m -> MessageBox.Show("Loaded male improvement rates at 0 " + m.ImpRates.[0,0].ToString())|>ignore)

  • Lines 4 and 5 creates Calculation Engine cells containing Imps Records.
  • Lines 10 and 11 uses the FCell SubscribeCell, which binds the records to ranges A1:EC122 in the relevant sheets. When you enter a new value in these ranges, the cells will update their values as well.
  • Line 13 has some temporary code which exposes maleMortImprov as IObservable<Imps>, so that when a new value has been set or recalculated a temporary function is triggered to show a MessageBox.
When you build this version and make an edit to any cell in the sheet MaleMortImprov, then the MessageBox is shown.

Base Mortality Rates

The base mortality rates were created by Mortality Manager, and we will load these from JSON files. To achieve this we could just reference the Dlls used in Mortality Manager. To simplify the explanation and deployment, instead I have just embedded a copy of some of the code used in the workbook. A reference is however needed to Newtonsoft.Json.Dll, which is added using the References tab loaded using the Doc .NET Addin button.  The mortality code is:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
36: 
37: 
38: 
39: 
40: 
41: 
42: 
43: 
44: 
45: 
46: 
47: 
//local mort table
type mortTyp = 
    | Sel
    | Ult

type mortInfo = 
    { nation : string
      reference : string
      typ : mortTyp
      description : string
      comments : string
      uri : string }

type mortTable = 
    | Select of int * float [] []
    | Ultimate of float []

type MortTbl = 
    { Name : string
      MortInfo : mortInfo
      MortRates : mortTable }

type MortIndx = 
    { Nation : string
      LocalFile : string
      Uri : string }

type MortInds = System.Collections.Generic.Dictionary<string, MortIndx>

module Mort = 
    /// load morttbl from json
    let load jfil = 
        let jfol = @"I:\vs\CalcEng\SingleProj\Mort"
        let jsonfil = Path.Combine(jfol, jfil)
        let mtab = JsonConvert.DeserializeObject<MortTbl>(File.ReadAllText jsonfil)
        mtab

module Ind = 
    /// load morttbl inds from json
    let load() = 
        let jfol = @"I:\vs\CalcEng\SingleProj\Mort"
        let jsonfil = Path.Combine(jfol, "Index.json")
        
        let mtabs = 
            if File.Exists jsonfil then JsonConvert.DeserializeObject<MortInds>(File.ReadAllText jsonfil)
            else new System.Collections.Generic.Dictionary<string, MortIndx>()
        mtabs

  • Lines 2 to 4 define a Discriminated Union type to define whether a Select or Ultimate table.
  • Lines 6 to 12 define details about the mortality table.
  • Lines 14 to 16 define a Discriminated Union type to define rates for a mortality table, depending on whether a Select or Ultimate table.
  • Lines 18 to 21 define the mortality table itself.
  • Lines 23 to 26 define the index for a mortality table.
  • Line 28 defines a dictionary that holds the mortality indices.
  • Lines 30 to 36 is a module to load a table from a JSON file.
  • Lines 38 to 47 is a module to load the indices from a JSON file.
 Lines 33 and 41 need to be edited to specify the tables used. A copy of these tables can be found here:
https://bitbucket.org/pb_bwfc/calceng/src/308b1e991fbc64b46c4d0e439bfddcbe811b8c2c/SingleProj/Mort/?at=default

The data is then loaded using this code:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
[<XlInvisible>]
module Model = 
    //Mortality
    let maleMort = Cell<mortTable>.Create()
    let femaleMort = Cell<mortTable>.Create()

    [<XlWorkbookOpen>]
    let workbookOpen() = 
        //Mortality
        let LoadMort(nm : string) = 
            try
                let mortinds = Ind.load()
                let ind = mortinds.[nm]
                let tbl = Mort.load ind.LocalFile
                let rts = tbl.MortRates
                rts
             with
             |ex ->  MessageBox.Show("Load mort rates failed with error : " + ex.Message)|>ignore
                     Ultimate([||])
        maleMort.SetFormula(maleMortNm, LoadMort)
        femaleMort.SetFormula(femaleMortNm, LoadMort)
        let ShowMort shnm (rts : mortTable) = 
            match rts with
            | Select(per, rates) -> failwith "TODO"
            | Ultimate(rates) -> 
                let hdr = [ "Age"; "Ult q" ]
                ThisWorkbook.SetValue(hdr, shnm, 1, 1, 1, 2)
                ThisWorkbook.SetValue([ 0..120 ], shnm, 2, 1, 122, 1)
                ThisWorkbook.SetValue(rates, shnm, 2, 2, 122, 2)
        maleMort.AsObservable.Add(ShowMort "MaleMort")
        femaleMort.AsObservable.Add(ShowMort "FemaleMort")

  • Lines 4 and 5 creates Calculation Engine cells containing mortTable Records.
  • Lines 10 to 19 define a function to load a mortality table.
  • Lines 20 and 21 use SetFormula to load the rates using the names of the tables defined in the Settings sheet.
  • Lines 22 to 29 define the ShowMort function that shows the rates in a sheet whose name is defined using the variable shnm. (Note that this fails with Select tables currently - see line 24).
  • Lines 30 and 31 call the ShowMort function when the the mortality cells change.
When you build this version and make an edit to the names of the mortality tables, then the rates get updated when you click the Calculate Now button.

Tuesday, 19 May 2015

FCell Calculation Engine - Loading the Inputs

Introduction

In this post, we will just be loading inputs stored in sheets of the Excel workbook so that they are available for use by the Calculation Engine. In the sections below, we will cover loading:
  1. Policy Data
  2. General Settings
  3. Investment Rates and Illiquidity Premiums
  4. Lapse Rates
The model at the end of the post will therefore not be doing anything, but processing of these inputs will be added in subsequent posts.

A copy of the workbook created at this stage can be found here:
https://bitbucket.org/pb_bwfc/calceng/src/308b1e991fbc64b46c4d0e439bfddcbe811b8c2c/SingleProj/Excel/SingleProj_v1.xlsm?at=default

 

Policy Data

The policy data is loaded from sheet Pol. It seems sensible to load this into a F# Record. We therefore use this code to define the type Pol:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
36: 
37: 
//define type for policies
[<XlInvisible>]
type Pol = 
    { EntryYear : int
      EntryMonth : int
      EntryAge1 : float
      Sex1 : string
      EntryAge2 : float
      Sex2 : string
      DeathBen : float
      MaturityBen : float
      Term : int
      PremTerm : int
      AnnPrem : float
      Freq : int
      EscRate : float
      PolNum : string }
    //define conversion from Excel data to policy data type
    static member op_Explicit (v : XlData) : Pol = 
        match v with
        | XlArray2D(arr) when arr.GetLength(0) = 1 && arr.GetLength(1) = 14 -> 
            { EntryYear = (!!arr.[0, 0] : float) |> int
              EntryMonth = (!!arr.[0, 1] : float) |> int
              EntryAge1 = !!arr.[0, 2]
              Sex1 = !!arr.[0, 3]
              EntryAge2 = !!arr.[0, 4]
              Sex2 = !!arr.[0, 5]
              DeathBen = !!arr.[0, 6]
              MaturityBen = !!arr.[0, 7]
              Term = (!!arr.[0, 8] : float) |> int
              PremTerm = (!!arr.[0, 9] : float) |> int
              AnnPrem = !!arr.[0, 10]
              Freq = (!!arr.[0, 11] : float) |> int
              EscRate = !!arr.[0, 12]
              PolNum = !!arr.[0, 13] }
        | XlArray2D(arr) -> raise (new ArgumentException("wrong array size"))
        | _ -> raise (new ArgumentException("Model Point invalid"))
  • Line 2 ensures that this code is not treated as a source for Excel UDFs by FCell
  • Lines 4 to 17 define the fields of the Pol Record type and each is given a suitable type.
  • Lines 19 to 37 handle conversion from the FCell XlData type to the Pol type.
  • Line 19 uses op_Explicit to define casting from XlData.
  • Lines 21 to 35 handle the valid case of being provided with a range of the right shape. The code uses the !! operator provided with FCell, which converts an Excel cell to a float or string automatically. In some lines (e.g. line 22), we then need to convert to the type used in the Record.
  • Line 36 handles the case where the range is not the right shape.
  • Line 37 handles any other errors.

The data is then loaded using this code:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
[<XlInvisible>]
module Model = 
    //Pol
    let pol = Cell<Pol>.Create()

    [<XlWorkbookOpen>]
    let workbookOpen () = 
        //Pol
        ThisWorkbook.SubscribeCell(pol, "Pol", "A2:N2") |> ignore
        //temporary code to confirm pol loads
        pol.AsObservable.Add(fun p -> MessageBox.Show("Loaded policy number " + p.PolNum)|>ignore)

  • Line 4 creates a Calculation Engine cell pol containing a Pol Record.
  • Line 9 uses the FCell SubscribeCell, which binds pol to the range A2:N2 in the sheet Pol. When you enter a new value in that range, the cell will update its value as well.
  • Line 11 has some temporary code which exposes pol as IObservable<Pol>, so that when a new value has been set or recalculated a temporary function is triggered to show a MessageBox.
When you build this version and make an edit to any cell in the specified range, then the MessageBox is shown.

 

General Settings

The settings are loaded from sheet Settings. These are loaded individually using this code (only relevant parts shown):

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
[<XlInvisible>]
module Model = 
    //Settings
    let startDate = Cell<DateTime>.Create()
    let futYears = Cell<int>.Create()
    let inflatRate = Cell<float>.Create()
    let renExp = Cell<float>.Create()
    let maleMortNm = Cell<string>.Create()
    let femaleMortNm = Cell<string>.Create()

    [<XlWorkbookOpen>]
    let workbookOpen () = 
        //Settings
        ThisWorkbook.SubscribeCell(startDate, "Settings", "B2") |> ignore
        ThisWorkbook.SubscribeCell(futYears, "Settings", "B3") |> ignore
        ThisWorkbook.SubscribeCell(inflatRate, "Settings", "B4") |> ignore
        ThisWorkbook.SubscribeCell(renExp, "Settings", "B5") |> ignore
        ThisWorkbook.SubscribeCell(maleMortNm, "Settings", "B6") |> ignore
        ThisWorkbook.SubscribeCell(femaleMortNm, "Settings", "B7") |> ignore
        //temporary code to confirm renExp loads
        renExp.AsObservable.Add(fun r -> MessageBox.Show("Loaded renExp " + r.ToString())|>ignore)

  • Lines 4 to 9 creates Calculation Engine cells for each item of appropriate types. FCell can automatically handle these types.
  • Lines 14 to 19 uses the FCell SubscribeCell, which binds each item in the sheet Settings. When you enter a new value in that range, the relevant cell will update its value as well.
  • Line 21 has some temporary code which exposes renExp as IObservable<float>, so that when a new value has been set or recalculated a temporary function is triggered to show a MessageBox.
When you build this version and make an edit to cell B5, then the MessageBox is shown.

Investment Rates and Illiquidity Premiums

The rates are loaded from sheets InvRates and IllPrem. These are loaded using this code (only relevant parts shown):

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
[<XlInvisible>]
module Model = 
    //Investment
    let invRates = Cell<float []>.Create()
    let illPrem = Cell<float []>.Create()

    [<XlWorkbookOpen>]
    let workbookOpen () = 
        //Investments
        ThisWorkbook.SubscribeCell(invRates, "InvRates", "B2:B136") |> ignore
        ThisWorkbook.SubscribeCell(illPrem, "IllPrem", "B2:B136") |> ignore
        //temporary code to confirm invRates loads
        invRates.AsObservable.Add(fun i -> MessageBox.Show("Loaded invRates at 0 " + i.[0].ToString())|>ignore) 
  • Lines 4 and 5 creates Calculation Engine cells invRates and illPrem of type float[] . FCell can automatically handle this type.
  • Lines 10 and 11 uses the FCell SubscribeCell, which binds the ranges in sheets InvRates and IllPrem. When you enter a new value in these ranges, the relevant cell will update its value as well.
  • Line 13 has some temporary code which exposes invRates as IObservable<float[]>, so that when a new value has been set or recalculated a temporary function is triggered to show a MessageBox.
When you build this version and make an edit in sheet invRates, then the MessageBox is shown.

Lapses

The rates are loaded from the sheet Lapses. These are loaded using this code (only relevant parts shown):

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
[<XlInvisible>]
module Model = 
    //Lapses 
    let lapses = Cell<float []>.Create()

    [<XlWorkbookOpen>]
    let workbookOpen () = 
        //Lapses
        ThisWorkbook.SubscribeCell(lapses, "Lapses", "B2:B136") |> ignore
        //temporary code to confirm invRates loads
        lapses.AsObservable.Add(fun l -> MessageBox.Show("Loaded lapses at 0 " + l.[0].ToString())|>ignore)
  • Line 4 creates Calculation Engine cell lapses of type float[].
  • Line 9 uses the FCell SubscribeCell, which binds the ranges in sheet Lapses. When you enter a new value in that range, the cell will update its value as well.
  • Line 11 has some temporary code which exposes lapses as IObservable<float[]>, so that when a new value has been set or recalculated a temporary function is triggered to show a MessageBox.
When you build this version and make an edit in sheet Lapses, then the MessageBox is shown.