Ø HRA
(House Rent Allowance)
Ø DA
(Dearness Allowance)
Ø PF
(Provident Fund)
|
|
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
I
|
J
|
K
|
|
1
|
PAYROLL
STATEMENT FOR THE MONTH OF THE JANUARY
|
|
2
|
|
|
|
|
40%
|
75%
|
10%
|
|
|
|
|
|
3
|
Emp No
|
Name
|
Grade
|
Basic
|
HRA
|
DA
|
PF
|
Total
|
Loan Amt
|
Loan Amt Paid
|
Net Salary
|
|
4
|
1001
|
Mohan
|
A
|
2200
|
|
|
|
|
|
|
|
|
5
|
1002
|
Sohan
|
B
|
1500
|
|
|
|
|
|
|
|
|
6
|
1003
|
Rohan
|
D
|
3500
|
|
|
|
|
|
|
|
|
7
|
1004
|
Deepak
|
E
|
6500
|
|
|
|
|
|
|
|
|
8
|
1005
|
Sanjay
|
C
|
4500
|
|
|
|
|
|
|
|
|
9
|
1006
|
Irshad
|
D
|
8500
|
|
|
|
|
|
|
|
|
10
|
1007
|
Ramesh
|
A
|
7500
|
|
|
|
|
|
|
|
|
11
|
1008
|
Raju
|
E
|
2500
|
|
|
|
|
|
|
|
|
12
|
1009
|
Jilani
|
E
|
1500
|
|
|
|
|
|
|
|
|
13
|
1010
|
Shahid
|
D
|
6000
|
|
|
|
|
|
|
|
Question
v
Calculate
HRA 40% of Basic.
v
Calculate
DA 70% of Basic.
v
Calculate
PF 10% of Basic.
v
The
TOTAL salary is to be calculated as BASIC+HRA+DA-PF.
v
For
the month, calculate the LOAN as 30 times the total salary for employees
whose total is above Rs. 4500.
v
Calculate
the loan repayment for per month if the loan is to be paid in 15 years
at an interest rate of five percent.
v
Rename
the sheet1 to as January.
v
Insert
a FBONUS column after the DA column. (Contain Festival Bonus Amount).
v
Festival
Bonus will be given @ 25 percent of BASIC only to those employees whose BASIC
is above Rs. 2700.
v
Calculate
the NET SALARY of every employee as TOTAL+F BONUS-LOAN.
v
Print
the above worksheet after it has been complete in one single sheet.
Solution
1)
HRA: - =D4*40%
2)
DA: - =D4*90%
3)
PF: - =D4*10%
4)
Total: - =D4+E4+F4-G4
5)
Loan Amt: - =If (H4>=4500, H4*30)
6)
Loan Amt
Paid: - =PMT (5%/12, 180, I4)
7)
FBONUS: - =If (D4>=2700, D4*35%)
Net Salary: - =Total+
FBONUS -Loan Amt Paid