Total Other

Employ Years of Total Overtim

Pay / Overtim Gross Income Deducti
ee Experie Hours e/ Net Pay
Hour e Hours Pay Tax ons (If
Name nce Worked Hour
Pay any)

David
20 30 160 20 10
Miller

Peter
2 20 148 10 15
Brain

Crawl
14 30 150 15 13
Peak

Ronald
10 25 135 25 12
Mark

Steve
5 20 150 20 18
Paul

Cristono
3 35 130 15 20
Hard

Harry
4 25 145 10 19
Module
 SOLUTION
Employ
Reg. Bonus Bonus
ee Exp Pay/hr Hours OT/hr OT Hrs OT Pay OT Bonus Gross Pay
Pay (Q4) (Q7)
Name

David
20 30 160 20 10 ₹ 4,800 ₹ 200 ₹ 1,000 ₹ 2,000 ₹0 ₹ 8,000
Miller
Peter
2 20 148 10 15 ₹ 2,960 ₹ 150 ₹0 ₹ 500 ₹0 ₹ 3,610
Brain
Crawl
14 30 150 15 13 ₹ 4,500 ₹ 195 ₹ 0 ₹ 1,000 ₹0 ₹ 5,695
Peak
Ronald
10 25 135 25 12 ₹ 3,375 ₹ 300 ₹ 0 ₹ 1,000 ₹0 ₹ 4,675
Mark
Steve
5 20 150 20 18 ₹ 3,000 ₹ 360 ₹0 ₹ 500 ₹ 500 ₹ 4,360
Paul

Cristono
3 35 130 15 20 ₹ 4,550 ₹ 300 ₹0 ₹ 500 ₹ 500 ₹ 5,850
Hard

Harry
4 25 145 10 19 ₹ 3,625 ₹ 190 ₹0 ₹ 500 ₹ 500 ₹ 4,815
Module
 Excel Formula Example
Component Description
(Assume row 2)

Notes and Questions

Regular Pay + Overtime Pay + RegularPay +

Gross Pay
Bonus (if applicable) OvertimePay + Bonus

10% of Gross Pay (or apply GrossPay * 10% or

Income Tax
slab logic if needed) =GrossPay * 0.1

Custom: e.g., if Overtime IF(OvertimeHours<10

Other Deductions
Hours < 10 → Deduct ₹500 ,500,0)

Gross Pay - Income Tax - GrossPay - IncomeTax

Net Pay
Other Deductions - OtherDeductions

Questions
Q. Sample Excel Formula
Topic Question
No. (Answer)
Calculate Regular
Regular Pay (Pay/Hour ×
1 4800
Pay Total Hours
Worked)
Calculate Overtime
Overtime Pay (Overtime/Hour
2 200
Pay × Total Overtime
Hours)
Add Regular Pay =G2+H2 (Assume G=Reg
3 Total Pay
and Overtime Pay Pay, H=OT Pay)
If Total Hours
IF Worked > 150,
4 1000
Function Bonus = ₹1000, else
₹0
If Pay/Hour > ₹30
OR OR OT/Hour > ₹20,
5
Function then “High Cost”, Normal
else “Normal”
If Experience > 5
AND AND Total Hours >
6
Function 140, then “Eligible”, Eligible
else “Not Eligible”
Bonus: >15 yrs →
Nested IF ₹2000, 6–15 yrs →
7 2000
Bonus ₹1000, ≤5 yrs →
₹500
 If OT Hours > 15,
8 OT Bonus 0
add ₹500, else ₹0

Final Regular Pay + OT =G2+H2+I2 (Assume I =

9
SalaryPay + Bonus (Q4) Bonus)
If Total Pay > ₹5000
Custom AND Exp > 10 →
10 Logic (IF + “Senior Regular Staff
AND) Contributor”; else
→ “Regular Staff”
Add Regular Pay + =G2+H2+I2 (Assume I =
11 Gross Pay
OT Pay + Any Bonus Bonus)

Income Income Tax = 10% =J2*0.1 (Assume J = Gross

12
Tax of Gross Pay Pay)

Other If OT Hours < 10 →

13 Deductio ₹500 deduction, 0
ns else ₹0
Net Pay = Gross Pay
=J2-K2-L2 (J = Gross, K =
14 Net Pay – Income Tax –
Other Deductions Tax, L = Deductions)
Income
Slab: >7000 → 15%,
Tax Slab
15 5001–7000 → 10%, 0
(Nested
≤5000 → 5%
IF)

De
Income duc
Net Pay
Tax tio
n

₹ 800.00 ₹ 0 ###

₹ 361.00 ₹ 0 ###

₹ 569.50 ₹ 0 ###

₹ 467.50 ₹ 0 ###
₹ 436.00 ₹ 0 ###

₹ 585.00 ₹ 0 ###

₹ 481.50 ₹ 0 ###
 Payroll or Salary sheet for XYZ Company
A B C D E F

1 (1*10%) 1*8% 1*12% A+B+C+D D/2

