Cellwise Start free

Excel guide · 6 min read

How to build a deferred revenue schedule in Excel

Quick answerList each prepaid contract with its amount, start and end date. Revenue for a month = amount x days of service in that month / total days, which in Excel is =$D2*MAX(0,MIN($G2,I$1)-MAX($B2,EOMONTH(I$1,-1)+1)+1)/$H2 with the month-end date in I1 and the service end in G2. The deferred balance at any date is the cash received minus the revenue recognised so far.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1CustomerStartsEndsPaidCancelledRefundService toDaysJul 31, 2026Aug 31, 2026Sep 30, 2026Oct 31, 2026
2Brightline DentalJul 1, 2026Jun 30, 20276000046568365509.5890411509.5890411493.15068493509.5890411
3Harbor Yoga StudioAug 15, 2026Aug 14, 202736500466133650170300310
4Northfield BakerySep 1, 2026Aug 31, 20272400Nov 30, 20261801.64463569100197.26153846203.83692308
5Pine & Co ArchitectsOct 10, 2026Oct 9, 20274380046669365000264
6Total164301801.64509.5890411679.5890411990.412223391287.4259642
7
8Cash kept14628.36Recognised Jul-Dec6009.0175342Deferred 31 Dec8619.3424658

Download this sheet as an Excel practice file (.xlsx)

The layout: one row per contract, one column per month

When a customer pays a year up front, you've received the cash but haven't delivered the service yet. A deferred revenue schedule spreads that payment over the months the service covers, so each month shows only the revenue it earned, and the unearned part sits as a deferred balance until it's earned.

The sample has four annual contracts for a small IT support business, each paid on its start date. B and C hold the service start and end dates, D the amount paid. E and F stay blank unless a contract is cancelled. G is the date service actually runs to, =IF(E2="",C2,E2), and H the number of service days, =G2-B2+1 (plus 1 because both the first and last day count). Row 1 from I onwards holds month-end dates, built with =EOMONTH(I1,1) so they're always real month ends.

Spreading by day rather than by twelfths matters for contracts that start mid-month. Harbor Yoga Studio's year starts on 15 August, so August earns 17 days, not half a month.

The monthly recognition formula

For each month, count the days the contract overlaps it: from the later of the start date and the first of the month, to the earlier of the service end and the month end. MAX(0, ...) turns months outside the contract into 0 days. Multiply the amount kept by those days over the total days.

Brightline Dental paid 6,000 for 1 July 2026 to 30 June 2027, 365 days. July has 31 of them, so July earns 6,000 x 31 / 365 = 509.59. September, with 30 days, earns 493.15. Simple twelfths would say 500 every month; day-based is more precise, and both methods add up to 6,000 over the year.

The $ signs matter: $D2, $B2, $G2 and $H2 lock the columns so the formula reads the right contract as you fill right, and I$1 locks the row so it always reads the month header as you fill down.

=($D3-$F3)*MAX(0,MIN($G3,J$1)-MAX($B3,EOMONTH(J$1,-1)+1)+1)/$H3

Result: 170

170: Harbor Yoga Studio's 17 days of service in August (15 to 31 August) at 10 a day, from 3,650 over 365 days.

A cancellation with a refund

Northfield Bakery paid 2,400 for a year from 1 September but cancelled on 30 November and was refunded 1,801.64 for the 274 unused days. Type the cancellation date in E4 and the refund in F4. Service now runs to 30 November (G4) and covers 91 days (H4), and the formula spreads the 598.36 actually kept over those days: 197.26 in September, 203.84 in October, 197.26 in November and nothing after.

Because the refund matches the unused days exactly, the daily rate is the same as before the cancellation, so the months already reported don't change. If your refund is rounded or follows other terms, the amount kept won't match the days delivered exactly, and how to treat the difference is a question for your accountant, not a formula.

=SUM(I4:N4)

Result: 598.36

598.36: everything recognised for the bakery, which equals the 2,400 paid minus the 1,801.64 refunded.

The deferred balance at an as-of date

