Catastrophe Bond (ILS)Free Financial Model Download

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.