Skip to content
Property-cat reinsurance analytics for software engineers

Financial Modelling · Section 4.5

Aggregate excess

The term that applies an attachment and limit to the trial total rather than each occurrence, attributing each occurrence's erosion of the layer, so the output stays a YELT.

10 min read

Step 4 of the catastrophe excess of loss (CatXoL) walkthrough applied the aggregate excess as Contract 1’s annual cap. The intuition is a deductible that the whole trial’s claims erode, followed by a ceiling. No single occurrence needs to be large, and the layer carries state from the occurrences before it (the one term in the catalog that does). The aggregate excess applies the attachment AA (the aggregate attachment, which a treaty wording calls the cedent’s aggregate deductible) and the aggregate limit ℓ\ell to the total loss across all occurrences in a trial, not to individual occurrences. Per trial, the layer pays:

Ljgross=min⁡(max⁡(∑(t,e)Lj,t,esubject−A,0),ℓ)L_j^{\text{gross}} = \min\left(\max\left(\sum_{(t,e)} L_{j,t,e}^{\text{subject}} - A, 0\right), \ell\right)

The key difference from occurrence excess: occurrences accumulate against the attachment. Once the aggregate exceeds the attachment, the layer begins to pay. Once cumulative gross reaches the limit, coverage is exhausted.

A natural interpretation of that formula is “sum the trial, output one number per trial”, which would make the output a year loss table (YLT) rather than a year event loss table (YELT), and quietly break the YELT in, YELT out principle every other term obeys. The term conforms by applying the layer to the running aggregate instead: it tracks the layer’s erosion occurrence by occurrence, and each occurrence’s output row carries its contribution to the aggregate gross.

Index a trial’s occurrences k=1,…,njk = 1, \dots, n_j in claims order: chronological order within the trial. The term holds two pieces of state per trial: the running subject aggregate and the previous layered amount,

Sj,k=∑m=1kLj,msubject,Gj,k=min⁡(max⁡(Sj,k−A, 0), ℓ)S_{j,k} = \sum_{m=1}^{k} L_{j,m}^{\text{subject}}, \qquad G_{j,k} = \min\left(\max\left(S_{j,k} - A,\, 0\right),\, \ell\right)

and each occurrence’s gross is the increase in the layered aggregate it causes:

Lj,kgross=Gj,k−Gj,k−1,Gj,0=0L_{j,k}^{\text{gross}} = G_{j,k} - G_{j,k-1}, \qquad G_{j,0} = 0

In plain language: occurrences below the attachment contribute nothing; the occurrence that pushes the aggregate across the attachment contributes the excess; every occurrence after that contributes its full loss, until the limit, where contributions stop again. The sum telescopes, ∑kLj,kgross=Gj,nj\sum_{k} L_{j,k}^{\text{gross}} = G_{j,n_j}, which is exactly the trial-level formula above: attribution changes where the gross is booked, never how much. When one occurrence spans several YELT rows (different lines of business or geographies), its contribution splits across those rows in proportion to each row’s share of the occurrence loss.

The running aggregate moves the term through five states, and the state of occurrence kk fixes what it books:

The diagram draws only the transitions that change the state. Below the attachment, within the layer, and exhausted each last for as many occurrences as fire none of them; crossing and exhausting each describe a single occurrence. The exhausting occurrence is the one that reaches A+ℓA + \ell: it books whatever of the layer remains, and only the occurrences after it book nothing. A first occurrence large enough to clear both AA and A+ℓA + \ell goes from below to exhausting in one step and books the whole limit ℓ\ell. The Trial 9 audit trail below never leaves the crossing and within-layer states (its $272.12M total sits under A+ℓ=300A + \ell = 300), which is why every occurrence after the October hurricane books its full loss; the quota share’s annual limit shows the exhausting state on Baltica’s 5 December storm.

