Payroll Fundamentals in Nigeria | How to Compute PAYE in Microsoft Excel

This article on payroll fundamentals focusing on how to compute PAYE in Microsoft excel is based on the provisions of the recently enacted Finance Act 2020.

If you are an HR personnel working in a small company in Nigeria where you still use excel to prepare payroll, then count yourself lucky as this article on how to compute PAYE in Microsoft excel is specifically written for you. Also, if you are new to the world of accounting, please try to pick up as much excel skills as you can as that is one easiest way to grow your accounting soft skills. This is a typical example of how accountants use technology.

Please kindly download the attached excel in this article and follow along. I will be using two (2) formula methods to calculate PAYE of staff in Nigeria (Using the Vlookup function and IF Function). You don’t need a tax table if you are using the IF function method, just that the formula is ridiculously long. Vlookup makes use of a tax table but the formula is shorter and easy to maintain.

Overview of PAYE in Nigeria

PAYE stands for Pay-As-You-Earn. It is a progressive system of tax where the tax burden suffered by a tax payer increases as income increases. The Nigeria’s PAYE is capped at 24%.

The first N300,000 (Three Hundred Thousand Naira) is taxed at 7%, the next N300,000 (Three Hundred Thousand Naira) is taxed at 11%, the next N500,000 (Five Hundred Thousand Naira) is taxed at 15%, the next N500,000 (Five Hundred Thousand Naira) is taxed at 19%, the next N1,600,000 (One Million Six Hundred Thousand Naira) is taxed at 21%, while anything above N3,200,000 (Three Million Two Hundred Thousand Naira) is taxed at 24%.

The above rates are applied on taxable income progressively as described above. See below for how to calculate it.

How to compute PAYE in Microsoft excel in Nigeria.

Step 1. Establish total income of an employee

The first thing to do when computing the PAYE of an employee in Nigeria is to calculate the (total remuneration) total annual package due to the person. This typically includes gross annual salary and leave allowance. Depending on the company in question, annual leave is usually the monthly Basic salary of an individual.

Step 2. Calculate the TCRA (Total Consolidated Relief Allowance) under the 2020 Finance Act

The next thing to do after establishing the total income is to calculate the TCRA. This is calculated thus; N200,000 (Two Hundred Thousand Naira) + 20% of total annual income less employee pension. Pension was not deducted from total income when calculating the TCRA, but the 2020 Finance Act now requires this. This amendment eliminates the ‘double benefits’ that people have been hitherto enjoying.

Step 3. Determine the Taxable income of an individual under the 2020 Finance Act

The taxable income is arrived at by deducting the TCRA, Pension, and NHF (Total Income – TCRA- Pension-NHF) from the total income established in step 1.

Step 4. Build a Tax Table.

The reason for building this tax table is for you to be able to apply Vlookup function to it. See the tax table below: (I have also attached this excel sheet titled ‘AccountantNextDoorPayrollTemplateBlog‘ just for you to tweak to your organization’s need – note that I simplified the contents so feel free to add as many content as possible).

Vlookup Value	Over….	But not over….	Rate	Tax from Previous
₦0.00	₦0.00	₦300,000.00	7%	₦0.00
₦300,000.01	₦300,000.00	₦600,000.00	11%	₦21,000.00
₦600,000.01	₦600,000.00	₦1,100,000.00	15%	₦54,000.00
₦1,100,000.01	₦1,100,000.00	₦1,600,000.00	19%	₦129,000.00
₦1,600,000.01	₦1,600,000.00	₦3,200,000.00	21%	₦224,000.00
₦3,200,000.01	₦3,200,000.00		24%	₦560,000.00

You are now set for the fun part of calculating progressive tax in Nigeria using excel formula.

Step 5. Apply the Microsoft excel formula

Calculating PAYE in Nigeria using Vlookup

