Performance commerciale Performance technique Ratios de profitabilité : le système C.L.A.I.M.ombined Ratio (Ratio combiné)oss Ratio (Rapport Sinistres à Primes)cquisition Cost Ratio (Taux de commission d’apport)nsurance Operating Cost (or Overhead) Ratio (Ratio de frais assureur)anagement Cost Ratio (Taux de commission de gestion)

Performance commerciale

Expected Gross Written Premium

Description:

The calculation of expected_gross_written_premium prorates the business plan premiums (business_plan_y1, ..., business_plan_y4) across four years based on the months elapsed since the starting_at date. For periods beyond 48 months, the premium extrapolates linearly using the yearly growth rate of business_plan_y4. The formula ensures a smooth cumulative distribution over time.

Formula:

With:

portfolio’s start date (first premium):

Let:

(number of months elapsed),

(yearly business plans, ).

Then:

This formula ensures cumulative growth within each year, with an extrapolation for months exceeding 48.

Note: it is possible to re-write that formula as an one-liner:

Trino SQL (Athena-friendly) formula:

sql
SELECT
    CASE
        WHEN date_diff('month', starting_at, current_date()) < 0 THEN 0 -- Starting date is in the future
        WHEN date_diff('month', starting_at, current_date()) < 12 THEN business_plan_y1 * (date_diff('month', starting_at, current_date()) / 12.0)
        WHEN date_diff('month', starting_at, current_date()) < 24 THEN business_plan_y1 + (business_plan_y2 * ((date_diff('month', starting_at, current_date()) - 12) / 12.0))
        WHEN date_diff('month', starting_at, current_date()) < 36 THEN business_plan_y1 + business_plan_y2 + (business_plan_y3 * ((date_diff('month', starting_at, current_date()) - 24) / 12.0))
        ELSE business_plan_y1 + business_plan_y2 + business_plan_y3 + (business_plan_y4 * ((date_diff('month', starting_at, current_date()) - 36) / 12.0))
    END AS expected_gross_written_premium

Performance technique

Ratios de profitabilité : le système `C.L.A.I.M.`

Convention de nommage mnémotechnique pour nos ratios principaux :

📌

ombined Ratio (Ratio combiné)

Formula:

Definition: The sum of claims costs, insurer expenses, and distribution (acquisition + management) costs divided by total earned premiums (excl. taxes).

Purpose: Provides an aggregate view of overall profitability before investment income. A ratio below 100% indicates technical profitability.

oss Ratio (Rapport Sinistres à Primes)

Formula:

Definition: The cost of claims, including provisions, divided by total earned premiums (excl. taxes).

Purpose: Assesses technical profitability by showing the percentage of premiums used to cover claims.

cquisition Cost Ratio (Taux de commission d’apport)

Formula:

Definition: The sum of broker or distributor acquisition commissions divided by total earned premiums (excl. taxes).

Purpose: Highlights the cost of acquiring and managing policies relative to premium income.

nsurance Operating Cost (or Overhead) Ratio (Ratio de frais assureur)

Formula:

Definition: Total general administrative expenses divided by total earned premiums (excl. taxes). Examples include salaries, IT systems, rent…

Purpose: Measures internal efficiency, reflecting the portion of premiums used to run the insurer’s business.

anagement Cost Ratio (Taux de commission de gestion)

Formula:

Definition: The sum of broker management commission divided by total earned premiums (excl. taxes).

Purpose: Highlights the cost of acquiring and managing policies relative to premium income.

👉

Relations to other traditional ratios:

Expense Ratio: (see EIOPA definition in that, more info here)

Distribution Ratio:

Formula:

Ratio “chargé” ou (S+C)/P:

Vues

All years (Depuis le lancement) : Vision globale

Description :

Toutes primes émises

Acquisition jusqu’à aujourd’hui

Derniers IBNRs connus

Actual Gross Written Premium

Somme des primes émises hors taxe

sql
select
    sum(
        gross_written_technical_premium
        + gross_written_distribution_broker_commission
        + gross_written_management_broker_commission
    ) as all_years_actual_gross_written_premium,
    portfolio_id
from main_datastore_bi.data
group by portfolio_id

ALL_YEARS_ACTUAL_GROSS_WRITTEN_PREMIUM Trino SQL (Athena-friendly) formula

Actual Loss Ratio

Somme de la charge (paiements, provisions dossier/dossier, recouvrements, frais de dossiers) des sinistres survenus depuis lancement et de la provision pour sinistres survenus encore inconnus (IBNR) rapportée à la somme des primes hors taxe acquises depuis lancement