Applied to Caldera Re’s SunCoast subject for Trial 9 (all perils, no filter) at an illustrative $100M xs $200M layer, chosen so that the erosion is visible on this subject, the occurrences accumulate over the trial. The cumulative subject loss crosses the $200M aggregate attachment in early October; the contract then pays the excess up to the $100M limit. Trial 9’s total subject is $272.1M, so the gross is $72.1M, the portion of the aggregate above the attachment:

Earthquake Hurricane

Aggregate excess (100M xs 200M) on Trial 9. The line is the running total of the trial's $272.1M of subject loss; the layer pays the part of it between the attachment ($200M) and the top of the layer ($300M), $72.1M. The sliders set the attachment and the limit, and the EP curve chart on this page follows them.

The erosion audit trail for the same trial shows where that $72.1M is booked. Twenty-three occurrences accumulate to $177.82M without reaching the attachment; an October hurricane pushes the aggregate across it and carries the largest contribution; every later occurrence contributes in full. The table is shown at two decimals so it reconciles exactly. Each running aggregate is the sum of the losses above it, each gross is the increase in the layered column, and both columns foot to the trial totals:

Occurrences (claims order)LossRunning aggregate S9,kS_{9,k}Layered G9,kG_{9,k}Gross L9,kgrossL_{9,k}^{\text{gross}}
23 occurrences, Jan–Sep$177.82M$177.82M$0M$0M
Oct 5, event 10037$80.77M$258.59M$58.59M$58.59M
Oct 16, event 10340$0.71M$259.30M$59.30M$0.71M
Oct 21, event 10092$5.10M$264.40M$64.40M$5.10M
Oct 25, event 10077$5.65M$270.05M$70.05M$5.65M
Oct 25, event 10307$0.27M$270.32M$70.32M$0.27M
Oct 25, event 10310$1.35M$271.67M$71.67M$1.35M
Oct 29, event 10443$0.45M$272.12M$72.12M$0.45M
Trial 9 total$272.12M$72.12M
caldera_re/aggregate_excess.py Python
import numpy as np
import pandas as pd

from caldera_re.aep import aep
from caldera_re.data import cedents
from caldera_re.contract_period import contract_period
from caldera_re.report import table
from caldera_re.tvar import tvar
from caldera_re.utils import OCCURRENCE_KEY
from caldera_re.var import var


def aggregate_excess(
    loss: pd.DataFrame,
    attachment: float,
    limit: float,
    loss_column: str = 'loss',
) -> pd.DataFrame:
    """Apply an excess-of-loss layer to the aggregate loss per trial,
    attributing the layer's response back to occurrences (YELT in, YELT out).

    The term holds two pieces of running state per trial: the sum of
    occurrence losses so far (claims order = chronological order within the
    trial) and the previous layered amount. On each occurrence the layer

        G = min(max(running_sum - attachment, 0), limit)

    is re-evaluated; the increase over the previous occurrence's G is that
    occurrence's contribution to the aggregate gross — its erosion of the
    layer. An occurrence that spans multiple rows (several LOBs or
    geographies) splits its contribution across the rows in proportion to
    each row's share of the occurrence loss.

    Per trial, the contributions telescope back to the trial-level layer,
    sum(gross_loss) = min(max(trial_total - attachment, 0), limit): exact in
    the formulas, and to floating-point precision in the output (observed
    error on the demo data is below 1e-13),
    so a trial-level view is a groupby-sum over the output when one is wanted.

    Vectorized: one sort O(R log R) plus grouped cumsum/shift O(R) over the
    R input rows — no per-trial Python loop, so the erosion attribution
    costs no more than the occurrence excess does.

    Parameters
    ----------
    loss : pd.DataFrame
        YELT with the occurrence key columns (trial_id, timestamp,
        event_id) and a loss column.
    attachment : float
        Aggregate attachment point.
    limit : float
        Aggregate limit.
    loss_column : str
        Name of the column containing losses (default: 'loss'). When
        chaining, this may be a prior term's 'gross_loss'; the output's
        'gross_loss' column replaces it.

    Returns the input rows sorted into claims order, with 'gross_loss'
    carrying each row's attributed share of the aggregate erosion.
    """
    out = loss.sort_values(OCCURRENCE_KEY, kind='stable').copy()

    # Running aggregate per trial, evaluated at occurrence resolution.
    occ = out.groupby(OCCURRENCE_KEY, sort=True)[loss_column].sum()
    running = occ.groupby(level='trial_id').cumsum()
    layered = (running - attachment).clip(lower=0.0).clip(upper=limit)
    contribution = (layered
                    - layered.groupby(level='trial_id').shift(fill_value=0.0))

    # Attribute each occurrence's contribution proportionally to its rows.
    out = out.merge(contribution.rename('_occurrence_gross').reset_index(),
                    on=OCCURRENCE_KEY, how='left')
    out.attrs = dict(loss.attrs)   # merge drops the YELT's trial count
    occ_total = (out.groupby(OCCURRENCE_KEY)[loss_column]
                 .transform('sum').to_numpy())
    row_loss = out[loss_column].to_numpy()
    share = np.divide(row_loss, occ_total,
                      out=np.zeros(len(out)), where=occ_total > 0)
    out['gross_loss'] = out.pop('_occurrence_gross').to_numpy() * share
    return out


