Select Page

## Use case: Basic Tax Calculation using Lookup Tables

The tax brackets can be confusing to work with. Different ‘levels’ of your income needs to be multiplied with a different percentage to find the total. When you want to do tax calculations in Zapier, you are limited with regular IF function. You need to nest IF functions for each tax level that makes the formula long and hard to read.

Alternatively, you can use a SpreadsheetWeb Hub application to create a table using a single Excel-based lookup function to calculate tax amounts.

Note: Zapier do has lookup table support as well. However, it is hard to create and maintain due to website capabilities and also limited with exact match option. In other words, Zapier's lookup table cannot return a value when you search for an amount between two tax brackets.

## Model

Our sample is based on tax brackets and fictitious basic tax rates below:

 Low High Rate 0 9,999 7% 10,000 24,999 10% 25,000 49,999 14% 50,000 99,999 22% 100,000 249,999 30% 250,000 and over 45%

For example, if your salary is 150,000, you will pay 150,000 * 30% = 45,000 in taxes.

## Zapier Approach

As mentioned before, you need nested IF functions to calculate basic tax rate of your income. Zapier's spreadsheet-style formulas can be found in their Formatter app. Select Numbers as Event.

You need to select Spreadsheet-Style Formula option for the Transform property in the Set-up action step. Once selected, you can see a textbox where you can enter your formula.

As you can see in the screenshot and the formula at below, evaluating a tax table with 6 rows requires 5 IF functions to be nested. Also, we need to mention that you have to move your hand from keyboard and use your mouse to add a dynamic field, e.g., Salary in our sample.

IF({{1. Salary}}<10000,0.07,IF({{1. Salary}}<25000,0.1,IF({{1. Salary}}<50000,0.14,IF({{1. Salary}}<100000,0.22,IF({{1. Salary}}<250000,0.3,0.45)))))

Here is the tax rate for 50,000:

With SpreadsheetWeb Hub, you are not limited with IF function. You can use any lookup function of your choice. In the next section you can find how to build your tool in Excel. If you already have a model in an Excel workbook, you can skip to the App creation section directly.

### Preparing model in Excel

First, create a table that contains lower limits and tax rates. (B5:C10)

 Limit Rate 0 7% 10,000 10% 25,000 14% 50,000 22% 100,000 30% 250,000 45%

Then, enter the value you want to calculate its tax into a cell (F4).

Here are both XLOOKUP and VLOOKUP versions of tax calculation:

Finally, give names into cells that you want to use on your Zaps. For example, the value you want to calculate its tax (F4) and the tax value (F7).

### Creating a SpreadsheetWeb Hub Application

Once your Excel file is uploaded, you will be directed to the Applications page. Unless you want to generate a custom user interface for your app, you can continue with Zapier.

Note: To create your custom web application, use Go to Designer button to open the Designer module and start building.

In Zapier, choose the application as SpreadsheetWeb Hub app and Calculate as the event to calculate basic tax rate according to amount in previous steps.

Choose your workspace and application to populate the named ranges in your workbook. For example, we named the cell F4 as "Income" which appears as an input box. Bind the value you want to calculate its tax to "Income" and select the named range of the cell that contains the formula ("TaxRate").