Advertisment

Advance Formulae in Excel

author-image
PCQ Bureau
New Update

Computing your tax liability manually can be daunting. Or, you could use MS Excel and save time and effort. For those conversant with Excel, the conventional IF statement is not useful here because it solves an ‘or’ condition, ie, If X=Y, then Z, else A. Since multiple tax rates apply to different slabs of the same income, the IF statement has to be coupled with other features in Excel to work around the problem. 

Advertisment

You can solve a complex problem of this nature by using a powerful Excel feature called array formula.

Shareholding of companies Beta and Theta

What is an array formula?



An array formula can perform multiple calculations and then return either a single result or multiple results. Array formula act on two or more sets of values known as array arguments. 

Advertisment

Consider the example given in the table of two companies, Beta and Theta. Here, the cell D8 computes the value of shareholding in these two companies.

Shareholding of companies Beta and Theta



This formula is entered by pressing Ctrl+Shift+Enter in cell D8 . It performs multiple operations of multiplication (C5*D5 & C6*D6) and addition <(C5*D5) + (C6*D6)>.

Tax computation 



Now let’s tackle the bigger problem of computing the tax liability on a person’s income. According to Table 2, the data in range B4:C8 depicts that for the first Rs 50,000 of income, the rate is 0%, for the next Rs 100,000 (ie, Rs 150,000-50,000 of income), the rate is 10% and so on. Your income is entered in cell D10 and the tax expense is returned in cell D12 (which houses the formula).

Advertisment

Income tax computation using Excel

To arrive at the formula in cell D10, you need to perform the following three tasks.

  • Categorize the income into various income slabs(akin to the shares range C5:C6 in table 1 above) (Task 1)
  • Multiply the income falling in a particular slab with the relevant tax rate (akin to the price range D5:D6 in table 1) to obtain the tax liability of each slab (Task

    2)
  • Sum the tax liabilities of the individual slabs to get the total tax liability (Task 3)
Advertisment

The step-by-step computation is explained below. 

If your taxable income is greater than the lower limit of a particular slab, then if the difference between the upper and lower limit of the current slab is greater than the taxable income, the income falling in the particular slab is the excess of total income over the lower limit of the current slab. For example, if the total income is Rs 175,000, then the income in slab 0-50,000 would be Rs 50,000, in slab Rs 50,000-150,000, it would be Rs 100,000 and in slab Rs 150,000-320,000, it would be Rs 25,000. This is achieved by the following XL arguments.






IF($D$10>B5:B9,IF(B6:B10-B5:B9>$D$10,$D$10-B5:B9...




If total income is lesser than the lower income of a particular slab, then the income of that particular slab is 0. For example, if

income is

Rs 175,000 then income in the slab Rs 320,000- Rs 600,000 would be nil. The arguments in Excel for this are: 



IF($D$10

If the difference between the lower and upper limits of a particular slab is positive (ie, the slab is not the last slab), then if the

income of a particular slab is greater than the excess of income over the lower limit of a particular slab, the income of the slab

is the excess of income over the lower limit of a particular slab. If not so, then the income of the particular slab is

the difference in upper and lower incomes of a particular slab. For example, if the income is >=

Rs 50,000, then the income in the first slab is Rs 50,000, else it is (income — 0). The argument for this is:

Advertisment

IF(B5:B9-B4:B8>0,IF(B5:B9-B4:B8>$D$10-B4:B8,$D$10-B4:B8,B5:B9-B4:B8)...



If the difference between the lower and upper limits of a particular slab is negative (ie, the slab is the last slab), then the income

for the last slab is the difference between the total income and the lower limit of the last slab. For example, if the income is Rs

780,000, the income in the last slab would be Rs 180,000. The argument in Excel is as follows:

IF(B5:B9-B4:B8<0,$D$10-B4:B8...



Hence the consolidated IF statement in cell C12 becomes (after combining all the arguments mentioned above):

IF($D$10$D$10,$D$10-B4:B8,IF(B5:B9-B4:B8>0,IF(B5:B9-B4:B8>$D$10-B4:B8,$D$10-B4:B8,B5:B9-B4:B8),$D$10-B4:B8)))

The result of the aforementioned formula is that the income gets classified in the various slabs. This is Task 1. 

The income tax expense is arrived at by multiplying the tax rates with the income of the respective slabs.

Advertisment

SUM(IF($D$10$D$10,$D$10-B4:B8,IF(B5:B9-B4:B8>0,IF(B5:B9-B4:B8>$D$10-B4:B8,$D$10-B4:B8,B5:B9-B4:B8),$D$10-B4:B8)))*(C4:C8))



Multiplication with the range C4:C8. This is Task 2.

Task 3 is accomplished by pressing Ctr+Shift+Enter. 

Array formula in action



Enter the income slabs in the range B4:B8 and tax slabs in the range C4:C8. Now enter the taxable income in cell D10. The

tax liability would automatically appear in cell D12.

The income slabs and tax rates may be adopted as per the actual provisions stipulated in the Income Tax Act, 1961. 

Ashish Mathur

Advertisment