In this lesson, we will learn how to create "Tax Calculator" in Microsoft Excel:
In Microsoft Excel, you can easily create tax calculator to compute your monthly or yearly tax liability for salaried individuals by using incremental taxation system (tax rate increases with the increase in amount of income) or periodic tax slab and Excel IF functions.
Below is the new tax slab for salaried individuals that we will use in this example:
To create Tax calculator, you need to take below mentioned steps:
Step 1. First step is to prepare the format of tax calculator as mentioned below:
Step 2. Now insert below mentioned formula in cell "D21" to calculate taxable income.
Step 3. To calculate Monthly Tax Liability, Please insert below mentioned formula in cell "D20" to divide Yearly Tax Liability into 12:
Step 4. To calculate Yearly Taxable Income, Please insert below mentioned formula in cell "D19" to multiply Monthly Taxable Income by 12:
Step 5. You can also insert comment for your assistance, as mentioned in below snapshot:
Now you can see that, Tax Calculator has been created as mentioned below:
Now insert your Monthly Taxable Income in Cell "D18" to calculate your Monthly and Yearly Tax Liability as mentioned below:
If your Yearly Income is non-taxable, then your calculated tax liability will be always equal to zero as mentioned in below snapshot:
We hope that you would like this lesson.
=IF(D19<B6,0,IF(D19<B7,(D19-C5)*0.05,IF(D19<B8,(D19-C6)*0.1+30000,IF(D19<B9,(D19-C7)*0.15+90000,IF(D19<B10,(D19-C8)*0.175+195000,IF(D19<B11,(D19-C9)*0.2+370000,IF(D19<B12,(D19-C10)*0.225+670000,IF(D19<B13,(D19-C11)*0.25+1345000,IF(D19<B14,(D19-C12)*0.275+2345000,IF(D19<B15,(D19-C13)*0.3+7295000,IF(D19<B16,(D19-C14)*0.325+13295000,(D19-C15)*0.35+21420000)))))))))))
Step 3. To calculate Monthly Tax Liability, Please insert below mentioned formula in cell "D20" to divide Yearly Tax Liability into 12:
=+D21/12
Step 4. To calculate Yearly Taxable Income, Please insert below mentioned formula in cell "D19" to multiply Monthly Taxable Income by 12:
=+D18*12
Step 5. You can also insert comment for your assistance, as mentioned in below snapshot:
Now you can see that, Tax Calculator has been created as mentioned below:
Now insert your Monthly Taxable Income in Cell "D18" to calculate your Monthly and Yearly Tax Liability as mentioned below:
If your Yearly Income is non-taxable, then your calculated tax liability will be always equal to zero as mentioned in below snapshot:
We hope that you would like this lesson.
---Thank You---
1 Comments
thank you for making this!
ReplyDelete