### 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.

**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.

**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