Deferred Revenue Schedule in Excel: ASC 606 Guide (2026)

August 2, 2026 · Dezzmond Team

Automation Financial Modeling Formulas

Nine times out of ten, a broken deferred revenue schedule in Excel fails for the same boring reason: rounding drift. You divide a $47,500 contract by 17 months, copy the formula across, and by month 17 the row sums to $47,499.98. Multiply that by 4,000 contract lines and your contract liability balance misses the general ledger by a few hundred dollars every single close — small enough that nobody catches it in month three, large enough that your auditor writes it up in month twelve.

The fix is not a bigger spreadsheet. It's a different formula shape: compute cumulative revenue at each period end and take the difference, rather than computing period revenue and hoping it adds up. That one change makes the schedule self-correcting by construction, and it's the backbone of everything below.

This guide builds a complete ASC 606 revenue recognition schedule in Excel — transaction price allocation across performance obligations, day-weighted proration for mid-month starts, a recognition grid that regenerates itself from one formula, contract asset/liability classification at the right level, and a rollforward that proves out to zero.

What Is a Deferred Revenue Schedule Under ASC 606?

A deferred revenue schedule is a period-by-period record of how much of each customer contract has been billed, how much has been recognized as revenue, and what remains unearned. Under ASC 606, the unearned remainder is presented as a contract liability, and the schedule is the evidence that supports both the balance sheet number and the revenue disclosure.

The schedule has to answer four questions on demand, for any period:

How much revenue did we recognize? — the income statement line.
What's left unearned? — the contract liability on the balance sheet.
Have we performed ahead of billing? — a contract asset, not a receivable.
What's still unsatisfied? — the remaining performance obligation (RPO) disclosure under ASC 606-10-50-13.

Most spreadsheets answer question 1 and fake the other three. That's why the reconciliation never works.

Deferred revenue vs. contract liability vs. unbilled receivable

These get used interchangeably in conversation and they are not the same thing.

Term	What it means	Arises when	Balance sheet treatment
Contract liability	Obligation to transfer goods/services	Billings exceed revenue recognized	Liability (often labeled "deferred revenue")
Contract asset	Right to payment, conditional on more than time	Revenue recognized exceeds billings	Asset, separate from AR
Receivable	Unconditional right to payment	Invoice issued, only time remains	Accounts receivable
Deferred revenue	Legacy label for a contract liability	Same as contract liability	Same as contract liability
Unbilled revenue	Loose term; usually a contract asset	Performance ahead of invoicing	Depends on conditionality

⚠️ Warning: ASC 606-10-45-1 requires a contract to be presented net as a single contract asset or contract liability. If one line of a contract is $40k overbilled and another is $15k underbilled, you present $25k of contract liability — not $40k of liability and $15k of asset. Netting at the line level instead of the contract level is the single most common presentation error in spreadsheet-built schedules.

How Do You Structure the Workbook?

Use four objects: a contract line table (one row per performance obligation), a billings table, a recognition grid driven entirely by formulas, and a rollforward that proves the grid against the GL. Nothing is typed twice, and no period column is ever hardcoded.

graph TD
    A[Contract Line Table<br/>one row per performance obligation] --> B[Step 4: SSP Allocation]
    C[Billings Table<br/>one row per invoice] --> E
    B --> D[Recognition Grid<br/>periods across, lines down]
    D --> E[Contract-Level Rollforward]
    E --> F[Contract Liability / Contract Asset]
    E --> G[Journal Entry Extract]
    D --> H[RPO Disclosure<br/>ASC 606-10-50-13]
    E --> I{Open + Billed - Recognized = Close?}
    I -->|No| B

The contract line table

One row per performance obligation, not per contract and not per invoice. This is the decision that determines whether the rest of the model works.

Column	Field	Example
A	Contract ID	`C-10442`
B	Customer	`Northwind Freight`
C	Line	`1`
D	PO Type	`Subscription`
E	Method	`Ratable`
F	Service Start	`2026-02-15`
G	Service End	`2027-02-14`
H	Transaction Price (contract)	`144,000`
I	SSP	`120,000`
J	Allocated Raw	formula
K	Allocated	formula

Format it as an Excel Table named Rev. Structured references keep every downstream formula readable and let the grid grow without touching a single range. If you want the mechanics of table references and spill behavior, the XLOOKUP and dynamic arrays guide covers the patterns this schedule leans on.

