[Solved] Paye As You Earn Calculation

[Maps]

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.

 

[Ridwan]

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.
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

 

[Maps]

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.

 

[reza_akbar]

try this

 

[Maps]

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.

 

[reza_akbar]

hey for the one that work less then 12 month, its has separate sheets for it or not?

 

[Ridwan]

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:

and for example 2:

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

 

[Ridwan]

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:

Also, I'm sharing the workbook with you. Have a great day, and reply if you have any further queries.

Regards,
Ridwan
Exceldemy

 

[Maps]

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.

 

[shamimarita]

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.

Dear Maps,

You are most welcome.