Function Problems

How to Calculate Tiered Bonus Formulas in Excel (Progressive vs Flat Rate)

Khadija Khan Sep 28, 2026 868 views

Question details

The user needs to calculate a tiered bonus in Excel, but is unsure whether to apply a progressive calculation (split across tiers) or a flat-rate calculation (highest rate applied to the whole amount), as they produce different payout totals.

How to Calculate Tiered Bonus Formulas in Excel

Product: Excel
Device & OS: not provided
Scenario: Setting up a payroll, sales commission, or bonus tracking spreadsheet where payout rates change depending on specific target boundaries (e.g., £10,000 and £25,000).
Observed behavior: Different calculation methods yield significantly different results for a value like £18,513. A progressive formula yields £4,128.25, while a flat-rate formula yields £4,628.25.

Before you start

Confirm your organization's specific bonus policy before building your formula. You must know whether the commission is progressive (rates apply only to the portion of money within each tier) or flat (the highest qualifying rate applies to the entire amount).

Solution 1Recommended

Calculate a Progressive Tiered Bonus (Split Rates)

Use this method when different percentage rates apply only to the portion of the amount that falls within specific tier boundaries.

In a progressive structure, a value of £18,513 crossing a £10,000 boundary means the first £10,000 is taxed or bonused at the lower rate (e.g., 20%), and only the remaining £8,513 is calculated at the higher rate (e.g., 25%).

Set up your tier boundaries

Identify your tier thresholds. For this example, Tier 1 is up to £10,000 (at 20%), and Tier 2 is anything above £10,000 up to £25,000 (at 25%). Enter your base calculation value (e.g., 18513) into cell A2.

Construct the nested IF formula

Select the cell where you want the bonus to appear. Enter the formula: =IF(A2<=10000, A2*0.20, (10000*0.20) + ((A2-10000)*0.25)). Press Enter.

Verify the calculation result

Check that the formula returns £4,128.25. The logic calculates £2,000 for the first £10,000, and £2,128.25 for the remaining £8,513, summing them together.

Calculate a Progressive Tiered Bonus (Split Rates)

Pro Tip: For more than three progressive tiers, using the SUMPRODUCT function combined with a dedicated reference table is much cleaner and less error-prone than long nested IF statements.

Solution 2

Calculate a Flat Rate Bonus (Highest Applicable Rate)

Use this method if your policy dictates that reaching a new tier upgrades the percentage rate for the entire sales or bonus amount.

Calculate Bonuses Efficiently

Use WPS Spreadsheet for Advanced Formula Calculations

WPS Spreadsheet provides powerful data analysis tools and complete support for logical formulas like IF, IFS, and SUMPRODUCT, making it incredibly easy to calculate complex commission and tiered bonus structures.

1. Open your data in WPS Spreadsheet: Launch WPS Office, open a new Spreadsheet, and enter your sales data and tier boundaries.
2. Apply the bonus formula: Click on the target cell, navigate to the Formulas tab if you need function assistance, or type your IF/SUMPRODUCT formula directly into the formula bar.
3. Drag to fill remaining cells: Hover over the bottom-right corner of the formula cell until the crosshair appears, then drag down to apply the tiered bonus calculation to all employees automatically.

100% compatibility with Microsoft Excel formulas and cell formattingBuilt-in financial and logical functions for progressive calculationsFree, lightweight, and features a familiar user interface for quick adoptionIncludes hundreds of free templates for payroll and sales tracking

Frequently Asked Questions

Can I use VLOOKUP to calculate a tiered bonus?

Yes, VLOOKUP is excellent for flat-rate tiers. By setting up a lookup table with your tier boundaries sorted in ascending order and using TRUE (approximate match) as the final argument, VLOOKUP can instantly return the correct percentage rate to multiply against your total amount.

What function is best for a progressive bonus with many tiers?

If you have more than three tiers, the SUMPRODUCT function is the most efficient choice for progressive calculations. By multiplying the total amount against the marginal difference between tier rates, it eliminates the need for deeply nested IF formulas.

Why is my nested IF formula calculating the wrong tier?

Nested IF formulas evaluate conditions sequentially from left to right and stop at the first TRUE result. If you are using 'greater than' (>), you must test the largest number first. If using 'less than' (<), you must test the smallest number first.

Excel & Data Problems