Code
VBA: Cash Flow Model and Obligation
With Visual Basic (VB), we can repilcate a cash flow model n times for n policyholders.
Here we use a simplified obligation model (No spouse benefit, no childern benefit, and no contribution). Pension PVCF depends on the membership type. We include lapse and mortality rates in this model. Life table is the latest United States 2021 life table. We do not consider mortality experience loading or mortality improvement. Retirement age is set at 65.
Model point set is directly collected from another excel file using Excel Macro, see “Data” sheet and “Raw_data.xlsx”. We collect the data from all worksheets belonging to Type A benefit scheme. I do not encourage copy-pasting raw data into your current workbook. This can produce many unexpected errors. A single model point is used to build the obligation model, see “Data” and “CF_Calculation” sheets.
MATLAB: Conditional Mean and Variance Models
The code shows how to estimate conditional mean and variance models, how to choose the best model, and how to make prediction using the estimated model.
R: ROC curve
Fit a logistic regression model using the training dataset and plot the ROC curve for the test data.
- Please find ROC Curve (R Code) here.
Python: Ho-Lee-Model
Ho-Lee Model is the simplest arbitrage-free model since the drift term theta_t is calibrated to match market bond prices. We show how to build a tree of Ho-Lee model and how to obtain the calibrated theta_t for each step.
- Please find Ho and Lee Calibration (Python Code) here.
- Please find Ho and Lee Calibration (Matlab Code) here. (Additional: error1, error2, error3, calibrate_theta_1, calibrate_theta_2, calibrate_theta_3)
VBA: Fundation
(1) Visual Basic Editor (VBE) contains a Project Explorer, a Properties Window, a Code Window, and a Local Window. Local Window allows you to debug your code with F8 (Debug – Step Into).
If one of these windows is accidently closed, you can always add it back with View – Code (F7) / Project Explorer (Ctrl+R) / Properties Window (F4) / Local Window.
(2) Difference among Forms, Module, and Class Module. Module is a place you put code. For example: Function, Sub, etc. Class module allows for creating custom object. Forms contain code that relates to a form.
One can add Module, Forms, or Class Module by right clicking the sheet (the sheet you want to insert code) – Insert – UserForm/Module/Class Module.
(3) ReDim: if the rang of an array depends on a var, use ReDim not Dim to define the array. For example:
For n = 1 To 65
att_age = att_age_store(n)
age_rang = 100- att_age + 1
ReDim qt (1 To age_rang ) As Double
next n
(4) Function and Sub
– Build-in Function: there are build-in functions, such as: sum, max, min, average, power, count, etc. Use Application.WorksheetFunction to call. See for example:
Application.WorksheetFunction.Sum(Price1, Price2); Application.WorksheetFunction.Max(Price1, Price2)
Application.WorksheetFunction.Min(Price1, Price2); Application.WorksheetFunction.Average(Price1, Price2)
Application.WorksheetFunction.Power(Price1, 2); Application.WorksheetFunction.Count(Price1, Price2)
– Custom Function: One can call it by type =new_function() in Excel cell. Function structure should follow:
Option Explicit ‘clear all history
Function cap_income(net_income As Double, oth_income As Double, avs As Double) As Double
Dim gross_income As Double ‘Declaration statement
gross_income = net_income + oth_income ‘Assignment statement
If gross_income >= avs Then ‘If statement
cap_income = avs ‘Result assignment (must)
Else
cap_income = gross_income
End If
End Function
– Sub: with Developer – Insert – Form Controls. A sub structure is similar to a function structure, but no need to have the final name aligned result assignment.
- Please find Simple Calculation xlsm file here.
- “Sheet 1” Excel Macro: call “WorksheetFunction”; “Sheet 2” Excel Macro: define “Custom Function”; “Sheet 3” Excel Macro: Show Msgbox; “Sheet 4” Excel Macro: Annuity Calculation