Help with developing a formula for an employee longevity report

cjackson30

Does anyone have advice on building a formula that properly calculates longevity for employees that includes an escalator based on their job effective date? I have everything built in the model but I cannot figure out how to get it to include the increases. Assuming someone is paid 20 dollars per month for every 2 years they are employed.

Example: Steve earns 60 dollars/month in longevity and their anniversary date is November 1st. Their earnings would be:

September - 60

October - 60

November - 80

…. 80 continuing until next anniversary date (every 2 years)

jepley

Not an issue, I added some assumed complexity but you could likely go a little simpler, check it out:

1. Employee attributes, with start date (or effective date) and the current salary

2. Compensation metrics, this gives you flexibility if you want to change the interval of increase or the value

3. Salary: Checks to see if the employee is active

IF END() > '01 Employee Attributes'.Start Date THEN '01 Employee Attributes'.'Salary (Monthly)' ELSE 0

Salary with increase: Adds existing salary to the increased value based on the multiplier

Salary + Multiplier * '02 Compensation metrics'.Increase Value

Multiplier: This is where I went a bit overboard… First it subtracts the current date from the employee start date, next it makes sure that is greater than 0.

Then it divides that by 365.25 to get how time they've been active in Years.

It then rounds the result down because someone who has been active for 1.9 years should not get an increase.

Next you divide that by the Increase Interval to understand how many intervals they have crossed.

It then rounds the result down because intervals should be whole numbers and only apply once crossed.

ROUND(ROUND(MAX(START() - '01 Employee Attributes'.Start Date, 0) / 365.25, 0, DOWN) / '02 Compensation metrics'.'Increase interval (years)', 0, DOWN)