# Catastrophe Bond (ILS)

A 4-year indemnity cat bond from the investor perspective: 200M principal, 4.5% collateral + 6.5% spread coupon paid on declining notional, sponsor UNL attachment and exhaustion layer, four scenario loss paths driven by a CHOOSE selector, IRR / YTM / multiple / loss-adjusted return, and a 5x5 risk-free by spread sensitivity grid.

- Canonical: https://finamodel.com/templates/catastrophe-bond
- Excel download: https://finamodel.com/templates/catastrophe-bond.xlsx
- Category: Insurance
- Model type: Credit model
- Difficulty: Intermediate
- Audiences: Investors & analysts, Bankers & advisors, ILS funds, Reinsurance capital markets, Pension allocators, Multi-strategy hedge funds
- Tags: cat bond, ils, insurance-linked, structured credit, reinsurance

## Overview

A catastrophe bond model captures the cash flows and return profile of an insurance-linked security (ILS) from the institutional investor perspective. The workbook lays out a single-tranche indemnity cat bond - 200M principal, 4-year tenor, annual coupon pay - across eight sheets: Cover, Assumptions, Bond_Structure, Loss_Model, Cash_Flows, Returns, Sensitivity, Checks. Every input is a named-range cell; every formula is one or two operations long; the workbook passes static-value, self-reference, and dead-assumption scans.

The Bond_Structure sheet runs the notional roll-forward and coupon stream. Notional Open equals principal at Year 1 and prior Notional Close in subsequent years. Annual Loss in dollars is the smaller of scenario-driven loss percent times principal and notional open, so cumulative principal write-downs never exceed 100%. Notional Close equals Open minus Loss. Coupon equals Notional Open times All-In Coupon (collateral yield plus risk spread, here 4.5% + 6.5% = 11.0%). Principal Returned hits the cash flow only at maturity, equal to the final-year Notional Close.

The Scenario_Sel parameter drives a CHOOSE() through four canonical loss paths: no event (base case, IRR equals coupon), partial loss Y2 (attachment breached once, 25% principal write-down), total loss Y3 (exhaustion breached, full write-down), and EL each year (the analytical benchmark where realised loss equals expected loss every period). The Loss_Model sheet echoes the loss path with cumulative loss tracking, payout to cedant, and layer reference rows showing attachment (1.5B sponsor UNL) and exhaustion (1.9B). The Cash_Flows sheet stitches Year 0 outflow (–principal at issuance) with per-year coupons received and maturity principal returned, then sums to net and cumulative cash flows.

The Returns sheet reports investor IRR (IFERROR-wrapped so the total-loss path returns 0 instead of erroring on a no-sign-change series), no-loss YTM (= all-in coupon), loss-adjusted return (= coupon minus expected loss), spread multiple (= risk spread / expected loss, here ~3.1x against the 2024 market average of 3.0x), total return on principal, plus aggregate cash flows: sum of coupons, sum of losses, and remaining principal. The Sensitivity sheet flexes a 5x5 grid of risk-free offset by spread offset (each from -200 to +200 basis points stored as decimal inputs) producing total yield. The Checks sheet runs seven validation checks: notional non-negative, cumulative loss capped at principal, expected loss within probability bounds (exhaustion ≤ EL ≤ attachment), coupon identity (Notional Open × Coupon rate), cash flow reconciliation (Sum CF = Sum Coupons + Remaining Principal − Principal), layer width positive, and multiple ≥ 1.0.

Target users are ILS-dedicated funds, reinsurance company capital markets desks, pension funds allocating to insurance-linked securities, and multi-strategy hedge funds with cat-bond sleeves evaluating tranches in the 50M to 500M size range. Useful for primary issuance pricing exercises (does spread × EL coverage clear the hurdle), secondary-market stress testing (toggle to total loss to read the maximum-loss IRR), EL benchmarking (scenario 4 should produce IRR equal to loss-adjusted return), and educational walkthroughs of how cat bonds differ from convertible debt and traditional fixed income. Calibrate against the Aon ILS Annual Report, Artemis.bm deal database, Lane Financial pricing benchmarks, and AM Best ratings for sub-investment-grade ILS issuance.

## What's included

