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(A18000,.25*8000,.25*A1)




"brenda" wrote:

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