Insurance Layers

[imported_unknown]

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.

Thanks in advance

R

 

[Dave Patton]

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.

Thanks in advance

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]

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.