Tax Rates

 

Sometimes you are not looking for an exact match when you use the VLOOKUP function in Excel. For example, when you want to calculate the tax on an income.

The following tax rates apply to individuals who are residents of Australia.

Taxable income

Tax on this income

0 - $6,000

Nil

$6,001 - $35,000

15c for each $1 over $6,000

$35,001 - $80,000

$4,350 plus 30c for each $1 over $35,000

$80,001 - $180,000

$17,850 plus 38c for each $1 over $80,000

$180,001 and over

$55,850 plus 45c for each $1 over $180,000


Example: if income is 37000, tax equals 4350 + 0.3 * (37000 - 35000) = 4350 + 600 = $4950

Tax Rates in Excel

To automatically calculate the tax on an income, execute the following steps.

1. On the second sheet, create the following range and name it Rates.

Named Range

2. We already know how the VLOOKUP function can return an exact match or a #N/A error if not found, by setting the fourth argument to FALSE. However, when you set this argument to TRUE, it returns an exact match or if not found, it returns the largest value smaller than lookup_value (A2). That's exactly what we want!

VLookup Function

Explanation: Excel cannot find 37000 in the first column of Rates. However, it can find 35000 (the largest value smaller than 37000). As a result, it returns 4350 (col_index_num, the third argument, is set to 2).

3. Now, what's left is the remainder of the equation, + 0.3 * (37000 - 35000). This is easy. We can return 0.3 by setting col_index_num to 3 and return 35000 by setting col_index_num to 1. The complete formula below does the trick.

Tax Rates Formula

Note: when you set the fourth argument of the VLOOKUP function to TRUE, the first column of the table must be sorted in ascending order.