create formula using table

[brenda]

Hi,
I assign in the area in calculating claims recoveries. On the monhtly
benefit that we paid , we have to recover an amount to reinsurer based on the
table below.

Code Retention Eff Date from Eff Date to
1 2500 1/10/2000 30/09/2007
2 4000 1/10/2007 31/10/2008
3 75% up to 8000 1/11/2008 present

Review date - every 5th of December

Using the table above, if we paid 5,000, we have to recover 25% which is
$1,250. Could you please help me to create a formula using the above criteria?

Thanks in advance
Incident date - 27/01/2009
Monthly benefit - 5000

 

[joel]

It is easy if you reorganize your table

Code Retention Recover Eff Date from Eff Date to
1 2500 0% 1/10/2000 30/09/2007
2 4000 0% 1/10/2007 31/10/2008
3 8000 25% 1/11/2008 present


With Amount in A1
=if(A1>8000,.25*8000,.25*A1)

 

[brenda]

Thanks for you reply. I am processing a report like 3,000 different claims
and have different incident dates. also retention should be based on the
review dates of the fund. In my example below review date is 5th of December
2008 and the incident date is 27/1/2009, the retention is under code 3
because incident date happen after the reveiw date and within the effective
date of code 3 retention. what would be my formula in this case?

 

[joel]

A B C D
E
Code Retention Recover Eff Date from Eff Date to
1 2500 0% 1/10/2000 30/09/2007
2 4000 0% 1/10/2007 31/10/2008
3 8000 25% 1/11/2008 present

Using my table above with CODE in A1

A12 = Date 1/12/2008
A13 = 5000

Use an If statement to determine if you are above or below the retention value

=if(A13>=B4,B4,A13*C4)
=if(5000>8000,8000,5000*25%)

You need to lookup the two 8000's and the 25%

Putting the lookup date in the middle of the table makes the formula more
diffficult. You could use Vlookup if the date was the 1st column in the
table. Instead you have to use a combination of Match and Index.

To get the correct row use match on the Date
MATCH(A12,D24,1) - Returns Row 3
MATCH(1/12/08,D24,1)
This will return the row of the table. I started at D2 skipping the header.
The one in match is looking for the largest date less than or equal to the
lookup date. You only need the from date column

To get the retention value you now have to use column B and the row number
returned from the match
INDEX(B2:B4,MATCH(A12,D24,1),1)
INDEX(B2:B4,3,1)

Returns 8000 row 3, colunm 1,starting at B2.



The formula is the following. I addeed the dollar signs to fix the table if
you copy the formula.

=IF(A13>=INDEX($B$2:$B$4,MATCH(A12,$D$2:$D$4,1),1),INDEX($B$2:$B$4,MATCH(A12,$D$2:$D$4,1),1),A13*INDEX($C$2:$C$4,MATCH(A12,$D$2:$D$4,1),1))

 

[brenda]

thank you so much..

I have a follow up question. There's another criteria to consider, the
report is insured to different fund (group) with different review dates.

using the example below;
monthly benefit is 5,000
incident date is 1/12/2008
last review date is covered from 1/6/2008 up to 31/5/2009(note; review date
happens yearly)
retention to apply should be 4,000 because the last review started 1/6/2008
which the retention is still 4,000(per table). using this example can you
modify the formula? please help......

 

[joel]

The formula will still work if yo uput the correct date into the formula.
the table you provided doesn't give enough information to determine when the
event occured and when the policy was renewed. Al yo need to to is to put
the correct date into the formula and you will get the correct retention.