Excel guide · 5 min read
How to build an accrued expenses schedule in Excel
=IF(AND(C2<=$L$1,OR(F2="",F2>$L$1)),E2*(MIN(D2,$L$1)-C2+1)/(D2-C2+1),0), with the month-end date in L1. The formula also takes only the days up to month end for a service that runs into next month.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Vendor | Service | Period start | Period end | Estimate | Invoice date | Invoice amount | Status | Accrue at month end | Accrued last month | Month end | Oct 31, 2026 |
| 2 | Brightline Cleaning | Office cleaning Oct | Oct 1, 2026 | Oct 31, 2026 | 1450 | Nov 4, 2026 | 1450 | Accrue | 1450 | 0 | Last month end | Sep 30, 2026 |
| 3 | Northgrid Energy | Electricity Oct | Oct 1, 2026 | Oct 31, 2026 | 2380 | Accrue | 2380 | 0 | ||||
| 4 | Harbor Audit Partners | Audit fieldwork | Oct 15, 2026 | Nov 14, 2026 | 9000 | Accrue | 4935.483871 | 0 | ||||
| 5 | FastFreight | Inbound freight Oct | Oct 1, 2026 | Oct 31, 2026 | 3100 | Oct 29, 2026 | 3240 | Billed | 0 | 0 | ||
| 6 | TempForce Staffing | Temp staff week | Oct 26, 2026 | Nov 1, 2026 | 2240 | Accrue | 1920 | 0 | ||||
| 7 | Cloudnest Hosting | Hosting Nov | Nov 1, 2026 | Nov 30, 2026 | 800 | Not started | 0 | 0 | ||||
| 8 | Brightline Cleaning | Office cleaning Sep | Sep 1, 2026 | Sep 30, 2026 | 1450 | Oct 6, 2026 | 1450 | Billed | 0 | 1450 | ||
| 9 | Northgrid Energy | Electricity Sep | Sep 1, 2026 | Sep 30, 2026 | 2200 | Oct 9, 2026 | 2315 | Billed | 0 | 2200 | ||
| 10 | Total | 10685.483871 | 3650 |
Download this sheet as an Excel practice file (.xlsx)
The layout: one row per service, not per invoice
An accrual records a cost you've incurred in the month but haven't been billed for yet: the electricity you used in October that won't be invoiced until November, the temp staff who worked the last week, the audit team already on site. Without accruals, October looks cheaper than it was and November looks dearer.
The schedule in A:G has one row per service: vendor, what it is, the first and last day of the service period, your best estimate of the cost, and the invoice date and amount, left blank until the bill arrives. The month end you're closing goes in L1 (31 October 2026), and L2 holds last month's end with =EOMONTH(L1,-1).
Where the estimate comes from matters more than the formula: a purchase order, a contract rate, a timesheet, or last month's bill for a recurring cost. Write the source in a notes column so whoever reviews the close can check it.
Which items need accruing
Two tests decide it. Has the service period started by month end? If not, nothing has been incurred yet; the November hosting in row 7 is Not started. Has the invoice arrived by month end? If it has, the bill is already in your payables and accruing it again would count it twice; the freight in row 5, invoiced on 29 October, is Billed.
Everything else gets accrued: services that started on or before month end and either have no invoice yet or were invoiced after month end. The cleaning bill dated 4 November still belongs to October, so it's accrued at 31 October even though it's already in your inbox when you do the close.
Result: Accrue
Accrue: October's cleaning was invoiced on November 4, after the October 31 month end.
The accrual amount, with partial periods by day
Most items are a whole month and accrue in full. Some run across month end. The audit fieldwork runs from 15 October to 14 November, 31 days, and 17 of them fall in October, so October gets 17/31 of the 9,000 estimate. MIN(D2,$L$1) stops the count at month end, and the +1 counts both the first and last day.
The temp staff week runs 26 October to 1 November: 6 of its 7 days are in October, so 1,920 of the 2,240 is accrued. If the vendor bills by hours worked instead, use the timesheet hours to month end instead of a day share.
Result: 4935.483871 shows as 4,935.48 with Number format
4,935.48: 17 of the 31 days of audit fieldwork fall on or before October 31.
How do I get the total for the journal?
Add up column I. The usual entry debits each expense account and credits an accrued liabilities account; your chart of accounts and your accountant decide the exact accounts.
Result: 10685.483871 shows as 10,685.48 with Number format
10,685.48 to accrue at October 31 across four items.
List the accruals with FILTER (Excel 365)
For the close file or the reviewer, pull just the items being accrued into a short list. FILTER keeps the rows where the period has started by month end and the invoice date is blank or after month end. Multiplying the two tests means "and", and adding the two invoice tests means "or".
HSTACK picks the vendor, service and accrual columns to show. Change L1 to 30 November next month and the list rebuilds itself. In Excel 2019 and earlier, filter column H for Accrue instead.
Result: spills 4 rows × 3 columns
| Brightline Cleaning | Office cleaning Oct | 1450 |
| Northgrid Energy | Electricity Oct | 2380 |
| Harbor Audit Partners | Audit fieldwork | 4935.483871 |
| TempForce Staffing | Temp staff week | 1920 |
A four-row list: October cleaning 1,450, October electricity 2,380, audit fieldwork 4,935.48 and the temp staff week 1,920.
Reverse last month's accruals and tie out to the bills
A common approach is to reverse last month's accruals on the first day of the new month and then book each invoice in full when it arrives. Column J runs the same test against last month end in L2: at 30 September, the September cleaning and electricity were accrued, 3,650 in total. Reversing them in October and booking the real bills means October's expense picks up only the difference between estimate and actual.
That difference is your tie-out. September electricity was estimated at 2,200 and billed at 2,315, so 115 more lands in October. Large or repeated gaps say an estimate needs a better source. And watch for last month's items with no invoice yet: they need accruing again, which the schedule does automatically because their invoice date is still blank.
Result: 115 shows as 115.00 with Number format
115: September's accruals were 115 lower than the invoices that later arrived for them.
Common mistakes
Accruing a bill that's already in payables
Check the invoice date against month end. A bill received on or before month end is already recorded; accruing it again doubles the cost.
Accruing a whole service that runs into next month
Take only the days (or hours) up to month end: MIN(end, month end) - start + 1, over the total days.
Forgetting to reverse last month
If last month's accruals stay on the books and the invoices are booked in full, the cost is counted twice.
Estimates with no source
Note the purchase order, contract or timesheet behind each estimate, and review the tie-out difference each month.
FAQ
What is an accrued expenses schedule?
A list of costs incurred in the period but not yet invoiced, with the amount to record at month end. It supports the accrued liabilities balance on the balance sheet.
How do I calculate a partial month accrual in Excel?
Multiply the estimate by the days in the period up to month end over the total days: =E2*(MIN(D2,L1)-C2+1)/(D2-C2+1), with the month-end date in L1.
What's the difference between accrued and prepaid expenses?
An accrual is a cost you've used but not yet paid or been billed for, so it's a liability. A prepayment is a cost you've paid before using it, so it's an asset. See the prepaid expense amortization schedule.
Do accruals have to be reversed?
Many teams reverse them on the first day of the next month so invoices can be booked normally. Others clear each accrual against its invoice. Follow your organisation's policy and your accountant's advice.
Functions used
IFPicks a result from a testANDTrue if all tests are trueORTrue if any test is trueMINSmallest numberEOMONTHLast day of a monthFILTERKeeps matching rows, spillsHSTACKJoins arrays side by sideSUMAdds numbersSUMPRODUCTMultiply then add
Related guides
- How to make a prepaid expense amortization schedule in Excel
- How to build a deferred revenue schedule in Excel
- How to make an accounts payable aging report in Excel
- How to make a trial balance in Excel
- How to build a month-end close checklist in Excel
- Excel formulas cheat sheet: the 25 everyone should know
- How to add a watermark in Excel
Updated