How to calculate unearned premium in Excel

Well, so you have accidentally ended up working in an insurance company (trust me, that’s how most of us end up in insurance!). Your first week and your colleagues are throwing up all sorts of insurance lingo and jargon. One of the first one’s you will hear is earned premium’s, so let me help you out here. What really is earned premium?

Insurance companies usually receive premium’s for providing insurance cover in advance. And in return for this premium, the insurer agrees you to cover you for any loss for a fixed time period which is usually a year. Now to discuss earned premium let us take an e.g. Suppose you paid $365 for your home insurance which runs for a year from 01-Dec-2020. The insurance company will generally declare annual results at the end of the year and from a sales perspective because the contract is concluded and money is in the bank, you may be tempted to recognize $365 as revenue earned as part of annual results. But in an insurance context, this is not true- you have only earned premium for the expired portion of risk i.e. in this case the 31 days of December. The remaining portion of premium is unearned and is not recognized as revenue yet.

Hence earned premium is an important consideration for insurance companies. And in my e.g. earlier I have listed out the 365th methodology for calculating earnings which looks like

Earned Premium (EP) = Premium * (Calculation date-Inception date)/(Expiry date- Inception date)

Note — the denominator is simply the policy duration which in most insurance policies is 365 days

As you see, this is simple enough and you can easily do this in excel. But two problems will haunt you. First, even mid-size insurance companies sell upwards of 100,000 policies. Secondly insurance companies like to see the trend of their earnings and build what is called an earnings triangle. Calculating earnings monthly across 5 years etc. is common. And now, we will start to encounter a memory problem in excel as we will need to do 100,000 * 60 computations i.e. 6 million calculations.

So in this article, I am going to use Python’s Pandas library to speed up these calculations and also build a code which you can reuse most of the time. The entire script with a sample dataset can be found in my GitHub repository here.

I have tried to keep this very simple and I like to code step by step. So let us start and examine the sample random dataset I have created. I want to highlight the actual data points we need to complete this job.

So we load the data from excel and as you can see below we will just use need four columns-policy no, inception and expiry dates, premium to figure out the earnings triangle. That’s it

We want the code to be reusable, and the trick here is to dynamically find all calculation dates needed to compute the earnings in most scenarios. If we are doing monthly triangles, we will need to find earnings at every month end from the date the first policy is issued to usually the last month. And this will be our list of calculation dates. We will derive this list by using the inception date column, and

a. finding the first month end date or calculation date for our group of policies and

b. then using an iterator to iterate by every month end till the previous month.

We will achieve a. by using MonthEnd function of pandas tseries and code is as below

For our iterations, we will use dateutil package and use a while loop to iterate as below

Once we have the calculation dates (or month end dates) we will merge this with all rows of our original dataset and then calculate earnings for each policy at every relevant month end date.

We are going to use the 365th formula described previously.

To summarize and produce the actuarial triangles we will use a group by and a pivot

Congrats, you have figured out a very useful insurance metric and with Python can now perform this calculation on a large scale far in excess of excel’s capability . Earned premium triangles are just the beginning of a journey. I hope to write another article soon on claims development triangle where things will start to get really intense!