Although SGIII's single formula solution does use an easily editable formula, my preference os to put the tax rates and threshold values (where the rates change) into a visible table, modelled on the one included in the Canadian income tax forms.
For your example data, this table would look like the one on the right:
Column A lists the threshold values at which each tax rate begins.
Column B lists the tax rate that applies to the tier starting at that threshold and continuing to the next threshold value.
Column C contains the total tax due at that threshold income value.
The formula shown below the tables calculates the amount of tax at each threshold. The formula is entered in C3, then filled down to the last row containing a threshold value in column A and the rate charged, starting at that threshold inColumn B.
In C3, the formula gets the amount of tax due at the previous threshold (C2), then gets the threshold amount at 'this row' (A3), subtracts the threshold amount at the 'previous row' (A2), and multiplies the result by the tax rate on the 'previous row' (B2), and returns the result as the tax due at this threshold.
Table 1 (on the left) contains two columns. In the example, the table contains several rows to provide examples for several income amounts. In practice, there would be only two cells involved, one containing the amount to be taxed, the second containing the formula shown below the tables and displaying the amount of tax due.
The formula shown is in cell B9.
The formula contains three instances of VLOOKUP. In each, the search-for value is the amount to be taxed, contained in the cell in 'this row' of column A (A9).
The columns-range for all three instances is the same: columns A to C of Table 2. VLOOKUP always searches in the first (leftmost) column of columns-range, so each search is in column A of Table 2.
ll three instances use the same search-for value (the amount in 'this row' of Column A, and the same columns-range, so each finds the same value in column A of Table 2.
VLOOKUP can be set to find an exact-match, but in this case we want a close-match, defined as 'the largest value less than or equal to the search-for value.
In the example, that means the searches in rows 2 and 3 will find 0, the searches in rows 4, 5 and 6 will find 250, and the searches in rows 7, 8 and 9 will find 500.
Having found the value in column A, the first VLOOKUP returns the value in the same row of the third column (C), the amount of tax due at the threshold value that was found.
The second VLOOKUP returns the value in the same row of column 1, the threshold value that was found. This is subtracted from the search-for value to get the amount subject to the tax rate that starts at that threshold value.
The third VLOOKUP returns the value in the same row of the third column, the tax rate that applies to amounts over that threshold.
The amount subject to that tax rate, determined by the subtraction above, is multiplied by that tax rate, and the result added to the tax due at that threshold, retrned by the first VLOOKUP.
The result is displayed in the cell containing the formula.
EDITING when the tax rates and/or threshold values are changed:
NO changes are needed in the formula.
NO changes are needed in the formula in column C of Table 2.
The new threshold values and the tax rate that starts at each threshold are listed in columns A and B of Table 2.
If there is a change in the number of tiers, the formula entered in cell C3 of Table 2 in filled down to the row containing the last threshold value in column A.
Regards,
Barry
