Build a Deferred Revenue Schedule

A customer pays 12,000 in March for a year's subscription starting on 15 March. The cash is in the bank, but the revenue is not earned yet: it is earned day by day as the service is delivered, and until then the unearned part sits on the balance sheet as deferred revenue. Every month, finance must work out how much of each prepaid contract to recognise and what remains deferred. With a handful of contracts that is a spreadsheet; with hundreds, each starting on a different day and some with odd lengths, the spreadsheet becomes fragile — one dragged formula off by a row and the month's revenue is wrong. pandas builds the whole schedule from the contract list in a few lines, with daily precision and a built-in reconciliation. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

contracts.xlsx lists five prepaid contracts with their invoice date, amount and service period — a one-year subscription starting mid-January, a six-month contract, a one-year contract from 15 March, a quarterly contract and a two-year contract from 10 September. The schedule covers January 2026 to December 2027, and month-end reporting is for September 2026.

Step 1 — Daily revenue for each contract

The fairest and most common straight-line method spreads the amount evenly over every day of the service period, so a contract starting on the 15th earns about half a month in its first month:

import pandas as pd

contracts = pd.read_excel("contracts.xlsx")
contracts["Days"] = (contracts["Service end"] - contracts["Service start"]).dt.days + 1
contracts["Per day"] = contracts["Amount"] / contracts["Days"]
print(contracts[["Contract", "Service start", "Service end", "Days", "Amount", "Per day"]].round({"Per day": 4}).to_string(index=False))

Other recognition patterns exist — usage-based, milestone-based, or equal monthly amounts regardless of days — and the contract terms decide which applies; straight-line daily is the usual default for subscriptions and support contracts, and the structure below works for the others by changing how each month's share is computed. The + 1 counts both the first and last day: 15 January 2026 to 14 January 2027 is 365 days. Contract C-1003 covers 365 days for 9,125, which is exactly 25 a day; the two-year C-1005 runs over 2027–2028 and includes 29 February 2028, so it has 731 days. Daily rates are kept unrounded here; rounding happens once, per month, in the next step, so the parts add back to the invoice.

Step 2 — Spread the days into months

For each contract and month, count the service days that fall in the month and multiply by the daily rate. Building the overlap with dates rather than loops keeps it fast for thousands of contracts:

import numpy as np

months = pd.period_range("2026-01", "2028-09", freq="M")
m_start = months.to_timestamp(how="start")
m_end = months.to_timestamp(how="end").normalize()

def schedule_row(c):
    start = m_start.where(m_start > c["Service start"], c["Service start"])
    end = m_end.where(m_end < c["Service end"], c["Service end"])
    days = np.maximum((end - start).days + 1, 0)
    return pd.Series(np.round(days * c["Per day"], 2), index=months.astype(str))

sched = contracts.set_index("Contract").apply(schedule_row, axis=1)
# put any rounding difference in the final month so each contract totals its invoice exactly
diff = (contracts.set_index("Contract")["Amount"] - sched.sum(axis=1)).round(2)
last_month = sched.apply(lambda r: r[r > 0].index[-1], axis=1)
for c, m in last_month.items():
    sched.loc[c, m] = round(sched.loc[c, m] + diff[c], 2)
print(sched.loc[:, "2026-01":"2026-04"].to_string())
print("contract totals equal invoices:", (sched.sum(axis=1).round(2) == contracts.set_index("Contract")["Amount"]).all())

For each month, the overlap starts at the later of the month start and the service start and ends at the earlier of the month end and the service end; a negative day count means no overlap and is clipped to zero. C-1001 earns 17 days in January (15–31 January) and a full month after that. np.maximum(..., 0) does the clipping element-wise across all months at once. Laying the months out as columns in this way also makes the schedule easy to read in Excel, where accountants expect one row per contract and one column per period. Rounding each month to cents leaves a few cents of difference over a contract's life; the code puts that difference in the contract's last month so every contract recognises exactly its invoiced amount — auditors check this.

Step 3 — Deferred balance at each month end

Deferred revenue at a month end is everything invoiced so far minus everything recognised so far. Invoices are recorded in the month they are issued, which may be before the service starts:

invoiced = (contracts.assign(Month=contracts["Invoice date"].dt.to_period("M").astype(str))
                     .pivot_table(index="Contract", columns="Month", values="Amount", aggfunc="sum")
                     .reindex(columns=sched.columns, fill_value=0).fillna(0))

roll = pd.DataFrame({
    "Invoiced": invoiced.sum(),
    "Recognised": sched.sum(),
})
roll["Deferred (closing)"] = (roll["Invoiced"] - roll["Recognised"]).cumsum().round(2)
roll["Deferred (opening)"] = roll["Deferred (closing)"].shift(fill_value=0)
roll = roll[["Deferred (opening)", "Invoiced", "Recognised", "Deferred (closing)"]]
print(roll.loc["2026-06":"2026-10"].round(2).to_string())

