calculate end of services gratuity

[Rashed3030]

Hello all ,
I need an excel template to calculate the end of services gratuity according to IAS19 to make acturial valuation
End of Service Benefit Rules

< 2 years= 1/2 month Scheme Salary
2 - 5 years =1/2 month Scheme Salary
5 - 10 years= "First 5 years: 1/2 month Scheme Salary > 5 years: 1 month Scheme Salary

[Kaper]

Will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If trere are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, etc. - please show them all or at least indicate in text) The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).

3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!

To attach an Excel file you have to do the following: Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.

[Rashed3030]

here is the sample file and I try to put in it all available assumption, I hoped it helped

[JeteMc]

I assume that you are looking for formulas/code that will fill values on the Data sheet where individual years of service are given.
It may help us if you provide manually filled in the values for those examples, so that we have an idea as to whether or not our proposed formulas/code are yielding the expected results.
Let us know if you have any questions.

[Rashed3030]

Thanks, JeteMc for replay
I only have the total number not single number to each one
I can send you the report if it would help

[Pepe Le Mokko]

Administrative Note:

Welcome to the forum.


Don't private message, visitor message or email Excel (or Access, Word, etc.) questions/files to moderators or other members.

All questions and answers will benefit other posters like yourself when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.

Post your question in a public thread and our many contributors will come to your assistance, especially if the title is accurate (see Rule #1) and you include a sample desensitized workbook that makes it easy for others to try and help.

Thank you

[Rashed3030]

ok I get it Pepe thank you

[Shareez Saleem]

Hey, I have prepared something for you.
Just have a look.

The calculation should be checked before using it.

Please Login or Register  to view this content.

C3=Separation Type
C4= Scheme Salary
C5= Joining Date
C6= Last Working Date
C8= Total Working Days

[JeteMc]

If Shareez's post resolves the issue then please mark the thread as 'Solved' using the thread tools menu above your first post.
If not then it would help if you would upload a sample of the report to this thread, clearly indicating the results that you would like automated by formula/code.
Let us know if you have any questions.

[Rashed3030]

Thanks bro Shareez for your contribution
but the main idea is to calculate the expected future service for the employee, by calculate the probability of surviving using mortality tables wich include in first file