Build an Accruals and Prepayments Schedule

At every month end, accounting has to move costs into the months they belong to. An insurance premium paid in January for the whole year is not a January cost: one twelfth of it belongs to each month, and the unused part sits on the balance sheet as a prepayment. A delivery received in March but not invoiced until April is a March cost even though no invoice exists yet; it is accrued. Both are usually kept in spreadsheets maintained by hand — a prepayments register with a row per invoice and a column per month, and an accruals list rebuilt every month. Both are mechanical, and both are easy to get slightly wrong. This page builds the two schedules with pandas: a prepayment release calculated by days, the month's journal and the closing balance, and accruals from goods received not invoiced that reverse automatically the next month, each reconciled to the ledger balance. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

Two inputs: a prepayments register with one row per invoice paid in advance — amount, service period and cost centre — and a list of goods received notes (GRNs) for the month with the invoice number, if the invoice has arrived. The month being closed is September 2026.

Step 1 — Spread each prepayment over its service period

import pandas as pd

reg = pd.read_excel("prepayments_register.xlsx", parse_dates=["Invoice date", "Service start", "Service end"])
months = pd.period_range("2026-01", "2027-09", freq="M")

def release_by_month(row):
    days = pd.date_range(row["Service start"], row["Service end"], freq="D")
    per_day = row["Amount"] / len(days)
    by_month = pd.Series(per_day, index=days).groupby(days.to_period("M")).sum().round(2)
    by_month.iloc[-1] += round(row["Amount"] - by_month.sum(), 2)          # rounding goes to the last month
    return by_month.reindex(months, fill_value=0.0)

schedule = reg.set_index("Invoice").apply(release_by_month, axis=1)
assert (schedule.sum(axis=1).round(2) == reg.set_index("Invoice")["Amount"]).all()
print(schedule.loc[:, "2026-07":"2026-10"].to_string())

Each prepayment is released over the days of its service period, so a month's share is proportional to the number of service days that fall in it. That matters for periods that do not start on the first of a month: the maintenance contract starting on 15 September releases sixteen days' worth in September, not a full month. Releasing by days rather than by twelfths is also what auditors expect for irregular periods, and it gives the same answer as twelfths for a calendar-year policy to within a few pence. The release is rounded to pence month by month, and any rounding difference is put into the last month so that each invoice releases exactly its amount — the assertion checks that for every row. The trade show stand, a three-day event in October, releases entirely in October, even though it was paid in August.

The extract shows the day-based logic at work: the insurance premium releases exactly 40.00 a day, so 1,240.00 in the 31-day months and 1,200.00 in September, and the quarter's rent of 18,000 is split 6,065.22, 6,065.22 and 5,869.56 across July, August and September rather than 6,000 each.

Step 2 — The month's release, closing balances and the ledger check

month = pd.Period("2026-09", freq="M")
released_to_date = schedule.loc[:, :month].sum(axis=1)
detail = reg.set_index("Invoice")[["Description", "Cost centre", "Amount"]].assign(
    **{"Released in month": schedule[month], "Released to date": released_to_date.round(2),
       "Closing prepayment": (reg.set_index("Invoice")["Amount"] - released_to_date).round(2)})
print(detail[["Released in month", "Released to date", "Closing prepayment"]].to_string())

journal = detail.groupby("Cost centre")["Released in month"].sum().round(2)
print("\nSeptember release by cost centre:", journal.to_dict(), "| total", round(journal.sum(), 2))

ledger_prepayments = 19_183.29                      # balance of the prepayments account in the trial balance
schedule_total = round(detail["Closing prepayment"].sum(), 2)
print(f"schedule {schedule_total:,.2f} | ledger {ledger_prepayments:,.2f} | difference {schedule_total - ledger_prepayments:,.2f}")

For the month being closed, each invoice gives three numbers: what is released this month, what has been released since the service began, and what remains as a prepayment. The trade show stand, paid in August for an event in October, has released nothing yet, so its full 4,800 is still a prepayment; the third-quarter rent is fully released and drops off. Summing the month's release by cost centre gives the journal: debit each cost centre's expense, credit the prepayments account. The closing balances add up to the figure that should be in the ledger, and comparing the two is the control that matters — a difference means an invoice was posted to prepayments but never added to the register, or the reverse. Here the schedule and the ledger agree to the penny: 19,183.29 of prepayments at 30 September, and a September release of 8,129.29.

If the two had differed by a cent or two, the likely cause would be rounding in a register kept by hand in earlier months; anything larger points to an invoice missing from one side, and the per-invoice closing balances make it quick to find.

Step 3 — Accruals from goods received not invoiced

