Skip to main content

Rate Formulas

Learn how to configure earnings rate multiplier formulas in plan components

Updated today

Video overview of Rate Formulas

What are Rate Formulas

Within Plan Builder, you can create an earnings rate multiplier formula that is written using SQL-like syntax at the component level. This formula will define logic to multiply against a base rate to dynamically compute an earnings rate.

You can think about a rate formula in the following way: Base rate * Formula = Final earnings rate

Here's an example of how this might play out with sample numbers:

  • Defined component base rate = 5%

  • Formula says add a 1.5x multiplier when Y conditions are met (Y conditions may be something like "contract term length is over 12 months)

  • Resulting effective rate = 5% * 1.5 = 7.5% final earnings rate

Formulas can be created in:

  • Single rate commission & bonus components

  • Multi rate commission & bonus components

  • Milestone bonus components

Where to enable a formula

  1. In Plan Builder, add a component as your normally would

  2. Scroll down to the “Rate Configuration” section

  3. Observe the “Rate Formula” toggle

    • Switch this on and hit “Add New Formula”

How to write and configure a formula

You will now be brought to a full screen editor where you will be able to write your SQL-like formula

  • Reminder: The formula you write will act as a multiplier on whatever Commission Rate value(s) you have input in the component

Using Rate Formula AI

QuotaPath’s Rate Formula AI helps you build dynamic rate formulas without needing to write SQL manually. Enter a plain-text description or an Excel-style formula, and the Rate Formula AI will automatically generate a SQL formula for you.

For example, you could type:

“Multiply the rate by 1.5 when contract term is longer than 12 months”
​or
​=IF({Term Duration} > 12, 1.5, 1)

How to access Rate Formula AI
When you open the formula editor, you’ll see the Rate Formula AI option at the top of the editing window.

  1. Click into the AI input box

  2. Type your plain-text or Excel-style formula

  3. Hit "Generate" to create your SQL formula

The resulting SQL will appear in the main formula editor window below.

The AI will translate your input into a SQL formula. Click the "Format Formula" button to help organize the SQL that the AI generates.

Tip: You can still manually edit the generated SQL directly to fine-tune your logic before saving.

Manually writing SQL

If you want to edit your AI output, or want to compose your own formula from scratch, you can start writing your SQL directly into the formula editor.

You can select QuotaPath fields from the “Insert Fields” button:

The Source Field modal will appear

  • Click “Add” to directly insert fields into the formula

  • Click "Done" and see the field now visible in the formula SQL editor window

Alternatively, if you already know the name of the field you want to use and don’t want to hit the “Insert Fields” button each time, you can type field names using curly brackets like {Term Duration} and observe a typeahead list of available fields appear as you start typing:

Enter an optional plain-text description of the formula that will be visible throughout the app to all other users in the workspace. Descriptions are highly recommended, as this will help end users better understand their earnings rates in plain text language.

Finally, save your formula.

Allowed fields in formulas

QuotaPath fields

The following list represents the QuotaPath fields that will be available for you to use in your formula.

Category

Field Name

Type

Field Definition

Deal-Related Fields

Cumulative Value

Number

Running cumulative attainment amount calculated on the component level

Deal-Related Fields

Deal Date

Date

The close date when the deal was finalized and recorded

Deal-Related Fields

Deal Value

Number

Value of the deal that will display as whatever the defined field on the component is (e.g. ARR, MRR, Demos Scheduled)

Deal-Related Fields

Integration Source

String

The source system where the deal originated (e.g., Salesforce, HubSpot, Close)

Deal-Related Fields

Term Duration

Number

Length of the deal contract term in months

Deal-Related Fields

Term End Date

Date

The end date when the deal contract term expires

Deal-Related Fields

Term Start Date

Date

The start date when the deal contract term begins

Deal-Related Fields

Total Attainment

Number

The cumulative value used as input for rate evaluation (e.g., "$150K cumulative sales this quarter")

Deal-Related Fields

Tier Value

Number

Tier value of the deal that will display as whatever the defined field on the component is (e.g. ARR, MRR, Demos Scheduled). This is only used in the case of MFERs and refers to the attainment field.

Earnings Fields

Earnings

Number

The calculated commission or bonus earnings for deals before this rate multiplier is applied

Earnings Fields

Earnings Override

Number

Manual override amount applied to earnings calculations

Plan Date Fields

Plan End Date

Date

The plan's end date

Plan Date Fields

Plan Start Date

Date

The plan's start date

Quota Fields

Quota Attainment

Number

Current attainment percentage against the quota target at the component level

Quota Fields

Quota Cumulative Attainment

Number

Running cumulative attainment amount calculated on the quota level when its component(s) calculates earnings based on the sum of deals. Running count of closed deals when the quota's component(s) calculates earnings based on each individual deal.

Quota Fields

Quota Cumulative Value

Number

Cumulative attainment amount calculated at the quota level rather than on the component level

Quota Fields

Quota End Date

Date

The end date of the quota measurement period

Quota Fields

Quota Period

String

The quota period type (monthly, quarterly, yearly, custom)

Quota Fields

Quota Start Date

Date

The start date of the quota measurement period

Quota Fields

Quota Value

Number

The target quota amount that needs to be achieved for the component

User Field

User Email

String

Email address of the user/rep who is earning commission on the deal

User Field

Variable Pay

Number

The defined variable pay for a given user, not taking into account the component's weighting percentage

Note: % values will appear as decimals in the formula (e.g. 0.98 to represent 98%)

Additional Fields (Integration Source Fields)

You will be able to insert Additional Fields directly for CRM into your formula. These refer to all additional deal fields that the user defined in the app.

  • Important configuration note: You will need to have the additional deal fields defined within QuotaPath first

Mapping your component

Configure mappings for your component as you normally would.

Must haves for your formula to calculate correctly:

  • Ensure that any Additional Fields that you used in writing your formula are also mapped to the component. For example, if you referenced the "Term Length" field or "ARR" value field in your formula, it must also appear in your component mapping (see example below):

  • You will need to have the additional deal fields defined within QuotaPath.

  • We will show warnings if either of these configuration steps is missing.

Helpful tips

SQL syntax

A list of “Allowed Operators” is accessible directly in the Formula writer.

Red highlighting within the formula editor will help alert you to invalid syntax, operators, or fields.

Duplicating formulas

Easily reuse an existing formula across multiple components and plans. Formulas can be cloned when:

  • Duplicating a singular component

  • Duplicating a plan

  • Bulk duplicating components

  • Duplicating components across plans (via Workspace Components)

Viewing the formula throughout the app

Once your formula is built, there are several places throughout the app where you will be able to view it. The (x) symbol will be an indicator that a formula is being applied.

Preview your formula in the component editor drawer:

Hover over the formula symbol in Plan Details or the Earnings page table to view the manually entered formula description:

Plan Details:

Earnings table:

See the full description and formula itself in:

  • The expanded Earnings Details row in the Earnings table

  • The Deal Details page

SQL examples to help you get started

To help you get started writing your formula, we've created a few helpful SQL template examples based on common comp plan scenarios.

Incremental attainment

Example: For every 1% attainment increases, increase the commission rate by 0.1%

Component type: Single rate or Multi rate

(0.1 / ({earnings} / {value})) * ({quota_attainment}) + 1

The {value} field represents something like “ARR”

Contract term length accelerator

Example: Multiply the rate by X if the term duration is greater than Y months

Component type: Single Rate or Multi Rate

CASE 
  WHEN term_duration >= 36 THEN 2
  WHEN term_duration >= 24 THEN 1.75
  WHEN term_duration >= 12 THEN 1.5
  ELSE 1 
END

Matrix

Example: Multiplies rate if term duration is more than 12 or 24 months, and multiplies if deal value is over 30,000

Component type: Single Rate or Multi Rate

1 + CASE
   WHEN {term_duration} >= 12 THEN 1
   WHEN {term_duration} >= 24 THEN 1.5
   ELSE 0
END + CASE
   WHEN {value} >= 30000 THEN 0.5
   ELSE 0
END

The {value} field represents something like “ARR”

Accelerator & Decelerator
​Example: Decelerate the rate for deals less than 12 months and accelerate the rate for deals over 12 months by 0.01% for every month above or below.

Component type: Single Rate or Multi Rate

1 + 0.01 * ({term_duration} - 12)

% of variable based on quota attainment

Example: Based on different quota attainment tiers, a CSM will earn different %s of their quarterly variable

Component type: Single Rate Bonus

CASE
  WHEN {quota_attainment} >= 0.60 AND {quota_attainment} < 0.70 THEN 0.45 * {variable_pay}
  WHEN {quota_attainment} >= 0.70 AND {quota_attainment} < 0.75 THEN 0.1 * {variable_pay}
  WHEN {quota_attainment} >= 0.75 AND {quota_attainment} < 0.80 THEN 0.1 * {variable_pay}
  WHEN {quota_attainment} >= 0.80 AND {quota_attainment} < 0.85 THEN 0.1 * {variable_pay}
  WHEN {quota_attainment} >= 0.85 AND {quota_attainment} < 0.90 THEN 0.07 * {variable_pay}
  WHEN {quota_attainment} >= 0.90 AND {quota_attainment} < 0.95 THEN 0.05 * {variable_pay}
  WHEN {quota_attainment} >= 0.95 AND {quota_attainment} < 1.00 THEN 0.05 * {variable_pay}
  WHEN {quota_attainment} >= 1.00 AND {quota_attainment} < 1.05 THEN 0.04 * {variable_pay}
  WHEN {quota_attainment} >= 1.05 AND {quota_attainment} < 1.10 THEN 0.04 * {variable_pay}
  ELSE 0
END

Defining the entire rate via formula

Example: Divide quota attainment by 2 to define the rate.

  • Set the component rate at 100 so the rate will become whatever quota attainment / 2 is (essentially negating the rate part of the calculation).

Component type: Single Rate or Multi Rate

{quota_attainment} / 2

Earning cap on a per deal level

Example: Cap on a single deal. Rep cannot earn more than $50k commission per deal.

Component type: Single Rate or Multi Rate

MIN(1,
    {variable_pay} / ({value} * 
({earnings} / {value})))

The {value} field represents something like “ARR”

Did this answer your question?