Reinsurance
Zamora 
Reinsurance_Risk_Fin_Sec_AssignmentExamplar.xlsxPrem Comp formula
| Year | Per Risk XL treaty Unadjusted Losses | Incurred loss & LAE | Retention | Excess over £50,000,000 Pure BC | Subtotals | Excess over £75,000,000 Pure BC | Subtotals | Spill Over | Per Risk Adjuste 1st Layer | Per Risk Adjusted 2nd Layer | QS R (60%) | Subtotal for Retention | QS Rein (40%) | Subtotal for QS Reins | Spill over | Catastrophe Excess of Loss Treaty - computations | Cat XL Adjusted 1st Layer | Per Risk Adjusted 2nd Layer | |||||
| 2011 | Panasonic Corporation - Tsunami | 74,718,110 | 50,000,000 | 24,718,110 | 0 | - 0 | [100m] | 50m | 50m | >200m | |||||||||||||
| Nissan Japan - Tsunami | 65,709,614 | 50,000,000 | 15,709,614 | 0 | - 0 | Per PERIL | CAT XL Losses | Retention | Layer 1 | Layer 2 | Spill Over | ||||||||||||
| Fukushima pollution liability - Tsunami | 162,957,408 | 112,957,408 | 25,000,000 | 25,000,000 | 62,957,408 | ||||||||||||||||||
| Toyota Corporation - Tsunami | 77,788,515 | 50,000,000 | 25,000,000 | 2,788,515 | - 0 | Total: |
tc={6C50EFE1-FD39-4CB4-A5A1-A9DE4E4404E5}: [Threaded comment] Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more: https://go.microsoft.com/fwlink/?linkid=870924 Comment: Please complete based on the NZ example below. Use the same formulas for the other cumulative losses and make the inflation adjustment using the same ethodology I used on the per risk basis. | - 0 | - 0 | - 0 | - 0 | =T5*relativity | =U5*relativity | ||||||||||
| Super Dry Corporation – Tsunami | 66,747,915 | 50,000,000 | 16,747,915 | 0 | - 0 | JAPAN | |||||||||||||||||
| Sky New Zealand - Earthquake | 55,633,151 | 50,000,000 | 5,633,151 | 0 | - 0 | ||||||||||||||||||
| Next New Zealand – Earthquake | 61,581,132 | 50,000,000 | 11,581,132 | 0 | - 0 | ||||||||||||||||||
| Macys New Zealand – Earthquake | 85,931,760 | 50,000,000 | 25,000,000 | 10,931,760 | - 0 | ||||||||||||||||||
| Christ Church Council NZ – Earthquake | 68,824,517 | 50,000,000 | 18,824,517 | 0 | - 0 | Total: | 244,498,610 | 144,498,610 | 50,000,000 | 50,000,000 | 44,498,610 | =T10*relativity | =U10*relativity | ||||||||||
| House damage – NZ - Earthquake | 44,498,610 | 44,498,610 | 0 | 168,214,439 | 0 | 35,931,760 | - 0 | ERROR:#NAME? | ERROR:#NAME? | New Zealand | |||||||||||||
| 2011 | Sony Thailand Floods | 54,584,962 | 50,000,000 | 4,584,962 | 0 | - 0 | 32,750,977 | 21,833,985 | |||||||||||||||
| Dell Thailand Floods | 65,521,731 | 50,000,000 | 15,521,731 | 0 | - 0 | 39,313,039 | 26,208,692 | ||||||||||||||||
| Daimler Thailand – floods | 66,525,422 | 50,000,000 | 16,525,422 | 0 | - 0 | 39,915,253 | 26,610,169 | ||||||||||||||||
| Thai Hotel – Floods | 56,266,020 | 50,000,000 | 6,266,020 | 0 | - 0 | 33,759,612 | 22,506,408 | ||||||||||||||||
| Thai Resorts - floods | 74,677,431 | 50,000,000 | 24,677,431 | 67,575,566 | 0 | 0 | - 0 | ERROR:#NAME? | ERROR:#NAME? | 44,806,458 | 190,545,339 | 29,870,972 | 127,030,226 | ||||||||||
| 2012 | 250,000,000 | 257,575,566 | 60,000,000 | 67,030,226 | |||||||||||||||||||
| 2013 | |||||||||||||||||||||||
| 2014 | |||||||||||||||||||||||
| 2015 | |||||||||||||||||||||||
| 2016 | |||||||||||||||||||||||
| 2017 | |||||||||||||||||||||||
| 2018 | |||||||||||||||||||||||
| 2019 | |||||||||||||||||||||||
| 2020 | |||||||||||||||||||||||
| 2021 | |||||||||||||||||||||||
