Net Earned in Triangles without a Triangular Join?

  • Thom A

    SSC Guru

    Points: 98447

    Something I've been trying to figure today is can I achieve a figure for net earned in our insurance triangles without using a triangular join. I'm sure I could do this with a Window Function, but I can't seem to think of a way to do so.

    The way the figures work is to do with insurance and that the policy has a period of indemnity; for simplicity we'll say that every policy has a period of indemnity of 12 months. Let's say that a policy incepts in January and has a value of £1,200 (pre tax). At the end of January the net Earned for the policy is £100 (ignore that the policy could incept at any point in January, we're specifically just looking at the month). At the end of February, the policy has earned £200, March £300; you get the idea.

    Lets say you then have another policy worth £600 in Feburary. As a result, the net earned value at the end of February is £250 (2 * (1200 / 12) from the policy in January and 1 * (600 / 12) from the policy in February), March would be £400, etc. The way I'm currently working out the cumulative value is using a triangular join. I can't use a SUM with a ROWS BETWEEN clause on it's own, as I have to multiple the value of the premium earned by the amount of months that have passed +1 (and cap at 12).

    With the triangular join, this gives me a solution like this:

    CREATE TABLE dbo.Triangle (EffectiveMonth date,
    FundsTaken decimal(12, 2));
    GO

    INSERT INTO dbo.Triangle
    VALUES ('20190101', 274027.39),
    ('20190201', 319976.3),
    ('20190301', 234576.66),
    ('20190401', 219056.01),
    ('20190501', 267799.13),
    ('20190601', 279493.83),
    ('20190701', 187711.28),
    ('20190801', 331990.56),
    ('20190901', 259942.14),
    ('20191001', 225755.82),
    ('20191101', 300988.78),
    ('20191201', 266831.51),
    ('20200101', 5228.2);
    GO

    WITH
    N AS
    (SELECT N
    FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS N (N)),
    Tally AS
    (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N AS N1,
    N AS N2),
    Months AS
    (SELECT TOP 36 T.EffectiveMonth,
    T.FundsTaken,
    ROW_NUMBER() OVER (ORDER BY T.EffectiveMonth ASC) AS MonthNo
    FROM dbo.Triangle AS T),
    NPE AS
    (SELECT DATEADD(MONTH, T.I - 1, MIN(M.EffectiveMonth) OVER ()) AS EffectiveMonth,
    M.FundsTaken,
    T.I AS MonthNo,
    M.FundsTaken / 12 AS PremiumEarned
    FROM Tally AS T
    LEFT JOIN Months AS M ON T.I = M.MonthNo)
    SELECT N1.EffectiveMonth,
    N1.MonthNo,
    ISNULL(N1.FundsTaken,0) AS FundsTaken,
    CONVERT(decimal(12,2),SUM(N2.PremiumEarned * V.Multiplier)) AS CumulativeEarned
    FROM NPE AS N1
    LEFT JOIN NPE AS N2 ON N1.EffectiveMonth >= N2.EffectiveMonth
    CROSS APPLY (VALUES (IIF(((N1.MonthNo - N2.MonthNo) + 1) > 12, 12, ((N1.MonthNo - N2.MonthNo) + 1)))) AS V (Multiplier)
    WHERE N1.MonthNo IS NOT NULL
    GROUP BY N1.EffectiveMonth,
    N1.MonthNo,
    N1.FundsTaken
    ORDER BY N1.MonthNo ASC;

    GO

    DROP TABLE dbo.Triangle;

    Perhaps this is the way to do it, but it just feels a little wrong. Anyone any ideas, or does this seem like the right way to go?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Matt Miller (4)

    SSC Guru

    Points: 124185

    I think you're making this complicated - by conflating two separate processes.  One is the schedule of earnings (how much from each policy is earned in each accounting period).  You'll need that to deal with all of your edge cases where your earning period may not match with your equity period, or partial months or back/forward dated transactions etc....

    Still you end up with a detail table with earnings by month by policy. Essentially:

    20190101 P1    100

    20190201 P1    100

    20190201 P2     50

    20190301 P1   100

    20190301 P2   50

    etc...

     

    Once you split it out this way - your cumulation becomes easy using

    SUM() over (order by Effective month.... Rows unbounded preceding)

    You can even choose to partition by policy or not.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • drew.allen

    SSC Guru

    Points: 76658

    I didn't round my results, but otherwise this gives the same results.  I also had to add extra values to the table to get all of the dates that you had.

    CREATE TABLE #Triangle (EffectiveMonth date,
    FundsTaken decimal(12, 2));

    INSERT INTO #Triangle
    VALUES ('20190101', 274027.39),
    ('20190201', 319976.3),
    ('20190301', 234576.66),
    ('20190401', 219056.01),
    ('20190501', 267799.13),
    ('20190601', 279493.83),
    ('20190701', 187711.28),
    ('20190801', 331990.56),
    ('20190901', 259942.14),
    ('20191001', 225755.82),
    ('20191101', 300988.78),
    ('20191201', 266831.51),
    ('20200101', 5228.2),
    -- Additional Dates
    ('20210101', 0),
    ('20220101', 0),
    ('20230101', 0),
    ('20240101', 0),
    ('20250101', 0),
    ('20260101', 0),
    ('20260501', 0)
    ;

    WITH Age AS (
    SELECT Age FROM ( VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11) ) a(Age)
    )
    SELECT
    ef.EffectiveMonth
    , SUM(CASE WHEN Age = 0 THEN FundsTaken ELSE 0 END) AS FundsTaken
    , SUM(SUM(FundsTaken / 12)) OVER(ORDER BY ef.EffectiveMonth ROWS UNBOUNDED PRECEDING) AS PremiumEarned
    FROM #Triangle t
    CROSS JOIN Age a
    CROSS APPLY ( VALUES(DATEADD(MONTH, a.Age, t.EffectiveMonth)) ) AS ef(EffectiveMonth)
    GROUP BY ef.EffectiveMonth
    ;

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Thom A

    SSC Guru

    Points: 98447

    Thanks Drew. I think that's what I'm after. The data above was pre-aggregated, so i'll give a go at applying this to the non-preaggregated but I think this is very much on the right path.

    Cheers!

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Thom A

    SSC Guru

    Points: 98447

    Yep, that got me to the answer I needed, Drew. My final query using production data is quite different, but the nested SUM with the OVER is exactly the route I needed.

    Would mark as the solution/answer, but seems that feature has been removed from SSC.

    Thanks again.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply