CEI calculator
- Collected in the period
- $850,000
- Collection effectiveness index
- 78.7%
Formula: CEI = (opening AR + credit sales − closing AR) / (opening AR + credit sales − closing current AR) × 100
Example
- Opening receivables ($)
- $600,000
- Credit sales in the period ($)
- $900,000
- Closing receivables ($)
- $650,000
- Closing receivables not yet due ($)
- $420,000
- Collected in the period
- $850,000
- Collection effectiveness index
- 78.7%
What the calculator works out
The calculator takes four numbers from your receivables records. Opening receivables is the AR balance at the start of the period. Credit sales in the period is the total you invoiced on credit, not cash sales. Closing receivables is the AR balance at the end. Closing receivables not yet due is the part of that closing balance with a due date still in the future.
The formula adds opening receivables and credit sales, then subtracts closing receivables. That gives the cash collected in the period. The bottom half does the same addition but subtracts only the closing receivables not yet due. That leaves the collectible balance, meaning what was actually due from customers during the period. Divide the first figure by the second and multiply by 100 to get the index.
You will find opening and closing receivables on your AR ledger or aged receivables report at the period start and end. Credit sales come from the sales journal or a revenue report filtered to credit invoices. The not yet due figure comes from the part of the aging report that shows invoices with due dates still in the future. Check that your aging report separates not yet due from overdue, because a generic current bucket can include invoices that are already due.
A worked example
Take opening receivables of $600,000, credit sales in the period of $900,000, closing receivables of $650,000 and closing receivables not yet due of $420,000. Those are the four inputs the calculator needs.
The first part of the formula combines opening receivables and credit sales, then removes closing receivables. The calculator reports collected in the period: $850,000. That is the cash the collections team brought in during the period.
The second part uses the same opening receivables and credit sales but removes only the $420,000 not yet due. That gives the collectible balance the team was working against. The calculator reports a collection effectiveness index of 78.7%. In plain terms, 78.7% of the collectible balance was collected in the period, with the remainder still sitting in closing receivables.
Reading the result
A CEI of 100% means you collected everything that was collectible in the period. Closer to 100% is better. The index measures collections rather than how long receivables sit, so use it to see whether cash coming in kept pace with what customers actually owed.
CEI is less distorted than DSO by a jump in sales near period end. A large credit sale late in the month raises closing receivables and can push DSO up even when collections were fine. CEI works from opening receivables, credit sales and the not yet due slice, so a late sales spike is less likely to make a good collections month look bad.
Measure monthly and compare the same months year on year. One month can move with a single large customer, a billing cutoff or a holiday. Year on year comparison keeps seasonality in view and shows whether the trend is real.
Apply unapplied cash before measuring. Cash sitting in a suspense or unapplied account is collected cash that the formula will not see, so the index will look worse than the team's actual work. If you borrow to cover a receivables gap, compare the borrowing rate with your own cost of funds.
Where the inputs go wrong
One common mistake is a period mismatch. Opening receivables come from the 1st, closing receivables from the 25th, and credit sales from a different month. The formula assumes one clean period. Pull all four numbers for the same start and end dates and write those dates on the report.
Another mistake is using total revenue instead of credit sales. Cash sales inflate both the top and bottom of the formula. Use credit invoices only. If your sales journal includes cash sales, filter them out. Keep the tax basis consistent too: if receivables include sales tax, credit sales should include it.
The not yet due figure is the one most often wrong. A current bucket in an aging report may include invoices that are due today, or it may include overdue invoices that an account manager moved. Check a sample against due dates. If not yet due is too low, the denominator grows and CEI falls.
Unapplied cash, credit memos and write-offs also distort the result. Unapplied cash makes collected cash invisible. Credit memos and write-offs reduce closing receivables without cash arriving, so decide how to treat them and keep that treatment consistent. Foreign currency receivables should be translated on a consistent basis. A single large invoice can swing a monthly CEI, which is another reason to compare the same months year on year.
Questions
What is a good collection effectiveness index?
There is no universal pass mark. Closer to 100% is better because it means you collected more of what was collectible. Compare your own monthly results with the same months year on year, and look for a trend rather than one number.
How is CEI different from DSO?
CEI measures what share of collectible receivables was collected in a period. DSO measures how long receivables sit. A late sales spike can distort DSO, while CEI is less affected because it works from opening receivables and the not yet due slice.
Why is my CEI over 100%?
A result over 100% usually means the inputs are inconsistent. Common causes are a closing receivables figure that is lower than the cash actually collected, credit memos or write-offs that reduced AR, or a not yet due figure that is too high. Check the period dates and the not yet due classification first.
Should I use gross or net credit sales in the CEI formula?
Use the credit sales figure that matches your receivables. If receivables are recorded gross of sales tax, use gross credit sales. If they are net, use net. Apply the same basis to opening receivables, credit sales and closing receivables.
Updated Nov 9, 2026. Figures on this page are calculated by ERPSav from the definitions shown. The explanatory text is drafted with AI from those figures and checked automatically before it is published. General information only, not legal, tax or financial advice.