Ms excel Salary calculator format helps you to calculate individuals salary. It also very helpfull to manage a Salary pay Slip. In a firm or small company where the number of employees are upto 500, there is always a need of a salary calculator which can calculate the salary of full month. It's totally Excel Logical Function (If Function) based application which reduce energy & time to calculate salary or prepare salary slip.

Example Salary Calculation & Use of IF Function

PART-1

To calculate salary 1st open Excel 2003 / 2007 file & create a simple database like this:-PART-1

Employee Code No = keep it same like in picture

Name = keep it same like in picture

Designation = keep it same like in picture

Basic Salary = keep it same like in picture

Now calculate some ALLOWANCES based on assumption

House Rent Allowance (HRA) = If 10% of basic salary is higher than Rs 2000, than HRA will Rs 2000. If 10% of basic salary is lower than Rs 2000, then HRA will 10% of Basic Salary

Formula =IF(D2*10%>2000,2000,D2*10%)

write formula in E2 cell & drag formula to E21 cell,

Dearness Allownace (DA) = DA is 25% on Basic Salary

Formula =D2*25%

write formula in F2 cell & drag formula to F21 cell

Medical Allowance (MA) = Executives get MA Rs 1000, Officers get MA Rs 700 & Assistants get MA Rs 500

Formula =IF(C2="Excutives",1000,IF(C2="Officers",700,500))

write formula in G2 cell & drag formula to G21 cell

PART-3

Gross Salary = Total of Basic + HRA + DA + MA

Formula =SUM(D2:G2)

write formula in H2 cell & drag formula to H21 cell

Professional Tax = Upto 5000 = 0, upto 1000 = 60, upto 15000 = 100 & over 15000 = 150

Formula =IF(H2<=5000,0,IF(H2<=10000,60,IF(H2<=15000,110,150)))

write formula in I2 cell & drag formula to I21 cell

Salary Paid Per Month = Gross Salary - Professional Tax

Formula =H2-I2

write formula in J2 cell & drag formula to J21 cell

Annual Salary = Salary Paid Per Month * 12

Formula =J2*12

write formula in K2 cell & drag formula to K21 cell

Income Tax = Upto 100000 = 0, then 50000 = 10%, then 100000 = 20%, over 250000 = 30%

Formula =IF(K2<=100000,0,IF(K2<=150000,(J2-100000)*10%,IF(K2<=250000,(K2-150000)*20%+5000,(K2-250000)*30%+25000)))

write formula in L2 cell & drag formula to L21 cell

Net Salary Payable = Annual salary - income tax

Formula =M2-M2

## Post a Comment