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.

No comments:

Post a Comment