sql
with reported_claims_cost as (
    select
        sum(
            gross_claim_paid
            + gross_claim_rbns
            + gross_lae_paid
            + gross_lae_rbns
            - gross_salvage_subrogation_paid
            - gross_salvage_subrogation_rbns
        ) as value,
        portfolio_id
    from main_datastore_bi.data
    group by portfolio_id
), earned_premium as (
    select
        sum(
            gross_written_technical_premium
            + gross_written_distribution_broker_commission
            + gross_written_management_broker_commission
        ) as value,
        portfolio_id
    from main_datastore_bi.data
    where earning_month <= current_date
    group by portfolio_id
), current_ibnr as (
    select sum(value) as value,
        portfolio_reference as portfolio_id
    from main_datastore_output.accounting_provisions provisions
    where provision_date = (
        select max(provision_date)
        from main_datastore_output.accounting_provisions latest
        where provisions.portfolio_reference = latest.portfolio_reference
    )
    and type = 'IBNR' and sandboxed = false
    group by portfolio_reference
)
select
    earned_premium.portfolio_id,
    (coalesce(current_ibnr.value, 0) + coalesce(reported_claims_cost.value, 0)) / earned_premium.value as all_years_actual_loss_ratio
from earned_premium
left join reported_claims_cost on earned_premium.portfolio_id = reported_claims_cost.portfolio_id
left join current_ibnr on earned_premium.portfolio_id = current_ibnr.portfolio_id

ALL_YEARS_ACTUAL_LOSS_RATIO Trino SQL (Athena-friendly) formula

Actual Acquisition Cost Ratio

Somme des commissions d’acquisition acquises depuis lancement rapportée à la somme des primes hors taxe acquises depuis lancement

sql
select
    sum(
        gross_written_distribution_broker_commission
    ) / sum(
        gross_written_technical_premium
        + gross_written_distribution_broker_commission
        + gross_written_management_broker_commission
    ) as all_years_actual_acquisition_cost_ratio,
    portfolio_id
from main_datastore_bi.data
where earning_month <= current_date
group by portfolio_id

ALL_YEARS_ACTUAL_ACQUISITION_COST_RATIO Trino SQL (Athena-friendly) formula

Actual Management Cost Ratio

Somme des commissions d’acquisition acquises depuis lancement rapportée à la somme des primes hors taxe acquises depuis lancement

sql
select
    sum(
        gross_written_management_broker_commission
    ) / sum(
        gross_written_technical_premium
        + gross_written_distribution_broker_commission
        + gross_written_management_broker_commission
    ) as all_years_actual_acquisition_cost_ratio,
    portfolio_id
from main_datastore_bi.data
where earning_month <= current_date
group by portfolio_id

ALL_YEARS_ACTUAL_MANAGEMENT_COST_RATIO Trino SQL (Athena-friendly) formula

12 last months (12 derniers mois) : Vision Client Perf

Description :

Primes souscrites sur les 12 derniers mois

Acquisition sur 12 derniers mois des primes toutes dates de souscription

Sinistres survenus sur les 12 derniers mois, derniers IBNRs connus

Actual Gross Written Premium

Somme des primes émises hors taxe souscrites sur les 12 derniers mois

sql
select
    sum(
        gross_written_technical_premium
        + gross_written_distribution_broker_commission
        + gross_written_management_broker_commission
    ) as twelve_last_months_actual_gross_written_premium,
    portfolio_id
from main_datastore_bi.data
where subscribed_at > date_add('year', -1, current_date)
group by portfolio_id

12_LAST_MONTHS_ACTUAL_GROSS_WRITTEN_PREMIUM Trino SQL (Athena-friendly) formula

Actual Loss Ratio

Somme de la charge (paiements, provisions dossier/dossier, recouvrements, frais de dossiers) des sinistres survenus sur les 12 derniers mois et de la provision pour sinistres survenus encore inconnus (IBNR) rapportée à la somme des primes hors taxe acquises sur les 12 derniers mois

sql
with reported_claims_cost as (
    select
        sum(
            gross_claim_paid
            + gross_claim_rbns
            + gross_lae_paid
            + gross_lae_rbns
            - gross_salvage_subrogation_paid
            - gross_salvage_subrogation_rbns
        ) as value,
        portfolio_id
    from main_datastore_bi.data
    where occurred_at > date_add('year', -1, current_date)
    group by portfolio_id
), earned_premium as (
    select
        sum(
            gross_written_technical_premium
            + gross_written_distribution_broker_commission
            + gross_written_management_broker_commission
          ) as value,
        portfolio_id
    from main_datastore_bi.data
    where earning_month <= current_date
    and earning_month > date_add('year', -1, current_date)
    group by portfolio_id
), current_ibnr as (
    select sum(value) as value,
        portfolio_reference as portfolio_id
    from main_datastore_output.accounting_provisions provisions
    where provision_date = (
        select max(provision_date)
        from main_datastore_output.accounting_provisions latest
        where provisions.portfolio_reference = latest.portfolio_reference
    )
    and type = 'IBNR' and sandboxed = false
    group by portfolio_reference
)
select
    earned_premium.portfolio_id,
    (coalesce(current_ibnr.value, 0) + coalesce(reported_claims_cost.value, 0)) / earned_premium.value as twelve_last_months_actual_loss_ratio
