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.

No comments:

Post a Comment