Basic Gross EPF(half

S.No Emp.Name Designation DA(10%) HRA(8%) PF(12%)
Salary Salary of PF)

32500 1500
1 Jawad Ahmad Lecturer 25000 2500 2000 3000
2 Adnan Lecturer 25000
3 Numan professor 50000
4 Asad Ahmad professor 50000
5 Saad Ahmad professor 50000

6 Naveed Ali Lecturer 25000

7 Anwar Ali Assistant Prof 65000

8 Saeed Ahmad Assistant Prof 65000

9 Kashif Khan Assistant Prof 65000

10 Adil ahmad Lecturer 25000

Solution
Emp. Designati Basic DA (10%) HRA (8%) PF (12%)
S.No
Name on Salary (D) (E) (F) (G)

Jawad
1 Lecturer ₹ 25,000 ₹ 2,500 ₹ 2,000 ₹ 3,000
Ahmad
2 Adnan Lecturer ₹ 25,000 ₹ 2,500 ₹ 2,000 ₹ 3,000

3 Numan Professor ₹ 50,000 ₹ 5,000 ₹ 4,000 ₹ 6,000

Asad
4 Professor ₹ 50,000 ₹ 5,000 ₹ 4,000 ₹ 6,000
Ahmad
 Saad
5 Professor ₹ 50,000 ₹ 5,000 ₹ 4,000 ₹ 6,000
Ahmad
Naveed
6 Lecturer ₹ 25,000 ₹ 2,500 ₹ 2,000 ₹ 3,000
Ali
Assistant
7 Anwar Ali ₹ 65,000 ₹ 6,500 ₹ 5,200 ₹ 7,800
Prof
Saeed Assistant
8 ₹ 65,000 ₹ 6,500 ₹ 5,200 ₹ 7,800
Ahmad Prof
Kashif Assistant
9 ₹ 65,000 ₹ 6,500 ₹ 5,200 ₹ 7,800
Khan Prof
Adil
10 Lecturer ₹ 25,000 ₹ 2,500 ₹ 2,000 ₹ 3,000
Ahmad
 G H I
Gross Salary -
F/2 F+G Deductions

LIC(half Deductio Designation Final

Net Salary Bonus Cost Category Tax Deduction Salary
of EPF) n Check
After tax
30250 1000 General Normal
750 2250

olution
Gross EPF (½
LIC (½ EPF) (J) Deduction (K) Net Salary (L)
Salary (H) PF) (I)

₹ 32,500 ₹ 1,500 ₹ 750 ₹ 2,250 ₹ 30,250

₹ 32,500 ₹ 1,500 ₹ 750 ₹ 2,250 ₹ 30,250

₹ 65,000 ₹ 3,000 ₹ 1,500 ₹ 4,500 ₹ 60,500

₹ 65,000 ₹ 3,000 ₹ 1,500 ₹ 4,500 ₹ 60,500

₹ 65,000 ₹ 3,000 ₹ 1,500 ₹ 4,500 ₹ 60,500

₹ 32,500 ₹ 1,500 ₹ 750 ₹ 2,250 ₹ 30,250

₹ 84,500 ₹ 3,900 ₹ 1,950 ₹ 5,850 ₹ 78,650

₹ 84,500 ₹ 3,900 ₹ 1,950 ₹ 5,850 ₹ 78,650

₹ 84,500 ₹ 3,900 ₹ 1,950 ₹ 5,850 ₹ 78,650

₹ 32,500 ₹ 1,500 ₹ 750 ₹ 2,250 ₹ 30,250

Q. No. Topic Question
1 DA (10%) Calculate 10% of Basic Salary

2 HRA (8%) Calculate 8% of Basic Salary

3 PF (12%) Calculate 12% of Basic Salary

4 Gross Salary Add: Basic Salary + DA + HRA + PF

5 EPF (½ of PF) Calculate 50% of PF
6 LIC (½ of EPF) Calculate 50% of EPF
7 Deduction Add EPF and LIC
8 Net Salary Subtract Deduction from Gross Salary
Bonus (IF
9 If Basic Salary > ₹50,000 → ₹2000; Else → ₹1000
function)
Designation
If Designation = "Professor" AND Basic > ₹45000 →
10 Check (AND
"Senior Staff", Else "General Staff"
function)
Cost Category If HRA > ₹5000 OR PF > ₹7000 → "High Cost"; Else
11
(OR function) "Normal"
Tax Deduction
13 Gross > 80K → 15%, >60K → 10%, Else → 5%
(Nested IF)
Final Salary After
14 Net Salary - Tax from Q13
Tax
 Excel Formula (Assume First Row = Row 5)
=D5*10% or =D5*0.10

=D5*8% or =D5*0.08

=D5*12% or =D5*0.12

32500

G5/2
H5/2
H5+I5
J5-K5

IF(D5>50000,2000,1000)

IF(AND(C5="Professor",D5>45000),"Senior Staff","General")

IF(OR(F5>5000,G5>7000),"High Cost","Normal")

IF(J5>80000,J5*15%,IF(J5>60000,J5*10%,J5*5%))