if __name__ == '__main__':
    # Demo: US perils at 100M xs 200M
    A = 200.0
    L = 100.0

    subject = contract_period(cedents.suncoast, '2024-01-01', '2024-12-31')
    result = aggregate_excess(subject, attachment=A, limit=L)

    print(f"Aggregate excess: {L:.0f}M xs {A:.0f}M (US perils)")

    # Trial 9's erosion audit trail: each occurrence's contribution to the
    # aggregate gross, in claims order.
    occ9 = (result[result['trial_id'] == 9]
            .groupby(OCCURRENCE_KEY, sort=True)
            .agg(loss=('loss', 'sum'), gross=('gross_loss', 'sum'))
            .reset_index())
    occ9['running'] = occ9['loss'].cumsum()
    quiet = occ9[occ9['gross'] <= 0]
    print(f"\nTrial 9 erosion audit trail: {len(quiet)} occurrences "
          f"accumulate to {quiet['loss'].sum():.2f}M below the "
          f"{A:.0f}M attachment, then:")
    table(['Date', 'Event', 'Loss', 'Running agg', 'Gross'],
          occ9.loc[occ9['gross'] > 0,
                   ['timestamp', 'event_id', 'loss', 'running', 'gross']],
          fmt=['{}', '{}', '{:.2f}M', '{:.2f}M', '{:.2f}M'])
    print(f"Trial 9 gross: {occ9['gross'].sum():.2f}M "
          f"(= min(max({occ9['loss'].sum():.2f} - {A:.0f}, 0), {L:.0f}))")

    # Trial-level view: the attributed rows summed per trial.
    by_trial = (result.groupby('trial_id')
                .agg(aggregate_subject=('loss', 'sum'),
                     gross_loss=('gross_loss', 'sum'))
                .reset_index())

    N = 20
    rows = []
    for s in range(1, N + 1):
        row = by_trial[by_trial['trial_id'] == s]
        if len(row) > 0:
            subj = row['aggregate_subject'].iloc[0]
            gross = row['gross_loss'].iloc[0]
        else:
            subj = 0.0
            gross = 0.0
        if subj > 0:
            if subj <= A:
                status = "Below attach"
            elif subj >= A + L:
                status = "Exhausted"
            else:
                status = "Partial"
            rows.append((s, subj, gross, status))
    print()
    table(['Trial', 'Agg Subject', 'Gross', 'Status'], rows,
          fmt=['{}', '{:.1f}M', '{:.1f}M', '{}'])

    print(f"\nAggregate Subject EL: {by_trial['aggregate_subject'].sum() / N:.2f}M")
    print(f"Gross EL: {by_trial['gross_loss'].sum() / N:.2f}M")

    gross_arr = np.zeros(N)
    for _, row in by_trial.iterrows():
        gross_arr[int(row['trial_id']) - 1] = row['gross_loss']

    gross_curve = aep(pd.DataFrame({
        'trial_id': range(1, N + 1), 'loss': gross_arr,
    }), n_trials=N)
    print(f"Gross VaR(90%): {var(gross_curve, 0.90):.1f}M")
    print(f"Gross TVaR(90%): {tvar(gross_curve, 0.90):.1f}M")