This is the deferred revenue roll-forward: opening balance, plus new invoices, minus revenue recognised, equals closing balance. In September 2026 the two-year 24,000 contract is invoiced, so the balance jumps, while recognition continues on the others. The closing balance for September is the figure that must agree with the deferred revenue account in the general ledger — see Build a Trial Balance from a General Ledger.

Step 4 — The month's journal and the checks

Each month, one journal moves the recognised amount from the deferred revenue liability to revenue. Generate it from the schedule, and check that the schedule reconciles before posting:

month = "2026-09"
journal = pd.DataFrame([
    {"Account": "2400 Deferred revenue", "Debit": round(sched[month].sum(), 2), "Credit": 0.0},
    {"Account": "4100 Subscription revenue", "Debit": 0.0, "Credit": round(sched[month].sum(), 2)},
])
print(journal.to_string(index=False))
print("by contract:", sched[month][sched[month] > 0].round(2).to_dict())

total_invoiced = contracts["Amount"].sum()
recognised_to_date = sched.loc[:, :month].sum().sum()
deferred_now = roll.loc[month, "Deferred (closing)"]
print(f"invoiced {total_invoiced:,.2f} = recognised to date {recognised_to_date:,.2f} + deferred {deferred_now:,.2f}:",
      round(recognised_to_date + deferred_now, 2) == round(total_invoiced, 2))

The journal debits deferred revenue and credits revenue for the month's total, with a by-contract breakdown for the audit file. The final check is the identity that keeps the schedule honest: everything invoiced to date equals what has been recognised plus what is still deferred. C-1002 finished at the end of August and recognises nothing; C-1004 recognises its final month, 30 days at 1,800 ÷ 92 a day; and C-1005, which started on 10 September, recognises 21 days.

Step 5 — Write the schedule to Excel

The workbook carries the roll-forward, the contract-by-month schedule and the journal:

from openpyxl.styles import Font

with pd.ExcelWriter("deferred_revenue_2026-09.xlsx", engine="openpyxl") as xw:
    roll.round(2).to_excel(xw, sheet_name="Roll-forward")
    out = contracts.set_index("Contract")[["Customer", "Amount", "Service start", "Service end"]].join(sched)
    out.to_excel(xw, sheet_name="Schedule")
    journal.to_excel(xw, sheet_name=f"Journal {month}", index=False)
    for ws in xw.book.worksheets:
        for c in ws[1]:
            c.font = Font(bold=True)
        ws.freeze_panes = "B2" if ws.title != "Schedule" else "F2"
    ws = xw.sheets["Schedule"]
    for row in ws.iter_rows(min_row=2, min_col=3):
        for c in row:
            if isinstance(c.value, float):
                c.number_format = "#,##0.00;-#,##0.00;\"\""
print(out.shape[1] - 4, "monthly columns written")

The schedule sheet freezes the contract details on the left so the monthly columns scroll past them, and the number format's third section ("") hides zeros, so each contract's recognition shows as a band across the months it covers — a quick visual check for start and end dates. The roll-forward goes first because it is what the month-end reviewer signs off. Adding the totals as formulas, or a chart of the deferred balance over time, follows the same patterns as Build a Fixed Asset Depreciation Schedule, whose contract-by-month structure is the same.

Common pitfalls

• Off-by-one service days. Count both the start and end day.
• Equal monthly amounts for mid-month starts. Use daily pro-rata so partial months are fair.
• Rounding drift. Round per month and put the remainder in the final month so totals match invoices.
• Invoices recorded in the service month. Deferred revenue starts when the invoice is issued.
• No reconciliation. Check invoiced equals recognised plus deferred every month.

Conclusion

A deferred revenue schedule is daily straight-line recognition made systematic: compute each contract's daily rate over its service days, count the days each month overlaps the service period, round per month with any remainder in the final month, and roll the deferred balance forward from invoices and recognition. The month's journal, the by-contract breakdown and the check that invoiced equals recognised plus deferred all come from the same schedule, which then goes to Excel for review.

Frequently asked questions

How do I calculate deferred revenue in Python? For each month, deferred revenue is the cumulative amount invoiced minus the cumulative revenue recognised. Build a contract-by-month recognition schedule and take the running difference.

How do I recognise revenue for a contract that starts mid-month? Divide the contract amount by its number of service days and multiply by the days of service in each month, so the first and last months get a partial share.

How do I stop rounding differences in a revenue schedule? Round each monthly amount to cents and add the remaining difference to the contract's final month, so every contract recognises exactly its invoiced amount.

What journal records monthly revenue recognition? Debit the deferred revenue liability and credit revenue for the total recognised in the month, with a breakdown by contract kept as support.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports in Excel.
• Build a Fixed Asset Depreciation Schedule — a schedule with the same shape.
• Build a Date Table for Excel Reports with pandas — month boundaries and calendars.
• Build a Profit and Loss Statement from a General Ledger — where recognised revenue appears.