Create intermediate Works with: Any AI

Commission and Tiered Pricing Formulas

Few shot Has example

Calculate bonuses and bracketed rates correctly.

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

Write formulas for tiered commission and pricing. App: [SPREADSHEET_APP]. Tier table (thresholds and rates): [TIER_TABLE]. Rules (marginal tiers versus a single rate for the total, caps, clawbacks, accelerators): [RULES]. Sample sales (fake): [SAMPLE_SALES].

Explain the difference first with this example: sales 120,000; tiers: first 50,000 at 5%, next 50,000 at 7%, above 100,000 at 9%.
Marginal method: 50,000x5% + 50,000x7% + 20,000x9% = 2,500 + 3,500 + 1,800 = 7,800.
Whole-amount method (the highest tier applies to everything): 120,000 x 9% = 10,800. They differ greatly, so confirm the plan wording.

Provide formulas for my rules: a lookup-table method (store thresholds and rates on a sheet); marginal calculation with SUMPRODUCT: =SUMPRODUCT((sales>thresholds)*(sales-thresholds)*(rate_differences)); the lookup approach for whole-amount tiers (XLOOKUP with approximate match or LOOKUP); caps and minimums with MIN and MAX; split by period; and attainment against quota. Add a verification table that checks each formula against a manual calculation for five sales levels, including exactly on a threshold. Note that compensation plans can have legal and tax implications; plan wording must be checked by the responsible professionals.

Fill in before use

Optional: type your details and the prompt above updates. Nothing is saved or sent.

Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.

Sales 120,000; tiers: first 50,000 at 5%, next 50,000 at 7%, above 100,000 at 9%.
Marginal: 50,000 x 5% + 50,000 x 7% + 20,000 x 9% = 2,500 + 3,500 + 1,800 = 7,800.
Whole amount: 120,000 x 9% = 10,800. Confirm which the plan wording uses.
Formula (marginal) with thresholds in E2:E4 (0, 50000, 100000) and rate differences in F2:F4 (5%, 2%, 2%): =SUMPRODUCT((B2>E2:E4)*(B2-E2:E4)*F2:F4) = 7,800.
Tests: sales exactly 50,000 = 2,500; 100,000 = 6,000; 0 = 0. Plan wording needs checking by the responsible professionals.

• commission