💡 Pro Tip: Put Service Start and Service End as real dates, never text. Run =SUMPRODUCT(--ISTEXT(Rev[Service Start])) once — if it returns anything but zero, stop and fix the import before writing another formula. Text dates fail silently in date arithmetic and produce a schedule that looks right and is wrong.

How Do You Allocate the Transaction Price Across Performance Obligations?

Under ASC 606 Step 4, you allocate the transaction price to each performance obligation in proportion to its standalone selling price. In Excel, that's the line's SSP divided by the sum of SSPs for the contract, multiplied by the transaction price — then a rounding true-up so allocations sum exactly to the contract value.

The relative SSP formula

In column J (Allocated Raw):

=ROUND(
    [@[Transaction Price]] * [@SSP]
    / SUMIFS(Rev[SSP], Rev[Contract ID], [@[Contract ID]]),
  2)

For a $144,000 contract with a $120,000 SSP subscription, a $30,000 SSP implementation, and a $10,000 SSP training block (SSP total $160,000):

Subscription: 144,000 × 120,000 / 160,000 = $108,000.00
Implementation: 144,000 × 30,000 / 160,000 = $27,000.00
Training: 144,000 × 10,000 / 160,000 = $9,000.00

That one ties cleanly. Change the SSPs to $115,000 / $31,000 / $9,000 and you get $107,466.67 + $28,032.00 + $8,136.00 = $143,634.67 before rounding behaves — the cents will not always cooperate.

Forcing the allocation to tie

Put the rounding difference on one designated line. Column K (Allocated):

=LET(
  raw,    [@[Allocated Raw]],
  first,  MINIFS(Rev[Line], Rev[Contract ID], [@[Contract ID]]),
  diff,   [@[Transaction Price]]
            - SUMIFS(Rev[Allocated Raw], Rev[Contract ID], [@[Contract ID]]),
  IF([@Line] = first, raw + diff, raw)
)

The plug lands on the lowest-numbered line of each contract, deterministically. No circular reference — Allocated Raw and Allocated are separate columns. If you haven't used LET to keep formulas like this readable, the LET function guide for financial formulas is worth ten minutes.

Verify with a single check cell:

=SUMPRODUCT(
   --(ROUND(SUMIFS(Rev[Allocated], Rev[Contract ID], Rev[Contract ID]), 2)
      <> ROUND(Rev[Transaction Price], 2))
 )

Anything other than 0 means at least one contract's allocation doesn't tie.

ℹ️ Note: SSP is an estimate, and ASC 606 permits three approaches — adjusted market assessment, expected cost plus margin, and the residual approach (only where the selling price is highly variable or uncertain). Keep the SSP basis in a column next to the number. When your auditor asks how you got $30,000 for implementation, "it's in the model" is not an answer.

How Do You Prorate Revenue for a Mid-Month Contract Start?

Compute cumulative revenue through each period end using a day-weighted fraction of the service term, then subtract the prior period's cumulative figure. A contract running Feb 15 to Feb 14 recognizes 14 days in February, a full month in March, and the exact remainder in the final month — with zero rounding drift, because every period is derived from a rounded cumulative total.

The cumulative-difference engine

This is the formula that fixes the drift problem in the opening paragraph. Define a named LAMBDA once (Formulas → Name Manager → New), called REVCUM:

REVCUM = LAMBDA(amt, st, en, d,
  LET(
    term,    en - st + 1,
    elapsed, d - st + 1,
    clamped, IF(elapsed < 0, 0, IF(elapsed > term, term, elapsed)),
    IF(term <= 0, 0, ROUND(amt * clamped / term, 2))
  )
)

REVCUM returns cumulative revenue for one line through any date. Period revenue is then the difference between two calls:

=REVCUM($K5, $F5, $G5, J$4) - REVCUM($K5, $F5, $G5, EOMONTH(J$4, -1))

Where row 4 holds month-end dates across the grid and column K holds the allocated amount.

Walk through a $108,000 subscription running 2026-02-15 to 2027-02-14 (term = 365 days):

