Calculate Progressive Tax and Tiered Rates in Excel
Question details
Create a formula that applies different rates to successive portions of an amount.

- 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.
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.
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.
In Excel, enter the amount to calculate in cell A2.
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.
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%.
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.
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 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. Open a spreadsheet: Open WPS Spreadsheet and create a workbook or open an existing Excel file. Enter the amount in A2.
- 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. Calculate rate changes: Enter =E2 in F2 and =E3-E2 in F3, then drag the fill handle from F3 through F5.
- 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.

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.