Erosion settles the order across occurrences. The rows inside one occurrence, one per geography and line of business, have no claims order among them, so the occurrence’s erosion is split across its rows in proportion to their losses. The two methods are the two kinds of back-allocation: by order where an order exists, by share where none does. The occurrence excess has no order to respect and uses the second alone.

The running state reads like a loop, and the implementation is not one. It sorts the rows into claims order once (O(Rlog⁡R)O(R \log R) over the RR input rows). It then computes the running aggregate and the layered amount with a grouped cumulative sum and a shift, both O(R)O(R). The attribution itself adds only that linear pass. The sort is what a claims-order term costs over the occurrence excess, which needs no order and runs in O(R)O(R). It is why this term, not the occurrence excess, sets the cost of a CatXoL. The state is two columns wide and never materialises a per-trial object; memory is the input frame plus those columns. The row count RR, not the trial count NN, drives the cost, and because the term carries no state across trials it runs on a partition by trial as well as on the whole table.

Across all 20 trials of the demo tier, most trials cross the $200M aggregate attachment and the heaviest seven exhaust the $100M limit:

Aggregate excess (100M xs 200M) over the 20 trials: SunCoast's subject against the gross loss. 14 of 20 trials pay; the expected gross loss is $58.84M. The sliders are shared with the Trial 9 chart on this page.

The aggregate excess is the term behind an aggregate excess of loss (AggXoL) contract and the annual cap of a CatXoL (where the attachment is zero and the limit encodes the reinstatements), and the annual limit of a quota share.

Footnotes

Section titled “Footnotes”

  1. Homeowners Choice Property & Casualty Insurance Company, Catastrophe Aggregate Excess of Loss Reinsurance Contract, 2013, SEC EDGAR. Where an aggregate reimbursement is based on “losses in more than one Loss Occurrence” and no amount is designated per occurrence, it “shall be prorated in the proportion that the Reinsured’s losses in each Loss Occurrence bear to the Reinsured’s total losses arising out of all Loss Occurrences to which the FHCF reimbursement applies”. The clause is Article 5, on inuring reinsurance, so it prorates an inuring recovery rather than the layer’s own: it establishes proportional attribution as contract practice, not as any platform’s default. ↩

  2. Markel CATCo Investment Management, “Net Asset Value(s)”, regulatory announcement for CATCo Reinsurance Opportunities Fund Ltd., July 2018. The listed retrocession fund strikes its unaudited net asset value per share “as at the close of business on 30 June 2018”, part-way through the contracts it holds, “inclusive of an attritional loss reserve of approximately 15bps per month (year-to-date: 90bps)”. “ILS Advisers Insurance Linked Securities Fund Index”, Artemis, is a benchmark published monthly from the returns of the ILS funds it tracks. ↩

  3. Standards Board for Alternative Investments, Side-Pocketing in ILS Funds, SBAI Toolbox Memo, May 2021. Valuation uncertainty in insurance-linked strategies “is often caused by the occurrence of large loss events where the losses have yet to be defined or realised”, and the memo, describing how a fund side-pockets a position after a large loss, separates the loss reserves developing for the event from “the fair value of the remaining contracts including all associated premiums and other economics”. That separation is what the memo describes for an event-driven side pocket; reading it onto the routine monthly mark of one aggregate layer’s eroded limit is this page’s. ↩