- 200M single-tranche indemnity cat bond, 4-year tenor, annual coupon pay
- Notional roll-forward with annual loss capped at notional open so principal cannot go negative
- Coupon stream paid on declining notional after a loss event
- Scenario selector with four canonical paths: no event, partial loss Y2, total loss Y3, EL each year
- Investor cash flows: Year 0 outflow, Year 1-4 coupons, maturity principal
- Returns: IRR (IFERROR-wrapped), no-loss YTM, loss-adjusted return, spread multiple, total return
- Sensitivity grid: 5x5 risk-free offset by spread offset producing total yield
- Seven validation checks: notional non-negative, loss capped, EL bounds, coupon identity, CF reconciliation, layer width, multiple >= 1.0
- 200M single-tranche indemnity cat bond with 4-year tenor and annual coupon pay
- Notional roll-forward where annual loss is capped at notional open so principal never goes negative
- Scenario selector with four loss paths: no event, partial loss Y2, total loss Y3, EL each year
- Investor cash flows: Year 0 issuance outflow, Years 1-4 coupons, maturity principal returned
- Returns: IRR (IFERROR-wrapped for total-loss path), no-loss YTM, loss-adjusted return, spread multiple, total return on principal
- Seven validation checks: notional non-negative, cumulative loss capped, EL within probability bounds, coupon identity, CF reconciliation, layer width positive, multiple >= 1.0

## Catastrophe Bond Template: How the ILS Cash-Flow Model Works

A values-only preview of a catastrophe bond template that models one indemnity tranche from the investor's side. It links sponsor loss layers, a scenario-driven write-down path, and the resulting cash flows to returns.

Four sections explain the operating drivers, how losses and coupons interact, what the model produces, and where the structure is useful.

### What drives the bond's economics

The model evaluates a single-tranche indemnity catastrophe bond from an institutional investor's perspective over a four-year horizon. Its economics are set by the sponsor's underlying loss layer: the attachment point where payouts begin, the exhaustion point where the full principal is exhausted, and the width between them.

- Investor return combines a collateral yield from short-dated Treasuries with a risk spread, together forming the all-in coupon. Risk is quantified by attachment probability, exhaustion probability and expected loss, with expected loss constrained to sit between the two.

- The multiple expresses the spread as a coverage ratio of expected loss, so the pricing relationship between spread and modelled loss is explicit rather than implicit.

### How losses flow through principal and coupons

Each year a scenario selector chooses a loss path, from no event through partial and total write-down to an expected-loss average. Annual loss dollars are a percentage of principal, capped by the notional still outstanding so cumulative losses cannot exceed the principal.

- The notional pool does not replenish: once written down, principal stays reduced even if no later event occurs. Coupons are paid only on the opening notional each year, so after a partial loss the coupon stream and any final principal return are both smaller.

- Payouts crystallise at year-end in this abstraction, and a scenario with a full early write-down leaves no remaining principal to return.

### Outputs the investor can review

The model produces per-year notional rolls, coupon streams and investor cash flows, then derives IRR, yield-to-maturity in a no-loss case, total return, a capital-at-risk multiple and a loss-adjusted return.

- A probability-weighted expected return combines the four scenarios using assigned probabilities, alongside their life-to-date loss percentages, with a check that the probabilities sum to 100%.

- A sensitivity grid cross-tabulates an expected-loss ladder against a multiple ladder to recompute all-in yield.

- Validation checks cover notional non-negativity, cumulative loss capping, expected-loss bounds, coupon consistency, cash-flow reconciliation, layer width, multiple sanity and scenario-probability sums.

### Where the template is useful

This template suits an investor or analyst reviewing a single-peril US named-storm tranche rather than a multi-tranche programme. It helps explain layer attachment, exhaustion, expected loss and spread multiple, and shows how write-downs affect coupons and principal over time.

- Scenario switching makes the difference between no-event, partial-loss, total-loss and average-loss paths easy to compare, while the sensitivity grid relates expected loss and multiple to all-in yield. The probability-weighted return puts the catastrophe cost beside the no-loss return.

- The public download is a values-only preview; the underlying model captures these relationships rather than recalculating live in the preview.

## Built for the ILS investor desk

Cat bonds price off a single number - the multiple of expected loss. This template lays out the spread, the EL, the multiple, and the loss-adjusted return on a single Returns sheet so a primary-market or secondary-market pricing call is one cell-read away.

## Designed for scenario stress

A single Scenario_Sel cell on the Assumptions sheet toggles through no-event, partial-loss, total-loss, and EL-each-year paths. The notional roll-forward, coupon stream, cash flows, IRR, and checks all recompute immediately - no formula rewrites.

## Audit-friendly mechanics

Every input is a named-range cell, every formula is one or two operations, loss is capped at notional open so principal never goes negative, and the workbook passes static-value, self-reference, dead-assumption, and unused-named-range scans.

## Built for the ILS investor desk

Cat bonds price off a single number - the multiple of expected loss. This template lays out the spread, the EL, the multiple, and the loss-adjusted return on a single Returns sheet so a primary-market or secondary-market pricing call is one cell-read away.

## Designed for scenario stress

A single Scenario_Sel cell on the Assumptions sheet toggles through no-event, partial-loss, total-loss, and EL-each-year paths. The notional roll-forward, coupon stream, cash flows, IRR, and checks all recompute immediately - no formula rewrites.

## Audit-friendly mechanics