Put the reporting date in an input cell (Q1, 31 December here). The deferred balance is the cash kept by that date minus the revenue recognised up to it. The formula in O also returns 0 for contracts that start after the as-of date (not yet paid, on this sheet's assumption) and ignores a refund that hasn't happened yet.

At 31 December, Brightline has 2,975.34 still deferred, Harbor Yoga 2,260, Pine & Co 3,384, and the cancelled bakery 0. Total: 8,619.34.

=IF($B2>$Q$1,0,$D2-IF(AND($E2<>"",$E2<=$Q$1),$F2,0)-($D2-$F2)*MAX(0,MIN($G2,$Q$1)-$B2+1)/$H2)

Result: 2975.3424658

2,975.34: the part of Brightline's 6,000 for the 181 days from January to June 2027.

What was the bakery's balance before it cancelled?

Change the as-of date to 30 September and its deferred balance is 2,400 minus 30 days of revenue, because the refund hadn't been paid yet. Here the date is typed into the formula so you can compare without changing Q1.

=IF($B4>DATE(2026,9,30),0,$D4-IF(AND($E4<>"",$E4<=DATE(2026,9,30)),$F4,0)-($D4-$F4)*MAX(0,MIN($G4,DATE(2026,9,30))-$B4+1)/$H4)

Result: 2202.7384615

2,202.74: 2,400 less the 197.26 recognised in September.

Tie it out: cash in = revenue recognised + deferred balance

The schedule must reconcile. Cash kept (paid minus refunds, 14,628.36 in B8) minus everything recognised from July to December (6,009.02 in D8) must equal the deferred balance at 31 December from column O. If it doesn't, a contract is missing from the grid, a month column has a broken reference, or the grid doesn't cover the whole period up to the as-of date.

Check each month too: the monthly totals in row 6 are what goes to the revenue line of a profit and loss statement, and the deferred balance is a liability on the balance sheet. If you prepay costs rather than receive prepayments, the same grid works on the expense side: see the prepaid expense amortization schedule.

=ROUND(O6-(B8-D8),2)

Result: 0

0: the 8,619.34 still deferred is exactly the 14,628.36 kept minus the 6,009.02 recognised.

Adding new contracts and rounding

For a new sign-up, insert a row inside the table and fill the formulas down; the totals in row 6 include it as long as the row is inside the SUM range. Better still, turn the contract list into an Excel table (Ctrl+T) so new rows pick up the formulas automatically.

The grid keeps full precision, so a month can show 509.5890411. If your books need cents, wrap the monthly formula in ROUND(...,2) and expect the last month of each contract to be off by a cent or two; many people put the remainder in the last month so each contract adds back exactly. For subscription revenue as a run rate rather than recognised revenue, see MRR.

Common mistakes

Spreading every contract in equal twelfths from the month it starts

Spread by day. A contract starting on the 15th earns only part of its first month and the rest of it 12 months later.

Forgetting the +1 in the day count

1 July to 30 June is 365 days, not 364. Without +1 the schedule over-recognises and never ties out.

Deleting a cancelled contract's row

Keep the row, add the cancellation date and refund, and let the formula stop the schedule. Deleting it removes revenue you've already reported.

Letting the as-of balance use a refund that hadn't happened yet

Only subtract the refund when the cancellation date is on or before the as-of date, as the formula in O does.

FAQ

What is a deferred revenue schedule?

It's a table that spreads payments received in advance over the periods the service is delivered, showing revenue earned each month and the unearned balance still owed in service.

How do I calculate deferred revenue in Excel?

Deferred revenue at a date = cash received for the contract - revenue recognised up to that date. Recognised revenue is the amount x days of service delivered so far / total days.

Should I recognise revenue by day or by month?

Both are used. Day-based handles mid-month starts precisely; monthly twelfths are simpler. Use whichever your accountant uses, and stick to it for every contract.

How do I handle a cancelled subscription in the schedule?

Add the cancellation date and the refund in their own columns. Service runs to the cancellation date and the amount kept is spread over the days actually delivered.

Functions used

Related guides

Updated