Period end	Days elapsed	Cumulative	Period revenue
2026-02-28	14	4,142.47	4,142.47
2026-03-31	45	13,315.07	9,172.60
2026-04-30	75	22,191.78	8,876.71
2027-01-31	351	103,857.53	9,172.60
2027-02-28	365 (capped)	108,000.00	4,142.47

The cumulative column is capped at the allocated amount by construction, so the row must sum to exactly $108,000. There is no plug, no last-month IF, and no drift to chase.

💡 Pro Tip: Resist the temptation to write MEDIAN(0, elapsed, term) as a shorthand clamp. It works perfectly in a single cell and breaks the moment you feed it arrays — MEDIAN aggregates all its arguments into one value instead of broadcasting element by element. The nested IF clamp above works in both modes, which is what lets you convert the whole grid to a single spilled formula later.

Equal-month ratable, when policy requires it

Some companies recognize equal monthly amounts and prorate only the stub periods. Use months instead of days:

=LET(
  months, DATEDIF($F5, $G5 + 1, "m"),
  IF(AND(J$4 >= EOMONTH($F5, 0), EOMONTH(J$4, -1) < $G5),
     ROUND($K5 / months, 2), 0)
)

Cleaner-looking, but it reintroduces drift and it misstates stub months. Use the day-weighted engine unless your revenue policy explicitly says otherwise.

Which Recognition Method Applies to Each Line?

Not every performance obligation is ratable. Drive the method from column E and let SWITCH route each line to the right engine — one grid, five behaviors.

graph TD
    A[Performance obligation] --> B{Satisfied over time?<br/>ASC 606-10-25-27}
    B -->|No| C[Point in time<br/>recognize at transfer of control]
    B -->|Yes| D{Best measure of progress?}
    D -->|Time elapsed| E[Ratable, day-weighted]
    D -->|Costs incurred| F[Cost-to-cost input method]
    D -->|Units or milestones| G[Output method]
    D -->|Customer usage| H[Variable / usage-based]
    H --> I{Series of distinct periods?}
    I -->|Yes| J[Recognize as consumed]

Method	ASC 606 basis	Excel driver	Watch-out
Ratable (day-weighted)	Time-based measure of progress	`REVCUM` on service dates	Stub months must prorate, not round
Ratable (equal-month)	Time-based, policy election	`DATEDIF` month count	Drift + misstated stubs
Point in time	Control transfers at a moment	`IF(EOMONTH(delivery,0)=hdr, amt, 0)`	Delivery date ≠ invoice date
Cost-to-cost	Input method, ASC 606-10-55-20	Cumulative cost ÷ estimated total cost	Estimate changes = cumulative catch-up
Output / milestone	Units delivered or milestones met	`SUMIFS` on a milestone log	Milestone ≠ performance obligation
Usage-based	Variable consideration as consumed	`SUMIFS` on a usage feed	Constrain estimates, don't forecast

Routing with SWITCH

=SWITCH($E5,
  "Ratable",
     REVCUM($K5,$F5,$G5,J$4) - REVCUM($K5,$F5,$G5,EOMONTH(J$4,-1)),
  "Point",
     IF(EOMONTH($F5, 0) = J$4, $K5, 0),
  "Usage",
     SUMIFS(Usage[Amount], Usage[Line], $C5, Usage[Month], J$4),
  "POC",
     LET(
       ec,   SUMIFS(Est[Total Cost], Est[Line], $C5),
       cumC, SUMIFS(Cost[Amount], Cost[Line], $C5, Cost[Month], "<=" & J$4),
       prvC, SUMIFS(Cost[Amount], Cost[Line], $C5, Cost[Month], "<=" & EOMONTH(J$4,-1)),
       IF(ec = 0, 0,
          ROUND($K5 * MIN(cumC/ec, 1), 2) - ROUND($K5 * MIN(prvC/ec, 1), 2))
     ),
  0)

Notice the POC branch uses the same cumulative-difference shape. When the total cost estimate changes, the cumulative figure re-bases and the current period absorbs the catch-up automatically — which is exactly the accounting answer ASC 606 requires for a change in the measure of progress.

How Do You Turn the Grid Into One Formula?

Because REVCUM broadcasts, the entire recognition grid — every line, every period — can be one spilled formula. This is the difference between a schedule you rebuild each month and one that regenerates itself when a contract is added.

Put the model start month in $B$1 and the number of periods in $B$2, then:

=LET(
  hdr,  EOMONTH($B$1, SEQUENCE(1, $B$2, 0)),
  amt,  Rev[Allocated],
  st,   Rev[Service Start],
  en,   Rev[Service End],
  REVCUM(amt, st, en, hdr) - REVCUM(amt, st, en, EOMONTH(hdr, -1))
)

amt, st, and en are M×1 columns; hdr is a 1×N row. Excel broadcasts them into an M×N grid, and the whole schedule spills from a single cell. Add a contract line to the table and the grid grows a row. Change $B$2 from 36 to 60 and it grows 24 columns.

⚠️ Warning: Do not mix the spilled grid with typed overrides. If someone hardcodes a correction into a spilled range, Excel throws #SPILL! and the entire schedule blanks. Keep manual adjustments in a separate adjustments table keyed on Contract ID and Line, and add them in the rollforward — never on top of the engine. Locking the grid and validating inputs is standard practice; see the data validation patterns for financial models.

For contract data arriving from a billing system as monthly CSV exports, land it through Power Query rather than pasting. The refresh-and-recalculate loop turns a two-day close task into a button; the Power Query financial reporting workflow covers the transformation steps.

How Do You Build the Rollforward That Proves It Out?

The rollforward is the control. For each contract and period: opening contract liability, plus billings, less revenue recognized, plus or minus adjustments, equals closing. If it doesn't equal, the schedule is wrong and the check cell says so before your controller does.

The identity

Opening balance + Billings − Revenue recognized ± Adjustments = Closing balance

Closing balance per contract, at any period end, is simply cumulative billings less cumulative revenue:

=LET(
  billed, SUMIFS(Bill[Amount], Bill[Contract ID], $A5,
                 Bill[Invoice Date], "<=" & J$4),
  rec,    SUMIFS(RecCum[Amount], RecCum[Contract ID], $A5,
                 RecCum[Month], "<=" & J$4),
  billed - rec
)

A positive net is a contract liability. A negative net is a contract asset. Classify at the contract level:

Contract liability:  =MAX(0,  net)
Contract asset:      =MAX(0, -net)

This is where the ASC 606-10-45-1 netting rule earns its keep. Aggregate to Contract ID first, then apply MAX. Applying MAX line by line and summing afterward inflates both sides of the balance sheet.

The tie-out check

One cell, always visible, always green or you don't close:

=SUMPRODUCT(
   --(ROUND(Open_Bal + Billings_Per - Rev_Per + Adj_Per - Close_Bal, 2) <> 0)
 )

Zero means every contract in every period reconciles. Anything else means find it now. This is the same philosophy as the broader financial model audit checklist — build the proof into the model, don't perform it by eye.

Example: The $144,000 contract above bills in full on 2026-02-01 and starts service 2026-02-15. February: opening $0 + billings $144,000 − revenue $13,142 (the $4,142 subscription stub plus the $9,000 training block delivered at a point in time) = closing contract liability of $130,858. The GL shows $130,858. That's the number you put on the balance sheet, and the schedule is the support.

The RPO disclosure falls out for free

ASC 606-10-50-13 requires disclosure of revenue allocated to unsatisfied performance obligations, with expected timing. Because the grid already extends into future periods, the disclosure is a sum of columns to the right of the reporting date:

Within 12 months:  =SUM(OFFSET(grid_row, 0, current_col, 1, 12))
Thereafter:        =SUM(row) - SUM(OFFSET(grid_row, 0, 0, 1, current_col + 12))

Or, avoiding volatile functions, use SUMIFS against a flattened recognition table with a Month column. Flattening the grid also makes it far easier to summarize by customer, product, or geography — GROUPBY and PIVOTBY turn that flat table into disclosure-ready summaries in one formula.

How Do You Handle Contract Modifications?

Modifications are where spreadsheet revenue schedules go to die. ASC 606 gives three treatments, and each maps to a different Excel action.

Separate contract — added goods are distinct and priced at SSP. Add new rows with a new Contract ID. Nothing about the original changes.
Termination and new contract (prospective) — remaining goods are distinct but not priced at SSP. Set the original line's Service End to the modification date less one day, then add a new line for the remaining unrecognized amount over the remaining term.
Cumulative catch-up — remaining goods are not distinct. Update the allocated amount on the existing line; the cumulative-difference engine recomputes cumulative revenue on the new basis and the current period absorbs the entire catch-up automatically.

