Business math word problem: calculating salary through "growth tiers"

April 22, 2015 9:43 AM

I'd like some help coming up with a formula for calculating annual salary increases. The idea is that a given job description will have two "growth tiers", through which an employee's salary will increase at different rates, and beyond which their salary cannot increase further (except for cost-of-living adjustment). There's also a "base growth factor", which is determined by individual employee performance--e.g. 10% for a stellar employee, 6% for a good employee, etc. Lots more inside.

So here's an example: Let's say we have a Field Engineer, Amazing Ashley, who's currently making $63,000 (in 2015 U.S. dollars). The 2015 growth tiers for the Field Engineer position are the following:

$60,000: minimum salary
$60,000 - $72,000: first, "quick" growth tier
$72,001-$84,000: second, "slow" growth tier
$84,001 and above is "maxed"--no further growth possible except cost-of-living adjustment

Ashley has done really well at her job in 2015, so in early 2016, at annual review time, she's assigned a "base growth factor" of 10%. Ashley's salary will be increased by the unmodified "base growth factor" until it hits the top of the first growth tier ($72K). It will then continue to increase through the second growth tier, but at a modified factor of 5% (half her base growth factor).

I also want to account for cost of living. Let's peg this at 1.7%. So Ashley's new 2016 salary would be something like (63000 + 6300) 1.017 = $70,478 (I think?).

Easy enough--because she hasn't crossed into the second growth tier yet. But the 2017 raise will be trickier. And here's the other thing: the growth tiers also adjust to account for increased cost of living. So while the 2015 tier caps for Field Engineer are $72,000 and $84,000, the 2016 numbers will be slightly more: $73,224 and $85,428.

Assuming Ashley continues to do amazingly at her job (10% base growth factor), what will her 2017 salary be? 2018? 2023?

And what about Decent Denise? (Sorry Denise.) She's a Field Engineer making $61,500 in 2015 who'll be given a base growth factor of 6% at her 2016 review, which means her new 2016 salary will be $66,298 (I think?). Assuming she remains merely decent, what is her 2020 salary?

So there you have it. I don't think I'm fully grasping the issue, so my presentation of the information might be bad and might be based on some wrong assumptions. There may also very well be "real" math/statistics terms for the concepts I'm describing, but I don't know them (which might be why I'm having trouble googling this one). If you know them, please set me straight.

Thanks in advance everyone!

posted by Mr. Professional to Education (4 answers total)

This Compensation Guidelines System from the University of Delaware might work. It has a tiered system of compensation, and the examples in Part 3 show exactly how to use it.

This quartile raise system from Colorado State University shows a similar way of doing things.
posted by jabes at 10:32 AM on April 22, 2015

I find this is easiest to set up as a spreadsheet with rows that increment by year, and a few columns:

1. A "base pay" column shows your minimum, including COLA adjustment, so this will go $60,000, $61,020, $62,057, etc.
2. A "max pay" column is similar, but starting from $84,000
3. A "breakpoint" column again is similar, but is calculated as the halfway point between the two, so in pseudo-Excel, that would be "=Base pay 2015+(Max pay 2015-Base pay 2015)/2"
4. The meat of this comes in the fourth column, where you put all this to work. If you didn't have two tiers, this would be calculated simply as "=base pay 2015+(base pay 2015 * growth factor)". But you do have two tiers, so you need an IF to wrap this all up. Basically you're going to test whether the calculated pay is more than the breakpoint, and if so, use half the growth factor. Which looks like this, to calculate pay in 2016, based on known figures for 2015: "=IF((actual pay 2015*growth rate)+ actual pay 2015>Breakpoint 2016,(actual pay 2015*growth rate/2)+actual pay 2015,(actual pay 2015*growth rate)+actual pay 2015)"
5. But wait there's more. Now you need to check whether the result for 2016 actual pay exceeds the max pay for 2016, and if so, use the max pay value. So that's another column.

Also: did I just do your homework for you?
posted by adamrice at 10:36 AM on April 22, 2015 [1 favorite]

Response by poster: (adamrice: I assure you I'm just a middle manager who never did so great in stats class. My last math homework was over ten years ago--and for that I thank heaven every morning.)
posted by Mr. Professional at 11:16 AM on April 22, 2015

Best answer: To follow up: I wasn't able to come up with a single equation to handle everything. But since I'm building my salary calculator in a system that allows for a little conditional logic, I was able to get the outcome I needed with a set of conditional cases and a bunch of variables. (Hopefully the variable names will make clear what they're keeping track of.)

---

1. Update the year

(If True):
Set value of RunningYearVar equal to "[[RunningYearVar + 1]]"


2. Adjust starting salary and tier caps by cost-of-living growth rate

(If True):
Set value of StartingSalaryVar equal to "[[(StartingSalaryVar * (1 + CostOfLivingGRVar)).toFixed(0)]]"
Set value of FirstTierCapVar equal to "[[(FirstTierCapVar * (1 + CostOfLivingGRVar)).toFixed(0)]]"
Set value of SecondTierCapVar equal to "[[(SecondTierCapVar * (1 + CostOfLivingGRVar)).toFixed(0)]]"


3. Calculate salary if "maxed" (above tier 2 cap)

(If "[[(RunningSalaryVar * (1 + CostOfLivingGRVar)).toFixed(0)]]" is greater than or equals value of SecondTierCapVar):
Set value of RunningSalaryVar equal to value of SecondTierCapVar


4. Calculate salary if crossing from second tier to maxed

(Else If "[[(RunningSalaryVar * (1 + (RunningMeritGRVar / 2) + CostOfLivingGRVar)).toFixed(0)]]" is greater than value of SecondTierCapVar):
Set value of RunningSalaryVar equal to value of SecondTierCapVar


5. Calculate salary if in second tier

(Else If "[[(RunningSalaryVar * (1 + CostOfLivingGRVar)).toFixed(0)]]" is greater than value of FirstTierCapVar):
Set value of RunningSalaryVar equal to "[[ (RunningSalaryVar * (1 + (RunningMeritGRVar / 2) + CostOfLivingGRVar) ).toFixed(0) ]]"


6. Calculate salary if crossing from first to second tier

(Else If "[[(RunningSalaryVar * (1 + RunningMeritGRVar + CostOfLivingGRVar)).toFixed(0)]]" is greater than value of FirstTierCapVar):
Set value of RunningSalaryVar equal to "[[ (FirstTierCapVar + ( ( (RunningSalaryVar * (1 + RunningMeritGRVar + CostOfLivingGRVar) ) - FirstTierCapVar) /2) ).toFixed(0) ]]"


7. Calculate salary if in first tier

(Else If True):
Set value of RunningSalaryVar equal to "[[ (RunningSalaryVar * (1 + RunningMeritGRVar + CostOfLivingGRVar) ).toFixed(0) ]]"

---

So there you have it. To have just one equation, no matter how hairy, would be better than this I think--but this did the trick. Please send me a MeFi Mail if you have any questions. Thanks for the replies!
posted by Mr. Professional at 7:07 PM on April 26, 2015