Merit Increase Budget Optimizer

Purpose and model boundary

This calculator shares a merit increase budget among up to 10 employees. You enter the employees, each with a salary, a full-time equivalent (FTE), a grade, a region and a performance rating; your salary bands; and a merit matrix that gives a target increase for each rating and compa-ratio interval. The workbook looks up each employee's target raise, applies the minimums, caps and eligibility you set, and then finds the raises that stay as close to target as the available budget allows. The raises are rounded to the cent, and the workbook checks on every calculation that no other allocation does better.

A second action adds an 11 by 11 grid of available budget against the maximum increase for anyone, showing how close to target the raises can get at each combination.

Every figure on this page is computed by the spreadsheet; the page adds nothing to it. The model is educational policy arithmetic for the pay policy you enter. It does not make compensation decisions, test pay equity, or certify compliance with any payroll, tax or employment law.

Inputs and units

• Budget basis: Annual run-rate counts each raise for a full year; Remaining plan year cash counts only the days from each employee's effective date to the end of the plan year.
• Available merit budget, from 0 to 100,000,000. 0 is a valid budget; blank is not.
• Employer load on raises, optional: extra cost per raise dollar, such as payroll taxes and benefits that scale with pay. Blank means none.
• Plan year start, the first day of a month, and an optional default effective date. Both are read only on the cash basis; the plan year is the 12 months from the start date.
• Employees, up to 10 rows, put in ID order: a synthetic ID, never a name (a capital letter, then up to 15 capital letters or digits); the current annual salary actually paid at the employee's FTE; the FTE, from 0.01 to 1; the grade and region codes; the rating, a whole number from 1 to the number of ratings; an optional effective date; eligibility, Yes or No; an optional individual minimum and maximum increase as a share of salary; an optional mandatory adjustment in dollars, such as a legal or equity adjustment; an optional weight from 0.01 to 100 (blank means 1); and an optional Exclude flag. Rows can be pasted from a spreadsheet.
• Band definition and salary grades, up to 30 rows, in the same layout as the salary structure analyzer: a grade code, an optional rank that this calculator ignores, the full-time midpoint in the base region, and either the range spread, required in mode B, or the minimum and maximum, both required in mode A. The other mode's columns may be left blank; values entered there are ignored, and in mode B the status says so.
• Pay regions, up to 10 rows: a code and a multiplier from 0.01 to 5, applied to the band minimum, midpoint and maximum alike. Use one region at 1.00 if pay does not vary by place.
• Merit matrix: the number of ratings and of compa-ratio intervals, each from 1 to 10; the intervals, each from a lower compa-ratio (included) to an upper one (excluded), starting at 0, following on without gaps or overlaps, and open-ended at the last; and the target increase for each rating and interval, from 0% to 50%.
• Raise limits: the maximum increase for anyone as a share of salary; whether raises stop at the band maximum; optional minimum and maximum increases by grade; and whether a minimum above a cap is reported as infeasible or allowed to exceed the cap.
• Allocation: the mode (minimize deviation from target, or scale everyone proportionally), the optional second pass that uses budget left over, the deviation it may add, and whether raises may go above target.
• The action, and for the grid the budget step and the maximum-increase step.

Money is dollars a year; rates are percentages of current salary.

Governing relationships

Bands and compa-ratio. In mode B the range spread is (maximum − minimum) / minimum, with

minimum = 2 × midpoint / (2 + spread), maximum = minimum × (1 + spread)

so a 100,000 midpoint with a 50% spread gives 80,000 to 120,000. Each employee's band is the grade's band times the region multiplier. Compa-ratio is full-time pay divided by that midpoint, (salary / FTE) / midpoint, rounded to 10 decimal places before the interval lookup so that a ratio exactly on a boundary lands in the interval that starts there.

Target and bounds. The target raise t is salary times the matrix cell for the employee's rating and interval. The lower bound is the largest of the mandatory amount and the individual and grade minimums; the upper bound is the smallest of the band headroom (band maximum at the employee's FTE less salary, never below 0, when the band cap is on), the individual and grade maximums, and the maximum increase for anyone. Lower bounds are rounded up and upper bounds down to the cent, so a posted cent can never break a bound. An employee who is not eligible stays at 0.

Cost of a raise. Each raise dollar costs c in budget: 1 on the run-rate basis, or the share of the plan year from the effective date to the year end on the cash basis, times one plus the load. A raise that starts after the plan year ends costs nothing this year.

Minimize deviation from target. The workbook minimizes

Σ weight × |raise − t| / max(t, 1) subject to each raise within its bounds and Σ c × raise ≤ budget.

