How we built a policy-level commission engine in Zoho CRM for a multi-level insurance agency

The challenge

Commissions in a multi-level insurance agency are a bookkeeping problem disguised as a sales problem. One policy can touch a writing agent, a split agent, the chain of uplines above each of them, and the agency itself. Each party is paid on a different percentage. The carrier then pays over time in pieces: an advance up front, earned commission later, and sometimes a chargeback or reversal if the policy lapses.

Three things make this hard in a CRM:

• The rules change under old data. Agents get promoted. Carriers revise rate schedules. A policy written last year must still be paid on last year's terms, not today's.
• Money arrives as many small rows. Totals per policy, per agent and per agency have to be rebuilt every time a payment row is added or edited.
• The commission step lived outside the CRM. Carrier payments and the commission on each payment row were handled by a tool outside Zoho, so the CRM held the records but not the logic.

The agency needed the CRM to answer one question reliably for any policy: who is owed what, and how does that compare with what the carrier has actually paid?

What we built

We designed the engine around one principle: a policy should carry its own commission context, frozen at the moment it was written.

1. A policy record that knows its agents and its product

Policies live in the Deals module. Each links to a product, a writing agent and an optional split agent. Layout rules keep the form short: the split agent field appears, and becomes mandatory, only when the split flag is Yes. Life-specific and annuity-specific fields appear only for the matching product category. One layout serves every product type.

2. A snapshot that freezes the hierarchy and the rates

This is the part that makes the rest trustworthy. When agents or the product on a policy change, a custom function writes a subform on that policy. It stores:

• the upline chain above the writing agent and the split agent, level by level, with each agent's level at that time
• the product's year-by-year agent rates and agency rates

Later promotions, restructures or rate-sheet changes do not touch policies already written. A button on the policy lets an admin rebuild the snapshot on demand. Keys are numbered by role and depth, so a single subform holds both agents' chains and every rate row.

3. Payout percentages from the agent's level

A function reads each agent's producer level and writes the writing and split payout percentages onto the policy. A second function walks up the hierarchy to find the top-of-line agent for each side and records it on the policy for reporting.

4. Carrier money as rows, totals as a rollup

Agent payments and agency payments are separate modules, each related to the policy. Every row carries a payment type (advance, earns, chargeback, reversal and others), a role and an amount. A workflow on each module runs one rollup function whenever a row is created or edited. That function sums rows by role and type and writes roughly 25 totals back to the policy: earned, advanced, chargeback and net, separately for the writing agent, the split agent and the agency.

Having one rollup behind both payment modules means one code path, and one place to fix a bug.

5. Projected versus actual, on the same record

Twenty-four formula fields combine the rates, the percentages and the rollup totals into projected revenue, projected commission and projected agency profit. The rollup supplies the actuals. Anyone opening a policy sees what should have been paid next to what has been paid.

6. The wiring around it

• Nine active workflow rules on the policy module handle recalculation when agents change, the split percentage, copying category and carrier from the product, setting the commission basis from the annuity amount, and team notifications.
• A validation rule checks policy-number format with a custom function.
• Six profiles control who sees and edits commission data.

A simplified illustration

This example uses round numbers and is not client data.

A policy is written by an agent whose level pays 50%, on a product whose first-year agent rate is 100% of the commissionable premium. The carrier sends an advance, then later an earned payment, then a chargeback when the policy lapses early. Each row lands on the policy. The rollup adds advances and earns, subtracts the chargeback, and updates the net total for the writing agent and the agency in one pass. If the agent is promoted next month, this policy still shows the 50% that was frozen when it was written.

How we approach a build like this

• Read the whole system before designing. We reviewed the full function library, a couple of hundred functions, and isolated the dozen or so that actually carry commission logic. The rest were left out of the design.
• Audit our own work. When we reviewed the build for reuse, we found defects and wrote them down: totals written to fields that did not exist, a subform field written under the wrong name, two snapshot key conventions, and values hard-coded into function code. We fixed them in the design rather than copying them forward.
• Make failures visible. The functions caught their own errors, so the platform's failure log stayed empty even where failures were likely. The next version writes failures to a log that someone will see.
• Test on a case that exercises every path. Our acceptance test is one writing agent, one split deal, a three-level upline, and an advance, an earns and a chargeback, with every total on the policy checked by hand.

From one agency's build to a reusable package

We are now generalising the engine for other agencies. Level percentages move into a configuration module. The top-of-hierarchy name and the depth limit move into a settings record. Carrier lists start empty and agencies add their own. We are also adding the missing per-payment commission calculation and a statement import. The aim is that a new agency configures the engine rather than editing code.

What changed

• Carrier payments roll up into totals on each policy automatically, split by writing agent, split agent and agency.
• Old policies keep the hierarchy and rates that applied when they were written, however the organisation changes afterwards.
• Every policy shows projected and actual commission side by side, so gaps between expected and paid are visible per policy.
• Commission rules, meaning rates, percentages and hierarchy, now sit in the CRM as data a business user can read, not in someone's head or an outside tool.

Key takeaways

• Freeze the rules at the moment of the sale. If history can be rewritten by a promotion or a rate change, no commission total can be trusted.
• Put the business rules in data, not in code. Percentages, level names and the top of the hierarchy belong in a settings module that an admin can change.
• Give each calculation one home. One rollup function behind every payment module is easier to test, fix and explain than logic scattered across workflows.
• Treat silent failure as a defect. If your automation catches its own errors, give it somewhere to report them.

Working with Softily

If your business pays commissions through a hierarchy, or your Zoho setup has outgrown the logic built into it, we can map what you have, find what is fragile, and rebuild it so the rules are visible and testable. Get in touch to talk through your setup.