Recognising Annual Prepaid Subscription Revenue in Excel

Why Prepaid Subscriptions Create a Balance Sheet Liability

When a SaaS customer pays upfront for a 12-month subscription, the cash hits your bank account immediately — but you haven't earned that revenue yet.

Under ASC 606 and IFRS 15, revenue is recognised as the performance obligation is satisfied, which for a SaaS subscription means ratably over the service period.

The full prepayment is recorded as deferred revenue (a liability) and systematically released to recognised revenue each month. This ensures your P&L reflects economic reality rather than billing timing.

Step-by-Step: Building a Recognition Schedule in Excel

Step 1: Set Up Your Contract Input Sheet

Create a worksheet with these columns for each contract:

• Contract ID — unique identifier
• Customer name
• Contract start date
• Contract end date
• Total contract value (TCV) — the prepaid amount
• Billing date — when cash was received

For a £24,000 annual subscription starting 1 March 2026 and ending 28 February 2027, your row would show TCV of £24,000 and a 12-month term.

Step 2: Calculate the Monthly Recognition Amount

Divide TCV by the number of months in the contract term. For a straight 12-month contract:

£24,000 ÷ 12 = £2,000 per month

If the contract starts or ends mid-month, pro-rate those partial months by day count. Use Excel's DAYS function to calculate the fraction:

=TCV * DAYS(end_of_month, start_date, ) / DAYS(contract_end, contract_start)

In practice: Day-count proration matters most for contracts with high TCV or short terms. For a £120,000 annual contract starting mid-month, the difference between day-count and 1/12 allocation can exceed £1,000 in the first period — enough to be material in aggregate.

Step 3: Build the Monthly Schedule

Create a grid with months as columns and contracts as rows. Each cell contains the revenue recognised for that contract in that month.

The sum of each row should equal the TCV. The sum of each column gives you total recognised revenue for the month.

A typical layout:

Step 4: Track the Deferred Revenue Balance

Add a deferred revenue rollforward below or alongside your recognition schedule. For each contract, track:

• Opening deferred revenue — prior month's closing balance
• (+) Cash received — new prepayments in the period
• (-) Revenue recognised — from Step 3
• = Closing deferred revenue — carried forward

The closing balance should always equal the sum of future months' recognition amounts. Use a check formula to verify this:

=IF(closing_balance = SUM(future_months), "OK", "ERROR")

In practice: This check formula is your first line of defence against data-entry errors. If any row shows "ERROR", investigate before posting the month-end journal. Common causes include a missing cash receipt entry or an incorrect contract end date.

Step 5: Generate Journal Entries

Each month, you need two journal entries per contract:

At billing (when cash is received):

• Dr Cash £24,000
• Cr Deferred Revenue £24,000

At month-end (recognition):

• Dr Deferred Revenue £2,000
• Cr Subscription Revenue £2,000

Automate this in Excel with an IF formula that generates the debit/credit based on the recognition schedule.

In practice: Post the billing journal on the date cash is received. Post the recognition journal as a single month-end batch (one journal line per contract, or one summary line for the total). Keeping these two entry types separate makes reconciliation straightforward.

Common Prepaid Revenue Mistakes to Avoid

1. Recognising revenue at billing.
   The most common error. The full prepayment must go to deferred revenue first, then release ratably.
2. Forgetting to pro-rate partial months.
   A subscription starting on the 15th should not recognise a full month of revenue in the first period.
3. Not reconciling deferred revenue to the GL.
   Your spreadsheet schedule must tie back to the general ledger balance. Discrepancies compound over time and are painful to unwind.
4. Hardcoding amounts instead of using formulas.
   When contracts are modified, hardcoded cells don't update. Always derive recognition amounts from TCV and term length.
5. No version control.
   Overwriting last month's file loses audit trail. Save each period as a separate version or use a tool that maintains history.

When Excel Stops Scaling

Excel works well for 10-50 contracts. Beyond that, the spreadsheet becomes fragile: formulas break when rows are inserted, cross-references between sheets drift, and reconciliation takes hours instead of minutes.

Common signs you've outgrown Excel:

• More than 50 active contracts
• Frequent mid-term modifications (upgrades, downgrades, early terminations)
• Multiple billing frequencies (monthly, quarterly, annual) in the same book
• Audit findings related to manual errors
• Monthly close takes more than a day for revenue

Download the Free Template

We've built a free Excel template that implements everything described above:

• Contract input sheet
• Monthly recognition schedule
• Deferred revenue rollforward
• Journal entry generator

Download the Revenue Recognition Excel Template