Calculating Earned Premium in SQL Server

Microsoft SQL Server

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:

Day Premium = Total Premium / No days
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:

Day Premium = 1200 / 365 = 3,28
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:

Earned Premium Demo Policies

Earned Premium Demo 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:

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.

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:

Earned Premium Demo Pro Rata Method

Earned Premium Demo Pro Rata Method

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:

January – 1%
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')
Earned Premium Demo fGetRiskMonths 12 Months

Earned Premium Demo fGetRiskMonths 12 Months

If we call the function with less than one year, we get:

SELECT * FROM dbo.fGetRiskMonths('2015-01-01', '2015-07-15')
Earned Premium Demo fGetRiskMonths Less Than 12 Months

Earned Premium Demo fGetRiskMonths Less Than 12 Months

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')
Earned Premium Demo fGetRiskMonths 12 Months And More

Earned Premium Demo fGetRiskMonths 12 Months And More

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:

Earned Premium Demo Risk Exposure Policy1

Earned Premium Demo Risk Exposure Policy1

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:

Earned Premium Demo Risk Exposure Policy2_

Earned Premium Demo Risk Exposure Policy2_

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:

Earned Premium Demo Risk Exposure Policy3

Earned Premium Demo Risk Exposure Policy3

Policy4 – Valid 2016-01-01 — 2016-12-31, Total Premium 1000

Policy4 starts in 2016, so it is completely unearned:

Earned Premium Demo Risk Exposure Policy4

Earned Premium Demo Risk Exposure Policy4

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:

Earned Premium Demo Risk Exposure Policy5

Earned Premium Demo Risk Exposure Policy5

5 comments » Write a comment

  1. Thank you for this tutorial! I’m working on calculating earned premium right now. I’ll add this to a function so that I don’t have to worry about it!

    I noticed that the Earned Days + Unearned Days is off by 1 from the Total Days figure. If you absolutely need Earned Premium + Unearned Premium to equal back to the original premium, you’ll have to get rid of one day. Maybe instead of adding 1 to both Earned Days and Unearned days after a date difference, you could add a half-day.

    Thanks again!

    • Hi Joe, and thanks for the feedback!

      You are absolutely right, the number of days is one too many if I add it to both earned and unearned. And as you say, the solution is to add only half a day, or to add one day only to either earned or unearned.

      I’ll insert an update notice above. Once again, thanks for the feedback!

      Regards,
      Tomas

  2. Hi Tomas,

    going by your example , say Policy No 1 has endorsement of 200 in the month of May 2015 while Policy No 5 got cancelled say in Mar 2015. So how are we going to calculate the earned premium ?

    Thanks
    Rahul Sahay

    • Hi, I would say the calculation is the same. But there would probably be business rules stating how the situation should be handled in the insurance system. If retroactive changes to coverage are allowed, then the earned premium changes.

  3. One Problem here If the policy will get issue after one or two month of Policy start date then this calculation will be wrong.

Leave a Reply

Required fields are marked *.