variable annual wage increases based on start date

[spence]

I have a five year month-by-month personnel budget with the staff names down
column A and their start dates in columb B. The months and years through 2012
go across row 1.

My question is, is there a way to build formulas that will calculate annual
wage increases based on start date where the increase is:

1. 2% at year one anniversary
2. 3% at year two anniversary
3. 1% at every anniversary thereafter

Thanks.

 

[William Horton]

Would you be able to use something like the following...

=IF(C$1>DATE(YEAR($B2)+3,MONTH($B2),DAY($B2)),1%,IF(C$1>DATE(YEAR($B2)+2,MONTH($B2),DAY($B2)),3%,IF(C$1>DATE(YEAR($B2)+1,MONTH($B2),DAY($B2)),2%,0%)))

That will show whether 3%, 2%, 1%, or 0% needs to be applied.

Hope that is a start.

 

[Mike H]

I hope for the sake of your employees I've got this wrong (A 1% rise after 3
yrs!!) but here goes:-


First create a cell (C2) with today date in using the formula =now()
Then create a cell (D2) that calculates length of service with the formula:-

=(C2-B2)/365.25 Note cell must be formatted as general

lastly in E2 the formula for the annual increase.
=IF(D2>3,"1% Rise",IF(D2>2,"3% Rise",IF(D2>1,"2% Rise","")))

Mike

 

[spence]

This is exactly the kind of syntax I was looking for, so thanks very much for
your excellent guidance. Your help was exactly the jumping-off point I needed
to puzzle this thing out. However, I'm running into a couple of challenges
that I hope you might be able to assist me with. I'm pasting my adaptation of
your formula below for you to see. The example from my original post was
simplified for clarity's sake, but I'm going to give you the more detailed
scenario here so we're on the same page and so you'll understand the
alternations I've made to your sampe formula:

Actual wage schedule:
1. 2.5% at one year anniversary
2. 2.5% at two year anniversary
3. 3% at years three, four, and five anniversaries
4. 1% every year thereafter

My anniversary dates are in column C and the starting wage is in column F.
My row of months begins in row G. So:

=IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4)+1826),(F4*1.03),IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4)+1461),
(F4*1.03),IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4)+1096), (F4*1.03),
IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4)+731), (F4*1.025),
IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4)+365), (F4*1.025),F4)))))

If it's not obvious, the values for a given month are based on the pay from
the previous month.

I was having two problems with your formula:

1. Using the Year count caused my wage increases to fall one month too late
(e.g. a start date of 11/01/06 should have produced a 2.5% raise as of
11/01/07. but instead didn't show the increase until 12/01/07.) I solved this
by changing to a Day count which is working but which seems like a terrible
idea...the leap years had me baffled for a good couple of hours. Perhaps
there's a better way to do this and get the same result?

2. Using ">" caused a problem because once an anniversary date was reached,
every month following showed the same % increase since those months also
matched the criteria in the IF statement. I resolved this by using "="
instead of ">". This works fine except for those years AFTER the fifth year
when I need to continue to show an annual increase of 1% for perpetuity. The
only solution I can see is to add IF statements to the forumla for year 5, 6,
7, 8...etc. This doesn't seem like a good way to solve the problem.

Any thoughts?

Thanks again for your prior assistance. I've been at this all day but it's
been worth the learning experience and then some.

 

[spence]

I simplied my scenario considerably so the truth isn't quite as ugly as all
that. The reduction to a mere 1% annual increase doesn't actually start until
the sixth year. It's the joy of working for a non-profit.

Thanks for your help.

 

[spence]

And I just realized as I tried to populate this down the rows that the day
count is not only messy but that it *won't* work because the leaps years make
the counts different depending on the year someone started. Argh.

 

[Pete_UK]

Here's another approach that you might like to play about with. Set up
a little table somewhere as follows:

0 0%
1 2.5%
2 5.0%
3 8.0%
4 11%
5 14%

You can give this table a name, and as you can see it represents the
cumulative percentage increase from the starting salary. I have
assumed that in year 2 the increase is 5% on the starting salary,
rather than 2.5% on top of 102.5 from the previous year, but if this
is the case then you can easily re-work the figures. Then basically
you will want the working year from the start date up to present -
something like this will give you that:

=INT((TODAY()-C$2)/365.25)

but instead of TODAY() you might like to use the DATE formula you
already have.

Then you can have a formula like:

=IF(calc_year>6,(100+calc_year+14)/100,1+
VLOOKUP(calc_year,table_name,2))*F2/12

where calc_year is the above formula. This will give you the monthly
salary increased by the appropriate percentage.

Hope this helps.

Pete

 

[spence]

Pete,

Thanks. I'd like to try your approach and I shall when I rework this thing
in a few weeks. For now I'm stuck on a bit of a deadline. I solved my Day
counting issue but switching to Month, so the only problem I'm left with is
how to get the annual anniversary month after year five to bump t (e 1%
without having every month that follows that month also move up 1%. Do you
know of syntax I could use in an IF statement (nested in my current formula)
that would say something like:

IF G$1 > ($C4)+5 AND the month in those two cells matches, then multiply by
1%?

That would alllow me to get my 1% increase only on the anniversary months
after five years rather than on every month after five years.

Thanks again,
spence