L5-M5 (Assuming M5 is Tax from #REF!)

 EMP-ID NAME BASIC DA HRA PF GROSS/M SALARY/A

S1 Ram ₹ 9,200.00 ₹ 920.00 ₹ 1,840.00 ₹ 920.00 ₹ 11,040.00 ₹ 132,480.00

S2 Amith ₹ 20,400.00
S3 Gopal ₹ 31,000.00
S4 Praful ₹ 37,500.00
S5 Asim ₹ 14,500.00
S6 Lucas ₹ 7,000.00
S7 Ravi ₹ 22,500.00
S8 Kannan ₹ 27,000.00

S9 Kamat ₹ 49,000.00

Solution

EMP-ID NAME BASIC DA (10%) HRA (20%) PF (10%) GROSS/M SALARY/A

S1 Ram ₹ 9,200.00 ₹ 920.00 ₹ 1,840.00 ₹ 920.00 ₹ 11,040.00 ₹ 132,480.00

S2 Amith ₹ 20,400.00 ₹ 2,040.00 ₹ 4,080.00 ₹ 2,040.00 ₹ 24,480.00 ₹ 293,760.00
S3 Gopal ₹ 31,000.00 ₹ 3,100.00 ₹ 6,200.00 ₹ 3,100.00 ₹ 37,200.00 ₹ 446,400.00
S4 Praful ₹ 37,500.00 ₹ 3,750.00 ₹ 7,500.00 ₹ 3,750.00 ₹ 45,000.00 ₹ 540,000.00
S5 Asim ₹ 14,500.00 ₹ 1,450.00 ₹ 2,900.00 ₹ 1,450.00 ₹ 17,400.00 ₹ 208,800.00
S6 Lucas ₹ 7,000.00 ₹ 700.00 ₹ 1,400.00 ₹ 700.00 ₹ 8,400.00 ₹ 100,800.00
S7 Ravi ₹ 22,500.00 ₹ 2,250.00 ₹ 4,500.00 ₹ 2,250.00 ₹ 27,000.00 ₹ 324,000.00
S8 Kannan ₹ 27,000.00 ₹ 2,700.00 ₹ 5,400.00 ₹ 2,700.00 ₹ 32,400.00 ₹ 388,800.00
S9 Kamat ₹ 49,000.00 ₹ 4,900.00 ₹ 9,800.00 ₹ 4,900.00 ₹ 58,800.00 ₹ 705,600.00
 TAXABLE TAX Q. No.

₹ 102,480.00 ₹ 248.00 1

2
2.1
2.2
2.3
2.4
2.5
2.6

3.1
3.2

3.3

3.4

TAXABLE (– TAX (Rounded

30,000) to ₹10)

₹ 102,480.00 ₹ 250.00
₹ 263,760.00 ₹ 22,760.00
₹ 416,400.00 ₹ 53,280.00
₹ 510,000.00 ₹ 81,000.00
₹ 178,800.00 ₹ 7,880.00
₹ 70,800.00 ₹ 0.00
₹ 294,000.00 ₹ 39,800.00
₹ 358,800.00 ₹ 45,160.00
₹ 675,600.00 ₹ 105,120.00
 Expected Action / Excel Formula
Question
(start with =)
Enter data in Excel columns A–J
Create a worksheet as shown above
based on employee details
Create a Tax Calculator using the following formulas: See sub-parts below
DA = 10% of Basic =C2*10% or =C2*0.10
HRA = 20% of Basic =C2*20% or =C2*0.20
PF = 10% of Basic =C2*10% or =C2*0.10
Gross/m = Basic + DA + HRA – PF C2+D2+E2–F2
Salary/A = Gross/m × 12 G2*12
Taxable = Salary/A – 30,000 H2–30000

Compute tax as per slabs: Use IF() or IFS() based on taxable

amount
If Taxable < ₹100000 → Tax = Nil IF(I2<=100000,0,...)
IF(I2<=200000,(I2–
₹100000–₹200000 → 10% on amount above ₹100000
100000)*10%,...)
IF(I2>200000,(I2–200000)*20%
> ₹200000 → 20% on amount above ₹200000 + slab 2 tax
+10000,...)
Tax to be rounded to nearest multiple of 10 CEILING(J2,10)
SUMIFS(K2:K10,C2:C10,">=3500
Total tax where Basic is between ₹35,000 and ₹45,000
0",C2:C10,"<=45000")
Average of Salary/A where Basic is between ₹20,000 and AVERAGEIFS(H2:H10,C2:C10,">=
₹30,000 20000",C2:C10,"<=30000")
Find the 2nd smallest Tax SMALL(K2:K10,2)
COUNTIF(K2:K10,ROUND(K2:K10,
Count how many entries have Tax = Payable Tax (rounded -1)) (Use array or helper column with
value equals original value) IF(K2=ROUND(K2,-1),1,0) then
sum)
 Answer (if applicable)

—
—
—
—
—
—
—

—
—

₹ 134,280

₹ 335,520

₹ 248

1 (only ₹0.00 is exact multiple of 10)