Insurance Layers

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
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
 

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
Joined
Feb 15, 2002
Messages
5,011
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Mar 20, 2002
Messages
64
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top