Reinsurance & Actuarial Pricing
Excess-of-Loss (XL) Layer & Rate-on-Line Formula
Calculate per-occurrence Excess-of-Loss reinsurance recovery MIN(Limit, MAX(0, Loss - Retention)), Burning-Cost Rate, Loaded Treaty Rate-on-Line (ROL = Premium / Limit), and Payback Years.
Clip individual catastrophe and large-property claims cleanly to an XL treaty layer!
Non-proportional Excess-of-Loss (XL) reinsurance is quoted as "Limit xs Retention" (for example, $3,000,000 xs $1,000,000, covering losses between attachment $1M and exhaustion ceiling $4M)! A classic spreadsheet bug writes MAX(0, MIN(Loss, Limit) - Retention), which confuses Layer Limit ($3M width) with Exhaustion Ceiling ($4M = Retention + Limit) and wrongly caps a $4.5M claim at $2M instead of $3M! The exact XL Layer Recovery formula for every individual claim X_i is MIN(Limit, MAX(0, X_i - Retention))!
Verify across four historical claims in A2:C5 ($600k, $1,250k, $1,200k, and $900k) on a $3,000k xs $1,000k property treaty (Retention D = 1,000 in B7, Layer Limit L = 3,000 in B8) over Subject_Earned_Premium = $15,000k (B9) with Reinsurer_Loading_Factor = 1.20 (100 / 83.33, B10): Individual Layer Recoveries are 0, 250, 200, and 0 (totaling $450k Ceded XL Recovery out of $3,950k Gross Claims, leaving $3,500k Net Retained); Historical Burning-Cost Rate on Subject Premium is 450 / 15,000 = 0.0300 (3.00%); Loaded XL Treaty Premium is 450 * 1.20 = $540k; and Reinsurance Rate-on-Line ROL = Treaty_Premium / Layer_Limit = 540 / 3,000 = 0.1800 (18.00% Rate-on-Line, or 1 / 0.18 = 5.56-year Payback Period)!
| XL Treaty Metric ($3,000k xs $1,000k; Claims 600, 1250, 1200, 900) | Formula | Verified Output |
|---|---|---|
| Total Ceded Layer Recovery ($k) | SUM of MIN(Limit, MAX(0, Loss_i - Retention)) | 450.00 (0 + 250 + 200 + 0) |
| Historical Burning-Cost Rate | Total_Ceded / Subject_Premium ($15,000k) | 0.0300 (3.00% of Subject Prem) |
| Loaded Treaty Premium ($k, 1.20x Load) | Total_Ceded * Loading_Factor | 540.00 |
| Rate-on-Line (ROL) & Payback Period | Treaty_Premium / Limit; 1 / ROL | 0.1800 (18.0% ROL; 5.5556 yr) |
Per-Occurrence Excess-of-Loss Layer Recovery Formula
Never swap MIN and MAX arguments: first subtract Retention via MAX(0, Loss - Retention), then cap the excess at Layer Limit via MIN(Limit, ...)!
=IF(OR($B$7 < 0, $B$8 <= 0), "Invalid XL Layer", MIN($B$8, MAX(0, B2 - $B$7)))Loaded Treaty Rate-on-Line (ROL) Formula
Test raising Claim_4 in cell B5 from 900 to 4,500 (an exhaustion-piercing catastrophe!): C4 caps cleanly at 3,000, Total Ceded rises to 3,450, and ROL jumps to (3,450 * 1.20) / 3,000 = 1.3800!
=IF(OR(B7 < 0, B8 <= 0, B9 <= 0, B10 < 1), "Invalid XL Layer", ROUND((SUM(C2:C5) * B10) / B8, 4))Try the example
Use the original values or change them to test your understanding.