Catastrophe Bond (ILS)
Insurance Financial Model (Free Excel Download)
Model catastrophe bond issuance, collateral income, attachment points, expected losses, and investor returns to evaluate insurance-linked risk transfer economics.
professionals from Deloitte
Used by professionals from
About this model
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 every model includes
Live formulas, no hardcoded values
Outputs are driven by live formulas, so the workbook updates from its assumptions instead of relying on hardcoded results.
All assumptions in one tab
Inputs are clearly marked in the Assumptions tab and separated from calculations, making it clear what to change and what to leave intact.
Statements always balancing
For integrated-statement models, the balance sheet, cash flow, and supporting schedules tie through properly.
Distinct schedules for clarity
Debt, working capital, taxes, and cash flow can get messy quickly. We group calculations in clear schedules, not across disconnected tabs.
No hidden macros or external links
There are no unexplained external workbook links or macros to undermine auditability or portability.
Changes flow through the model
Update a key driver and see the impact carry through the forecast, financing, and return outputs. We never use hardcoded numbers in formulas.
What's inside the Catastrophe Bond (ILS)
- 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
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.
Formatted to IB standards
Named theme colors repaint the whole workbook in one click, on top of an investment-banking structure with clear input, output, and cross-sheet reference styling - brand-ready, institutional-grade, and fully auditable.
Created by ex-finance professionals
Hey, I’m Alex and I created Finamodel.
Over my years in the finance industry I kept building the same models over and over again. Same structure, same assumptions, different logo. So I started building frameworks to turn them into clean, reusable templates.
Every model here is one I’d actually use for a client, and I personally vet each one before it goes up.
I’m not an expert in every industry, but I’ve built enough models to know what belongs in one. And when something is completely foreign to me, I reach out to my network for experts to work on our models with us.
Having a template library on hand cuts a first build from hours to minutes.
Need help finding your model? You’ll find me in the Finamodel app!
Frequently asked
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.
Have more financial modelling questions? Contact us
Related templates
Reinsurance Company Underwriting Model
Reinsurance premium income, claims reserves, underwriting margin, and ROE for reinsurance portfolios.
Convertible Bond Model
Model convertible bond economics with conversion scenarios, dilution, and bond value.
Infrastructure Bond Valuation Model
Valuation model for infrastructure bonds including project cash flows, debt service coverage, and credit rating sensitivity.
Life Insurance Valuation Model
Valuation model for life insurance products and in-force book value using mortality assumptions and surrender rates.
