Reinsurance by Hand: Quota Share, Surplus Treaties, Excess-of-Loss Layers, Reinstatements & the Ceded Result

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Split Tallowfield's premiums and claims under a 40 % quota share, work out the ceding commission and compare the gross and net loss ratio and the net result.
• Size a 9-line surplus treaty, work out the ceded sum insured and the cession % of eight property risks, share their premiums and claims and flag the risks that are too big for the treaty.
• Apply a 3,000 xs 1,000 per-risk layer to ten claims, split each loss into ceded and retained, and count the claims that reach and exhaust the layer.
• Run six large losses through a three-layer tower, split each into the layers and the retained part, find the highest layer reached and measure how much of each limit was used.
• Group fourteen claims into three fictional catastrophe events with SUMIFS, apply a 12,000 xs 8,000 cat XL per event and see why a per-risk view would miss the danger.
• Follow a cat XL through three events in one year, work out how much limit is reinstated, the reinstatement premium pro rata to amount and to time, and the cover that is left.
• Price a fictional 10,000 xs 2,500 layer from six years of history: trend the losses with POWER, work out the burning cost rate, load it into a technical premium and express it as rate on line and payback years.
• Compare no reinsurance, a 30 % quota share and a cat XL over eight fictional years: net cost per year, average net result, volatility with STDEV.S and the worst year, finished with a chart.