A simple commission record has one line per sale: the bill, the agent, the amount, the agreed rate, the commission worked out, the date you paid it and the proof of payment. With that, you pay each commission once, only on the sales that qualify, and you can answer an agent's question in a minute.
What columns does the record need?
- Bill or order number and sale date.
- Customer name, so two agents are not paid for the same customer.
- Agent or broker name.
- Bill amount and the agreed rate, written as a percentage or a fixed sum.
- Commission amount, with the formula =E2*F2 if E is the bill amount and F the rate.
- Date the customer paid you in full, if your agreement ties commission to receipt of money.
- Date you paid the agent, the mode (UPI, bank transfer, cash) and the reference number or a note of the receipt.
- Remarks: part return, disputed bill, rate changed.
Decide with each agent, and write down, whether commission is due when the sale is booked or only after the customer has paid. This single rule prevents most arguments.
A made-up month with three agents
Invented figures to show how the record works.
| Agent | Sale | Rate | Commission | Paid on | Proof |
|---|---|---|---|---|---|
| Ramesh | ₹2,40,000 | 2% | ₹4,800 | 10 Oct | UPI ref noted |
| Suma | ₹1,50,000 | 3% | ₹4,500 | 10 Oct | UPI ref noted |
| Imran | ₹80,000 | 5% | ₹4,000 | Pending | Customer yet to pay |
| Ramesh | ₹60,000 | 2% | ₹1,200 | Pending | Customer yet to pay |
Check: 240,000 times 2% is 4,800; 150,000 times 3% is 4,500; 80,000 times 5% is 4,000; 60,000 times 2% is 1,200. Total commission earned is 14,500. Paid so far is 4,800 plus 4,500, which is 9,300, so 5,200 is still to pay, and only once those two customers pay in full.
How do I avoid paying a commission twice?
Double payment usually happens when the same bill appears in two months, or the agent is paid in cash and again by UPI. Two habits stop it. First, never pay without the bill number, and tick the line as paid at that moment, not at the month's end. Second, add a check column. If the bill number is in column A, put this in column K, where rows 2 to 200 hold your records: =IF(COUNTIFS($A$2:$A$200,A2,$D$2:$D$200,D2)>1,"Duplicate","") where D is the agent. Any line that says Duplicate is a line to look at before paying.
What if the goods are returned or the bill is cancelled?
Agree this before the first sale: does commission come back? Say Suma's customer returns goods worth ₹30,000 from her ₹1,50,000 bill. At 3%, the commission on the returned part is ₹900, so her commission becomes ₹3,600. If you have already paid ₹4,500, write a note of the ₹900 as an adjustment against her next payout, and keep her informed in writing rather than just short-paying.
How much does each agent still have coming?
If column G holds commission amounts, column D the agent and column I the paid date, the amount still due to Ramesh is =SUMIFS($G$2:$G$200,$D$2:$D$200,"Ramesh",$I$2:$I$200,""). The empty string in the last criterion matches rows where the paid date is blank. Do the same for each agent and read the totals before you make month-end payments.
When the commissions go out by UPI or bank transfer, they appear in your statements as expenses. SpendixAI lets you attach a receipt to a transaction and add a category of your own, such as commission paid, so the statement line and the proof stay together.
Month-end checklist
- Total commission earned, from the commission column.
- Total paid, and the proofs for each payment.
- Total pending, split by agent, with the reason it is pending.
- Any sale that was returned or cancelled this month, and the adjustment.
- A signed or messaged confirmation from each agent that the month's figures match.
Useful ideas start with a clearer picture of your own spending.
Explore your reports
