• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Incentive calculation formula after Tax

Dear Experts,

I need assistance in creating a formula to calculate incentives after tax, considering multiple incentive slabs. The formula should factor in tax deductions applicable to each slab and provide a clear post-tax incentive amount for different levels of performance.

Below are the summary of the data:

1737356412649.png
Incentive slabs
IF NP is upto or less than 110% achievement against budget, incentive will be 3.5%.If achievement is less than 100% achievement, no incentive.​
Anything above 110% achievement against budget, incentive will be 10%​
All incentive calculation will be after tax​

Please let me know if you need any additional details or data to proceed. Your expertise in structuring this calculation would be highly appreciated.

Looking forward to your response.
 

Nandakumar

Post an Excel related Challenge for others to Solve (You must have a solution) Don't post questions here!
Your post has moved to Ask an Excel Question.
 
How did You get those pictures values?
Could You send an Excel-file?
Could You name - which value needs formula?
 
Hi,

Please find attached excel sheet.Formula is required in actuals row which is highlighted in yellow.
 

Attachments

  • Chandoo_21Jan25.xlsx
    10.5 KB · Views: 2
Hi,

Attached is the Excel sheet for the calculation, along with the image pasted below. Here, I have kept the net profit after tax as an amount that was manually calculated. However, it should be derived from the formula after adjusting for the incentive (where the incentive will be paid on the net profit after tax).
1737451675867.png
 

Attachments

  • Chandoo_21Jan25.xlsx
    11 KB · Views: 1
This sample gives same values.
I had to skip 'Your Incentive slabs for actuals & Budget'-writing.
You should use same terms there with 'table'.

Nandakumar

Your ... manually entered ...
Do You mean that Your sample file was something else than valid?
 

Attachments

  • Chandoo_21Jan25.xlsx
    10.7 KB · Views: 3
Last edited:
Hi,

Attached is the Excel sheet for the calculation, along with the image pasted below. Here, I have kept the net profit after tax as an amount that was manually calculated. However, it should be derived from the formula after adjusting for the incentive (where the incentive will be paid on the net profit after tax).
View attachment 89624
Hi,

Thanks for the file. However, as I mentioned, the net profit after tax should not be a manually entered figure. It should be calculated after considering the incentive. The incentive calculation should be structured so that it is based on the pre-tax amount to arrive at the net profit after tax.

I hope I have explained this clearly.
 
Hi @vletm - Thanks for pointing out.

In this file,ideally i will be having Net Profit before incentive,whereas Net profit after tax should be formula driven.Formula will be required for C5,C8.Attached herewith excel sheet for your reference.
 

Attachments

  • Chandoo_21Jan25 (1).xlsx
    10.6 KB · Views: 3

Nandakumar

Why did just change cell C7 to fixed value?
... why You didn't have it in original file?
As well as cell C6 have to fixed too!
Please recheck - what do You would really like to get?
 
@vletm - C7 to be fixed because i will get this value when i work the profitability.
Why i didnt mentioned C6 because i can put the straight formula of C9 X 10% to calculate tax.I hope i cleared.
I am sorry initially i am not clear in my sheet.
 
Back
Top