Each employee first gets the value closest to target that its own bounds allow. If those raises fit the budget, they are the answer. If not, the excess is removed by cutting raises in ascending order of weight / max(t, 1) / c, the deviation added per budget dollar, with ties broken by employee ID; the employee at the margin is cut partly. Each cut lies on one straight piece of that employee's objective, so this order is exactly optimal, and a dual check in the workbook confirms it before the result is marked optimal. Because the objective counts a shortfall as a share of target, a dollar cut from a large target costs less than one cut from a small target: the budget falls first, and in full, on the employees with the largest targets. Use the weight column to protect an employee.

Scale everyone proportionally. Every eligible employee gets the same share s of their target, raise = s × t, held within the same bounds. s is the largest value from 0 to 1 whose total cost fits the budget, or above 1 up to the caps when raises may go above target. The cost is a rising, piecewise-linear function of s that bends only where a minimum or cap meets s × t, so the workbook finds s exactly from those breakpoints, without iteration. Its total deviation is reported for information and is not comparable with the default mode's optimum, which stays shown as the lower bound.

Second pass. With leftover budget after every target or cap is reached, and raises allowed above target, the second pass adds raises up to the caps while the total weighted deviation grows by no more than the allowed extra deviation, filling in ascending deviation per budget dollar. Without raises above target it cannot change the result, and the workbook says so.

Rounding. Every raise is rounded down to the cent; leftover cents then go one at a time to the employees with the largest dropped fraction, ties by ID, while each cent fits the budget and takes no one past a cap or past the nearest cent of the continuous raise. The budget used and the budget left are recomputed from the rounded raises.

Feasibility. No allocation exists when an employee's lower bound is above its upper bound, or when the lower bounds alone cost more than the budget. The minimum feasible budget is the cost of every lower bound, and the shortfall is how far the budget falls below it.

Scenario grid. Rows are 11 budgets centred on yours, one budget step apart; columns are 11 maximum increases centred on yours, one step apart. Each cell is the exact optimum for its budget and maximum increase, before cent rounding, shown as the average weighted shortfall from target. A cell with a budget below 0, a maximum increase outside 0% to 100%, a bound conflict or a budget shortfall shows its reason instead of a figure. The centre cell equals the base case.

Calculation sequence

The spreadsheet first checks the inputs and every employee, grade, grade-limit, region, interval and matrix row. It then builds the bands, places each employee in the merit matrix, sets each target and bound, prices each raise dollar, tests feasibility, runs the allocation in the chosen mode with its optimality check, applies the second pass if it is on, rounds to the cent and rechecks every bound and the budget on the rounded raises. In the grid action it solves the allocation again for each budget and maximum increase. A block of consistency checks, including that the grid's centre equals the base case, runs before the status is returned.

Outputs and interpretation

The headline gives the total recommended raises, the budget left unused and the average weighted shortfall from target. The cards also show how the optimizer finished, the common scale factor in proportional mode, the minimized objective and its name, the objective of the rounded raises, the lower bound, the rounding gap and its largest possible size, the deviation saved by one more budget dollar while the budget binds, the residual rechecks (0 means every constraint holds), the second-pass result, the total target, the raises at run-rate and in the plan year with and without load, the budget used, the minimum feasible budget, the shortfall, whether the budget limits the raises, any employees whose minimum is above their maximum, and the counts of employees in scope, excluded, not eligible, cut by the budget and above their band maximum. The employee table gives each person's target, bounds, recommended raise, deviation, salary and compa-ratio before and after, cash-year and loaded cost, and the constraint that decided the raise.

Any warnings appear under the results. The model version and the other run details are in the downloaded CSV and JSON files. The per-row check tables are returned to the API and MCP, and the first problem they find is named in the status. When the allocation is infeasible, the raises are withheld; the minimum feasible budget, the shortfall, the employees in conflict, the counts and the employee table's bounds stay on the page, and a button copies the minimum feasible budget into the budget input.

In the grid, the first column gives each row's available budget, and the column headings count maximum-increase steps from −5 to +5, with the maximum increase at each step listed below the grid. A cell without a value is explained in the table beside the grid, which has the same layout; the budget used in each cell is in a third table of the same shape.

Under the default inputs: ten employees in four grades and two regions, an annual run-rate budget of 20,000 with no load, a 6% maximum increase and the band cap on. The targets total 25,952.50, and the raises closest to target within each employee's own bounds cost 22,982.50, because employee E010's 4,970.00 target is held to the 2,000.00 left below the band maximum. The 2,982.50 excess is cut first from E010, the largest target, by its full 2,000.00, and then 982.50 from E007, whose raise falls from 3,960.00 to 2,977.50. The other eight employees get their targets. The raises total 20,000.00 with nothing unused, the average weighted shortfall from target is 12.48%, each extra budget dollar would reduce the objective by 1/3,960, about 0.000253, and the status is OK and OPTIMAL. Warnings note the two employees cut to fit the budget.

The first chart compares each employee's target and recommended raise. The second shows each compa-ratio before and after the raise against the midpoint. The third, in the grid action, traces the shortfall from target across the budgets.

Validation and status logic

The workbook returns one status, chosen in this order: NOT VALID, then INFEASIBLE, then NO SOLUTION FOUND, then CHECK, then OK.

• NOT VALID names the first problem: a scalar input outside its limits (the budget, the load, the maximum increase, the plan year start on the cash basis, the default effective date, the numbers of ratings and intervals, the allowed extra deviation, or the grid steps in the grid action), a choice that is not one of its options; then the grade, grade-limit, region, interval and matrix rows (including an interval gap, overlap, closed last interval or first interval not starting at 0, and a blank or filled-but-inactive matrix cell); then the employee rows (a gap, the ID form, a repeated ID, a zero or out-of-range salary or FTE, a grade with no band or a region not in its table, the rating, the dates, and a mandatory amount on an ineligible employee); and finally no employee left to allocate. Inputs that are hidden are not checked. NOT VALID is a refusal, not a numeric answer: every figure is withheld.
• INFEASIBLE means no allocation meets every rule: some employees have a minimum raise above their maximum (they are named), or the minimum raises cost more than the budget. The raises are withheld.
• NO SOLUTION FOUND means the rounded raises failed an independent constraint recheck. It is never expected.
• CHECK accompanies a valid result that needs attention: rows excluded explicitly; mandatory amounts above caps applied because you chose that relaxation; grades whose minimum or maximum band mode B ignores; an optimality check that did not pass although every constraint holds; a failed workbook self-check; or a grid centre that does not equal the base case.
• OK otherwise.

Warnings list, with the first employee concerned: employees above their band maximum, minimums above target, raises effective after the plan year, budget left unused, relaxations, employees cut to their minimum or to fit the budget, employees not eligible, cents left by rounding, a second pass that found nothing to add, ignored band values, and the proportional mode's note. Only those named under CHECK change the status.

Assumptions and limitations

• Capacities are 10 employees, 30 grades and grade limits, 10 regions, 10 ratings and 10 intervals.
• Salary is always actual pay at the employee's FTE; compa-ratio uses full-time pay, and the money bounds use the band at the employee's FTE.
• The objective is the absolute deviation from target as a share of target. It cuts the largest targets first and in full; weights change that. A squared-deviation objective is not offered.
• The scenario grid is before cent rounding.
• Ratings are whole numbers with an optional label; the matrix is at most 10 by 10.
• No raise reduces pay. An employee above the band maximum gets no merit raise while the band cap is on.
• Market pay, pay-equity analysis, currency conversion, payroll calendars and taxes on the raises are not modelled. Amounts are in one currency.
• Comparing pay by any protected characteristic is outside the model.

Restrictions and non-computing states

The calculator enforces its numeric and choice limits before it accepts a result. A gap between rows, a repeated ID or code, a grade or region missing from its table, an interval gap or overlap, and a zero salary that is not excluded are refused by the workbook. In the Allocate budget action the scenario grid is left blank.

Errors and warnings

A request rejected by the page's input limits is not a computed result. A transport or service failure, a timeout or a cancelled run cannot establish a fresh workbook answer, and the last result stays on the page marked as out of date. Review the workbook's References and Disclaimer, and your actual pay policies, before using a result for a real decision.

Sources and further reading

Your employees, bands, merit matrix, limits and budget come from the inputs; the calculator holds no statutory or market data, and its arithmetic follows the pay policy you describe. The public sources below show how a published pay structure ties raises to performance, where to find market pay movement and pay levels, what an employer load on pay can include, and the legal frame a merit system sits in. They do not endorse this calculator.

• U.S. Office of Personnel Management, General Schedule describes a public pay structure of 15 grades with steps, where within-grade increases depend on acceptable performance and an outstanding rating can earn an extra step, plus locality pay by area, the same ideas as the calculator's grades, regions and rating-based targets (retrieved 2026-10-27).
• U.S. Bureau of Labor Statistics, Employment Cost Index measures the quarterly change in employers' cost of wages, salaries and benefits, a public reference when setting the size of a merit budget (retrieved 2026-10-27).
• U.S. Bureau of Labor Statistics, Occupational Employment and Wage Statistics publishes yearly pay estimates for about 830 occupations by nation, state and metropolitan area, a starting point for band midpoints and regional multipliers (retrieved 2026-10-27).
• IRS Topic no. 751, Social Security and Medicare withholding rates gives the employer's 6.2% Social Security and 1.45% Medicare rates, the payroll-tax part of an employer load on raises (retrieved 2026-10-27).
• U.S. Equal Employment Opportunity Commission, Equal Pay Act of 1963 sets out the statute, including the exceptions for pay differences made under a seniority system or a merit system; the calculator does not test whether a pay practice meets them (retrieved 2026-10-27).

This is an educational model of the pay policy you enter, not legal, tax or compensation advice. It does not certify pay equity or compliance with any employment law; review a result against your actual policies and with a qualified professional before acting on it.