Wednesday, 3 June 2015

FCell Calculation Engine - modelling discounted values

Introduction

In the earlier posts, we have set up the inputs to the projection model and the projection of decrements and cashflows. In this post, we will be using the cashflows and information on discount rates to generate discounted values.

If you look at the Excel only version in sheet Values, the discount factors are first projected and then these are used to calculate the discounted values of the cashflows previously calculated in the sheet Revenue.

A copy of the workbook created which completes the replication of the Excel model can be found here:
https://bitbucket.org/pb_bwfc/calceng/src/8dc2b20e0d9bcfefcbc53c5a00998c7ce92b3348/SingleProj/Excel/SingleProj_v5.xlsm?at=default


Discount Factors

We first need to create FCell cells to hold these projected values. For these calculations, we will again use CellArray to make it easier to generate a value for each projected month.

The code to create the cells is shown below:

1: 
2: 
3: 
4: 
5: 
6: 
[<XlInvisible>]
module Model = 
    //Values
    let iRates = CellArray<float>.Create(sz,0.0)
    let iPrem = CellArray<float>.Create(sz,0.0)
    let discRates = CellArray<float>.Create(sz,0.0)

In each case, we create arrays of floats for 30 years and we initially populate each element with 0.0.

The code to calculate and display the values in Excel is:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
[<XlWorkbookOpen>]
let workbookOpen() = 
    //Values
    tend.AsObservable.Add(fun t -> ThisWorkbook.SetValue([0..t], "Values", 2, 1, t+2, 1))
    calMonth.AsObservable.Add(fun cm -> ThisWorkbook.SetValue(cm, "Values", 2, 2, cm.Length+1, 2))
    calYear.AsObservable.Add(fun cy -> ThisWorkbook.SetValue(cy, "Values", 2, 3, cy.Length+1, 3))
    iRates.[1..].SetFormula(t.[1..],!!tend,!!invRates,fun t te (i:float[]) -> if t>te then 0.0 else i.[(t-1)/12])
    iRates.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Values", 2, 4, r.Length + 1, 4))
    iPrem.[1..].SetFormula(t.[1..],!!tend,!!illPrem,fun t te (i:float[]) -> if t>te then 0.0 else i.[(t-1)/12])
    iPrem.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Values", 2, 5, r.Length + 1, 5))
    discRates.SetFormula(t,!!tend,iRates,iPrem,fun t te ir ip -> if t>te then 0.0 else 1.0/(1.0+ir+ip)**(1.0/12.0))
    discRates.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Values", 2, 6, r.Length + 1, 6))

  • Line 4 updates column A of the sheet Values when tend changes.
  • Line 5 updates column B of the sheet Values when calMonth changes.
  • Line 6 updates column C of the sheet Values when calYear changes.
  • Line 7 sets the formula for iRates for the interest rate for each month, using the rates in the InvRates sheet.
  • Line 8 updates column D of the sheet Values when iRates changes.
  • Line 9 sets the formula for iPrem for the illiquidity premium for each month, using the rates in the IllPrem sheet.
  • Line 10 updates column E of the sheet Values when iPrem changes.
  • Line 11 sets the formula for discRates for the discount factors for each month, using iRates and iPrem.
  • Line 12 updates column F of the sheet Values when discRates changes.
When you build this version and click the Calculate Now button, then the formulas in columns A to F in sheet Values get replaced by values generated by the Calculation Engine.

Values

We first need to create FCell cells to hold these projected values to be shown in sheet Values.

The code to create the cells is shown below:

1: 
2: 
3: 
4: 
5: 
6: 
7: 
[<XlInvisible>]
module Model = 
    //Values
    let valPrem = CellArray<float>.Create(sz,0.0)
    let valBen = CellArray<float>.Create(sz,0.0)
    let valExp = CellArray<float>.Create(sz,0.0)
    let bEL = CellArray<float>.Create(sz,0.0)

In each case, we create arrays of floats for 30 years and we initially populate each element with 0.0.

