BonusPayCalc
Guide

Short Term Incentive Plan Spreadsheet Template

Use this structure when HR or finance needs to calculate many short term incentive payouts consistently.

STIP calculatorSoftware vs spreadsheet

Last reviewed 20 August 2026

Worksheet columns

ColumnPurpose
Employee IDStable identifier for lookup and audit.
SalaryBase pay used for target bonus.
Target bonus %Plan target rate.
Company scoreShared company multiplier.
Individual scorePersonal score or rating factor.
Eligibility %Proration or plan eligibility.
CapMaximum payout boundary.

When spreadsheets break

Spreadsheets are fine for simple teams. They become risky when there are many exceptions, approvals, audit trails, version control issues or payroll handoff requirements.

Approval checks

✓
Lock formula cells before review.
✓
Keep an approval column with owner and timestamp.
✓
Separate input assumptions from payout output.

Related tools and next steps

STIP calculator
Validate one payout.
Software vs spreadsheet
Know when to switch.
Bonus calculation software
Buyer guide and checklist.

How to structure a STIP worksheet

Tab 1 — Plan parametersOne cell each for the company factor, the funding threshold, the cap percentage and the performance period dates. Everything else references these.
Tab 2 — Grade tableGrade to target bonus percentage. Looked up by exact match, never typed per employee.
Tab 3 — Rating tableIndividual rating to multiplier, with the exact band definitions written next to it.
Tab 4 — Employee rosterOne row per person: ID, grade, eligible salary, start and end dates, FTE, rating. Inputs only.
Tab 5 — CalculationFormulas only, no typed values. Target, proration, factors, uncapped payout, cap, final payout.
Tab 6 — ReconciliationTotal payouts against the funded pool, plus a count of overrides and their combined value.
The STIP formula in one cell: =MIN(salary*target%*proration*company*team*individual, salary*target%*proration*cap%). Everything else in the workbook exists to make sure the inputs to that line are right.

Controls worth building in

1
Data validation on ratings. A dropdown, not free text. “Exceeds” and “exceeds expectations” will not match a lookup.
2
A flag for proration above 100%. Catches date-entry errors immediately.
3
A flag for any payout above the cap. Should be impossible; if it fires, the MIN is missing on that row.
4
An override column paired with a mandatory reason. Blank reason, conditional formatting turns red.
5
Protected formula cells. Lock the calculation tab so a manager reviewing the file cannot overwrite a formula with a number.

Check individual cases against the STIP calculator, or read how to calculate a STIP bonus for the formula in detail.

STIP spreadsheet questions

How do you build a STIP spreadsheet?

Separate parameters, lookup tables, the employee roster and the calculation into different tabs. Keep the calculation tab formula-only, and add a reconciliation tab that checks total payouts against the funded pool.

What is the STIP formula in Excel?

=MIN(salary*target_pct*proration*company*team*individual, salary*target_pct*proration*cap_pct). The MIN applies the cap last, against the prorated target.

What controls should a STIP worksheet have?

Data validation on ratings, flags for proration above 100% and payouts above the cap, a mandatory reason paired with every override, and locked formula cells.

Reviewed 20 August 2026 by the BonusPayCalc editorial team. Gross planning estimates only — not payroll, tax, legal or HR advice. See methodology for how formulas are chosen, or report a correction.