An accrual reconciliation template in Excel lists every accrued expense that makes up the ledger balance, with its estimate, its support and the month it was booked. It then checks that last month's reversing entries actually reversed, and rolls the balance forward from opening to closing so the total ties to the general ledger to the cent.
Columns of an accrual reconciliation template in Excel
An accrual reconciliation template needs one row per accrued expense and enough columns to answer four questions about each row: what it is for, how much, why that amount, and when it goes away. Anything beyond that slows the preparer down without helping the reviewer.
A workable layout uses these columns, left to right:
| Column | What goes in it | Example entry |
|---|---|---|
| Vendor or payee | Who will eventually bill you | Example Utilities Co |
| Description | The service received but not yet invoiced | Electricity, March |
| Period of service | The month or dates the cost belongs to | 2026-03-01 to 2026-03-31 |
| Amount accrued | The estimate booked this month | 1,850.00 |
| Basis | How the estimate was made | Average of last three bills |
| Support reference | File name or document number | UTIL-0326-est.pdf |
| Reversing? | Yes for auto-reversing entries, No for standing accruals | Yes |
| Expected settlement | When the invoice or payment should arrive | Mid-April |
| Date booked | The journal entry date | 2026-03-31 |
The values above are examples. Keep the template on one tab per month, with a header block at the top for the entity, the account number, the balance date, the preparer and the reviewer. A second tab holds the rollforward described further down, so the list and the proof sit side by side.
Formulas worth adding
Three formulas do most of the checking. A SUM of the "Amount accrued" column gives the schedule total. A cell that subtracts the trial balance figure from that total gives the difference, and conditional formatting turns it red when it is not zero. A third column computes days open from "Date booked" to the balance date, which feeds the stale accrual review.
Accruals have a mirror image on the asset side. Payments made before the service is received sit in prepaid expenses, and the prepaid account reconciliation template and amortization schedule uses a similar layout with the timing reversed.
What support should each accrued expense line carry?
Each accrued expense line should carry the document that justifies the amount: a contract rate, a usage report, a timesheet, a purchase order, or the vendor's invoice once it arrives. An accrual without support is a guess, and a reviewer cannot tell a careful guess from a convenient one.
Support falls into three grades, and the template should say which one each line has:
- Invoice received after month end. The strongest support. The invoice shows the exact amount for the period, so the accrual can be trued up before the books close.
- Contract or rate card. Good support for fixed fees such as rent, retainers or subscriptions, where the amount is known even though the bill has not arrived.
- Estimate from history or usage. Acceptable for utilities, freight or professional fees, provided the basis column says how the figure was built.
Replacing the estimate
The later invoice is the best test of the estimate. When it arrives during the close, compare it with the accrual. If the gap is small, note it and move on. If the gap is large, adjust the accrual before the balance is final and record why the estimate missed, because the same method will be used again next month.
A useful habit is to keep last month's estimates next to the invoices that settled them. After a few closes the pattern is obvious: one vendor always bills 20 percent more than the estimate, another always less. That history makes the next estimate better, and it gives a reviewer a reason to trust the basis column.
The accrual reversal check: did last month's entries reverse?
The accrual reversal check proves that every auto-reversing entry from last month posted its reversal on the first day of this month, once, and for the same amount. A reversal that fails leaves the old estimate on the books while the real invoice is also recorded, so the expense is counted twice.
Most accounting systems let you mark a journal entry as reversing, and the system then books the opposite entry on the first day of the next period. That feature is reliable, but the setting is easy to forget. One entry saved without the box ticked is enough to break the account.
Running the check
Put last month's reversing accruals on the left and this month's reversal entries on the right, then compare:
- Same count. Every reversing line from last month has a matching reversal.
- Same amount. Each reversal equals the original to the cent.
- Right date. The reversal posted on the first day of the new month, not at month end.
- No second reversal. A manual reversal on top of an automatic one doubles the reversal and can leave the account with a debit balance.
In Excel, a COUNTIFS on amount and vendor across the two lists finds the lines that are missing on either side. The count of unmatched lines should be zero before you move on to the rollforward.
Standing accruals, such as a year-end bonus built up month by month, do not reverse. Mark them "No" in the reversing column so they are excluded from this check and tested through the rollforward instead.
Accrued expenses rollforward from opening balance to ledger
An accrued expenses rollforward starts from last month's reconciled balance, subtracts the reversals and payments, adds the new accruals, and arrives at a computed closing balance. That computed figure must equal both the schedule total and the trial balance. When all three agree, the account is reconciled.
Here is a worked example for March. Every figure is an example. The opening balance of 26,400.00 was made up of 18,400.00 in reversing accruals and an 8,000.00 standing bonus accrual.
| Line | Amount |
|---|---|
| Opening balance, February 28 (reconciled) | 26,400.00 |
| Less: reversals due March 1 (every reversing line on February's schedule) | (18,400.00) |
| Add: new reversing accruals at March 31 | 21,150.00 |
| Add: bonus accrual for March | 2,000.00 |
| Less: accruals paid directly against the account | 0.00 |
| Computed closing balance | 31,150.00 |
| Schedule total (list tab) | 31,150.00 |
| Trial balance, accrued expenses | 33,550.00 |
| Difference | (2,400.00) |
The ledger is 2,400.00 higher than both the rollforward and the schedule. The reversal check finds the cause: a February utilities accrual of 2,400.00 was listed as reversing on the schedule but saved in the system without the reversing setting, so only 16,000.00 actually reversed. The real February invoice was then entered through payables, so the cost sits in the books twice. The fix is a manual reversal dated March 1, after which the ledger and the computed balance agree.
Why the rollforward and the list both matter
The list proves what the balance is made of. The rollforward proves how it got there. A list on its own can tie to the ledger by accident, for example when a missing accrual and a failed reversal happen to be the same size. The rollforward exposes that, because each movement has to be explained separately.
Not every accrual settles through an invoice. Card processor fees, for instance, are often deducted from payouts before the money reaches the bank, so the cost is settled inside the deposit. The Stripe payout reconciliation to the bank and ledger shows how those netted fees are split out.
Stale accruals that should be released
A stale accrual is an estimate that has stayed on the template long past the date its invoice should have arrived. It usually means the vendor will never bill, the cost was booked twice, or the service was cancelled. Carrying it overstates both the liability and the expense, and the error grows each year it stays.
The "days open" column catches these. Set a rule in the template, such as flagging any line open more than 60 days, and require a decision on each flagged line at every close:
- Release it. The vendor confirmed nothing is owed, or the service never happened. Reverse the accrual to the expense account it came from.
- Keep it, with a reason. A dispute is in progress, or the vendor bills twice a year. Write the reason on the line.
- Move it. The cost turned out to be a capital item or a payable already entered. Reclassify it.
A common way accruals go stale
The most frequent cause is a standing accrual created for a one-off cost and never marked as reversing. The invoice arrives, payables records it, and the accrual sits beside it indefinitely. A reviewer who opens the template should look at the oldest lines first, since the reasons for those lines are the ones most likely to be missing. The broader method for ranking accounts and aging items across the whole close sits in balance sheet account reconciliations at month-end.
When is an accrued expense too small to put on the template?
An accrued expense is too small for the template when booking it would not change any decision a reader of the accounts would make, and the cost of estimating it exceeds its value. A written threshold settles this in advance, so preparers do not accrue a 40.00 courier bill one month and skip a 900.00 one the next.
Many companies set a fixed amount per item, below which the cost is simply expensed when the invoice arrives. The right figure depends on the size of the business, and it should be written in the close checklist rather than left to memory. Two exceptions override any threshold:
- Recurring small items that add up. Twenty small monthly subscriptions, each under the limit, may total a meaningful sum. Accrue them as one line.
- Year end. Cut-off matters more in the annual accounts, and your auditors may expect a lower threshold than you use monthly.
When not to accrue at all
Some amounts belong elsewhere. A cost already invoiced belongs in payables, not accruals. Cash received in advance from customers is deferred revenue, a liability with its own schedule, covered in the deferred revenue reconciliation template for Excel. Keeping those out of the accrual template keeps it short enough to review properly.
The accruals template rests on figures that come from bank and card activity: what was paid, and when. PreciseBankFlow checks that each bank statement foots and matches its lines to your ledger, so you can see which payments have already reached the ledger before you decide what still needs accruing. It is opening to firms in small groups, and the judgment on each estimate stays yours.