1. Type equal to sign (=) to activate the excel cell where you want to calculate the PAYE
2. Type vlookup and hit the “Tab” button on tour keyboard.
3. Click on the Cell where the Taxable Income (TI) is
4. Highlight the tax table from the top left to the bottom right (see screen shot below on Table 2)
5. Hit (F4) on your keyboard to absolutely lock the cells. You will know that this has happened when you see $signs added to the text.
6. Put comma sign (,)
7. Close the bracket using ‘)’
8. Type plus sign (+)
9. Open a new bracket ‘(‘
10. Click on the Cell where the Taxable Income (TI) is
11. Type minus sign (-)
12. Type vlookup
13. Hit the ‘Tab’
14. Click on the Cell where the Taxable Income (TI) is
15. Put comma sign (,)
16. Repeat step 4 above
17. Put comma sign (,)
18. Type 2
19. Close the bracket using ‘)’
20. Repeat step 19
21. Type multiplication sign (*)
22. Type vlookup
23. Hit ‘Tab’
24. Click on the Cell where the Taxable Income (TI) is
25. Type comma sign (‘)
26. Type 4
27. Close the bracket using ‘)’
28. Drag down to cover range (note that the figure you get is the annual tax, divide by 12 to get the monthly PAYE)

Vlookup formula to calculate progressive tax like Nigeria’s PAYE

=VLOOKUP(AS6,$AP$253:$AT$258,5)+(AS6-VLOOKUP(AS6,$AP$253:$AT$258,2))*VLOOKUP(AS6,$AP$253:$AT$258,4)

Voala!! Your PAYE is done using Vlookup function. I know what you are thinking, but trust me you will get used to it. I do all of this in less than 2 minutes when pre auditing payroll with staff strength of over 2000 staff members. Make sure you download the attached excel sheet and have a look at the formula.

Calculating PAYE in Nigeria using IF function

1. Type equals to sign (=)
2. Enter the following formula

IF function formula to calculate progressive tax like Nigeria’s PAYE

=IF(Taxable Income<=300000,7%* Taxable Income,IF(AND(Taxable Income >300000, Taxable Income <=600000),21000+( Taxable Income -300000)*11%,IF(AND(Taxable Income >600000, Taxable Income <=1100000),54000+( Taxable Income -600000)*15%,IF(AND(Taxable Income >1100000, Taxable Income <=1600000),129000+( Taxable Income -1100000)*19%,IF(AND(Taxable Income >1600000, Taxable Income <=3200000),224000+( Taxable Income -1600000)*21%,560000+( Taxable Income -3200000)*24%)))))

PLEASE substitute the ‘words’ Taxable Income with the Cell where the Taxable Income is in your worksheet as highlighted in red colors below – I used BF6 as a place holder.

=IF(BF6<=300000,7%*BF6,IF(AND(BF6>300000,BF6<=600000),21000+(BF6-300000)*11%,IF(AND(BF6>600000,BF6<=1100000),54000+(BF6-600000)*15%,IF(AND(BF6>1100000,BF6<=1600000),129000+(BF6-1100000)*19%,IF(AND(BF6>1600000,BF6<=3200000),224000+(BF6-1600000)*21%,560000+(BF6-3200000)*24%)))))

Edit (12th August 2024): HR Cottage, an HRTech startup that we partner with has magnanimously released a component of its coveted Payroll Package that helps you calculate Nigerian paye for free. Head over to Free PAYE Calculator built by Nigerians for Nigerians to try it out. No download is required, all browser based and nothing is saved. Reach out to them if you would like to have access to the entire package, otherwise, you will be fine just using the free web version.

Conclusion

I hope you enjoyed this article on how to calculate PAYE in Nigeria using both Vlookup and IF function? Please kindly share with anyone you know who may benefit from this. Please download the attached PAYE tax template if you haven’t done so already. Note that I deliberately kept the template simple and the formulas are not hidden.

This is to encourage learning and transfer of knowledge. Do not forget to leave your questions in the comment section or use the contact form if you need further guidance. Thank you