End of service benefit (EOSB) calculat.

[marwan1]

Dear Gentleme,

I have a problem in calculating the EOSB using IF funct.
We have system which generates the calculation for us but I want excel function to help double checking my results.

The rules are as follows:

<= 5 years gets half salary
> 5 years gets full salary

Thanks in advance

 

[DonkeyOte]

Not much info. to go on here but you might want to investigate the DATEDIF function

=B2/(1+(DATEDIF(A2,TODAY(),"y")<=5))

where A2 holds start date and B2 annual salary

You may want to adjust the DATEDIF output should 5 years and 1 day = 100%

 

[marwan1]

Thanks DonkeyOte, closer to the answer. Let me give you more info on this:

$5000 Monthly salary, start date 11 sep 1994, end date 30 Apr 2011. The first 5 years gets half of his salary for each year of the five, and one full salary for each year thereafter.

Hope this explains...

 

[DonkeyOte]

So given the example the result would be ? Is it 810k or 67.5k ?

In other words - is "salary" equal to the annual or the monthly salary ?

Annual (810k)

=SUMPRODUCT((DATEDIF($A2,TODAY(),"y")>{0,5})+0,DATEDIF($A2,TODAY(),"y")-{0,5},$B2*{6,6})

or

=SUM(MIN(5,DATEDIF(A2,TODAY(),"y"))*B2*6,MAX(0,DATEDIF(A2,TODAY(),"y")-5)*B2*12)

Monthly (67.5k)

=SUMPRODUCT((DATEDIF($A2,TODAY(),"y")>{0,5})+0,DATEDIF($A2,TODAY(),"y")-{0,5},$B2*{0.5,0.5})

or

=SUM(MIN(5,DATEDIF(A2,TODAY(),"y"))*B2/2,MAX(0,DATEDIF(A2,TODAY(),"y")-5)*B2)

 

[marwan1]

Great DonkeyOte very close to the answer, the second formula is correct (67.5k). But I noticed that I'm exactly 4% more than your numbers!

 

[DonkeyOte]

Regards the 4% - is that a question for me or for yourself ?

If you need to apply a 4% increase multiply the formula result by 1.04

 

[L'fier]

Thanks for the formula, but to me, I found a new problem. Suppose, the employee works for 7.29 years, the calculation is only applicable for 7 years and the 0.29 years are brushed off from his benefits. Can you revise the formula based on this.

So given the example the result would be ? Is it 810k or 67.5k ?

In other words - is "salary" equal to the annual or the monthly salary ?

Annual (810k)

=SUMPRODUCT((DATEDIF($A2,TODAY(),"y")>{0,5})+0,DATEDIF($A2,TODAY(),"y")-{0,5},$B2*{6,6})

or

=SUM(MIN(5,DATEDIF(A2,TODAY(),"y"))*B2*6,MAX(0,DATEDIF(A2,TODAY(),"y")-5)*B2*12)

Monthly (67.5k)

=SUMPRODUCT((DATEDIF($A2,TODAY(),"y")>{0,5})+0,DATEDIF($A2,TODAY(),"y")-{0,5},$B2*{0.5,0.5})

or

=SUM(MIN(5,DATEDIF(A2,TODAY(),"y"))*B2/2,MAX(0,DATEDIF(A2,TODAY(),"y")-5)*B2)