Skip down to the research record

Commission formula field: 3 checks before deal close

Quota Forecast Editorial Team · Published · 13 min read

Before deal close, check that your commission formula reads the right field, matches your plan's arithmetic, and handles return types and empty inputs.

Key takeaways

  • Confirm the formula reads the intended deal value field.
  • Compare its result with a hand calculation.
  • Test the return type and empty-input result.

A formula field is one way to get there, because it calculates a value from other fields. The trick is making sure the calculation reads the right inputs and returns the right kind of result, because a formula that runs without errors can still give a wrong number.

HubSpot’s help page on this kind of property is titled “Create calculation and rollup properties,” and it says: “Create properties that populate data based on calculations from other properties.” Freshsales calls its version a formula field and works through a commission example, which this post follows.

The Freshsales article rests on one assumption: “Let’s consider that the commission value for a salesperson is 5% of the deal value.” The checks below use that assumption as a stand-in. Your own plan may use a different rate or a different base, so replace the 5% with the terms in your plan before you test anything.

Why a figure before close needs extra care

A figure shown before close needs extra care because the deal can still change after the figure is shown. A deal record is a working document until it closes. The deal value may be revised, the close date may move, and the stage may change several times before anyone signs. A commission figure that looks final at one of those points can mislead the people who read it, especially if they treat it as a promise rather than a calculation.

The point of the 3 checks is to separate two things: whether the formula is working as written, and whether the number it produces is the number the plan intends. The first is a question about the formula. The second is a question about the plan.

Set up a test deal before you check anything

Set up a test deal with a value you can calculate by hand, and keep it apart from live pipeline records. Testing on a live deal means that a mistake in the formula could show up in front of the people who rely on the pipeline report. A test deal avoids that. Name it clearly, such as “Commission test, do not use,” so that no one mistakes it for a real opportunity.

Pick the value for the test deal before you open the formula. Write it down, and work out the commission figure by hand so you have an expected answer. Once the formula shows a result, you have something to compare it with, and a mismatch tells you that something needs a closer look.

Also keep the test deal’s inputs fixed while you check the formula. If the deal value, the stage, and the close date all change during one test, you cannot tell which change caused a difference. Change one input at a time, and write down each change with the result it produced.

Check 1: confirm the formula reads the right deal field

The formula should read the deal value field on the deal record. The Freshsales example builds its formula in the deal module, and the article gives the reason: “So we’ll create the formula in the deal module, to access the “Deal value” field.” (Freshsales article)

The article also defines “Field” as “This refers to the default fields in the chosen module.” (Freshsales article) That points to a practical check: the field the formula reads has to belong to the module you chose. Open the formula, find each field it names, and confirm each one is the deal value you mean. A field with a similar name can be a different field. If the formula sits in a different module, test whether it can still reach the value you need before you rely on it.

Next, test whether the figure follows the deal value. On a test deal, change the value, then check that the commission field changes with it. If the commission figure stays the same after a change to the value, the formula may not be reading the field you think it is, and that is worth a closer look before you go further.

Also look at the record the formula lives on. A formula field on a deal record shows values for that deal, so a test on a different deal may not match what the team sees on a live one. Open at least two deals and confirm the figure changes from one to the other as the deal value does.

Write down the exact field names the formula uses, and keep that list with your test notes. If someone changes a field later, the list shows which part of the record the formula depends on, and the check can be repeated from there. For related record checks, see deal field edit history.

Check 2: confirm the percentage math matches your commission plan

The arithmetic should match your commission plan exactly, and the Freshsales example shows a 5% rate written as a fraction. It turns that rule into a formula: “So our formula would be Formula = (5/100) * Deal value.” (Freshsales article)

The rate has to appear in the formula in the right form. If the rate is written as 5 with no division by 100, the result is 100 times the 5% commission. That is the difference this check is looking for. Read the plan’s rule in words, read the formula in symbols, and confirm they describe the same calculation.

Test the arithmetic with a deal value you can work out by hand. Multiply it by 5, divide by 100, and compare that answer with what the formula field shows. If they differ, fix the formula before anyone sees the figure. Repeat the test with a second value, so that one correct result is not mistaken for proof that the formula is right.

Then check the parts of your plan that the example does not show. The Freshsales example covers one rate applied to the deal value. If your plan has tiers, caps, or different rates for some deals, the example does not cover them, so add each one to the formula only after you have tested the basic case, and test each addition the same way.

Decimal places are a separate detail. A commission figure may need to be shown to a set number of decimal places, and the arithmetic can produce more digits than the screen shows. Decide how the figure should be rounded before the team compares it with the plan, so that two people do not get different totals from the same deal. See also deal amount rounding.

Illustrative example: calculate the expected result

For an illustrative deal value of 100 and a commission rate of 5%, the expected commission is 5: multiply 100 by 5, then divide by 100. Compare that answer with the test field’s result before changing another input.

Check 3: confirm the return type and what the field shows when it has no input

The return type should match the kind of value the commission field should show, and you should test what the field shows when its input is empty. The article lists the term this way: “Return type: The type of value that will be returned when the formula is computed.” (Freshsales article) For a commission figure, check that the return type is a numeric type, so the result can be added, compared, and reported like other amounts. A text result can look right on screen and still be unusable when someone tries to total it.

