Use Marginal Payout Rule Functions

Learning Objectives

After completing this unit, you’ll be able to:

• Explain a sample range table for the mamount function.
  
• Describe the mamount function syntax.
  
• Explain a sample range table for the mpercent function.
  
• Describe the mpercent function syntax.
  

Explore the Mamount Function

The mamount function is a commission-specific function in Spiff that calculates tiered commission amounts. It doesn't return a commission rate, but rather a commission amount. In Spiff, you need a range table to use this function.

Here's a sample marginal payout range table for the mamount function.

Let’s consider an example to understand how it works. A rep has closed $145,000 in annual recurring revenue (ARR). This rep closes a new deal with $20,000 in ARR. What commission rate would this deal receive? $5,000 of the deal is allocated to Tier 1 and commissioned at 4%, while $15,000 is allocated to Tier 2 and commissioned at 6%.

This results in a blended rate. In this example, the calculation works out this way: (0.04 * 5000 + 0.06 * 15000) / 20000 = 5.5%. The function won't return 5.5% as a result of our $20,000 deal, but will return $1,100, which is 5.5% of $20,000. It multiplies the blended rate by the deal amount to return the commission.

For every deal closed, the mamount function does three things.

1. It determines the portion to distribute to each tier of the range table. These allocations are determined by calculating how much of the value falls within each tier range.
   
2. It calculates the payout for each tier.
   
3. It sums up the results of these formulas.
   

The syntax for the function is mamount(input_value, range_table, initial_value, return_column).

Here’s what each argument means.

• input_value is the amount that is checked against the range table associated with the function, such as the ARR from a closed deal.
  
• range_table is a range table that’s created for the function. The syntax turns purple when the table is recognized.
  
• initial_value is an optional starting value that affects which ranges the input value falls into.
  
• return_column is an optional argument that returns a specified return column. If multiple return columns exist on a range table, use this argument. If only one return column exists on the range table, it will be selected by default without this argument. A specified return column is wrapped in quotation marks, such as “Corporate AE Rate”.
  

Let’s pause to learn more about the optional arguments.

First, consider initial_value. In the previous example, the rep had already closed $145,000 in ARR when a new $20,000 deal closed. Without knowing the initial_value of $1450,000, Spiff would not know that $5,000 in ARR belongs to Tier 1 and $15,000 in ARR belongs to Tier 2. To find the right initial_value, we often create an accumulated variable, which tracks prior progress toward tier breakpoints. We cover accumulated variables in the next unit.

Now, consider return_column. Using multiple return columns on a range table is useful when the same value ranges (lower and upper bounds) are used across different plans that need to return different rates.

The mamount function is helpful, but it isn’t the only marginal commission function in Spiff.

Explore the Mpercent Function

The mpercent function is another commission-specific function in Spiff. It calculates tiered commission rates in a marginal fashion, requires a range table, and returns a rate from the specified return column.

Here's an example of a marginal payout range table for the mpercent function.

The upper- and lower-bound values in this table represent attainment percentages. When attainment is between 0% and 50%, deals or opportunities are commissioned at 5%. When attainment is between 50% and 100%, deals or opportunities are commissioned at 7%. But this 7% rate doesn't apply to the opportunities that closed between 0% and 50% attainment.

For example, if a rep is already at 48% attainment and closes a new deal representing 5% of their total attainment, what commission rate would this deal receive? A portion of the deal would be allocated to Tier 1 and commissioned at 5%, while the rest would be allocated to Tier 2 and commissioned at 7%. This results in a blended rate somewhere between 5% and 7%. Here’s the calculation: (2% * 5% + 3% * 7%) / 5% = 6.2%.

For every deal closed, the mpercent function determines the portion to distribute to each tier of the range table. These allocations are determined by calculating how much of the value falls within each tier range. Then it calculates the payout rate for each deal.

The syntax of the mpercent function is mpercent(input_value, range_table, initial_value, return_column, return_value_when_input_value_is_zero).

Let’s examine each argument.

• input_value is the value checked against the range table associated with the function. This could be the percentage of quota retired per deal or an ARR amount from each deal. The mpercent function can handle both percentage values and dollar amounts. It's important to note that the input_value needs to match in type to the ranges in your table. For example, you can’t feed a dollar amount into a table that contains ranges in percentages.
  
• range_table is the range table that’s created for the function. The syntax turns purple when the table is recognized.
  
• initial_value is optional, and is often referred to as a prior-accumulated variable. It's a starting value that affects which ranges the input value falls into. This is used to include previous progress toward tier breakpoints, such as previous progress toward a quarterly quota, where commissions are calculated monthly.
  
• return_column is an optional argument that returns a specified return column. If multiple return columns exist on a range table, you use this argument. If only one return column exists on the range table, it'll be selected by default without this argument. A specified return column is wrapped in quotation marks, such as “Corporate Rate”.
  
• return_value_when_input_value_is_zero is an optional argument that avoids mathematical problems. Because Spiff is calculating a weighted average for deals that cross our tier boundaries, there's some mathematical division happening in the background. If the input value (ARR or some other value) is 0, it creates a mathematical impossibility because you can’t divide by zero. Spiff’s default setting in this case is to return a 0 instead of an error. If you don’t want to return a 0, specify a different value in this argument. The argument accepts strings, numbers, currencies, and other calculations.
  

In the example given previously, a rep was already at 48% quota attainment. ‌Closing a new deal that represents 5% of their total quota attainment would place some of the deal in Tier 1, and some of the deal in Tier 2. ‌The initial_value that can be used in this case would be the current 48% quota attainment. ‌Using this as the initial_value allows Spiff to allocate each deal to the appropriate tier.

Consider this datasheet and the formula =mpercent(AttainmentPercent, CorporateAE_AttainmentRate, QuarterlyARRPriorAttainment).

In the datasheet, AttainmentPercent is calculated per deal. ‌The QuarterlyARRPriorAttainment is the accumulated variable that tracks the attainment progress for a rep just before the deal that was closed. The first deal had an attainment percentage of 5.00%. ‌Because this was the first deal of the quarter, the QuarterlyARRPriorAttainment is 0.00%. The second deal shows a deal attainment of 12.48%, and a prior attainment of 5.00%, as expected.

Now that you’re familiar with the two functions, let’s use one in the next unit to build a marginal payout rule.