If you are an insurance agent, track each commission line with the policy reference, the commission type (first-year or renewal), the date you expect the credit, the date it arrives and the amount in each case. Then match your tracker with the insurer's commission statement and the bank credit. The difference between expected and credited is where late and missing payments show up.
Which columns should the tracker have?
| Column | What to record |
|---|---|
| Policy reference | A policy number or your own client code, not full personal details |
| Insurer | The company that pays the commission |
| Commission type | First-year, renewal or any other category in your statement |
| Premium date | The date the premium was received |
| Expected amount in ₹ | What you calculated from your own terms |
| Expected date | When you think it will be credited |
| Statement amount in ₹ | The figure on the commission statement |
| Credited date | The bank credit date |
| Status | Pending, Statement received, Credited or Disputed |
Rates and terms come from your agency agreement and the insurer's communication, so write them from your own documents. Do not copy a rate from someone else.
How do first-year and renewal credits differ in the tracker?
They usually arrive on different timelines and sometimes in different statements, so keep them in separate rows with a clear type label. With a Type column in C and an Expected amount in E, you can total each type.
- First-year pending: =SUMIFS($E$2:$E$500, $C$2:$C$500, "First-year", $I$2:$I$500, "Pending")
- Renewal pending: =SUMIFS($E$2:$E$500, $C$2:$C$500, "Renewal", $I$2:$I$500, "Pending")
- Count of disputed lines: =COUNTIF($I$2:$I$500, "Disputed")
These work the same in Excel and Google Sheets. Use the same status words every time, since misspellings split the totals.
How do I reconcile with the commission statement?
- When the statement arrives, tick off each line against your tracker by policy reference.
- Write the statement amount next to your expected amount.
- Mark any line that is lower, missing or not in your tracker.
- When the bank credit arrives, check the total against the statement total, allowing for any deductions the statement shows.
- Move every matched row to Credited and fill in the date.
Hypothetical example: you expected ₹1,850 for a renewal and ₹6,200 for a first-year policy, a total of ₹8,050. The statement shows ₹1,850 and ₹5,900, which is ₹7,750. The shortfall of ₹300 goes into a query list with the policy reference, and the bank credit is checked against 7,750, not 8,050.
What if a deduction appears on the statement?
Some statements show tax or other deductions. Add a column called Deductions and note what the statement says, rather than quietly adjusting the credit. How such deductions are treated in your return is for your CA to say, so keep the statements as they come.
Where does the bank statement fit?
Commission credits can arrive in irregular amounts and with similar narrations. After you pick the account in SpendixAI and upload your bank statement, you can review the rows and see all credits in one list, which helps when you tick off what has landed. The commission detail by policy stays in your tracker.
What should I do about missing renewal credits?
Renewal commission depends on the policy continuing, so a lapsed or late-paid premium can mean a missing credit. Before you query the insurer, check your own tracker: was the premium actually paid, and when? Write down the policy reference, the premium date and the amount you expected, then raise the query in writing. Keep the reply with the tracker row. Over a few cycles you will see which insurers pay promptly and which need regular chasing.
Monthly routine
- Update statuses for every line
- List policies where renewal date is coming up
- Check pending lines older than the usual wait for your insurer
- Send a query for each missing line with the policy reference
Useful ideas start with a clearer picture of your own spending.
Explore your reports

