Monday, 11 May 2015

FCell Calculation Engine - a Simple Projection Model

Introduction

I have recently stopped mainstream actuarial systems contract work, so I have now more time to devote to F#. I should therefore (hopefully!) be able to generate a few more posts.

The next series of posts will be on the Calculation Engine added to FCell 3.0 - see http://fcell.io/Help_3_0/Index.html.This allows us to replicate an Excel model using pure F# code that can then be run in Excel or anywhere else!

The model I will use is a simple projection model of a Term product including projected profits and a calculation of the best estimate liability (BEL).

Spreadsheet Model

The spreadsheet model is stored on BitBucket at:
 https://bitbucket.org/pb_bwfc/calceng/src/2797abf436fc46a7bb60f077af84eb0375c35c78/SingleProj/Excel/SingleProjXl.xlsx?at=default

The Sheet "Notes" describes the contents:

Sheet Description
Pol Information for the modelled policy, including entry date, age, term, benefits and premiums.
Settings General settings.
InvRates Investment returns for each projection year.
IllPrem Illiquidity premium for each projection year.
Lapses Lapse rates by policy duration year.
MaleMortImprov Mortality improvements factors for males by age and calendar year.
FemaleMortImprov Mortality improvements factors for females by age and calendar year.
MaleMort Mortality rates for males by age.
FemaleMort Mortality rates for females by age.
Decrements Projection of decrements.
Premiums Projection of premiums received.
Expenses Projection of expenses incurred.
Revenue Projection of income and outgo.
Values Projection of values including best estimate liability (BEL).
Results NOT USED: To be used for results when extended for multiple policies.

Blog Series

The intention in the series is to build an equivalent model that uses the Calculation Engine. One key advantage of using this technology is that it allows you to create a working model that uses both the Excel facilities and those provided by the Calculation Engine. We can therefore build the model in stages, gradually adding the Calculation Engine functionality, whilst still retaing a fully working model.

We will create both a model that has the Calculation Engine embedded in the Workbook using the FCell F# Editor and also created a version that uses a separate DLL built using Visual Studio.

The plan for the series is:
  1. Setting up the development environment, including support for debugging using Visual Studio
  2. Loading the inputs from Excel
  3. Accessing mortality rates created using Mortality Manager
  4. Modelling Decrements
  5. Modelling Cashflows
  6. Modelling Values including calculating BEL
We will then look at how the Calculation Engine model created can be extended to handle multiple policies and run in different environments.

No comments:

Post a Comment