Insurance-Linked Securities (ILS) & Reinsurance Capital Markets
Catastrophe Bond Spread Multiple & Layer Payout Formula
Calculate ILS Catastrophe Bond Risk-Spread-to-Expected-Loss Multiple, Actuarial Net Excess Spread (bps), Proportional Layer Principal Write-Down Principal * MIN(1, MAX(0, (Loss - Attach)/(Exhaust - Attach))), and Surviving Investor Principal.
Price collateralized Catastrophe Bond tranches from Attachment, Exhaustion, and Expected Loss!
Property-catastrophe insurers transfer peak hurricane and earthquake tail risk to capital-market investors via Rule 144A Catastrophe Bonds (ILS), where investor principal sits in a Treasury money-market collateral trust earning SOFR plus an Insurance Risk Spread! ILS portfolio managers benchmark deal pricing via the Cat Spread Multiple = Risk_Spread / Annual_Expected_Loss (typically 2.5x–4.0x modeled AIR/RMS Expected Loss EL) and Net Actuarial Risk Premium = 10,000 * (Risk_Spread - EL) bps! If a qualifying catastrophe causes Ultimate Loss L between Attachment Point A and Exhaustion Point X, principal is written down pro-rata across the layer: Triggered_Payout = Principal * MIN(1, MAX(0, (L - A) / (X - A))), returning Surviving_Principal = Principal - Triggered_Payout to bondholders!
Verify on a $100M Florida Wind Cat Bond—Tranche_Principal = $100.0M (B2), Layer_Attachment A = $400.0M (B3), Layer_Exhaustion X = $500.0M ($100M layer width, B4), Modeled_Annual_Expected_Loss EL = 0.025 (2.50%, B5), Risk_Spread_Over_SOFR = 0.075 (7.50% = 750 bps, B6), and Realized_Hurricane_Loss L = $460.0M (B7): Spread-to-EL Multiple is 0.075 / 0.025 = 3.00x (E2); Actuarial Excess Spread is 10,000 * (0.075 - 0.025) = 500.00 bps (E3); Triggered Reinsurance Payout is $100M * ((460 - 400) / (500 - 400)) = $60.00M (E4); leaving $40.00M Surviving Principal for bondholders (E5)!
| ILS Cat Bond Metric ($100M xs $400M–$500M, EL=2.50%, Spread=7.50%, Loss=$460M) | Formula | Verified Output |
|---|---|---|
| Spread-to-Expected-Loss Multiple | Risk_Spread / Expected_Loss | 3.00x |
| Net Excess Risk Spread (bps) | 10000 * (Risk_Spread - Expected_Loss) | 500.00 bps |
| Triggered Principal Write-Down | Prin * MIN(1, MAX(0, (L - A)/(X - A))) | $60.00M ($0 @ $350M; $100M @ $650M) |
| Surviving Investor Principal | Principal - Triggered_Write_Down | $40.00M ($100M @ $350M; $0 @ $650M) |
Catastrophe Bond Proportional Layer Write-Down Formula
Validate Principal > 0, 0 <= Attachment < Exhaustion, 0 < EL < 1, Spread >= 0, and Event_Loss >= 0!
=LET(pr_n, B2, at_p, B3, ex_p, B4, el_r, B5, sp_r, B6, ls_e, B7, IF(OR(pr_n <= 0, at_p < 0, ex_p <= at_p, el_r <= 0, el_r >= 1, sp_r < 0, ls_e < 0), "Invalid Cat Bond Terms", ROUND(pr_n * MIN(1, MAX(0, (ls_e - at_p) / (ex_p - at_p))), 2)))Sub-Attachment Intact Principal ($350M) & Total Exhaustion ($650M) Checks
Test Event_Loss B7 = $350M (<= $400M attachment): Payout E4 = $0.00M and Surviving Principal E5 = $100.00M! Test B7 = $650M (>= $500M exhaustion): MIN(1, ...) caps Payout E4 at $100.00M (Surviving Principal E5 = $0.00M)!
=IF(ISTEXT(E4), E4, ROUND(B2 - E4, 2))Try the example
Use the original values or change them to test your understanding.