from earned_premium
left join reported_claims_cost on earned_premium.portfolio_id = reported_claims_cost.portfolio_id
left join current_ibnr on earned_premium.portfolio_id = current_ibnr.portfolio_id

12_LAST_MONTHS_ACTUAL_LOSS_RATIO Trino SQL (Athena-friendly) formula

Actual Acquisition Cost Ratio

Somme des commissions d’acquisition acquises sur les 12 derniers mois rapportée à la somme des primes hors taxe acquises sur les 12 derniers mois

sql
select
    sum(
        gross_written_distribution_broker_commission
    ) / sum(
        gross_written_technical_premium
        + gross_written_distribution_broker_commission
        + gross_written_management_broker_commission
    ) as twelve_last_months_actual_acquisition_cost_ratio,
    portfolio_id
from main_datastore_bi.data
where earning_month <= current_date
and earning_month > date_add('year', -1, current_date)
group by portfolio_id

12_LAST_MONTHS_ACTUAL_ACQUISITION_COST_RATIO Trino SQL (Athena-friendly) formula

Actual Management Cost Ratio

Somme des commissions d’acquisition acquises sur les 12 derniers mois rapportée à la somme des primes hors taxe acquises sur les 12 derniers mois

sql
select
    sum(
        gross_written_management_broker_commission
    ) / sum(
        gross_written_technical_premium
        + gross_written_distribution_broker_commission
        + gross_written_management_broker_commission
    ) as twelve_last_months_actual_acquisition_cost_ratio,
    portfolio_id
from main_datastore_bi.data
where earning_month <= current_date
and earning_month > date_add('year', -1, current_date)
group by portfolio_id

12_LAST_MONTHS_ACTUAL_MANAGEMENT_COST_RATIO Trino SQL (Athena-friendly) formula

Year to Date (Depuis le début de l’année) : Vision Finance

Subscription 202x (Souscriptions 202x) : Vision Actuariat

Description :

Primes souscrites sur l’année considérée

Acquisition jusqu’à aujourd’hui des primes souscrites sur l’année considérée

Sinistres (toutes dates de survenance) rattachés aux contrats souscrits sur l’année considérée.

IBNRs ventilés selon la proportion de primes acquises ce mois-ci liées à des souscriptions sur l’année considérée ?

Actual Gross Written Premium

Somme des primes émises hors taxe souscrites en 202x

sql
select
    sum(
        gross_written_technical_premium
        + gross_written_distribution_broker_commission
        + gross_written_management_broker_commission
    ) as subscription_2020_actual_gross_written_premium,
    portfolio_id
from main_datastore_bi.data
where year(subscribed_at) = 2020
group by portfolio_id

SUBSCRIPTION_2020_ACTUAL_GROSS_WRITTEN_PREMIUM Trino SQL (Athena-friendly) formula

Actual Loss Ratio

Somme de la charge (paiements, provisions dossier/dossier, recouvrements, frais de dossiers) des sinistres survenus liés à des contrats souscrits en 202x et de la part de provision pour sinistres survenus encore inconnus (IBNR) liés à des contrats souscrits en 202x (selon la proportion de primes acquises sur le mois en cours liée à des contrats souscrits en 202x) rapportée à la somme des primes hors taxe acquises pour des contrats souscrits en 202x

👉

Clé de répartition des IBNR par année de souscription : répartition de l’acquisition du mois en cours

⚠️

~~Need to have~~ ~~subscribed_at~~ ~~filled for claims first!! See~~

