Quick, clean, and to the point

Tax rate calculation with fixed base

Excel formula: Tax rate calculation with fixed base
Generic formula 

This example shows how to set up simple formula using the IF function to calculate a tax amount with both fixed and variable components. In the example shown, the formula in C5 is:


in a worksheet with the following named ranges: rate = F4, fixed = F5, limit = F6.

The rules of this problem are as follows:

  1. If amount is less than $1000, tax is amount * 20%.
  2. If amount is greater than or equal to $1000, tax is $200 + (amount over 1000) *20%

The core of this formula is a single IF statement that checks the amount in column B against the base limit:


If TRUE, the formula simply multiplies the amount in B5 by tax rate:


If FALSE, the formula applies the tax rate to the amount over 1000, then adds the fixed amount:

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.