Excel guide · 6 min read
How to build a deferred revenue schedule in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Customer | Starts | Ends | Paid | Cancelled | Refund | Service to | Days | Jul 31, 2026 | Aug 31, 2026 | Sep 30, 2026 | Oct 31, 2026 |
| 2 | Brightline Dental | Jul 1, 2026 | Jun 30, 2027 | 6000 | 0 | 46568 | 365 | 509.5890411 | 509.5890411 | 493.15068493 | 509.5890411 | |
| 3 | Harbor Yoga Studio | Aug 15, 2026 | Aug 14, 2027 | 3650 | 0 | 46613 | 365 | 0 | 170 | 300 | 310 | |
| 4 | Northfield Bakery | Sep 1, 2026 | Aug 31, 2027 | 2400 | Nov 30, 2026 | 1801.64 | 46356 | 91 | 0 | 0 | 197.26153846 | 203.83692308 |
| 5 | Pine & Co Architects | Oct 10, 2026 | Oct 9, 2027 | 4380 | 0 | 46669 | 365 | 0 | 0 | 0 | 264 | |
| 6 | Total | 16430 | 1801.64 | 509.5890411 | 679.5890411 | 990.41222339 | 1287.4259642 | |||||
| 7 | ||||||||||||
| 8 | Cash kept | 14628.36 | Recognised Jul-Dec | 6009.0175342 | Deferred 31 Dec | 8619.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.
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.
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.
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.
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.
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
MAXLargest numberMINSmallest numberEOMONTHLast day of a monthIFPicks a result from a testANDTrue if all tests are trueSUMAdds numbersROUNDRounds to digitsDATEBuilds a date
Related guides
- How to build an accrued expenses schedule in Excel
- How to make a prepaid expense amortization schedule in Excel
- How to calculate MRR (monthly recurring revenue) in Excel
- How to make a profit and loss statement in Excel
- How to calculate net revenue retention (NRR) in Excel
- Excel formulas cheat sheet: the 25 everyone should know
- How to create a progress bar in Excel
Updated