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

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.

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.

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

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.

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