The code to calculate and display the values in Excel is:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
[<XlWorkbookOpen>]
let workbookOpen() = 
    //Values
    valPrem.[0..valPrem.Length-2].SetFormula(t.[0..valPrem.Length-2],!!tend,valPrem.[1..],premInc.[1..],discRates.[1..],fun t te vp p d -> if t>te then 0.0 else vp*d+p)
    valPrem.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Values", 2, 7, r.Length + 1, 7))
    valBen.[0..valBen.Length-2].SetFormula(t.[0..valBen.Length-2],!!tend,valBen.[1..],dthOut.[1..],discRates.[1..],fun t te vb b d -> if t>te then 0.0 else (vb+b)*d)
    valBen.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Values", 2, 8, r.Length + 1, 8))
    valExp.[0..valExp.Length-2].SetFormula(t.[0..valExp.Length-2],!!tend,valExp.[1..],expOut.[1..],taxExp.[1..],discRates.[1..],fun t te ve e tx d -> if t>te then 0.0 else (ve+e-tx)*d)
    valExp.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Values", 2, 9, r.Length + 1, 9))
    bEL.SetFormula(t,!!tend,valPrem,valBen,valExp,fun t te vp vb ve -> if t>te then 0.0 else vb+ve-vp)
    bEL.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Values", 2, 10, r.Length + 1, 10))

  • Line 4 sets the formula for valPrem for the value of the premium income, using premInc and discRates. Note the use of the array slice to calculate a discounted value from the end, starting at the furthest period in the future.
  • Line 5 updates column G of the sheet Values when valPrem changes.
  • Line 6 sets the formula for valBen for the value of the benefit outgo, using dthOut and discRates.
  • Line 7 updates column H of the sheet Values when valBen changes.
  • Line 8 sets the formula for valExp for the value of the expense outgo net of tax, using expOut, taxExp and discRates.
  • Line 9 updates column I of the sheet Values when valExp changes.
  • Line 10 sets the formula for bEL for the best estimate liability, using valPrem, valBen and valExp.
  • Line 11 updates column J of the sheet Values when bEL changes.
When you build this version and click the Calculate Now button, then the formulas in columns G to J in sheet Values get replaced by values generated by the Calculation Engine.

To test that this is functioning correctly, you can try changing the illiquidity premium on the illPrem sheet from 0 to 0.02. 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 -70.1

This completes the replication of the pure Excel model using the FCell Calculation Engine.

Monday, 1 June 2015

FCell Calculation Engine - modelling cashflows

Introduction

In the earlier posts, we have set up the inputs to the projection model and the projection of decrements. In this post, we will be using the decrements and information on benefits and premiums to generate a projection of cashflows.

If you look at the Excel only version in sheets Premiums and Expenses, these are projected as they would progress for a single modeled policy. In the sheet Revenue, these are combined with the projected decrements to create a projection of the income and outgo for the policy allowing for the probability of exiting.

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

Premiums and Expenses

We first need to create FCell cells to hold these projected values. For these calculations, we will again use CellArray to make it easier to generate a value for each projected month.

The code to create the cells is shown below:

1: 
2: 
3: 
4: 
5: 
6: 
7: 
[<XlInvisible>]
module Model = 
    //Premiums
    let prm = CellArray<float>.Create(sz,0.0)
    //Expenses
    let infl = CellArray<float>.Create(sz,0.0)
    let exp = CellArray<float>.Create(sz,0.0)

In each case, we create arrays of floats for 30 years and we initially populate each element with 0.0.

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: 
[<XlWorkbookOpen>]
let workbookOpen () = 
    //Premiums
    tend.AsObservable.Add(fun t -> ThisWorkbook.SetValue([0..t], "Premiums", 2, 1, t+2, 1))
    dur.AsObservable.Add(fun d -> ThisWorkbook.SetValue(d, "Premiums", 2, 2, d.Length + 1, 2))
    prm.[1..].SetFormula(t.[1..],!!tend,!!pol,dur,fun t te p d -> if t>te||d%(12/p.Freq)<>0 then 0.0 else p.AnnPrem/float(p.Freq))
    prm.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Premiums", 2, 3, r.Length + 1, 3))
    //Expenses
    tend.AsObservable.Add(fun t -> ThisWorkbook.SetValue([0..t], "Expenses", 2, 1, t+2, 1))
    infl.[0].SetFormula(inflatRate,fun i -> (1.0 + i)**(-0.5/12.0))
    infl.[1..].SetFormula(t.[1..],!!tend,!!inflatRate,infl,fun t te i il -> if t>te then 0.0 else (1.0 + i)**(1.0/12.0) * il)
    infl.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Expenses", 2, 2, r.Length + 1, 2))
    exp.SetFormula(t,!!tend,!!renExp,infl,fun t te r il -> if t>te then 0.0 else r/12.0 * il)
    exp.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Expenses", 2, 3, r.Length + 1, 3))
  • Line 4 updates column A of the sheet Premiums when tend changes.
  • Line 5 updates column B of the sheet Premiums when dur changes.
  • Line 6 sets the formula for prm to set a value for a premium at each payment date, using the annual premium in the Pol sheet.
  • Line 7 updates column C of the sheet Premiums when prm changes.
  • Line 9 updates column A of the sheet Expenses when tend changes.
  • Line 10 sets the formula for the first month for infl using inflatRate from the Settings sheet.
  • Line 11 sets the formula for infl for all subsequent months again using the inflatRate and infl from the previous month.
  • Line 12 updates column B of the sheet Expenses when infl changes.
  • Line 13 sets the formula for exp using the renExp from the Settings sheet and infl.
  • Line 14 updates column C of the sheet Expenses when exp changes.
