An Interesting Payroll Solution in Excel

Find a payroll solution: For all employees getting a salary less than 20000 units, calculate the House Rent Allowance (HRA) at 130% of salary and for all others getting a salary greater than 20000 allow a HRA of 110% of salary but with a ceiling of 30000.

We can break down the problem like this:

1. If an employee gets a salary less than 20000 units then calculate his house rent allowance at 130% of salary

2. If he gets a salary greater than 20000 then he gets a house rent allowance of 110% of his salary

3. However, if the calculation of the house rent allowance shows a value greater than 30000 units then cap it at 30000

4. Therefore we apply the MINIMUM Excel function and ensure that the employee drawing a salary greater than 20000 units gets the maximum of 30000 units as house rent allowance even if the calculation shows a higher entitlement

5. Our house rent allowance formula now reads as ‘=IF(C3<20000, C3*130%, MIN(C3*110%,30000)’. In plain English our formula says: If the employee gets a salary of less than 20000, then give him the value of the product of salary into 130% else give him the minimum of the following two values: salary x 110% or 30000 units.

Watch the video below to see how this achieved using a combination of the IF and MIN functions:

Further reading:

Excel Payroll Calculator Template Software