Function Problems

Calculate Progressive Tax and Tiered Rates in Excel

Natalie TaylorNatalie Taylor Nov 18, 2026 868 views

Question details

Create a formula that applies different rates to successive portions of an amount.

Calculate Progressive Tax and Tiered Rates in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to calculate progressive tax or another tiered charge for amounts below or above the listed thresholds.
Observed behavior
Each portion of the amount is charged at its corresponding tier rate, including any amount above the final threshold.
Before you start

Identify the lower boundary and marginal rate for each tier, including the rate above the highest threshold. The example assumes a nonnegative amount and a first threshold of zero.

Solution 1Recommended

Calculate tiered charges with SUMPRODUCT

Use a tier table and rate changes to calculate the total for any nonnegative amount.

The formula uses changes between successive marginal rates, not the marginal rates themselves. This prevents each tier from being counted at its full rate more than once.

1
Enter the amount

In Excel, enter the amount to calculate in cell A2.

2
Set up the tiers

Enter lower thresholds of 0, 10000, 50000, and 90000 in D2:D5. Enter example marginal rates of 10%, 15%, 17%, and 19% in E2:E5. Replace these values with your own tiers.

3
Calculate rate changes

In F2, enter =E2. In F3, enter =E3-E2, then drag the fill handle down through F5. The example changes are 10%, 5%, 2%, and 2%.

4
Enter the formula

In B2, enter =SUMPRODUCT((A2>$D$2:$D$5)*(A2-$D$2:$D$5),$F$2:$F$5). For an amount of 100000 using the example tiers, the result is 15700.

5
Adapt the table

Keep thresholds in ascending order. If you add or remove tiers, update both D2:D5 and F2:F5 in the formula to cover the same number of rows.

Calculate tiered charges with SUMPRODUCT
Use the actual tier rules: The example rates are illustrative, not tax advice. Enter the applicable thresholds and rates for your calculation.
Calculate tiered rates

Calculate Progressive Rates in WPS Spreadsheet

Build the same threshold and rate-change table in WPS Spreadsheet, then use SUMPRODUCT to calculate the charge across all tiers.

  1. 1. Open a spreadsheet: Open WPS Spreadsheet and create a workbook or open an existing Excel file. Enter the amount in A2.
  2. 2. Enter thresholds and rates: Enter tier lower boundaries in D2:D5 and their marginal rates in E2:E5, starting with a threshold of zero.
  3. 3. Calculate rate changes: Enter =E2 in F2 and =E3-E2 in F3, then drag the fill handle from F3 through F5.
  4. 4. Calculate the total: In B2, enter =SUMPRODUCT((A2>$D$2:$D$5)*(A2-$D$2:$D$5),$F$2:$F$5). Adjust both ranges if your table has a different number of tiers.
Calculate progressive charges with a reusable SUMPRODUCT formulaOpen and edit Microsoft Excel XLSX workbooksUpdate thresholds and rates in a simple tableUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula use rate changes instead of the full tier rates?

Each threshold contributes an adjustment to the rate applied above it. Enter the first marginal rate in F2 and subtract the previous marginal rate from each later rate in column F to avoid double-counting.

What happens when the amount is below a threshold?

The comparison A2>$D$2:$D$5 excludes that threshold's contribution. At exactly a threshold, its contribution is also zero because no amount is above it.

How do I include a rate for amounts above 90000?

Enter 90000 as a lower threshold in column D and its marginal rate in column E. In the corresponding cell in column F, subtract the previous tier's rate from that rate.

Can I add more tiers to the formula?

Yes. Add thresholds and marginal rates to columns D and E, extend the rate-change formulas in column F, and update both SUMPRODUCT ranges to include the new rows.