Cellwise Start free

Excel guide · 5 min read

How to build an accrued expenses schedule in Excel

Quick answerList every service you've received or started receiving with its period start and end, an estimated cost and the invoice date once the bill arrives. A cost needs accruing at month end when its period has started by then and the invoice hasn't arrived by then: =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.

RowABCDEFGHIJKL
1VendorServicePeriod startPeriod endEstimateInvoice dateInvoice amountStatusAccrue at month endAccrued last monthMonth endOct 31, 2026
2Brightline CleaningOffice cleaning OctOct 1, 2026Oct 31, 20261450Nov 4, 20261450Accrue14500Last month endSep 30, 2026
3Northgrid EnergyElectricity OctOct 1, 2026Oct 31, 20262380Accrue23800
4Harbor Audit PartnersAudit fieldworkOct 15, 2026Nov 14, 20269000Accrue4935.4838710
5FastFreightInbound freight OctOct 1, 2026Oct 31, 20263100Oct 29, 20263240Billed00
6TempForce StaffingTemp staff weekOct 26, 2026Nov 1, 20262240Accrue19200
7Cloudnest HostingHosting NovNov 1, 2026Nov 30, 2026800Not started00
8Brightline CleaningOffice cleaning SepSep 1, 2026Sep 30, 20261450Oct 6, 20261450Billed01450
9Northgrid EnergyElectricity SepSep 1, 2026Sep 30, 20262200Oct 9, 20262315Billed02200
10Total10685.4838713650

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.

=IF(C2>$L$1,"Not started",IF(AND(F2<>"",F2<=$L$1),"Billed","Accrue"))

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.

=IF(AND(C4<=$L$1,OR(F4="",F4>$L$1)),E4*(MIN(D4,$L$1)-C4+1)/(D4-C4+1),0)

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.

=SUM(I2:I9)

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.

=FILTER(HSTACK(A2:B9,I2:I9),(C2:C9<=L1)*((F2:F9="")+(F2:F9>L1)))

Result: spills 4 rows × 3 columns

Brightline CleaningOffice cleaning Oct1450
Northgrid EnergyElectricity Oct2380
Harbor Audit PartnersAudit fieldwork4935.483871
TempForce StaffingTemp staff week1920

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.

=SUMPRODUCT((J2:J9>0)*(F2:F9<>"")*(G2:G9-J2:J9))

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

Related guides

Updated