Blog

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:

[white_box]
Day Premium = Total Premium / No days
Earned Premium = Day Premium * No passed days
[/white_box]

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:

[white_box]
Day Premium = 1200 / 365 = 3,28
Earned Premium = 3,28 * 180 = 591
[/white_box]

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:

[green_box]
Policy1 Is in effect now and is valid a complete calendar year.
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.
[/green_box]

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:

[white_box]
January – 1%
February – 1%
March – 1%
April – 7%
May – 15%
June – 25%
July – 25%
August – 15%
September – 7%
October – 1%
November – 1%
December – 1%
[/white_box]

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: