In most business, when a company receives payment for a sold product, that payment can be considered an income. But to an insurance company, only the part of the premium that has been earned (Earned Premium) can be considered as income. The rest is the unearned premium, or the premium reserve. How the premium is earned vary between insurance products and industries. Here I’ll show a couple of methods and how they are calculated in SQL Server.
Earned Premium – Pro Rata or Accounting Method
The simplest calculation of earned premium is the pro rata, or accounting method. We simply calculate a premium per day, and then multiply this with the number of days passed since the policy start date:
Earned Premium = Day Premium * No passed days
So, if we have an insurance with an yearly premium of 1200 covering the whole 2015, at the middle of the year at 2015-07-01, about half of that premium is earned. Or more exactly:
Earned Premium = 3,28 * 180 = 591
Depending on requirements, a future policy that has not yet started could be considered as unearned as a whole. And an earlier policy that is completed (expired) can be considered as earned as a whole. Note that this may need adjustment to local requirements.
Let’s do this calculation in T-SQL. First we need a sample table with some policies. Run the following to create a new database with a table containing policies:
CREATE DATABASE InsurancePremiumReservesDemo GO USE InsurancePremiumReservesDemo GO CREATE TABLE dbo.Policies ( PolicyNo VARCHAR(20) NOT NULL ,Product CHAR(1) NOT NULL ,StartDate DATE NOT NULL ,EndDate DATE NOT NULL ,Premium FLOAT NOT NULL ) GO INSERT dbo.Policies VALUES ('PolicyNo1', 'A', '2015-01-01', '2015-12-31', 997) ,('PolicyNo2', 'B', '2015-01-01', '2015-07-15', 2000) ,('PolicyNo3', 'C', '2014-01-01', '2014-12-31', 10000) ,('PolicyNo4', 'A', '2016-01-01', '2016-12-31', 1000) ,('PolicyNo5', 'B', '2015-01-01', '2016-07-16', 5000) SELECT * FROM dbo.Policies
That gives us the following policies:
Let’s say we want to calculate the earned premium at 2015-06-30. With regard to this date, we have the following policy periods:
Policy2 Is in effect now, but ends after about 7,5 months after start date.
Policy3 Ended in 2014, so it is not in effect.
Policy4 The start date is at 2016-01-01 and the policy covers the whole calendar year.
Policy5 Is in effect now and is valid about 19,5 months.
The first values we need to calculate concerns the number of days: Total Days, No Days Earned and No Days Unearned. Those values are calculated using an ;WITH statement that is a so called common table expression, or CTE. By using a CTE, I’m able to re-use the calculation of the number of days simply by referencing them by name, rather than doing the same calculation several times:
DECLARE @EarnedToDate DATE SET @EarnedToDate = '2015-06-30' ;WITH CtePolicies AS ( SELECT PolicyNo ,Product ,StartDate ,EndDate ,Premium ,DATEDIFF(DAY, StartDate, EndDate) + 1 AS TotalDays ,CASE WHEN StartDate >= @EarnedToDate THEN 0 --Policy not yet in effect WHEN EndDate < @EarnedToDate THEN DATEDIFF(DAY, StartDate, EndDate) + 1 --Policy completed ELSE DATEDIFF(DAY, StartDate, @EarnedToDate) + 1 --Other cases, ie policy is current END AS EarnedDays ,CASE WHEN StartDate >= @EarnedToDate THEN DATEDIFF(DAY, StartDate, EndDate) + 1 --Policy not yet in effect WHEN EndDate < @EarnedToDate THEN 0 --Policy completed ELSE DATEDIFF(DAY, @EarnedToDate, EndDate) --Other cases, ie policy is current END AS UnearnedDays FROM dbo.Policies ) SELECT * ,Premium / TotalDays AS DayPremium ,CASE WHEN EarnedDays = 0 THEN 0 ELSE Premium / TotalDays * EarnedDays END AS EarnedPremium ,CASE WHEN UnearnedDays = 0 THEN 0 ELSE Premium / TotalDays * UnearnedDays END AS UnearnedPremium FROM CtePolicies
The results from the T-SQL should look like this:
The first 5 columns are the same as in the dbo.Policies table. The rest of the columns are:
TotalDays – This is the total number of days the policy is effective. Note that the DATEDIFF function returns the number of datepart boundaries crossed between the specified startdate and enddate. Therefore I add 1 to all DATEDIFF calculations to get 365 days for a year. Note that we cant’t add one day to both earned and unearned premium, since then we would have one day too many. (Thanks to Joe for commenting on this) In the code above, one day is added to the earned premium only. But be sure to investigate how policy periods should be handled in your own data.
EarnedDays – Returns the number of earned days. This calculation handles three different cases. Policies not yet in effect, Policies completed, and Current Policies.
UnearnedDays – Returns the number of unearned days. The calculation handles the same three cases as EarnedDays.
The rest of the columns are calculated outside of the CTE, in the end of the statement:
DayPremium – The calculation for the daily premium is: Premium / TotalDays.
EarnedPremium – The earned premium is calculated by the statement: CASE WHEN EarnedDays = 0 THEN 0 ELSE Premium / TotalDays * EarnedDays END.
UnearnedPremium – The earned premium is calculated by the statement: CASE WHEN UnearnedDays = 0 THEN 0 ELSE Premium / TotalDays * UnearnedDays END.
The sum of column EarnedPremium gives us the total earned premium at 2015-06-30, and the sum of column UnearnedPremium gives us the premium reserve for the same date.
Earned Premium – Risk Exposure Method
In the calculation above, we distributed the premium proportionally (pro rata) along the number of days the policy is in effect. But some insurance products are earned in a more uneven way. Take for instance the seasonal risk variation in boat policies that carries a greater risk during the summer season than during the winter. Or a traditional warranty, that can carry a greater risk the longer the policy is in effect.
Let’s complicate the example above by assuming that the policies cover boats. After studying the distribution of claim cost, we realize that risk is distributed seasonally along months in the following way:
February – 1%
March – 1%
April – 7%
May – 15%
June – 25%
July – 25%
August – 15%
September – 7%
October – 1%
November – 1%
December – 1%
Remember I’m from Sweden, we have short summer seasons 🙂
Now we want to earn the premiums in a way corresponding to the risk exposure above. First, let’s create a table with the risk months:
CREATE TABLE dbo.RiskMonths ( MonthNo TINYINT NOT NULL ,RiskPercentage TINYINT NOT NULL ) GO INSERT dbo.RiskMonths VALUES (1, 1) ,(2, 1) ,(3, 1) ,(4, 7) ,(5, 15) ,(6, 25) ,(7, 25) ,(8, 15) ,(9, 7) ,(10, 1) ,(11, 1) ,(12, 1)
What we want to do now is to modify the original T-SQL statement so that the seasonal variation comes into play. In order to do this we must first split each policy into as many rows as there are months in the policy. For each of those months, we want to return the weight (RiskPercentage) from above. To simplify things, we will consider the premium to be totally earned after 12 months. Therefore, the premium for month 13 and onward is 0. Further, if the coverage period is shorter than 12 months, we simply divide the premium into equally large parts. The sum of earned and unearned premium must always be 100% of the total premium. That’s why we need to create a function that handles all possible lengths of the policies.
Also note that the calculation is done per month now, not by day. Of course, a risk per day could also be used. But beware of “data explosion”, that is, huge amounts of data since each policy is multiplied with the number of days it covers.
To encapsulate the distribution over month calculation, I created a T-SQL function. This way the code becomes less complex, and a function is easier to maintain. It is also easier to create and test many different premium calculations.
But first we need a Numbers function, just to return a row for each month. The function simply accepts an integer and returns that many rows.
CREATE FUNCTION dbo.GetNums2(@N AS BIGINT) RETURNS TABLE AS RETURN SELECT TOP (@N) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS I FROM SYS.OBJECTS S1 CROSS JOIN SYS.OBJECTS S2 CROSS JOIN SYS.OBJECTS S3 CROSS JOIN SYS.OBJECTS S4 CROSS JOIN SYS.OBJECTS S5 GO
The function to calculate risk for each month is created with this T-SQL:
CREATE FUNCTION dbo.fGetRiskMonths (@StartDate DATE, @EndDate DATE) RETURNS @TblRiskMonths TABLE ( MonthNo TINYINT NOT NULL ,RiskPercentage FLOAT NOT NULL ) AS BEGIN DECLARE @NoMonths INT SELECT @NoMonths = DATEDIFF(MONTH, @StartDate, @EndDate) + 1 IF @NoMonths < 12 BEGIN INSERT @TblRiskMonths SELECT I ,1.00 / (@NoMonths) AS RiskPercentage FROM dbo.GetNums2(@NoMonths) END IF @NoMonths >= 12 BEGIN INSERT @TblRiskMonths SELECT I ,CASE WHEN I <= 12 THEN CAST(RM.RiskPercentage AS FLOAT) / 100 ELSE 0 END AS RiskPercentage FROM dbo.GetNums2(@NoMonths) N LEFT OUTER JOIN dbo.RiskMonths RM ON N.I = RM.MonthNo END RETURN END GO
The function accepts two input parameters, @StartDate and @EndDate. So if we call this function with the period 2015-01-01 to 2015-12-31, we get:
SELECT * FROM dbo.fGetRiskMonths('2015-01-01', '2015-12-31')
If we call the function with less than one year, we get:
SELECT * FROM dbo.fGetRiskMonths('2015-01-01', '2015-07-15')
The value 0,1428571428571 comes from the calculation 1 / 7, since we wanted to distribute the premium proportionally if the insurance period was less than 12 months.
If the period is more than 12 months, month 13 and above gets 0 percent of the premium:
SELECT * FROM dbo.fGetRiskMonths('2015-01-01', '2016-07-15')
The final statement to calculate earned and unearned premiums is similar to the pro rata method, but is calculated with a risk factor per month instead of a day premium:
DECLARE @EarnedToDate DATE SET @EarnedToDate = '2015-06-30' ;WITH CtePolicies AS ( SELECT PolicyNo ,Product ,DATEADD(MONTH, RM.MonthNo-1, P.StartDate) AS RiskMonthStart ,CASE WHEN EndDate >= DATEADD(DAY, -1, DATEADD(MONTH, RM.MonthNo, P.StartDate)) THEN DATEADD(DAY, -1, DATEADD(MONTH, RM.MonthNo, P.StartDate)) ELSE EndDate END AS RiskMonthEnd ,RM.MonthNo ,RM.RiskPercentage ,Premium * RM.RiskPercentage AS MonthPremium FROM dbo.Policies P CROSS APPLY dbo.fGetRiskMonths (StartDate, EndDate) AS RM ) SELECT PolicyNo ,Product ,RiskMonthStart ,RiskMonthEnd ,MonthNo ,RiskPercentage ,MonthPremium ,CASE WHEN RiskMonthStart <= @EarnedToDate THEN MonthPremium ELSE 0 END AS EarnedPremium ,CASE WHEN RiskMonthStart > @EarnedToDate THEN MonthPremium ELSE 0 END AS UnEarnedPremium FROM CtePolicies
Since we calculate the premiums unequally per month, we must use one row for each month the policy is in effect. Let’s walk through each policy.
Policy1 – Valid 2015-01-01 — 2015-12-31, Total Premium 997
Policy1 ends in the middle of the summer so the earned and unearned premiums are split by half:
Policy2 – Valid 2015-01-01 — 2015-07-15, Total Premium 2000
Policy2 ends in July and does not cover a whole year so the premiums are distributed equally along the months. Since the granularity is a month, July gets as large a premium as the other months even if it only covers half of July:
Policy3 – Valid 2014-01-01 — 2014-12-31, Total Premium 10000
Policy3 ended at 2014-21-31, so in this example it is earned as a whole:
Policy4 – Valid 2016-01-01 — 2016-12-31, Total Premium 1000
Policy4 starts in 2016, so it is completely unearned:
Policy5 – Valid 2015-01-01 — 2016-07-16, Total Premium 5000
Policy5 covers 19 months. But only the first 12 carry risk, so premiums are 0 from month 13 and later: