Free tools Windows power users keep installed
One-click scans. No signup required.
Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
There is no single bonus formula in Excel: the right calculation depends on the written bonus plan. The basic model is bonus base × bonus rate. If an eligible salary is in B2 and the rate is in C2, enter =B2*C2. A $60,000 base at 10% produces a $6,000 bonus.
Set up your bonus worksheet
Use a consistent table before adding formulas:
| Employee | Eligible salary | Performance score | Target met | Bonus rate | Bonus |
|---|---|---|---|---|---|
| Alex | $60,000 | 92% | Yes | 10% | $6,000 |
| Blair | $50,000 | 76% | Yes | 5% | $2,500 |
| Casey | $45,000 | 61% | No | 0% | $0 |
Decide what the bonus base means: annual salary, monthly salary, hourly pay, sales, profit, or another defined amount. Also document whether overtime, commissions, allowances, unpaid leave, and partial-year service are included. Enter 10% as 10% or 0.10, not 10. Excel’s formula and percentage guidance is available from Microsoft.
1. Calculate a fixed-percentage bonus
Use this when every eligible employee receives the same percentage of the defined base.
=B2*C2To keep one common rate in an assumptions cell such as H2, use an absolute reference:
=B2*$H$2The dollar signs prevent the rate from moving when you copy the formula down. To calculate from monthly salary, use =B2/12*C2. To prorate a bonus with a factor in D2, use =B2*C2*D2, where D2 might be 75%.
For rounding, use =ROUND(B2*C2,0) for whole dollars or =ROUND(B2*C2,2) for cents. Rounding rules are policy decisions, so apply the employer’s documented rule rather than assuming whole-dollar rounding.
2. Pay a bonus only after a target is met
Use IF for a threshold or “cliff” plan. If the score in C2 must be at least 80% and qualifying employees receive 10% of B2:
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minute=IF(C2>=80%,B2*10%,0)If the threshold is in H2 and the rate is in H3:
=IF(C2>=$H$2,B2*$H$3,0)To require both a score and an eligibility flag in D2:
Rank #2
=IF(AND(C2>=80%,D2="Yes"),B2*10%,0)To cap the result at $10,000:
=MIN(IF(C2>=80%,B2*10%,0),10000)This is a cliff formula: below 80% pays nothing, while 80% or higher unlocks the full rate. It does not mean that a 70% score pays 70% of the bonus. A proportional plan would require a different rule, such as =B2*C2*10%, and should only be used when the compensation policy explicitly defines the score as a multiplier.
3. Apply performance bands with IF or IFS
Suppose the policy is:
| Score | Rate |
|---|---|
| Below 70% | 0% |
| 70%–79.99% | 5% |
| 80%–89.99% | 8% |
| 90% or higher | 12% |
A nested formula is:
=IF(C2>=90%,B2*12%,IF(C2>=80%,B2*8%,IF(C2>=70%,B2*5%,0)))Test the highest threshold first so a 92% score is not captured by a lower band. In Excel versions that support IFS, use:
=IFS(C2>=90%,B2*12%,C2>=80%,B2*8%,C2>=70%,B2*5%,TRUE,0)For maintainability, store thresholds and rates in assumption cells rather than embedding policy values. Test values just below, exactly at, and just above every boundary—for example, 69.99%, 70%, 79.99%, 80%, 89.99%, and 90%.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →4. Look up the rate with VLOOKUP or XLOOKUP
A separate rate table is easier to maintain when thresholds change:
Rank #3
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
| Minimum score | Bonus rate |
|---|---|
| 0% | 0% |
| 70% | 5% |
| 80% | 8% |
| 90% | 12% |
If the table is in H2:I5, use approximate-match VLOOKUP:
=B2*VLOOKUP(C2,$H$2:$I$5,2,TRUE)Here, Excel returns the rate for the largest threshold that does not exceed the score. The threshold column must be sorted in ascending order, and the fourth argument should be explicitly set to TRUE. Omitting it makes approximate matching the default and can create silent errors. See Microsoft’s VLOOKUP documentation.
In newer Excel versions, the equivalent lower-bound lookup is:
=B2*XLOOKUP(C2,$H$2:$H$5,$I$2:$I$5,0,-1)The final -1 requests an exact match or the next smaller item, while 0 supplies a fallback if no match exists. XLOOKUP can specify separate lookup and return ranges, but it is not natively available in Excel 2016 or Excel 2019. Use VLOOKUP for legacy compatibility; Microsoft’s XLOOKUP documentation lists current availability.
If the range is converted to an Excel Table named BonusRates, use structured references:
=B2*XLOOKUP(C2,BonusRates[Minimum Score],BonusRates[Bonus Rate],0,-1)Structured references expand as rows are added or removed. Learn more in Microsoft’s guide to Excel Tables.
5. Combine multiple goals with SUMPRODUCT
For a scorecard, place achievements and weights in matching ranges:
Recommended Free Tools
| Metric | Achievement | Weight |
|---|---|---|
| Sales | 90% | 50% |
| Customer satisfaction | 80% | 30% |
| Quality | 95% | 20% |
The weighted score is:
=SUMPRODUCT(B2:B4,C2:C4)This produces 88% for the example. If eligible salary is in E2 and the target bonus rate is in F2:
Best Value
=E2*F2*SUMPRODUCT(B2:B4,C2:C4)To round and cap the result at $8,000:
=MIN(ROUND(E2*F2*SUMPRODUCT(B2:B4,C2:C4),0),8000)Check the weights with =SUM(C2:C4). A 100% total is common, but not mathematically mandatory; if the plan permits another total, document whether normalization is intended. Keep the achievement and weight ranges the same size. Mismatched arrays can return #VALUE!. Microsoft also advises avoiding full-column references in large SUMPRODUCT formulas; see the SUMPRODUCT documentation.
Cliff and progressive bonuses are different
A cliff plan applies one rate to the entire base after a threshold:
=IF(A2>=100000,B2*10%,0)A progressive plan applies separate rates to separate portions—for example, 2% on the first $50,000, 4% on the next $50,000, and 6% above $100,000. It requires helper columns or a carefully designed SUMPRODUCT calculation for each band. Looking up the highest rate and multiplying the entire amount is not automatically progressive.
Add practical controls
- Missing score:
=IF(C2="","Missing score",IF(C2>=80%,B2*10%,0)) - Nonnegative result:
=MAX(0,B2*C2) - Nonnegative result with a cap:
=MIN(MAX(0,B2*C2),10000) - Lookup warning:
=IFERROR(B2*VLOOKUP(C2,$H$2:$I$5,2,TRUE),"Check score")
A warning is often safer than converting every error to zero, because IFERROR can conceal invalid data. Check that scores are numbers rather than text, percentages use the same scale, lookup thresholds are sorted, and fixed ranges use absolute references.
For a controlled workbook, keep policy assumptions in a clearly labeled area, protect formula cells, and test below every threshold, exactly at every threshold, above every threshold, at the cap, with missing data, after changing a rate, and after adding an employee row. Excel’s percentage guidance explains why 10 and 10% are different values: 10 formatted as a percentage represents 1000%.
Which method should you use?
- One rate for everyone: multiplication.
- One minimum target:
IF. - A few performance bands: nested
IForIFS. - A maintained rate table:
VLOOKUPorXLOOKUP. - Several weighted metrics:
SUMPRODUCT.
Excel calculates the rules you give it; it does not determine whether the bonus policy is fair, legally compliant, or correctly interpreted. Confirm the plan’s eligible base, thresholds, caps, proration, rounding, approvals, and payroll treatment before paying results.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
