This article shows you how to calculate income-based tax using the VLOOKUP function in Excel. The following tax rates apply to residents of Australia.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
| Taxable income | Tax on this income |
|---|---|
| 0 – $18,200 | Nil |
| $18,201 – $37,000 | 19c for each $1 over $18,200 |
| $37,001 – $87,000 | $3,572 plus 32.5c for each $1 over $37,000 |
| $87,001 – $180,000 | $19,822 plus 37c for each $1 over $87,000 |
| $180,001 and over | $54,232 plus 45c for each $1 over $180,000 |
For example, if the income is 39000, the tax is equal to 3572 + 0.325 * (39000 – 37000) = 3572 + 650 = $4222.
How to calculate tax in Excel
1. On the second sheet, create the Rates area name.
2. When you set the fourth parameter of the VLOOKUP function to TRUE, the VLOOKUP function returns the correct result, or if not found, it returns the closest value to A2.
Explanation: Excel could not find 39000 in the first column of Rates. However, it can find 37000 (large value less than 39000). As a result, it returns 3572.
3. We can return 0.325 by setting col_index_num to 3 and return 37000 by setting col_index_num to 1 like below formula.
Note: when you set the fourth parameter of the Vlookup function to TRUE, the first column of the table must be sorted in ascending order.
In addition, you can also view many other excel articles here.
