Increase a base salary every year with different rate increases over multiple years, but write f(x) such that the base year could be different?

[josexcell]

I am trying to project out various cohorts of hires we are planning, several positions per year. I want to project out the growth of those base salaries over multiple years, with varying rate increases, to show their value at any given year in the future. I would like to have a formula that can adjust the increases, depending on the different year a base salary starts.

so, below would be an example: In K8, the first year for a position starting in FY23, it is just base salary times the fringe rate (K5, row 5). In L8, the formula must add an increase to the base of 3% (row 4, col L). And so on. So, each additional year adds another year of salary % increase to the base. But, as we go down the sheet (into future years), the base salary starts on a future year. For example, L12 is just the base salary (D12, first year) times the fringe rate (L5).

Is there a formula that would at adjust for at the least the non-base years?

 

[jtakw]

Hi,

Is this what you mean.

Use K8 formula if you don't mind the 0 (zeros) showing, otherwise, use Q8 formula in K8, formulas copied down and across:

Book3.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1										3%	3%	3%	3%
2									27.80%	28.35%	28.92%	29.50%	30.09%
3
4
5
6
7									FY23	FY24	FY25	FY26	FY27
8	FY23	180000							230040	236941.2	244049.4	251370.9	258912		230040	236941.2	244049.4	251370.9	258912
9	FY23	100000							127800	131634	135583	139650.5	143840		127800	131634	135583	139650.5	143840
10	FY23	50000							63900	65817	67791.51	69825.26	71920.01		63900	65817	67791.51	69825.26	71920.01
11	FY23	90000							115020	118470.6	122024.7	125685.5	129456		115020	118470.6	122024.7	125685.5	129456
12	FY24	180000							0	231030	237960.9	245099.7	252452.7			231030	237960.9	245099.7	252452.7
13	FY24	100000							0	128350	132200.5	136166.5	140251.5			128350	132200.5	136166.5	140251.5
14	FY24	50000							0	64175	66100.25	68083.26	70125.76			64175	66100.25	68083.26	70125.76
15	FY24	50000							0	64175	66100.25	68083.26	70125.76			64175	66100.25	68083.26	70125.76
16	FY24	50000							0	64175	66100.25	68083.26	70125.76			64175	66100.25	68083.26	70125.76
17	FY25	180000							0	0	232056	239017.7	246188.2				232056	239017.7	246188.2
18	FY25	100000							0	0	128920	132787.6	136771.2				128920	132787.6	136771.2
19	FY25	80000							0	0	103136	106230.1	109417				103136	106230.1	109417
20	FY25	80000							0	0	103136	106230.1	109417				103136	106230.1	109417
21	FY26	180000							0	0	0	233100	240093					233100	240093
22	FY26	50000							0	0	0	64750	66692.5					64750	66692.5
23	FY26	50000							0	0	0	64750	66692.5					64750	66692.5
Sheet973

Cell Formulas
Range	Formula
K8:O23	K8	=IF($C8=K$7,$D8*(1+K$2),J8*(1+K$1))
Q8:U23	Q8	=IF(RIGHT($C8,2)+0>RIGHT(K$7,2)+0,"",IF($C8=K$7,$D8*(1+K$2),J8*(1+K$1)))

 

[josexcell]

