Business and Taxation go hand in hand. However, taxation calculations are most of the time tedious and if you need to make a small change you might have to punch millions of calculator keys again to get the tax liability calculated. But we have a rescue pack for you in the name of Excel! Many accountants have been using excel power to calculate income tax, sales tax or other different types of taxes very easily using excel in the blink of an eye. And if taxation rules have not changed then you can use the old excel file for next period.
In this tutorial we will learn a very basic tax calculator. The basic idea is the get the instructions from relevant legislation and apply them using different excel formula and cell referencing and other techniques. Although tax calculation process is done in several steps. One of the common steps is application of tax rates. Many countries follow incremental taxation system i.e. tax rate increases with the increase in the amount involved like sales or income etc. So for each range or slab of income or turnover different rates are provided as follows:
| S.No. | Taxable Income | Rate of tax |
|---|---|---|
| (1) | (2) | (3) |
| 1. | Where taxable income does not exceed Rs.400,000 | 0% |
| 2. | Where the taxable income exceeds Rs.400,000 but does not exceed Rs.750,000 | 10% of the amount exceeding Rs.400,000 |
| 3. | Where the taxable income exceeds Rs.750,000 but does not exceed Rs.1,500,000 | Rs.35,000+15% of the amount exceeding Rs. 750,000 |
| 4. | Where the taxable income exceeds Rs.1,500,000 but does not exceed Rs.2,500,000 | Rs.147,500+20% of the amount exceeding Rs.1,500,000. |
| 5. | Where the taxable income exceeds Rs.2,500,000 | Rs.347,500+25% of the amount exceeding Rs.2,500,000 |
Source: Pakistan’s Income Tax Ordinance
In the above table you can see we have five different slabs that works as:
if your income is up to 400,000
then zero tax liability
otherwise check if your income exceeds 400,000 but is less than 750,000 than tax rate of 10% on amount exceeding 400,000 will be applied
And so on. You can see it is basically a combination of IFs and ELSEs statements. And believe it or not in Excel we have a function that works exactly the same way i.e. IF function. Combination of IF functions is called Nested IF statement. Using this function we can calculate the tax liability for a given taxable income. Although there are several methods that we can use however, IF function is easiest to understand after the explanation given 🙂
Let’s tango Taxation
Step 1: Punch in the slab information in excel. This will act as the instruction data set for excel on which function will work.
Step 2: Mark a field in which you will enter the taxable income for which you require tax liability to be calculated.
>snap
Put a value of taxable income in the cell you desire. In my case it is C10
Now in the cell you think best put in the following formula:
=IF(C10<A5,0,IF(C10<A6,(C10-B4)*0.1,IF(C10<A7,(C10-B5)*0.15+35000,IF(C10<A8,(C10-B6)*0.2+147500,(C10-B7)*0.25+347500))))
Cell references are according to my own worksheet. So you might have to change them if you have the data at a different location within worksheet.
Hit enter and DONE! Your tax liability is calculated 🙂
So we understood that it was a matter of constructing the nested IF statement. If we achieve it then its very easy for subsequent calculations. You can change the value of taxable income and your tax liability will be calculated for you on run time basis. Following is an interactive excel worksheet. You can change taxable income value to calculate the tax accordingly. Happy Taxing!
Not to Forget!
You can download this file by clicking the excel button at the bottom of interactive window.
Firstly, appreciating the posts ,
but
could you please try to have the titles be less misleading –
The calculation shown appears to be basic INCOME TAX calculator.
It does not address income taxed in other processes such as dividends and capital gains, or even disbursements that reduce the income tax liability.
Better Process for the calculations you are doing
rate 1 x max(0,min(upper bound of rate, and income less any tax free+ reduction allowance))
+
rate 2 x max(0,min(upper bound of rate, and income less max of previous band))
+
rate 3 x max(0,min(upper bound of rate, and income less max of previous band))
+
rate 4 x max(0,min(upper bound of rate, and income less max of previous band))
+
rate 5 x max(0,min(upper bound of rate, and income less max of previous band))
As in a test that needs a calculation to be above 0 is more efficiently built into the calculation itself to generate the 0 as the result.
Microsoft seems to have finally recognised that and will ( hopefully) be making the LET() function available to most users of excel 365.
So – considering this basic tiered structure :
Income in C2
Rates in A2 on
limit of rate in B2 onwards with Upper-limit of taxing process in last entry
=MAX(0,MIN($C$2,B2)*A2)+MAX(0,MIN($C$2,B3)*A3)+MAX(0,MIN($C$2,B4)*A4)+MAX(0,MIN($C$2,B5)*A5)+MAX(0,MIN($C$2,B6)*A6)+MAX(0,MIN($C$2,B7)*A7)+MAX(0,MIN($C$2,B8)*A8)+MAX(0,MIN($C$2,B9)*A9)
And you can reduce the calculation to just the entries needed
You can also – simplistically calculate the C2 entry to be the amount to be considered after allowances etc.
OK – where the taxation structure includes additional considerations at the steps, there would be adjustments needed o that basic process.
Mr Hassan…………must appreciate the work you are doing
wander full cores