Reinsurance & Insurance Accounting
Quota-Share vs Surplus-Share Reinsurance Formula
Compare fixed Quota-Share cession against risk-sized First-Surplus-Share cession MIN(Max_Lines * Line, MAX(0, Sum_Insured - Line)) / Sum_Insured, Ceding Commission, and Net Retained Cash Flow.
Keep small-policy profits in-house with Surplus-Share lines instead of Quota-Share!
Under Proportional Reinsurance, a Quota-Share treaty cedes a flat percentage q_qs of EVERY policy’s premium and claims — giving away profitable small policies that the insurer could easily retain! A First-Surplus-Share treaty solves this by specifying a Retention Line L_ret (say $2,000k) and up to M_lines Surplus Lines of capacity (say 4 lines = $8,000k max reinsurer limit, $10,000k total capacity): policies with Sum_Insured SI <= L_ret cede 0%, while larger policies cede exact Surplus Cession Ratio c_surp = MIN(M_lines * L_ret, MAX(0, SI - L_ret)) / SI, and the reinsurer pays Ceding Commission comm * Ceded_Premium back to reimburse acquisition costs!
Verify on Policy_Sum_Insured SI = $8,000k (B2), Gross_Written_Premium GWP = $160k (B3), Incurred_Policy_Loss = $100k (B4), Cedant_Retention_Line L_ret = $2,000k (B5), Treaty_Max_Surplus_Lines M_lines = 4 lines (B6 -> $8,000k treaty capacity), and Ceding_Commission_Rate comm = 0.25 (25.0%, B7): Surplus Ceded Limit is MIN(4 * 2,000, MAX(0, 8,000 - 2,000)) = $6,000k, yielding Surplus Cession Ratio c_surp = 6,000 / 8,000 = 0.7500 (75.00%); Ceded Premium is 0.75 * 160 = $120.00k less $30.00k Ceding Commission ($90.00k net reinsurance remit); Ceded Loss Recovery is 0.75 * 100 = $75.00k; and Cedant Net Underwriting Margin (Retained_Prem + Commission - Retained_Loss) is 40.00 + 30.00 - 25.00 = +$45.00k! Test a small policy SI = $1,500k (<= $2,000k line): c_surp drops to 0.0000 (100% retained)!
| Surplus-Share Treaty Metric (SI=$8M, GWP=$160k, Loss=$100k, Line=$2M, 4 Lines) | Formula | Verified Output |
|---|---|---|
| Surplus Cession Ratio (c_surp) | MIN(M_lines*L_ret, MAX(0, SI - L_ret)) / SI | 0.7500 (75.00% Ceded) |
| Gross Ceded Premium & Ceding Commission | c_surp * GWP; comm * Ceded_Prem | $120.00k Prem; $30.00k Comm |
| Ceded Loss Recovery ($k) | c_surp * Loss | $75.00k (Retained Loss = $25k) |
| Cedant Net Underwriting Result ($k) | (1 - c_surp)*(GWP - Loss) + comm*c_surp*GWP | +$45.00k |
Bounded First-Surplus-Share Cession Ratio Formula
Always cap ceded sum insured at M_lines * L_ret: if SI = $12,000k exceeds 1 retention line ($2M) + 4 surplus lines ($8M) = $10M treaty capacity, the 4-line surplus treaty covers $8M / $12M = 0.6667 (leaving $2M retained + $2M needing facultative/second-surplus cover)!
=LET(si_vl, B2, gw_pr, B3, ls_in, B4, ln_rt, B5, m_ln, B6, cm_rt, B7, IF(OR(si_vl <= 0, gw_pr <= 0, ls_in < 0, ln_rt <= 0, m_ln < 1, cm_rt < 0, cm_rt >= 1), "Invalid Surplus Treaty", ROUND(MIN(m_ln * ln_rt, MAX(0, si_vl - ln_rt)) / si_vl, 4)))Cedant Net Underwriting Cash Result Formula
Include Ceding Commission credit (+comm * c_surp * GWP) alongside retained premium and retained claim share!
=IF(ISTEXT(E2), E2, ROUND((1 - E2) * (B3 - B4) + B7 * E2 * B3, 2))Try the example
Use the original values or change them to test your understanding.