# Aggregate excess of loss

> The contract that omits the per-occurrence layer and applies an attachment and limit to the trial total, so occurrences accumulate before it responds.

Source: https://reinsurance.dev/financial-modelling/contracts/aggxol/

## The composition

An **[aggregate excess of loss](https://reinsurance.dev/glossary/#aggxol-aggregate-excess-of-loss)** contract (AggXoL) drops the per-occurrence layer. It is also sold as an **[aggregate stop loss](https://reinsurance.dev/glossary/#aggregate-stop-loss)**, the same structure under a different business name. After the [filter](https://reinsurance.dev/financial-modelling/terms/filter/) and [contract period](https://reinsurance.dev/financial-modelling/terms/contract-period/), it applies the [aggregate excess](https://reinsurance.dev/financial-modelling/terms/aggregate-excess/) directly to the trial total.

$$
\text{AggXoL}(A, \ell) = \text{agg}_{\text{excess}}(A, \ell) \circ \text{period}(t_0, t_1) \circ \text{filter}(P)
$$

The formula is read like every [composition](https://reinsurance.dev/financial-modelling/composition/#composing-contracts-from-the-blocks). Like every contract in the catalog, this one is defined at 100%, with [participation](https://reinsurance.dev/glossary/#participation) entering at [portfolio roll-up](https://reinsurance.dev/financial-modelling/portfolios/#the-roll-up-participation-then-sum).

Multiple occurrences accumulate against the attachment before the contract responds. Caldera Re Contract 2 is the SunCoast CA/AZ Earthquake AggXoL, \$10M xs \$15M on SunCoast's earthquake losses. In [Trial 9](https://reinsurance.dev/financial-modelling/contracts/aggxol/#contract-2-on-trial-9) the contract pays its full limit.

## Contract 2 on Trial 9

Applied to Trial 9's earthquakes, no single quake is large (the biggest is \$6.5M), but together they accumulate past the \$15M aggregate attachment, and the contract pays the excess up to the \$10M limit:

*[Interactive figure: Term explorer. See the web page: https://reinsurance.dev/financial-modelling/contracts/aggxol/]*

| Trial 9 (earthquake) | Value |
| --- | ---: |
| Aggregate covered loss (22 earthquakes) | \$27.6M |
| Aggregate attachment | \$15M |
| Gross (excess of attachment, capped at \$10M) | \$10M |

A cedent buys aggregate cover against a bad year of many small events, not against one large one.[^stop-loss-purpose] A per-occurrence layer leaves that frequency risk with the cedent, since no single earthquake in Trial 9 would reach even a modest attachment. The aggregate form transfers it. The risk profile differs accordingly: an aggregate layer with a low attachment on a high-frequency peril responds in most trials rather than in a few, so its expected loss is a large fraction of its limit and its price is a lower multiple of that expected loss than a remote occurrence layer's.[^loss-multiple] Contract 2 pays in 16 of the 20 demo trials and exhausts its limit in 6.

Python (`python/caldera_re/aggxol.py`, runnable on the web page):

```python
import numpy as np
import pandas as pd

from caldera_re.aggregate_excess import OCCURRENCE_KEY, aggregate_excess
from caldera_re.loss_filter import loss_filter
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.var import var

def aggxol(
    loss: pd.DataFrame,
    attachment: float,
    limit: float,
    period_start: str = '2024-01-01',
    period_end: str = '2024-12-31',
    **criteria,
) -> pd.DataFrame:
    """Apply AggXoL: filter + contract period + aggregate excess. The result
    is the 100% layer; participation is applied at roll-up.

    Composition: agg_excess(A, l) . period(t0, t1) . filter(criteria)

    Filters to the covered rows and contract period, then applies the layer
    to the running aggregate, attributing each occurrence's erosion of the
    layer to its rows (YELT in, YELT out). Summing 'gross_loss' per trial
    gives the trial-level layer result exactly.

    Parameters
    ----------
    loss : pd.DataFrame
        YELT with 'trial_id' and 'loss' columns.
    attachment : float
        Aggregate attachment point.
    limit : float
        Aggregate limit.
    period_start, period_end : str
        Contract period, inclusive. Defaults to full-year 2024.
    **criteria
        Filter column = value pairs, e.g. peril='EQ'.

    Returns the covered YELT rows in claims order with an
    erosion-attributed 'gross_loss' column.
    """
    covered = loss_filter(loss, **criteria)
    covered = contract_period(covered, period_start, period_end)
    result = aggregate_excess(covered, attachment, limit)
    return result

if __name__ == '__main__':
    # Contract 2: SunCoast CA/AZ Earthquake AggXoL, $10M xs $15M, full-year 2024.
    A = 15.0
    L = 10.0

    result = aggxol(cedents.suncoast, attachment=A, limit=L, peril='EQ')

    print(f"Contract 2: SunCoast CA/AZ Earthquake AggXoL ({L:.0f}M xs {A:.0f}M)")

    # Trial 9's erosion audit trail: no single quake reaches the attachment,
    # but the running aggregate crosses it and then exhausts the limit.
    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['running'] <= A]
    print(f"\nTrial 9 erosion audit trail: {len(quiet)} quakes accumulate "
          f"to {quiet['loss'].sum():.1f}M below the {A:.0f}M attachment, then:")
    table(['Date', 'Event', 'Loss', 'Running agg', 'Gross'],
          occ9.loc[occ9['running'] > A,
                   ['timestamp', 'event_id', 'loss', 'running', 'gross']],
          fmt=['{}', '{}', '{:.2f}M', '{:.1f}M', '{:.2f}M'])
    print(f"Trial 9 gross: {occ9['gross'].sum():.1f}M "
          f"(exhausted once the running aggregate passes {A + L:.0f}M)")

    # 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', '{}'])

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

    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_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")
```

The composition costs what its most expensive term costs. The filter and the contract period are $O(R)$ masks over the $R$ input rows; the [aggregate excess](https://reinsurance.dev/financial-modelling/terms/aggregate-excess/) sorts the surviving rows into claims order, $O(R \log R)$, and that sort dominates.

:::caution[Order matters]
The aggregate attachment and limit apply to the trial total, so the occurrences must be accumulated before the layer responds. Here no single earthquake reaches the \$15M attachment, but together they cross it. When a contract also carries a per-occurrence layer (as a [catastrophe excess of loss (CatXoL)](https://reinsurance.dev/financial-modelling/contracts/catxol/) contract does), that layer is applied to each event first, and the aggregate then acts on the running total of those per-occurrence results. The per-occurrence audit trail visible in the code output above comes from the [aggregate excess](https://reinsurance.dev/financial-modelling/terms/aggregate-excess/) term's **[erosion](https://reinsurance.dev/glossary/#erosion)** attribution, occurrence by occurrence.
:::

Trial 9 is not an outlier: [across the demo tier](https://reinsurance.dev/financial-modelling/contracts/aggxol/#across-the-demo-tier) the earthquake total exceeds the \$15M attachment in 16 of the 20 trials.

## Across the demo tier

Across all 20 trials of the [demo tier](https://reinsurance.dev/toolkit/trials/#demo-scale), Contract 2 responds whenever SunCoast's annual earthquake total exceeds \$15M, which it does in most trials, since the quakes are frequent and small:

*[Interactive figure: Term explorer. See the web page: https://reinsurance.dev/financial-modelling/contracts/aggxol/]*

Across those 20 trials the standalone [expected loss](https://reinsurance.dev/toolkit/metrics/expected-loss/) is \$5.03M at 100% (half the \$10M limit, the signature of a layer that responds in most trials) against a \$5M premium, which is why the [standalone pricing story](https://reinsurance.dev/applications/standalone-pricing/#step-3-from-metrics-to-premium) finds Contract 2 underpriced on its own.

Caldera Re's Contract 3 is also an AggXoL (\$50M xs \$300M on SunCoast's entire US book), but it operates on losses **net of Contracts 1 and 2**, so those recoveries inure to its benefit. That inuring step chains one contract onto another; the [Programs](https://reinsurance.dev/financial-modelling/programs/#inuring) page works it through, and the [standalone pricing story](https://reinsurance.dev/applications/standalone-pricing/) prices Contract 2 end to end.

[^stop-loss-purpose]: Swiss Re, [*Proportional and non-proportional reinsurance*](https://newdoc.nccu.edu.tw/teasyllabus/308002308018/Swiss%20Re%20Prop.%26Non-Prop..pdf) (UM3), Christoph Bugmann, 1997. Stop loss cover suits an insurer "looking for protection against a real threat to its existence as a result of an accumulation of negative influences in the same year of occurrence", and "limits the burden of loss from the net net retention each year".

[^loss-multiple]: Neil M. Bodoff and Yunbo Gan, ["An Analysis of the Market Price of Cat Bonds"](https://www.casact.org/sites/default/files/2021-02/pubs_forum_09spforum_02bodoff.pdf), Casualty Actuarial Society E-Forum, Spring 2009, §3.1: priced as a multiple of expected loss, "when expected loss is large, the multiple is small, and when expected loss is small, the multiple is large". The multiple is measured on catastrophe-bond issuance spreads rather than on treaty rates, and the paper's own model finds the absolute margin still rises with expected loss.