TAXBAND(income, taxrates, taxbands)

The TAXBAND function calculates the total tax payable on a given income subject to a series of banded tax rates. taxrates and taxbands are both lists and must be of equal length.

The function is calculated summing the tax rate and band product over the number of items in each list:

image\ebx_524920047.gif

where n is the number of items in each list and taxband[n+1] is defined as being equal to income.

Example 1: suppose you earned 28,800 in the tax year ending 2005 and had a tax-free personal allowance of 4,745. The tax rates for that year were published as follows:

10% 0 – 2,020
22% 2,021 - 31,400
40% Over 31,400

These rates apply to taxable income (income above the personal allowance). You could construct the spreadsheet to hold these values as follows:

 

A

B

1

10%

0

2

22%

2,020

3

40%

31,400

You would use the TAXBAND function to calculate tax due as follows:

TAXBAND(28800 - 4745, A1..A3, B1..B3) = 5049.70

Example 2: suppose a proportion of your income of 28,800 was taxed at source to contribute towards health care. The tax was at a flat rate of 7.3% on all income over 6,640 up to a ceiling of 22,880. The spreadsheet could look like this:

 

A

B

1

7.3%

6,640

2

0%

22,880

And then the tax can be calculated as follows:

TAXBAND(28800, A1..A2, B1..B2) = 1185.52

See also:

Other financial functions