Treatment 3 is where the cumulative-difference design pays off a second time. A period-based formula would require you to compute the catch-up by hand and hardcode it. The cumulative engine derives it.

⚠️ Warning: When you shorten a line for a prospective modification, the already-recognized revenue must not change. Snapshot cumulative revenue through the modification date to a static column before editing dates, and use it as the opening basis for the new line. Skipping this step silently restates prior-period revenue — which is exactly what a period-based schedule would have prevented, and the one trade-off of this design worth knowing about.

When Should You Move Off Excel?

Excel is the right tool for revenue recognition up to roughly a few thousand active performance obligations with mostly time-based recognition. Move to a subledger when usage-based pricing, high modification volume, or multi-entity consolidation push the workbook past the point where one person can audit it in an afternoon.

Practical thresholds:

Under ~500 lines: Excel comfortably. A spilled grid recalculates instantly.
500–3,000 lines: Excel with discipline. Flatten prior periods to values, avoid volatile functions, keep the live grid to open contracts only.
3,000–10,000 lines: Excel is straining. Recalc times climb and the audit trail thins. Power Query for staging, and consider a subledger.
Above 10,000 lines, or heavy usage/modification volume: a revenue subledger. Not because Excel can't compute it, but because SOX-grade change control on a spreadsheet at that scale costs more than the software.

Performance matters before you hit those limits. Eighty period columns × 5,000 rows of SUMIFS against unsorted tables will make the workbook unusable. Two fixes: sort billing and cost tables by key so SUMIFS short-circuits, and freeze closed periods to hardcoded values each month — closed periods should never recalculate anyway.

💡 Pro Tip: Keep a Period Status row above the grid marked Closed or Open. At close, copy-paste-values the closed columns. You get a permanent record of what was reported, and recalc time drops with every month that passes instead of growing.

Frequently Asked Questions

How do you build a deferred revenue schedule in Excel?

Create one table row per performance obligation with service start and end dates and an allocated transaction price. Build a grid with month-end dates across the top, and in each cell compute cumulative revenue through that period end minus cumulative revenue through the prior period end. Add a rollforward that proves opening plus billings less revenue equals closing.

How do you prorate revenue for a contract that starts mid-month?

Use day-weighted proration: recognize the allocated amount times days of service in the period divided by total days in the service term. A contract starting February 15 with a 365-day term recognizes 14/365 of the amount in February. Computing this from cumulative totals rather than period amounts guarantees the row sums exactly to the contract value.

What's the difference between deferred revenue and a contract liability?

They're the same balance under different labels. ASC 606 introduced "contract liability" as the technical term; "deferred revenue" is the legacy label most companies still use on the face of the balance sheet, which the standard permits. The meaningful distinction is against a contract asset — revenue recognized ahead of billing, which is an asset, not negative deferred revenue.

Why doesn't my deferred revenue schedule tie to the general ledger?

In order of frequency: rounding drift from period-based formulas, netting contract assets and liabilities at the line level instead of the contract level, billings recorded on invoice date in the GL but service date in the model, and manual journal entries booked directly to the deferred revenue account without a corresponding schedule line. The rollforward check isolates which one.

Can Excel handle ASC 606 multi-element allocation?

Yes. Relative standalone selling price allocation is one SUMIFS divided into a product, plus a rounding true-up so allocations sum to the transaction price. Excel handles the allocation itself well at any scale. What strains is the modification history and audit trail — tracking why an allocation changed across dozens of amendments is where a subledger earns its cost.

Closing the Loop

A revenue schedule earns trust the same way any model does: through checks that fire before a human notices. Build the allocation tie-out, the rollforward identity, and the text-date test into the workbook itself, and the monthly close becomes a refresh rather than an investigation.

That's also where AI assistance is genuinely useful — not writing the schedule for you, but catching the line that stopped tying after someone amended a contract. Dezzmond reads the structure of a live workbook and flags the broken link, the hardcoded override, and the row that no longer sums, in the model rather than in a report about it.

Start with one contract and one performance obligation. Prove the rollforward ties for a single line across the full service term, then scale the grid. A schedule that reconciles at n=1 will reconcile at n=4,000; one that doesn't never will.