2% Salary Increase for the first 4 years then 1.5 for the last

[Stephen_IV]

Good morning,

I am trying to figure out a shorter formula for figuring out what a 2% increase a year would be for the first 4 years and then a 1.5% increase for the final year. I have a value in A1 i.e. 73688 in C1 I have a value from 1 to 5 telling me what the salary increase would be for that year. Thanks in advance.

Based on the salary in A1: i.e. 73688 the following year values is what I would be looking for.
75161.76
76665.00
78198.30
79762.26
80958.69

=IF(C1=1,A1+(A1*2%),IF(C1=2,(A1+(A1*2%)+2%*(A1+(A1*2%))),IF(C1=3,(A1+(A1*2%)+2%*(A1+(A1*2%))+(2%*(A1+(A1*2%)+2%*(A1+(A1*2%))))),IF(C1=4,(A1+(A1*2%)+2%*(A1+(A1*2%))+(2%*(A1+(A1*2%)+2%*(A1+(A1*2%))))+(2%*(A1+(A1*2%)+2%*(A1+(A1*2%))+(2%*(A1+(A1*2%)+2%*(A1+(A1*2%))))))),IF(C1=5,(A1+(A1*2%)+2%*(A1+(A1*2%))+(2%*(A1+(A1*2%)+2%*(A1+(A1*2%))))+(2%*(A1+(A1*2%)+2%*(A1+(A1*2%))+(2%*(A1+(A1*2%)+2%*(A1+(A1*2%))))))+(1.5%*(A1+(A1*2%)+2%*(A1+(A1*2%))+(2%*(A1+(A1*2%)+2%*(A1+(A1*2%))))+(2%*(A1+(A1*2%)+2%*(A1+(A1*2%))+(2%*(A1+(A1*2%)+2%*(A1+(A1*2%))))))))))))))

 

[joeu2004]

I'm not clear about exactly how you want to present the results. Perhaps this will help.

The salary after C1 years of 2% increases is: =A1*(1+2%)^C1

The salary after that and a 1.5% increase is: =A1*(1+2%)^C1 * (1+1.5%)

 

[Fluff]

How about
=A$1*CHOOSE(C1,1.02,1.0404,1.061208,1.08243216,1.098668642)

 

[joeu2004]

The salary after that and a 1.5% increase is: =A1*(1+2%)^C1 * (1+1.5%)

Errata.... For C1=5, the 1.5% increase is =A1*(1+1.5%)*(1+2%)^4.

And if the intent is to have one formula to cover all possible years 1-5, I would suggest:

=IF(C1<5, A1*(1+2%)^C1, A1*(1+1.5%)*(1+2%)^4)

 

[Stephen_IV]

Thanks Fluff and joeu2004. That is exactly what I was after!!!!!! I appreciate all of the help on this!!!!!!!! Thanks again!!!!!

 

[Fluff]

Glad we could help & thanks for the feedback