sql
with reported_claims_cost as (
    select
        sum(
            gross_claim_paid
            + gross_claim_rbns
            + gross_lae_paid
            + gross_lae_rbns
            - gross_salvage_subrogation_paid
            - gross_salvage_subrogation_rbns
        ) as value,
        portfolio_id
    from main_datastore_bi.data
    where year(subscribed_at) = 2020
    group by portfolio_id
), earned_premium as (
    select
        sum(
            gross_written_technical_premium
            + gross_written_distribution_broker_commission
            + gross_written_management_broker_commission
          ) as value,
        portfolio_id
    from main_datastore_bi.data
    where earning_month <= current_date
    and year(subscribed_at) = 2020
    group by portfolio_id
), current_ibnr as (
    select sum(value) as value,
        portfolio_reference as portfolio_id
    from main_datastore_output.accounting_provisions provisions
    where provision_date = (
        select max(provision_date)
        from main_datastore_output.accounting_provisions latest
        where provisions.portfolio_reference = latest.portfolio_reference
    )
    and type = 'IBNR' and sandboxed = false
    group by portfolio_reference
), current_month_total_earned_premium as (
    select
        sum(
            gross_written_technical_premium
            + gross_written_distribution_broker_commission
            + gross_written_management_broker_commission
          ) as value,
        portfolio_id
    from main_datastore_bi.data
    where earning_month = date_trunc('month', current_date)
    group by portfolio_id
), current_month_filtered_earned_premium as (
    select
        sum(
            gross_written_technical_premium
            + gross_written_distribution_broker_commission
            + gross_written_management_broker_commission
          ) as value,
        portfolio_id
    from main_datastore_bi.data
    where earning_month = date_trunc('month', current_date)
    and year(subscribed_at) = 2020
    group by portfolio_id
)
select
    earned_premium.portfolio_id,
    case
        when coalesce(current_month_total_earned_premium.value, 0) = 0 then coalesce(reported_claims_cost.value, 0) / earned_premium.value
        else (coalesce(current_ibnr.value, 0) * coalesce(current_month_filtered_earned_premium.value, 0) / coalesce(current_month_total_earned_premium.value, 0) + coalesce(reported_claims_cost.value, 0)) / earned_premium.value
    end as subscription_2020_actual_loss_ratio
from earned_premium
left join reported_claims_cost on earned_premium.portfolio_id = reported_claims_cost.portfolio_id
left join current_ibnr on earned_premium.portfolio_id = current_ibnr.portfolio_id
left join current_month_total_earned_premium on earned_premium.portfolio_id = current_month_total_earned_premium.portfolio_id
left join current_month_filtered_earned_premium on earned_premium.portfolio_id = current_month_filtered_earned_premium.portfolio_id

SUBSCRIPTION_2020_ACTUAL_LOSS_RATIO Trino SQL (Athena-friendly) formula

Actual Acquisition Cost Ratio

Somme des commissions d’acquisition acquises sur les primes souscrites en 202x rapportée à la somme des primes hors taxe acquises sur les primes souscrites en 202x

sql
select
    sum(
        gross_written_distribution_broker_commission
    ) / sum(
        gross_written_technical_premium
        + gross_written_distribution_broker_commission
        + gross_written_management_broker_commission
    ) as subscription_2020_actual_acquisition_cost_ratio,
    portfolio_id
from main_datastore_bi.data
where earning_month <= current_date
and year(subscribed_at) = 2020
group by portfolio_id

SUBSCRIPTION_2020_ACTUAL_ACQUISITION_COST_RATIO Trino SQL (Athena-friendly) formula

Actual Management Cost Ratio

Somme des commissions d’acquisition acquises sur les primes souscrites en 202x rapportée à la somme des primes hors taxe acquises sur les primes souscrites en 202x

sql
select
    sum(
        gross_written_management_broker_commission
    ) / sum(
        gross_written_technical_premium
        + gross_written_distribution_broker_commission
        + gross_written_management_broker_commission
    ) as subscription_2020_actual_acquisition_cost_ratio,
    portfolio_id
from main_datastore_bi.data
where earning_month <= current_date
and year(subscribed_at) = 2020
group by portfolio_id

SUBSCRIPTION_2020_ACTUAL_MANAGEMENT_COST_RATIO Trino SQL (Athena-friendly) formula

Indicateurs de performance usuels

Performance commerciale

Expected Gross Written Premium

Description:

Formula:

Trino SQL (Athena-friendly) formula:

Performance technique

Ratios de profitabilité : le système C.L.A.I.M.

ombined Ratio (Ratio combiné)

oss Ratio (Rapport Sinistres à Primes)

cquisition Cost Ratio (Taux de commission d’apport)

nsurance Operating Cost (or Overhead) Ratio (Ratio de frais assureur)

anagement Cost Ratio (Taux de commission de gestion)

Vues

All years (Depuis le lancement) : Vision globale

Actual Gross Written Premium

Actual Loss Ratio

Actual Acquisition Cost Ratio

Actual Management Cost Ratio

12 last months (12 derniers mois) : Vision Client Perf

Actual Gross Written Premium

Actual Loss Ratio

Actual Acquisition Cost Ratio

Actual Management Cost Ratio

Year to Date (Depuis le début de l’année) : Vision Finance

Actual Gross Written Premium

Subscription 202x (Souscriptions 202x) : Vision Actuariat

Actual Gross Written Premium

Actual Loss Ratio

Actual Acquisition Cost Ratio

Actual Management Cost Ratio

Ratios de profitabilité : le système `C.L.A.I.M.`