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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

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
1
Views
3K
Replies
1
Views
371
Replies
2
Views
406
Replies
3
Views
562
Replies
11
Views
2K

1,147,517
Messages
5,741,631
Members
423,674
Latest member
Charles2dodo

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.

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