Hi All,
Can someone help me with a IF formula that will make the below tax calculation based on whatever taxable income is made?
I would also like the formula to calculate the taxes for each tax percentage line and not cumulative.
| Tax payable on income up to $1,000,000 @ | 5.5% | |||||
| Tax payable on income greater than $1,000,000 to $20,000,000 @ | 3.0% | |||||
| Tax payable on income greater than $20,000,000 to $30,000,000 @ | 2.5% | |||||
| Tax payable on income exceeding $30,000,000 @ | 1.0% | |||||
Thanks.
Hi Candid
You should read about nested IF statements
=IF(A2<=1000000,0.055,IF(A2<=20000000,0.03,IF(A2<=30000000,0.025,0.01)))
Workbook attached with example.
Regards
Phil
Hi Phil,
I did not explain myself well as to what I was requesting. I wanted the formula to calculate the taxes from the rates by the tax bands or ranges so my first formula should provide the tax calculation of $55,000 once the income reaches $1M or surpass and the other percentages would kick in once the income surpasses each range.
Thanks also for you response as I will also use that formula as well.
Regards,
Candid
Hi Candid,
This is why it's always best to include a workbook and several examples of the desired outcomes.
So to make sure we are clear, do you want to calculate a single figure for the total tax for a given income based on the tax bands e.g. for an income of $24,000,000 the workbook will show a single figure of $725,000?
Or do you want the tax for each band listed on separate rows e.g.
| Tax payable on income up to $1,000,000 @ 5.5% | 55,000 |
| Tax payable on income greater than $1,000,000 to $20,000,000 @ 3% | 570,000 |
| Tax payable on income greater than $20,000,000 to $30,000,000 @ 2.5% | 100,000 |
| Tax payable on income exceeding $30,000,000 @ 1% | 0 |
Regards
Phil
Hi Phil,
Yes, the tax for each band listed on separate rows like how you have as your eg.
Thanks,
Candid
Hi Candid,
try this attachment.
The values for the bands (1M, 20M etc) are hardcoded into the formulae. So you should replace these with cell references if these values could change.
Regards
Phil
Hi Phil,
Exactly what I wanted.
Thanks for your assistance.
Regards,
Candid
Hi Phil,
I just tried to replace the hard coded numbers with cell references and the calculations did not work. I reference it to the A6 but I must be doing something wrong.
Do you want to update the worksheet with this example as well?
Hope i am not asking too much.
Thanks.
No worries - see attached.
P
Hi Phil,
Thank you for your assistance to this novice, it was greatly appreciated.
Regards,
Candid
You're welcome.
