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.

No comments:

Post a Comment