NIPUN HARYANA: Make salary statement

Finance,Computer,Accounts,Management,Banking,Tax,Costing

8/9/11

Salary Calculator | Excel Salary Slip Calculation Format

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:-
View Excel Wrap Text :-http://www.pijushroy.com /2011/07/wrap-text-line-break-in-microsoft -excel.html

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

PART-2

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

Download This Example Salary File , Excel 2003 Format