Every input is a named-range cell, every formula is one or two operations, loss is capped at notional open so principal never goes negative, and the workbook passes static-value, self-reference, dead-assumption, and unused-named-range scans.

## Workbook structure

### Cover

Workbook overview, sheet legend, and tab-colour key for navigation.

- Title and scope framing
- Sheet-by-sheet purpose summary
- Tab-colour legend

### Assumptions

Every driver in one sheet: scenario selector, bond structure, trigger layer, loss model, and four scenario loss paths.

- Scenario_Sel parameter (1-4) drives the loss path
- Bond principal, tenor, collateral yield, risk spread, derived all-in coupon
- Attachment and exhaustion sponsor UNL with derived layer width
- Attachment probability, exhaustion probability, expected loss, derived multiple
- Four scenario loss paths stored Y1..Y4 as inputs

### Bond_Structure

Notional roll-forward and coupon stream over Year 0 to Year 4.

- Notional Open: principal at Y1, prior Close thereafter
- Annual Loss: MIN(scenario percent * principal, notional open)
- Notional Close = Open - Loss
- Coupon = Notional Open * All_In_Coupon
- Principal Returned at maturity year only
- Investor Inflow = Coupon + Principal Returned

### Loss_Model

Scenario loss path with cumulative tracking and layer reference.

- Annual Loss percent and dollars echoed from Bond_Structure
- Cumulative loss roll-forward
- Payout to cedant per period
- Attachment and exhaustion reference rows (constant)
- EL benchmark in dollars for comparison

### Cash_Flows

Year 0 issuance, per-year coupons, and maturity principal stitched into a net cash flow series.

- Principal Out: -Bond_Principal at Y0
- Coupon Received per year from Bond_Structure
- Principal Returned at maturity from Bond_Structure
- Net Cash Flow = sum of the three rows
- Cumulative Net CF roll-forward

### Returns

Headline return metrics plus aggregate cash flow rollups.

- Investor IRR (IFERROR-wrapped for total-loss path)
- No-loss YTM = All-In Coupon
- Loss-Adjusted Return = Coupon - Expected Loss
- Spread Multiple = Risk Spread / Expected Loss
- Return on Principal = SUM(CF post-issuance) / Principal
- Sum of Coupons, Sum of Losses, Principal Returned

### Sensitivity

No-loss yield ladder across risk-free and spread offsets.

- Spread offset header row (decimal inputs from -200 to +200 bps)
- Risk-free offset column (decimal inputs from -200 to +200 bps)
- Grid formula: Risk_Free + RF_offset + Risk_Spread + Spread_offset
- 5x5 = 25 total yield cells

### Checks

Seven validation checks plus an ALL CHECKS PASS rollup.

- Notional non-negative across all years
- Cumulative loss capped at principal
- EL within probability bounds: Exhaustion_Prob <= EL <= Attachment_Prob
- Coupon identity: Y1 Coupon = Notional Open * All_In_Coupon
- CF reconciliation: Sum(CF) = Sum(Coupons) + Remaining Principal - Principal
- Layer width positive: Exhaustion > Attachment
- Multiple >= 1.0: spread covers EL

### Cover

Workbook overview, sheet legend, and tab-colour key for navigation.

- Title and scope framing
- Sheet-by-sheet purpose summary
- Tab-colour legend

### Assumptions

Every driver in one sheet: scenario selector, bond structure, trigger layer, loss model, and four scenario loss paths.

- Scenario_Sel parameter (1-4) drives the loss path
- Bond principal, tenor, collateral yield, risk spread, derived all-in coupon
- Attachment and exhaustion sponsor UNL with derived layer width
- Attachment probability, exhaustion probability, expected loss, derived multiple
- Four scenario loss paths stored Y1..Y4 as inputs

### Bond_Structure

Notional roll-forward and coupon stream over Year 0 to Year 4.

- Notional Open: principal at Y1, prior Close thereafter
- Annual Loss: MIN(scenario percent * principal, notional open)
- Notional Close = Open - Loss
- Coupon = Notional Open * All_In_Coupon
- Principal Returned at maturity year only
- Investor Inflow = Coupon + Principal Returned

### Loss_Model

Scenario loss path with cumulative tracking and layer reference.

- Annual Loss percent and dollars echoed from Bond_Structure
- Cumulative loss roll-forward
- Payout to cedant per period
- Attachment and exhaustion reference rows (constant)
- EL benchmark in dollars for comparison

### Cash_Flows

Year 0 issuance, per-year coupons, and maturity principal stitched into a net cash flow series.

- Principal Out: -Bond_Principal at Y0
- Coupon Received per year from Bond_Structure
- Principal Returned at maturity from Bond_Structure
- Net Cash Flow = sum of the three rows
- Cumulative Net CF roll-forward

### Returns

Headline return metrics plus aggregate cash flow rollups.

