CALCULATION ENGINES BEYOND EXCEL: PART 1
In the introductory Part 0 we focused on installations and basic setup. If you are following along closely, you should have Python installed along with Git and VS Code. You should have decided where to locally develop, created an online repository, and made your first commit. From here, we will review some Python basics and build a very simple 1 month Universal Life (UL) account value rollforward. If you are skipping Git and not using a virtual environment that should not cause any issues.
In either case, feel free to refer to this repository if things are not behaving as expected and for this part please refer to tag v0.1.
GETTING STARTED WITH PYTHON
Before we jump into the deep end, let's go over a very simple example of writing a function in Python that accepts an input and how we can use it.
In your illustrator directory create a new file called hello.py and within it add the following code:
def hello(name:str) -> None:
print(f"Hello, {name}!")
We are defining a hello function that has a single parameter name and should be of type string. The function will print a message to the console and use the value that was provided to the function. If we provide Kevin to the function we should get the output: Hello, Kevin!
Save the file.
From your terminal enable the virtual environment we created. Assuming you are on a Windows computer and in the illustrator directory you should be able to use the command venv/scripts/activate. Next, start the Python REPL and import the hello function from the hello.py file. Finally, try using the hello function with your name. Your terminal should look something like this:
PS C:\...\illustrator> venv/scripts/activate
(venv) PS C:\...\illustrator> python
Python 3.12.3 (tags/v3.12.3:f6650f9, Apr 9 2024, 14:05:25) [MSC v.1938 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> from hello import hello
>>> hello("Kevin")
Hello, Kevin!
Let's break this down. First, we activate the virtual environment. We can tell that was successful because we should see (venv) at the start of the line in the terminal afterwards. Then we launch the Python REPL using python or python3 so we can interactively execute Python code. Next, we need get access to the code we wrote in hello.py so we ask Python to get the hello function from the hello file. If we instead declared our hello function as hello_name then the syntax would be from hello import hello_name.
We could go down a rabbit-hole with import processes but we'll avoid that. Suffice to say this works for importing our example. We could import the hello.py file as a module and then use our function, that would look like this:
>>> import hello
>>> hello.hello("Kevin")
Hello, Kevin!
Importing the whole hello.py file like this makes calling the hello function a little more verbose in the second line. If you import whole files, modules, or packages this way you can alias them like this:
>>> import hello as h
>>> h.hello("Kevin")
Hello, Kevin!
You can also alias functions if you import them the way we did initially, like this:
>>> from hello import hello as h
>>> h("Kevin")
Hello, Kevin!
Before we go any further, we should make sure we do not track any unnecessary files in our git repository. Open up your .gitignore and add *.pyc and __pycache__/*. This will tell Git to ignore some files that are often generated by VS Code when running Python. All in all your file should look like this:
venv/*
*.pyc
__pycache__/*
Once done, make sure you save your files and commit the changes to your repository.
UL ILLUSTRATOR SIMPLE EXAMPLE
Now that we have some Python basics let's get going with the intended project.
For our initial UL example let's start with a simple 1 month illustration and use the following product design:
Product has premium load, policy fee, per unit load, and COIs
The Net Amount At Risk (NAAR) for COI charges is calculated after expense charges are deducted
The annual interest rate is 1.0%
The death benefit is discounted one month for COI purposes
Charges happen monthly
Premium load: 6% of all premium
Annual per policy fee: $120
Annual per unit load: $12.00 per thousand of face amount
Annual COI: $3.00 per thousand of NAAR
We will assume a $100,000 face amount and a single annual premium of $2,000. We will also assume the death benefit is a level face amount and we will ignore corridor factor complications for simplicity.
Before we write any Python, let's write out what should happen and we can use that as a guide to writing the necessary Python.
The end of month account value should be the start of month account value plus premium less premium loads less expense charges less COI charges plus interest.
The COI charge is the net amount at risk (NAAR) multiplied by the COI rate.
The NAAR is the face amount multiplied by a discount factor less the account value for NAAR.
The account value for NAAR is the start of month value plus the premium less the premium load less the expense charge
The interest will be the value for interest multiplied by the interest rate
The value for interest is the value for NAAR less the COI charge
In formula form:
AV(1) = AV(0) + Premium - Premium Load - Expense Charge - COI + Interest
COI = NAAR x COI Rate
NAAR = Face Amount x Discount Factor - AV NAAR(0)
AV NAAR(0) = AV(0) + Premium - Premium Load - Expense Charge
Interest = Interest Rate x AV Interest(0)
AV Interest(0) = AV NAAR(0) - COI
Hopefully this looks pretty straightforward. We could even break some of these down further if we wanted. Create a new file called simple.py. Within it, add the following code:
if __name__ == '__main__':
face_amount = 100000
interest_rate = 0.01
premium = 2000
premium_load = premium * 0.06
expense_charge = (120 + 12 * face_amount / 1000) / 12
value_for_naar = premium - premium_load - expense_charge
naar = face_amount / (1 + interest_rate)**(1/12) - value_for_naar
coi = (naar / 1000) * 3.00 / 12
value_for_interest = value_for_naar - coi
interest = value_for_interest * ((1 + interest_rate)**(1/12)-1)
end_value = value_for_interest + interest
print(end_value)
Ignoring the first line for a moment, let's walk through what is happening here. We create a new variable face_amount and assign it a value of 100,000. Next we create a new variable interest_rate and assign it a value of 0.01 or 1.0%. We have some blank space for readability followed by creation of premium and setting the value to $2,000. We continue through the order of operations defined earlier in the article and we finally print out the end of month value. Going back to the first line, this is just a simple process to allow us to execute the code as a script. In a terminal within the illustrator directory you should be able to run python simple.py and get some output like this:
(venv) PS C:\...\illustrator> python simple.py
1746.9111495330083
I'll leave it to you to go through the math separately (perhaps in Excel!) to confirm the result. In Part 2 we will walk through how to take this one month toy example and update it to get through a full projection.
If you are stuck on anything it may be useful to refer to the v0.1 tag in this repository.
Have questions or feedback? Reach out to let us know!
updated January 21, 2025 to include links to public repository and Part 2