When you build this version and click the Calculate Now button, then the formulas in sheets Premiums and Expense get replaced by values generated by the Calculation Engine.

 

Income and Outgo

We first need to create FCell cells to hold these projected values to be shown in sheet Revenue.

The code to create the cells is shown below:

1: 
2: 
3: 
4: 
5: 
6: 
7: 
8: 
9: 
[<XlInvisible>]
module Model = 
    //Revenue
    let premInc = CellArray<float>.Create(sz,0.0)
    let dthOut = CellArray<float>.Create(sz,0.0)
    let expOut = CellArray<float>.Create(sz,0.0)
    let taxExp = CellArray<float>.Create(sz,0.0)
    let outgo = CellArray<float>.Create(sz,0.0)
    let income = CellArray<float>.Create(sz,0.0)

In each case, we create arrays of floats for 30 years and we initially populate each element with 0.0.

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: 
[<XlWorkbookOpen>]
let workbookOpen () = 
    //Revenue
    tend.AsObservable.Add(fun t -> ThisWorkbook.SetValue([0..t], "Revenue", 2, 1, t+2, 1))
    premInc.[1..].SetFormula(t.[1..],!!tend,prm.[1..],numPols,fun t te p np -> if t>te then 0.0 else p*np)
    premInc.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Revenue", 2, 2, r.Length + 1, 2))
    dthOut.SetFormula(t,!!tend,!!pol,numDeaths,fun t te p nd -> if t>te then 0.0 else p.DeathBen*nd)
    dthOut.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Revenue", 2, 3, r.Length + 1, 3))
    expOut.[1..].SetFormula(t.[1..],!!tend,exp.[1..],numPols,fun t te e np -> if t>te then 0.0 else e*np)
    expOut.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Revenue", 2, 4, r.Length + 1, 4))
    taxExp.SetFormula(t,!!tend,expOut,fun t te e -> if t>te then 0.0 else e*0.2)
    taxExp.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Revenue", 2, 5, r.Length + 1, 5))
    outgo.SetFormula(t,!!tend,dthOut,expOut,taxExp,fun t te d e tx -> if t>te then 0.0 else d+e-tx)
    outgo.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Revenue", 2, 6, r.Length + 1, 6))
    income.SetFormula(t,!!tend,premInc,fun t te p -> if t>te then 0.0 else p)
    income.AsObservable.Add(fun r -> ThisWorkbook.SetValue(r, "Revenue", 2, 7, r.Length + 1, 7))
  • Line 4 updates column A of the sheet Revenue when tend changes.
  • Line 5 sets a simple formula for premInc as the product of prm and numPols.
  • Line 6 updates column B of the sheet Revenue when premInc changes.
  • Line 7 sets a simple formula for dthOut as the product of the death benefit and numDeaths.
  • Line 8 updates column C of the sheet Revenue when dthOut changes.
  • Line 9 sets a simple formula for expOut as the product of exp and numPols.
  • Line 10 updates column D of the sheet Revenue when expOut changes.
  • Line 11 sets a simple formula for taxExp based on expOut.
  • Line 12 updates column E of the sheet Revenue when taxExp changes.
  • Line 13 sets a simple formula for outgo as the sum of outgo items.
  • Line 14 updates column F of the sheet Revenue when outgo changes.
  • Line 15 sets a simple formula for income as the sum of income items.
  • Line 16 updates column G of the sheet Revenue when income changes.
When you build this version and click the Calculate Now button, then the formulas in sheet Revenue get replaced by values generated by the Calculation Engine.

To test that this is functioning correctly, you can try changing the renExp on the Settings sheet from 25 to 30. 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 -42.2.

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.