Build a Dynamic Income Tax Calculator – Part 2 of 2

By David H. Ringstrom, CPA

(If you didn't already do so in Part 1, click here to download the accompanying Income Tax Calculator spreadsheet.)

Last month I explained how to use the VLOOKUP function to cross-reference tax rates from a tax rate table. I then extended the functionality by creating 4 different tables — Single, Married-Joint, Married-Single, and Head of Household, as shown in Figure 1. After assigning a range name to each table, I used Microsoft Office Excel’s Data Validation feature to create an in-cell drop-down list comprised of these range names. Finally, I modified my VLOOKUP functions to use Excel’s INDIRECT function. INDIRECT replaced the static look-up range originally specified in the VLOOKUP formula. If I choose Married-Joint from the list, the VLOOKUP function will calculate the tax due based on that table. I can choose Married-Single from the list to determine the additional tax due under that filing status. At this point I have an efficient calculator for determining the amount due based on a taxable income input, but I’d rather not have 4 separate tables. This month I’ll dig deeper, and show you how to create a single table of tax rates, as shown in Figure 2. The final result will involve two rather complex formulas, but we’ll build them a step at a time.

Figure 1: The VLOOKUP version of the tax calculator relies on four separate rate tables.

Figure 2: The new tax calculator will rely on a single rate table.

Understand MATCH

The MATCH function is akin to VLOOKUP — you specify what to look for, where to look, and the type of match that you’d like. When the MATCH function finds the criteria you specify, it returns the position number within the list —otherwise it returns #N/A. The position number can then be used within an INDEX function to return a specific value, much like VLOOKUP or HLOOKUP. Although VLOOKUP is a useful look-up function, your look-up criteria must always be the first column of your data table. Instead, the INDEX/MATCH combination enables you to create a look-up based on any column within the table.

The MATCH function has three arguments:

• Look-up value: What we’re looking for, such as a taxable income amount.
• Look-up array: A single column range that contains the potential look-up values, such as the taxable income tiers associated with various income tax rates.
• Match type: We can choose from three different match types:
  • -1 instructs MATCH to find the smallest value that is greater than or equal to the look-up value. In this case the list must be sorted in descending order.
  • 0 instructs MATCH to find an exact match. In this case the list can be in any order.
  • 1 instructs MATCH to find the largest value that is less than or equal to the look-up value. In this case the table must be sorted in ascending order.

Since MATCH only returns the position number within the list, we’ll then use the INDEX function to return actual amounts from the table.

Comprehend INDEX

I only have space in this article to describe the reference capability of the INDEX function, but Excel’s Help feature discusses the array and multiple area capabilities of INDEX. The reference capability of INDEX has three arguments:

• Reference: Typically this is the address of a table, such cells B2:J8 in Figure 2.
• Row number: This argument tells the INDEX function to look at a specified row within the table. In the case of our tax calculator, we’ll use the MATCH function to provide this value.
• Column number: This optional argument provides the column coordinate. You can omit the column number if you provide a single-column range for the reference argument.

Appreciate OFFSET

Not many Excel users know about the OFFSET function. In essence, it’s a means for shifting a range a certain number of columns and/or rows away from a starting point. In the case of our tax calculator, we’ll use OFFSET to have the MATCH function refer to the proper columns within our tax rate table. The OFFSET function has five arguments:

• Reference: The starting point for our range, such as A2:A8.

• Rows: The number of rows away from the starting point that you’d like the range to be shifted. Use a negative number to shift the range upward, or a positive number to move it down. Specify zero if you do not want to shift the range from the starting point. We’ll use zero, because we won’t want to shift the rows.
• Columns: The number of columns away from the starting point that you’d like the range to be shifted. Use a negative number to shift the range to the left, or a positive number to move it to the right. Specify zero if you do not want to shift the range from the starting point. We’ll use MATCH to shift the range to the right to correspond with the filing status that we choose from the drop-down list.
• Height: Indicates the height of the range in rows. Omit the argument if you don’t want to adjust the height of the range. We’ll omit this since we won’t need to adjust the height of our range.
• Width: Indicates the width of the range in columns. Omit this argument if you don’t want to adjust the width of the range. We’ll omit this since we won’t need to adjust the width of our range.

Create the Tax Calculator

Now that we have the function basics out of the way, let’s build a tax table that refers to a single table instead of four different tables.  Enter these values in cells A2 through A8 of a blank worksheet:

Enter these values in cells B2 through C8 of the worksheet:

Enter these values in cells D2 through E8 of the worksheet:

Enter these values in cells F2 through G8 of the worksheet:

Enter these values in cells H2 through I8 of the worksheet:

Add these headings to the spreadsheet:

Enter 125,000 in cell B11, and then use Data Validation to create an in-cell drop-down list in cell E10:

1. Excel 2007: Choose the Data Validation icon in the Data Tools section of the Data ribbon.
   1. Excel 2003 or earlier: Choose Tools, and then Data Validation.
2. In all versions of Excel, choose List, and then enter this into the Source field, as shown in Figure 3:

Single,Married-Joint,Married-Single,Head Of Household

Figure 3: Enter the filing statuses in the Source field of the Data Validation window.

Caution: Be sure that the contents of the Source field exactly match the values that you entered in cells B2, D2, F2, and H2.

3. Click OK. At this point, a drop-down list should appear when you click on cell E11, as shown in Figure 4. Choose Single from this list.

Figure 4: Data validation can provide an in-cell drop-down list.

We’ll now enter the formula to determine the tax rate. Enter this formula in cell C11:

=MATCH(B11,B3:B8,1)

Based on an input of 125,000 in cell B11, the formula should return the number 4. We’ve instructed MATCH to look at the taxable income for the Single filing status, and asked it to find the closest income bracket for $125,000. Next, we’ll add the INDEX function, so that we can get the actual tax rate. Modify the formula in cell C11 to be as follows:

=INDEX(A3:A8,MATCH(B11,B3:B8,1))

At this point the formula should return 28%. However, we’re referencing a static range of B3:B8 for our income brackets, and instead we want the formula to shift automatically based on our choice in cell E11. To do so, we’ll employ the OFFSET function. As shown in Figure 5, modify the formula in cell C11 to match this:

=INDEX(A3:A8,MATCH(B11,OFFSET(A3:A8,0,MATCH(E11,A2:I2,0)-1),1))

Figure 5: This formula determines the tax rate based on the taxable income amount and filing status.

Although this may look intimidating, we basically replaced the B3:B8 portion of the formula with this component:

OFFSET(A3:A8,0,MATCH(E11,A2:I2,0)-1)

Our OFFSET function contains these arguments:

• Reference: A3:A8 serves as the starting point.
• Rows: Zero indicates that we don’t want to shift the range up or down.
• Columns: We use an additional MATCH function to determine which column in the table has our filing status. Notice that this time we used a zero for the match type, since we need an exact match. I subtracted 1, because in the case of Single, the MATCH function inside OFFSET returns 2, but I only need to shift over 1 column.
• Height: I omitted this argument, since I didn’t need to resize the range.
• Width: I omitted this argument, since I didn’t need to resize the range.

At this point the formula should return 28%. This number should change to 25% if you choose Married-Joint in cell E11, 33% for Married-Joint, or  remain at 28% if you choose Head of Household.

We’re now ready to create the final formula in our table, which will perform the actual tax calculation. Enter this formula in cell D11:

=INDEX(A2:I8,MATCH(C11,A3:A8,0),MATCH(E11,A2:I2,0)+1)

In this case, we’re specifying the entire table range for the reference argument of the INDEX function, and then using two MATCH functions to return the row and column positions. This MATCH function determines the row for our tax rate:

MATCH(C11,A3:A8,0)

Notice the zero in the match type position, because we want to ensure an exact match on the tax rate. The second MATCH function determines which column has the base-tax amount:

MATCH(E11,A2:I2,0)+1

As before, we’re determining which column our filing status appears in within row 2, but then adding 1 to that amount, since the base tax amount is in the next column over.

We now need to calculate the marginal tax amount beyond the base tax. To do so, add this to the end of the formula in cell D11:

(B11-INDEX(A2:I8,MATCH(C11,A2:A8,0),MATCH(E11,A2:I2,0))+1)*C11

The INDEX function returns the tax tier associated with the tax rate, and this amount is subtracted from the taxable income. $1 is added to this amount to determine the precise marginal amount to be taxed, and then the amount in parenthesis is multiplied by the tax rate in cell C11. The complete formula is shown in Figure 6.

Figure 6:  The final piece of the calculator determines the tax due based on income level and filing status.

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.

This article first appeared Microsoft Professional Accountant's Network newsletter.

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link