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

Stephen_IV

Well-known Member
Joined
Mar 17, 2003
Messages
968
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


Code:
=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%))))))))))))))
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,813
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,659
Office Version
  1. 365
Platform
  1. Windows
How about
=A$1*CHOOSE(C1,1.02,1.0404,1.061208,1.08243216,1.098668642)
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,813
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 17, 2003
Messages
968
Thanks Fluff and joeu2004. That is exactly what I was after!!!!!! I appreciate all of the help on this!!!!!!!! Thanks again!!!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,659
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,465
Members
409,883
Latest member
asharris90
Top