Excel formula in the calculation of month-wise annual basic pay

[baidya91]

Suppose, an employee has a basic pay of Rs. 72600/- in the month of March, 2021.
He will get his annual increment in the month of July, 2021.
He will get a promotion increment in the month of November, 2021 on 30-10-2021 in the following way.:-

MONTH	BASIC PAY	Remark
Mar-21​	72600​
Apr-21​	72600​
May-21​	72600​
Jun-21​	72600​
Jul-21​	74800​
Aug-21​	74800​
Sep-21​	74800​
Oct-21​	74800​
Nov-21​	74873​	As he will receive promotional benefit w.e.f. 30-11-21, he will enjoy only 1 day's benefit in that month
Dec-21​	77000​
Jan-22​	77000​
Feb-22​	77000​

Now which single excel formula shall I use to prepare his month-wise annual basic pay with or without all the increments.
I want to mean a single excel formula (may be nested) that applies to all if they get one or two increments as mentioned above or not a single one of the increments.
Please help. Thanks in advance.

 

[Dave Patton]

I added Basic3.

T202105a.xlsm
	A	B	C	D	E	F
1		72,600	Option required	Basic3
2	MONTH	Pay
3	1-Mar-21	72,600		Basic1	1	1.06060606
4	1-Apr-21	72,600			3	1.00000000
5	1-May-21	72,600			7	1.03030303
6	1-Jun-21	72,600			11	1.03130854
7	1-Jul-21	74,800			12	1.06060606
8	1-Aug-21	74,800
9	1-Sep-21	74,800		Basic2		1.0
10	1-Oct-21	74,800
11	1-Nov-21	74,800		Basic3	1	1.03030303
12	1-Dec-21	74,800			3	1.00000000
13	1-Jan-22	74,800			7	1.03030303
14	1-Feb-22	74,800
15
3a

Cell Formulas
Range	Formula
B3:B14	B3	=CHOOSE(MATCH($D$1,{"Basic1","Basic2","Basic3"},0),LOOKUP(MONTH(A3),rBasic1)*$B$1,$B$1*1,LOOKUP(MONTH(A3),rBasic3)*$B$1)

Named Ranges
Name	Refers To	Cells
'3a'!rBasic1	='3a'!$E$3:$F$7	B3:B14
rBasic3	='3a'!$E$11:$F$13	B3:B14

 

[baidya91]

Please pardon me if I am wrong. I think I have not been able to make you understand what I mean.

Suppose, an employee has a basic pay of Rs. 72600/- in the month of March, 2021.
He will get his annual increment in the month of July, 2021.
He will get a promotion increment in the month of November, 2021 on 30-11-2021 in the following way:-

A4	B4	C4	D4	E4
MONTH	BASIC PAY	REMARK	Date of Promotion
	72600​		30-11-21
Mar-21​	72600​		DAYS	MONTH
Apr-21​	72600​		30	11
May-21​	72600​		30	11
Jun-21​	74800​		TOTAL NO OF DAYS OF THE MONTH	30
Jul-21​	74800​	ANNUAL INCREMENT	EFFECTIVE DAY (S) OF PROMOTION	1
Aug-21​	74800​		BASIC PAY OF NOVEMBER	BASIC OF OCTOBER + 1 DAY'S BASIC ON INCREMENT OF OCTOBER'S BASIC
Sep-21​	74800​		INCREMENT ON PROMOTION	74873
Oct-21​	74800​		2200
Nov-21​	74873​
Dec-21​	77000​
Jan-22​	77000​
Feb-22​	77000​

In the example above,

1. B9=B8=B7=B6

2. B10=B6+ROUND(B6*3%,-2) (CALCULATION OF INCREMENTED BASIC AFTER ANNUAL INCREMENT)

INCREMENT = ROUND(LAST BASIC*3%, -2)

3. B11=B10+ROUND(B10*3%M-2)/30*1 WHERE 30 = NO OF DAYS IN THE MONTH OF NOVEMBER AND 1= NO OF EFFECTIVE DAY(S) OF PROMOTIONAL INCREMENT

