Hi Jarek,
I really don't think this is the best solution, but here's the file. --> //drive.google.com/file/d/1rPvkPq14VYiTm3kGK0nbvmFvdD6c36D_/view?usp=sharing
The reason why I say this is becuase I'm creating a record for each policy/risk/coverage/month.
So, for 1 annual policy with 1 risk and 1 coverage, you will have 12 records, all calculated as the last day of the month.
I did it this way because I can't find the correct way of calculating the things I need, like the earned/unearned premium for the loss ratio, and because we don't have a lot of policies.
The good thing about this design, is that I don't have to do complex calculations, I leverage the out of the box functionalities of Power Bi to run simple aggregations (like sum, avg, count, etc). The more complex calculations you add, the slower your dashboard becomes.
But I will reach the day when the data will be too much data.
All data has been scrambled, so if you find James Bond as a client let me tell you, we don't insured James Bond. High risk and all...
If you have any question, please, let me know.
Gus
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!
Following on from our glossary series of insurance terms and methods in which we outlined Claims Loss Ratio and Combined Ratio, I would like to take a look at the concept of Earned Premium and give you a few examples as to the usage, importance and pitfalls when working with it.
In this article I investigate Earned Premium in the following ways:
What is Earned Premium?
Most insurance policyholders would probably assume that when they pay a premium their Insurers can immediately class this as premium income and incorporate this into their company accounts.
In reality Insurers to do not do this as they only class a premium as being Earned when it is based on the amount of time which has actually elapsed under a contract for an insurance policy.
Why is Earned Premium important? Why bother?
Whilst policyholders pay premiums for their insurance up front, the Insurer must earn the premium by exposing itself to the risk on behalf of the Insured.
Within the accounting systems used by insurers the Earned premium can be counted as part of the profit for a given accounting period while the unearned premium cannot.
For this reason, it is very important for insurance companies and their agents to put software in place to easily calculate Earned Premium.
Earned premium is also often used to calculate Insurers’ loss ratio where total losses for a period are divided by the Earned premium for the corresponding period.
Without determining Earned Premium, the true profitability of any insurance operation cannot be determined, which is why the savvy insurer doesn’t leave home without his Earned Premium report.
Calculating Earned Premium
To determine Earned premium, we need to look at the length of the policy, and determine how much time has already elapsed.
Earned Premium = Total Premium / 365 * Number of Days Elapsed
For example if a 365 day policy with a full premium payment at the commencement of the insurance has been in effect for 180 days, 180/365 of the premium can be considered as being Earned. This will also mean that 185/365 of the premium would have to be considered unearned.
The same rules apply for policies with a term of more than one year, if someone paid a premium for two years of home insurance and 18 months has elapsed the Insurance company has Earned three quarters the premium.
Note that in leap years you will need to use 366 and not 365 in the formula above.
Alternative method:
Instead of using days elapsed it’s also possible to use whole months to calculate Earned Premium. So for instance, if 3 whole months of a two year (24 month) policy have elapsed, the calculation would be as follows. Thanks for George for pointing this out in the comments!
Earned Premium = Total Premium / Full Policy Term in Months * Number of Months Elapsed
i.e. Earned Premium = Total Premium / 24 * 3
More advanced calculations
There are two different methods for calculating earned premiums, an accounting method and an exposure method.
The accounting method is highlighted above and is the more commonly used and is frequently used by Insurers in their corporate income statements.
Under the exposure method, Earned premiums are calculated based on the percentage of total premium that was exposed to loss during the period being calculated and does not take when the premium was actually collected into account.
Common mistakes and how to avoid them
It’s easy to calculate Earned Premium providing you have the right tools. Often we’ve seen mistakes made during manual calculation of Earned Premium or through oversights in Excel spreadsheets. We would always recommend using software to avoid mistakes and make Earned Premium a cornerstone of automatic reporting across your entire operation, but maybe we’re biased!
Feedback
As ever we absolutely thrive on your questions and feedback. Leave us a comment below!
511