Hi Jtakw, thank you for the reply.
This does it up to a point, but the value doesn't work because I only need one fringe value per year. (which makes me think it may be easier to separate the two somehow).
But, what I mean is that the base salary + fringe is year one. Then for year two, it is the sume of the base salary + the 2nd yr growth (%increase) all times the fringe rate for year 2. Then year three would be, the sum of the base salary + the 2nd yr growth (%increase) + the 3rd yr growth (%increase) all times the fringe rate of year 3. (so the fringe for years 1 & 2 at this point are ignored) (it's kind of like the fringe rate is the tax for the year on whatever the salary is.)

gratefully,

 

[jtakw]

I am trying to project out various cohorts of hires we are planning, several positions per year. I want to project out the growth of those base salaries over multiple years, with varying rate increases, to show their value at any given year in the future. I would like to have a formula that can adjust the increases, depending on the different year a base salary starts.

so, below would be an example: In K8, the first year for a position starting in FY23, it is just base salary times the fringe rate (K5, row 5). In L8, the formula must add an increase to the base of 3% (row 4, col L). And so on. So, each additional year adds another year of salary % increase to the base. But, as we go down the sheet (into future years), the base salary starts on a future year. For example, L12 is just the base salary (D12, first year) times the fringe rate (L5).
Is there a formula that would at adjust for at the least the non-base years?

That's not how I understand it based on your written description in OP (underlined above), but I think I know what you want now.

Busy right now, will look into it later.

 

[josexcell]

Thanks Jtakw. I think you are correct, I didn't call out the nuance on the fringe behavior.
Thanks again for taking a look at it.
J

 

[jtakw]

Is this giving you the results you're expecting:

Book3.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O
1										3%	3%	3%	3%
2									27.80%	28.35%	28.92%	29.50%	30.09%
3
4
5
6
7									FY23	FY24	FY25	FY26	FY27
8	FY23	180000							230040	237960.9	246188.2	254714.7	263551.4
9	FY23	100000							127800	132200.5	136771.2	141508.1	146417.4
10	FY23	50000							63900	66100.25	68385.61	70754.07	73208.72
11	FY23	90000							115020	118980.5	123094.1	127357.3	131775.7
12	FY24	180000								231030	239017.7	247295.8	255875.1
13	FY24	100000								128350	132787.6	137386.6	142152.9
14	FY24	50000								64175	66393.8	68693.28	71076.43
15	FY24	50000								64175	66393.8	68693.28	71076.43
16	FY24	50000								64175	66393.8	68693.28	71076.43
17	FY25	180000									232056	240093	248422.5
18	FY25	100000									128920	133385	138012.5
19	FY25	80000									103136	106708	110410
20	FY25	80000									103136	106708	110410
21	FY26	180000										233100	241186.9
22	FY26	50000										64750	66996.35
23	FY26	50000										64750	66996.35
Sheet973

Cell Formulas
Range	Formula
K8:O23	K8	=IF(RIGHT($C8,2)+0>RIGHT(K$7,2)+0,"",IF($C8=K$7,$D8*(1+K$2),$D8*(1+K$1)^COUNT($J8:J8)*(1+K$2)))

 

[jtakw]

Looking at it again, my formula above in Post # 6 can be shortened to this:

Book3.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O
1										3%	3%	3%	3%
2									27.80%	28.35%	28.92%	29.50%	30.09%
3
4
5
6
7									FY23	FY24	FY25	FY26	FY27
8	FY23	180000							230040	237960.9	246188.2	254714.7	263551.4
9	FY23	100000							127800	132200.5	136771.2	141508.1	146417.4
10	FY23	50000							63900	66100.25	68385.61	70754.07	73208.72
11	FY23	90000							115020	118980.5	123094.1	127357.3	131775.7
12	FY24	180000								231030	239017.7	247295.8	255875.1
13	FY24	100000								128350	132787.6	137386.6	142152.9
14	FY24	50000								64175	66393.8	68693.28	71076.43
15	FY24	50000								64175	66393.8	68693.28	71076.43
16	FY24	50000								64175	66393.8	68693.28	71076.43
17	FY25	180000									232056	240093	248422.5
18	FY25	100000									128920	133385	138012.5
19	FY25	80000									103136	106708	110410
20	FY25	80000									103136	106708	110410
21	FY26	180000										233100	241186.9
22	FY26	50000										64750	66996.35
23	FY26	50000										64750	66996.35
Sheet973

Cell Formulas
Range	Formula
K8:O23	K8	=IF(RIGHT($C8,2)+0>RIGHT(K$7,2)+0,"",$D8*(1+K$1)^COUNT($J8:J8)*(1+K$2))