Salary Calculator | Excel Salary Slip Calculation Format

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

Example Salary Calculation & Use of IF Function 


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

Employee Code No = keep it the same as in the picture
Name = keep it the same as in the picture
Designation = keep it same like in the picture
Basic Salary = keep it the same as in the picture

Now calculate some ALLOWANCES based on the 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


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 

Check Our New YouTube Video - SUBSCRIBE, LIKE, and SHARE

Post a Comment


Contact Form


Email *

Message *

Powered by Blogger.
Javascript DisablePlease Enable Javascript To See All Widget