grn = pd.read_excel("goods_received.xlsx", parse_dates=["Received"])
in_month = grn[grn["Received"].dt.to_period("M") == month]
accruals = in_month[in_month["Invoice"].isna()][["GRN", "PO", "Description", "Received", "Value"]]
print(accruals.to_string(index=False))
print("accrual total:", accruals["Value"].sum())

journals = pd.DataFrame([
    {"Date": "2026-09-30", "Account": "Purchases — goods received", "Debit": accruals["Value"].sum(), "Credit": 0.0, "Note": "September accrual"},
    {"Date": "2026-09-30", "Account": "Accruals", "Debit": 0.0, "Credit": accruals["Value"].sum(), "Note": "September accrual"},
    {"Date": "2026-10-01", "Account": "Accruals", "Debit": accruals["Value"].sum(), "Credit": 0.0, "Note": "reversal"},
    {"Date": "2026-10-01", "Account": "Purchases — goods received", "Debit": 0.0, "Credit": accruals["Value"].sum(), "Note": "reversal"},
])
assert journals["Debit"].sum() == journals["Credit"].sum()

An accrual records a cost for goods or services received in the month but not yet invoiced. The goods received list is the best source: every GRN dated in September without an invoice number is an accrual, at the value on the purchase order. Three of the four GRNs qualify — packaging, courier services and cleaning, 3,435.50 in total — while the raw material delivery already has its invoice and is in the ledger. The journal debits purchases and credits the accruals account at 30 September, and is reversed on 1 October, so that when the invoices arrive in October and are posted normally the cost is not counted twice. Rebuilding the accrual from the GRN list each month, instead of adjusting last month's spreadsheet, means nothing old lingers in it. Services rarely produce goods received notes, so recurring service costs — utilities, cleaning contracts, professional fees — are often accrued from a short standing list of expected monthly amounts instead, removed as soon as the invoice is posted.

Step 4 — The month-end workbook

with pd.ExcelWriter("month_end_2026-09.xlsx") as xw:
    detail.reset_index().to_excel(xw, sheet_name="Prepayments", index=False)
    journal.rename("Release").reset_index().to_excel(xw, sheet_name="Prepayment journal", index=False)
    schedule.round(2).rename(columns=str).reset_index().to_excel(xw, sheet_name="Release schedule", index=False)
    accruals.to_excel(xw, sheet_name="Accruals", index=False)
    journals.to_excel(xw, sheet_name="Accrual journals", index=False)
    pd.DataFrame({"Check": ["Prepayments: schedule vs ledger"], "Schedule": [schedule_total],
                  "Ledger": [ledger_prepayments], "Difference": [round(schedule_total - ledger_prepayments, 2)]}
                 ).to_excel(xw, sheet_name="Reconciliation", index=False)
print("written")

The workbook holds everything the month-end reviewer needs: the prepayment detail with this month's release and closing balance per invoice, the release journal by cost centre, the full month-by-month schedule for looking ahead, the accruals with their journals, and the reconciliation to the ledger. Keeping the forward-looking schedule is useful beyond month end: it shows the cost already committed for coming months, which feeds straight into a forecast — see Build a Cash Flow Forecast Report.

Common pitfalls

• Releasing in equal twelfths for odd periods. Release by days when periods start mid-month.
• Rounding that leaves pennies behind. Put the rounding difference in the last month and assert the total.
• No ledger reconciliation. Compare closing prepayments with the trial balance every month.
• Accruals without reversal. Reverse on the first day of the next month, or the cost is counted twice.
• Editing last month's accruals list. Rebuild it from the GRNs each month.

Conclusion

A prepayments schedule spreads each invoice over its service period by days, rounds month by month with the difference in the last month, and reports the month's release by cost centre and the closing balance, which must agree with the ledger. An accruals schedule takes the month's goods received without an invoice, accrues them at the order value and reverses them the next day. Both are rebuilt from source data every month and written to one workbook with their journals and the reconciliation.

Frequently asked questions

How do I calculate a monthly prepayment release in Python? Divide the amount by the number of service days, sum the daily amounts by month with pandas, and put any rounding difference into the last month.

What is the difference between an accrual and a prepayment? A prepayment is paid before the cost belongs to the period and sits on the balance sheet until released; an accrual is a cost incurred in the period before it is invoiced.

Why are accruals reversed the next month? So that when the invoice arrives and is posted normally the cost is not recorded twice.

How do I check a prepayments schedule? Compare the total of closing prepayments with the prepayments balance in the ledger, and check each invoice releases exactly its amount.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Build a Deferred Revenue Schedule — the revenue-side equivalent.
• Build a Purchase Order Tracking Report — where the GRNs come from.
• Reconcile Payroll to the General Ledger — another ledger control.