The article’s example also says that the formula field “Commission based on Deal Value” will then show the value as 0. (Freshsales article) Test empty-input behavior directly: enter a test deal with no deal value and look at what the commission field shows, then enter a value and compare the result with your hand calculation.

A 0 on a deal record can look like a real figure. Decide ahead of time how your team will read a zero, and make sure it cannot pass for a final commission amount by accident. Write that decision into your test notes so the team reads the field the same way each time.

Write the empty-input result in your test notes in plain words, not just as a screenshot. A note that says what the field showed, which input was empty, and what the team agreed to do with that result is easier to use later than an image, because a reviewer can read it without reproducing the test.

Walk through the Freshsales formula step by step

Walking through the Freshsales formula step by step shows where each of the 3 checks applies. Start with the expression as the article wrote it: Formula = (5/100) * Deal value. (Freshsales article) The parentheses group 5/100 so that the division is done as one step. The formula then multiplies that result by the Deal value field, so the commission is the deal value multiplied by 5/100. This is the first place to look, because the expression depends on which field is named as Deal value.

Next, apply the expression to a deal value you can work out by hand. Multiply that value by 5, divide by 100, and compare the answer with the 5% commission in the plan. This is the second check, and it is where a rate typed in the wrong form shows up.

Last, look at the result type and at what happens with no input. The article describes the return type as the type of value returned when the formula is computed. (Freshsales article) The return type is where you confirm what kind of value the field produces, and that is the third check.

Mistakes to watch for before you trust the figure

This post points to 3 mistakes to watch for: a wrong field, a rate in the wrong form, and a 0 result that could be mistaken for a real figure.

A wrong field is hard to notice because the formula still runs and still produces a number. The Freshsales example builds the formula in the deal module to access the “Deal value” field, so a formula that reads a different field can look just as finished.

The second mistake is the rate written in the wrong form. A 5 with no division by 100 gives a figure 100 times the 5% commission, and the mistake can be easy to miss when the number looks plausible on its own. Check the rate against the plan and the arithmetic against a hand calculation, as described in Check 2 above.

The third mistake is a 0 result that gets read as a real commission. The Freshsales example says the formula field “Commission based on Deal Value” will then show the value as 0, and the quoted text does not say when that happens, so the safest step is to test the empty case and decide in advance how a zero should be read.

Who should review the figure

The person who owns the commission plan should review the rate and terms, and the person who maintains the CRM should review the field setup. Those are two different reviews. The plan owner checks that the formula says what the plan says, and the CRM owner checks that the formula points at the right fields and that the field types are set up as intended. Keeping the reviews separate means a mistake in one area is less likely to be approved because the other area looked fine.

Write the sign-off down. A short note with the date, the reviewer, the rate used, and the test results gives the team a record to check against later. It also makes clear who answered for each part of the figure, which matters if the number is questioned after the deal closes.

Quick reference for the 3 checks

The table below lists each check, the question it answers, and what to look for in the result.

CheckQuestion it answersWhat to look for
1. FieldDoes the formula read the deal value I mean?The formula sits in the deal module, and the figure changes when the test deal’s value changes
2. ArithmeticDoes the formula match the plan’s rate?A result equal to the hand calculation on two test values
3. Return type and empty inputIs the result the right kind of value, and what does it show with no input?A numeric return type and an agreed reading of any 0

When to rerun the checks

Rerun all 3 checks whenever the rate, the formula, the field, or the return type changes, and before the figure leaves the team. A change to any one of those parts can alter the result, and a check that passed earlier says nothing about the formula as it stands now.

The same applies when the team starts to rely on the figure in a new way. If the number moves from an internal forecast into a document that someone outside the team reads, rerun the checks first and note the date. The figure is only as reliable as the last test that was run on it.

Run the 3 checks in order before close

Run the checks in the order given, on a test deal, before anyone treats the commission figure as final. Start with the field. Open the formula, confirm it reads the deal value you mean, and confirm it sits in the deal module as the Freshsales example describes. Next, check the arithmetic by hand with one test value, then with a second. Last, check the return type and the empty-input result, and write down what the field shows in each case so the team knows what to expect.

Keep a short record with the rate from the plan, the formula as written, the test values you used, and the results. When the plan changes, rerun all 3 checks. A result from the old rule does not show whether the new rule works, so the record should be updated along with the formula.

Once the 3 checks pass on the test deal, apply the same formula to a live deal and repeat the hand calculation there. A pass on the test deal does not guarantee that the live record holds the same inputs, so look at the live deal’s values as well.

Today, create the clearly named test deal and write down your expected commission before opening the formula.

What these checks do not cover

Do these checks confirm that the commission plan is right?

These checks confirm that the formula does what it was written to do, but they do not confirm that the commission plan itself is right. A formula can be correct arithmetic and still apply the wrong rate, so someone who owns the plan has to confirm the rate and the terms. The checks also do not cover how the figure appears in reports or how it is used elsewhere in your company. Those are separate questions, and this post does not address them.

Can you use these checks in another CRM?

Yes, the same 3 questions apply in another CRM: which field the formula reads, whether the arithmetic matches the plan, and what the return type and empty-input result are. Check that product’s own documentation for exact field names and behavior, and do not assume it matches the examples in this post.

Sources