[Solved] Paye As You Earn Calculation

Maps

New member
Good day.
Trusting you are well.
Can you please help me with a formula that will calculate tax from the attached table.
The expected tax deduction is on line 24 of the spreadsheet. However, the formula is supposed to be linked on the table below the example.
The rebate should be dependant on the age. if you are less than 60 years, the rebate is 8200 and if you are above 60 the rebate is 10900. The formula must be able to select the applicable rebate according to age.
 

Attachments

  • Template_PAYE calculator.xlsx
    13.7 KB · Views: 4
Hello Maps!
I hope you are having a good day. I have solved your problem. Here you can see the screenshot, and along with this, I am sharing the solved workbook with you. Tax Payment Calculator_Solved_Forum.png
Please reply back if you have any further queries about this topic. And browse Exceldemy blogs for solving Excel related problems. Thanks.

Regards,
Osman Goni RIdwan
Exceldemy
 

Attachments

  • Template_PAYE calculator_Solved.xlsx
    13.5 KB · Views: 8
Last edited:
Thank you for the reply. It's much appreciated.
From the formula that you have given, it does not give the same result compared to the one calculated manually.
CASE 1
From the taxable earnings on B6 the tax should be, in the case of someone below the age of 60= B17 and in the case of someone above 60 = B24

CASE 2
From the taxable earnings on F6 the tax should be, in the case of someone below the age of 60= F17 and in the case of someone above 60 = F24.

On the overall tax should be determined by the Tax table by calculating the TAX and deducting the rebate as determined by the person's age.

I am resending the file for ease of reference.
 

Attachments

  • Template_PAYE calculator.xlsx
    13.7 KB · Views: 3
Thank you so much for your response and the formula works fine.
In another scenario where the earnings are over a period of 9 months, the earnings and the rebates are annualised to enable proper calculation of tax. The resultant tax on the annualised aearnings are then divided by 12 and multiplied by the 9 months get get the correct tax.
I have attached the spreadsheet for ease of reference.
The workings start from A50 to E75.
Can we link a formula that will take into account the Tax start period on C51.
 

Attachments

  • Template_PAYE calculator(2).xlsx
    15.7 KB · Views: 3
Thank you for the reply. It's much appreciated.
From the formula that you have given, it does not give the same result compared to the one calculated manually.
CASE 1
From the taxable earnings on B6 the tax should be, in the case of someone below the age of 60= B17 and in the case of someone above 60 = B24

CASE 2
From the taxable earnings on F6 the tax should be, in the case of someone below the age of 60= F17 and in the case of someone above 60 = F24.

On the overall tax should be determined by the Tax table by calculating the TAX and deducting the rebate as determined by the person's age.

I am resending the file for ease of reference.
Hello Maps! I am seeing that the formula is giving the same result compared to the hand calculation that you have shared.
for example 1:
Forum 1.1.png
and for example 2:
Forum 1.2.png
Point to be noted that: I have calculated the age from birth date till the tax end period.
I hope the confusion will be solved.

Regards
Ridwan
ExcelDemy
 

Attachments

  • Template_PAYE calculator_Solved_2.xlsx
    13.4 KB · Views: 1
Last edited:
Thank you so much for your response and the formula works fine.
In another scenario where the earnings are over a period of 9 months, the earnings and the rebates are annualised to enable proper calculation of tax. The resultant tax on the annualised aearnings are then divided by 12 and multiplied by the 9 months get get the correct tax.
I have attached the spreadsheet for ease of reference.
The workings start from A50 to E75.
Can we link a formula that will take into account the Tax start period on C51.
Hello Maps! You are a real Excel enthusiast, I must say. We are here to solve your problem. I have tried to solve what you wanted. I have inserted a formula to connect the working month with the annualized tax. Here is an idea from the gif:
Forum 3.gif
Also, I'm sharing the workbook with you. Have a great day, and reply if you have any further queries.

Regards,
Ridwan
Exceldemy
 

Attachments

  • Template_PAYE calculator_Solved_4.xlsx
    13.6 KB · Views: 2
Hello Maps! I am seeing that the formula is giving the same result compared to the hand calculation that you have shared.
for example 1:
View attachment 331
and for example 2:
View attachment 332
Point to be noted that: I have calculated the age from birth date till the tax end period.
I hope the confusion will be solved.

Regards
Ridwan
ExcelDemy
Good evening.
Thanks so much for your assistance. The formula works correctly and you have been of great help and keep up the spirit of helping others.
 

Online statistics

Members online
0
Guests online
11
Total visitors
11

Forum statistics

Threads
311
Messages
1,378
Members
568
Latest member
WilliamHon
Top