End of service benefit (EOSB) calculat.?

[marwan1]

Dear fellows,

Below is a formula received by DonkeyOte here in the forum to calculate EOSB based on the following rules:

$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.

DonkeyOte's formula was as follows:

Monthly (67.5k)

Code:


=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)




</PRE>
where A2 holds start date and B2 monthly salary! The result is $67,500.00 in the both formulas!

However, our system calculates EOSB as equal to $70,680.48! Diff. is $3,180.48?

Which one to go with and any suggestion!

Thanks for any input!

 

[marwan1]

Any help!

 

[DonkeyOte]

It would seem as though final year is pro-rated
In your ex. 11-Sep-10 to 30-Apr-11 represents around 64% of a full year

(5 * 2500) + (11 * 5000) + (5000 * 64%) = 70.7k

which is seemingly in the right ball park

Calculating the pro-rated amount will be slightly more complicated given the need to establish if the pro-rated amount is 50% or 100% of monthly

Before getting into the specifics of your XL calculation I suggest you confirm how your "system" is calculating EOSB.

 

[marwan1]

Hi DonkeyOte,

Our system calculates the whole periods as: 16 years,7 months and 19 days!

Personally don't think this is correct because applies 30 days on each month.

 

[DonkeyOte]

start date 11 sep 1994, end date 30 Apr 2011

Our system calculates the whole periods as: 16 years,7 months and 19 days!

Personally don't think this is correct because applies 30 days on each month.

Not sure that's correct... the 360 Days rule applies only to the final year else the values you state above are inaccurate
(6076 days @ 360 days per year = 16 years, 10 months and 16 days)
Even then the days remainder you specify is inaccurate on a 30 day rule.

To be honest I can't match the exact figure you specify using 360/365 (non-leap year) ... you can get close but not exact.

It would be good to get an exact breakdown of how it's calculated.