# Aggregate excess > The term that applies an attachment and limit to the trial total rather than each occurrence, attributing each occurrence's erosion so the output stays a YELT. Source: https://reinsurance.dev/financial-modelling/terms/aggregate-excess/ [Step 4 of the catastrophe excess of loss (CatXoL) walkthrough](https://reinsurance.dev/financial-modelling/dissection/#step-4-apply-the-aggregate-limit) 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](https://reinsurance.dev/glossary/#attachment-point) $A$ (the aggregate attachment, which a treaty wording calls the cedent's [aggregate deductible](https://reinsurance.dev/glossary/#aggregate-deductible)) and the [aggregate limit](https://reinsurance.dev/glossary/#aggregate-limit) $\ell$ to the total loss across all occurrences in a trial, not to individual occurrences. Per trial, the layer pays: $$ 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](https://reinsurance.dev/financial-modelling/terms/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)](https://reinsurance.dev/glossary/#ylt-year-loss-table) rather than a [year event loss table (YELT)](https://reinsurance.dev/glossary/#yelt-year-event-loss-table), and quietly break the [YELT in, YELT out](https://reinsurance.dev/toolkit/trials/#the-yelt) principle every other term obeys. The term conforms by applying the layer to the running aggregate instead: it tracks the layer's **[erosion](https://reinsurance.dev/glossary/#erosion)** occurrence by occurrence, and each occurrence's output row carries its contribution to the aggregate gross. Index a trial's occurrences $k = 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, $$ 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: $$ L_{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, $\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 $k$ fixes what it books: ```mermaid graph TB B["Below the attachment
Sₖ ≤ A · books 0"]:::Contract C["Crossing
Sₖ₋₁ ≤ A < Sₖ < A + ℓ · books Sₖ − A"]:::Process W["Within the layer
A < Sₖ₋₁, Sₖ < A + ℓ · books Lₖ"]:::Metric E["Exhausting
Sₖ₋₁ < A + ℓ ≤ Sₖ · books A + ℓ − max(Sₖ₋₁, A)"]:::Process X["Exhausted
Sₖ₋₁ ≥ A + ℓ · books 0"]:::Losses B -- "Sₖ exceeds A" --> C B -- "one step past A + ℓ" --> E C -- "next occurrence" --> W C -- "next occurrence reaches A + ℓ" --> E W -- "next occurrence reaches A + ℓ" --> E E -- "next occurrence" --> X classDef Losses fill:#F5CEBC,stroke:#B16C4B,stroke-width:1.5px,color:#2B2620 classDef Contract fill:#BFDBF8,stroke:#4F84BA,stroke-width:1.5px,color:#2B2620 classDef Process fill:#F7CBCB,stroke:#B46669,stroke-width:1.5px,color:#2B2620 classDef Metric fill:#BDE2CB,stroke:#459269,stroke-width:1.5px,color:#2B2620 ``` 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 + \ell$: it books whatever of the layer remains, and only the occurrences after it book nothing. A first occurrence large enough to clear both $A$ and $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 + \ell = 300$), which is why every occurrence after the October hurricane books its full loss; the [quota share's annual limit](https://reinsurance.dev/financial-modelling/contracts/quota-share/#with-annual-limit) shows the exhausting state on Baltica's 5 December storm. :::note[In Practice] Claims-order erosion is the attribution convention this site implements: it mirrors how the layer pays as claims arrive through the year. The alternative, *proportional* attribution, spreads a trial's aggregate gross across its occurrences in proportion to their losses, and appears in treaty wordings for allocating an aggregate recovery across occurrences.[^proration] It needs the complete year before any one occurrence's share can be computed, so it cannot state how much of a layer has been consumed partway through. Under claims order that figure is settled when the occurrence is booked and is never restated. Trial 1 of the SunCoast subject shows the difference on the \$100M xs \$200M layer worked below. The running aggregate stands at \$195.17M after 22 September, and the \$174.10M hurricane of 24 September (event 10143) takes it to \$369.27M, past $A + \ell = 300$. Under claims order that occurrence exhausts the limit. Proportional attribution, computed from the trial's full \$616.18M, has booked \$59.93M of the \$100M gross to the occurrences up to and including that hurricane, and still shows \$40.07M of limit available on a layer that has none. A second hurricane carries the same date (event 10195, \$132.84M). Claims order breaks the tie by event id, and with the two reversed the limit is exhausted on the same day, since \$195.17M plus \$132.84M also passes \$300M. Partway-through is exactly the question an [insurance-linked securities (ILS)](https://reinsurance.dev/glossary/#ils-insurance-linked-securities) fund faces when it strikes a net asset value monthly on a book of twelve-month contracts.[^ils-nav] Marking mid-term separates the loss already attaching to events that have occurred from the value of what is left to run,[^ils-valuation] and on an aggregate layer both halves depend on its eroded state (how much limit is gone and how much is still exposed). Neither is recoverable from a trial-level total. This site's implementation and worked examples use claims order. ::: 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: *[Interactive figure: Term explorer. See the web page: https://reinsurance.dev/financial-modelling/terms/aggregate-excess/]* 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) | Loss | Running aggregate $S_{9,k}$ | Layered $G_{9,k}$ | Gross $L_{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** | :::tip[Key Insight] Erosion attribution changes where the gross is booked, never the trial's total. The output YELT's rows sum back to $\min(\max(\sum L - A, 0), \ell)$ per trial, so every trial-level number ([expected loss (EL)](https://reinsurance.dev/glossary/#el-expected-loss), the [exceedance probability (EP) curves](https://reinsurance.dev/glossary/#ep-curve-exceedance-probability-curve) below, every result in the [Caldera Re](https://reinsurance.dev/caldera-re/) canon) is identical to collapsing first. What attribution provides is resolution: downstream consumers that need occurrence rows (an [occurrence-level portfolio roll-up](https://reinsurance.dev/financial-modelling/portfolios/#two-resolutions-trial-and-occurrence), [occurrence exceedance probability (OEP)](https://reinsurance.dev/glossary/#oep-occurrence-exceedance-probability) metrics, retrocession with occurrence terms) receive a genuine YELT, not a trial-level table. Reducing to trial totals stays available to any consumer that wants it: a one-line groupby-sum over the output rows; the YLT is a [chosen reduction](https://reinsurance.dev/financial-modelling/portfolios/#two-resolutions-trial-and-occurrence), never the term's output type. ::: Python (`python/caldera_re/aggregate_excess.py`, runnable on the web page): ```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](https://reinsurance.dev/glossary/#back-allocation): by order where an order exists, by share where none does. The [occurrence excess](https://reinsurance.dev/financial-modelling/terms/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(R \log R)$ over the $R$ input rows). It then computes the running aggregate and the layered amount with a grouped cumulative sum and a shift, both $O(R)$. The attribution itself adds only that linear pass. The sort is what a claims-order term costs over the [occurrence excess](https://reinsurance.dev/financial-modelling/terms/occurrence-excess/), which needs no order and runs in $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 $R$, not the trial count $N$, drives the cost, and because the term carries no state across trials it runs on a [partition by trial](https://reinsurance.dev/applications/production-scale/#one-computational-shape) as well as on the whole table. Across all 20 trials of the [demo tier](https://reinsurance.dev/toolkit/trials/#demo-scale), most trials cross the \$200M aggregate attachment and the heaviest seven exhaust the \$100M limit: *[Interactive figure: Term explorer. See the web page: https://reinsurance.dev/financial-modelling/terms/aggregate-excess/]* The aggregate excess is the term behind an [aggregate excess of loss (AggXoL)](https://reinsurance.dev/financial-modelling/contracts/aggxol/) contract and the annual cap of a [CatXoL](https://reinsurance.dev/financial-modelling/contracts/catxol/) (where the attachment is zero and the limit encodes the reinstatements), and the annual limit of a [quota share](https://reinsurance.dev/financial-modelling/contracts/quota-share/). [^proration]: Homeowners Choice Property & Casualty Insurance Company, [Catastrophe Aggregate Excess of Loss Reinsurance Contract, 2013](https://www.sec.gov/Archives/edgar/data/1400810/000119312513324187/d542622dex109.htm), *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. [^ils-nav]: Markel CATCo Investment Management, ["Net Asset Value(s)"](https://www.investegate.co.uk/announcement/rns/catco-reinsurance-opportunities-fund-limited-di---cat/net-asset-value-s-/5296007), 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"](https://www.artemis.bm/ils-advisers-insurance-linked-securities-fund-index/), *Artemis*, is a benchmark published monthly from the returns of the ILS funds it tracks. [^ils-valuation]: Standards Board for Alternative Investments, [*Side-Pocketing in ILS Funds*](https://www.sbai.org/static/8ed3a78f-3212-4df2-ac96ad88d3dd7ab9/ILS-Funds-Side-Pocketing.pdf), 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.