# Insurance Layers

#### imported_unknown

##### Active Member
Hi all,

I work in the insurance industry and have the following Excel query. I have a spreadsheet whereby the losses to the insurance are listed by year. I want to create four columns to the right of the losses to show the following:

1. the portion of the loss retained by the client (the excess) which is the first \$1m.

2. also, the client takes up to \$1m in excess of this, subject to a maximum of \$1m in any one year.

3. If this secondary excess has been blown, the reinsurer takes \$1m excess of this \$1m.

4. Above this \$2m limit, there is another reinsurance layer to a limit of \$3m excess of this \$2m

I want it set up so that I can change the option of limit and retention as described above.

How can I get Excel to do this for me??!!

Your speedy responses would be greatly appreciated.

R

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Dave Patton

##### Well-known Member
On 2002-10-03 09:22, Rizly wrote:
Hi all,

I work in the insurance industry and have the following Excel query. I have a spreadsheet whereby the losses to the insurance are listed by year. I want to create four columns to the right of the losses to show the following:

1. the portion of the loss retained by the client (the excess) which is the first \$1m.

2. also, the client takes up to \$1m in excess of this, subject to a maximum of \$1m in any one year.

3. If this secondary excess has been blown, the reinsurer takes \$1m excess of this \$1m.

4. Above this \$2m limit, there is another reinsurance layer to a limit of \$3m excess of this \$2m

I want it set up so that I can change the option of limit and retention as described above.

How can I get Excel to do this for me??!!

Your speedy responses would be greatly appreciated.

R

It is difficult to provide useful assistance with limited information.

Can you show a few lines of data and the expected results.

data in Column?

#### Fryer Tuck

##### Board Regular
I believe what you want to do is set up your calculations in each of the columns in the way you have mentioned. However, instead of putting in the amounts, reference to a particular cell where you have that amount. If you place a dollar sign (\$) before the column referce, and another before the row reference (Eg. \$C\$2), then you can copy the formula to your hearts content and it will always reference that cell. In this way, you can change the amount and the results will be carried thru all formulas.

F.T.

Replies
0
Views
1K
Replies
3
Views
581
Replies
1
Views
5K
Replies
1
Views
2K
Replies
2
Views
464

1,181,781
Messages
5,932,005
Members
436,814
Latest member
Yurop

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back