- Investor IRR (IFERROR-wrapped for total-loss path)
- No-loss YTM = All-In Coupon
- Loss-Adjusted Return = Coupon - Expected Loss
- Spread Multiple = Risk Spread / Expected Loss
- Return on Principal = SUM(CF post-issuance) / Principal
- Sum of Coupons, Sum of Losses, Principal Returned

### Sensitivity

No-loss yield ladder across risk-free and spread offsets.

- Spread offset header row (decimal inputs from -200 to +200 bps)
- Risk-free offset column (decimal inputs from -200 to +200 bps)
- Grid formula: Risk_Free + RF_offset + Risk_Spread + Spread_offset
- 5x5 = 25 total yield cells

### Checks

Seven validation checks plus an ALL CHECKS PASS rollup.

- Notional non-negative across all years
- Cumulative loss capped at principal
- EL within probability bounds: Exhaustion_Prob <= EL <= Attachment_Prob
- Coupon identity: Y1 Coupon = Notional Open * All_In_Coupon
- CF reconciliation: Sum(CF) = Sum(Coupons) + Remaining Principal - Principal
- Layer width positive: Exhaustion > Attachment
- Multiple >= 1.0: spread covers EL

## Features

- **Notional roll-forward with loss cap:** Annual loss in dollars is the smaller of scenario loss percent times principal and notional open, so cumulative principal write-downs never exceed 100% and post-event coupons reflect the reduced remaining notional.
- **Scenario CHOOSE drives loss path:** Single-cell Scenario_Sel parameter routes through CHOOSE() into one of four canonical paths so users can toggle between no-event base case, partial-loss attachment breach, total-loss exhaustion breach, and the EL benchmark path with a single edit.
- **Multiple and loss-adjusted return:** Returns sheet reports the spread multiple (risk spread / expected loss) and the loss-adjusted return (coupon minus EL), matching how ILS investors and Aon ILS Annual Report benchmark cat bond pricing.

## Use cases

- **Investor pricing review:** Flex the risk spread and the expected loss inputs to see how the multiple, loss-adjusted return, and IRR move. Compare against current secondary-market levels to decide whether a primary issuance prices in or out of the money.
- **Tail-loss stress testing:** Switch the Scenario_Sel to total loss Y3 and read the IRR straight off the Returns sheet to quantify the maximum-loss path. The model produces a negative IRR with a clean cash flow trail rather than a divide-by-zero error.
- **EL benchmarking:** Scenario 4 distributes the expected loss evenly across the four years so the user can verify that a realised EL-each-year path produces an IRR equal to the loss-adjusted return, validating the model against the analytical benchmark.

## Frequently asked questions

### What is a catastrophe bond?

A catastrophe bond (cat bond) is a fully-collateralised reinsurance contract repackaged as a tradeable security. A sponsor (cedant) sets up a special-purpose vehicle that issues notes to investors, invests the proceeds in a short-dated Treasury collateral trust, and pays investors a collateral yield plus a risk spread. When a covered catastrophe event causes sponsor losses above the attachment point, principal is written down to pay the sponsor.

### How is the coupon calculated after a partial loss?

Coupon equals Notional Open times the all-in coupon rate, not principal. After a partial loss writes down principal, Notional Open falls and the next coupon is correspondingly smaller. Investors are only paid on remaining unpaid principal.

### What is the spread multiple?

The multiple equals risk spread divided by expected loss and is the canonical pricing benchmark for cat bonds. 2024 market average sits at ~3.0x. Investment-grade single-peril deals trade closer to 2.5x; high-risk multi-peril aggregate deals can reach 4-5x. A multiple below 1.0x means the spread does not even cover the expected loss - typically rejected by investors.

### Why does the IRR equal the all-in coupon in the base case?

In the no-event scenario the investor receives the full coupon every year and gets principal back at maturity, so the IRR equals the all-in coupon rate. Any loss event reduces both coupons (smaller notional base) and ultimately the principal returned, so realised IRR falls below the coupon.

### How is the total-loss scenario handled?

Scenario 3 writes off 100% of principal in Year 3. The IRR formula is wrapped in IFERROR returning 0% because a cash flow series with no positive flow has no meaningful IRR. The Checks sheet still validates that cumulative loss is capped at principal so the model does not produce nonsensical negative notional.

### Can I extend the tenor or change coupon frequency?

Yes. The builder is parameterised by NUM_PERIODS (currently 5 = Year 0 plus four annual coupons) and Bond_Tenor. Switching to semi-annual coupons requires halving the coupon rate per period and doubling the period count - a deliberate edit but mechanically simple.

## Related templates

- [Reinsurance Company Underwriting Model](https://finamodel.com/templates/reinsurance-model)
- [Convertible Bond Model](https://finamodel.com/templates/convertible-bond-model)
- [Infrastructure Bond Valuation Model](https://finamodel.com/templates/infrastructure-bond-model)