4. D8=TEXT (D6,”DD”), D9= VALUE (D8), D14=ROUND(B14*3%,-2)

5. E8=TEXT (D6, “MM”), E9 = VALUE (E8), E10 = DAY(EOMONTH(A15,0)), E11=(E10-D9)+1

6. E13 = B14+ROUND(D14/E10*E11,0)

7. B15 = E13

8. B16 = B17=B18= $B$14+$D$14

In another case, an employee has a basic pay of Rs. 50500/- in the month of March, 2021.

He will get his annual increment in the month of July, 2021.

He will get a promotion increment in the month of December, 2021 on 13-12-2021 in the following way:-

A4	B4	C4	D4	E4
MONTH	BASIC PAY	REMARK	Date of Promotion
	50500​		13-12-21
Mar-21​	50500​		DAYS	MONTH
Apr-21​	50500​		13	12
May-21​	50500​		13	12
Jun-21​	52000​		TOTAL NO OF DAYS OF THE MONTH	30
Jul-21​	52000​	ANNUAL INCREMENT	EFFECTIVE DAY (S) OF PROMOTION	18
Aug-21​	52000​		BASIC PAY OF DECEEMBER	BASIC OF NOVEMBER + 18 DAYS' BASIC ON INCREMENT OF NOVEMBER'S BASIC
Sep-21​	52000​		INCREMENT ON PROMOTION	52960
Oct-21​	52000​		1600
Nov-21​	52000​
Dec-21​	52960​
Jan-22​	53600​
Feb-22​	53600​

Please have time to see my post to help me. Thanks in advance.

 

[Dave Patton]

What is your actual question?

If you prefer, you can work out each calculation as you illustrated.

or

If you have some consistency or multiple employees that you escalate salaries based on stated factors, you can work out the escalation factors and use the choose formula or an alternative.

Alternatives that come to mind are using Indirect or an Array formula; these alternatives would also work by applying the arithmetic factor based on the escalation information.

I will post examples of the two alternatives that I just mentioned but they are similar to what I already posted.

Version2 uses Indirect Version 3 is using an Array Formula to record the monthly escalated salaries based on the derived factor.

T202105a.xlsm
	A	B	C	D	E	F
1	Initial Amount-->	100,000	Option required -->	Basic1
2	MONTH		Version 2
3	1-Mar-21		100,000.00	Basic1	1	1.06060606
4	1-Apr-21		100,000.00		3	1.00000000
5	1-May-21		100,000.00		7	1.03030303
6	1-Jun-21		100,000.00		11	1.03130854
7	1-Jul-21		103,030.30		12	1.06060606
8	1-Aug-21		103,030.30
9	1-Sep-21		103,030.30	Basic2		1.0
10	1-Oct-21		103,030.30
11	1-Nov-21		103,130.85	Basic3	1	1.03030303
12	1-Dec-21		106,060.61		3	1.00000000
13	1-Jan-22		106,060.61		7	1.03030303
14	1-Feb-22		106,060.61
15				Basic4	1	1.06060606
16					3	1.00000000
17					7	1.06060606
18
3a

Cell Formulas
Range	Formula
C3:C14	C3	=LOOKUP(MONTH(A3),INDIRECT($D$1))*$B$1

Array Formula using the factor for each month

T202105a.xlsm
	B	C	D	E	F	G	H
25	90,000.00				rFactor1	rFactor2	rFactor3
26		90,000.00			1	1	1.00000000
27		90,000.00			1	1	1.00000000
28		90,000.00			1	1	1.00000000
29		90,000.00			1	1	1.00000000
30		92,727.27			1.030303	1	1.03030303
31		92,727.27			1.030303	1	1.03030303
32		92,727.27			1.030303	1	1.03030303
33		92,727.27			1.030303	1	1.03030303
34		92,727.27			1.0313085	1	1.03030303
35		92,727.27			1.0606061	1	1.03030303
36		92,727.27			1.0606061	1	1.03030303
37		92,727.27			1.0606061	1	1.03030303
3a

Cell Formulas
Range	Formula
C26:C37	C26	=rFactors3*B25
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
rFactors3	='3a'!$H$26:$H$37	C26:C37