Skip to main content
A prepayment is cash paid in advance for goods or services not yet received. Common examples include insurance premiums, SaaS subscriptions, rent deposits, and annual maintenance contracts. Balance sheet forecasting is always a function of last period’s value, additions, and detractions. See BS fundamentals. For prepayments, this means prepaid asset last period + cash paid in period − amortisation recognised in period.

​
What approach to use

Driver-based is the right primary approach. Build a supporting sheet listing each contract with its payment amount, due month, and coverage period. The sheet flows into both the P&L and the balance sheet, so when assumptions change, both update automatically. Use statistical when the same contracts renew at roughly the same time each year with a small price increase. It is not flexible enough for a changing contract mix. Hardcoded is acceptable for specific contracts with known amounts and timing. Use it selectively. The BS roll-forward must still hold regardless of approach: Opening + Cash Paid − Amortisation = Closing.

​
Where to forecast prepayments

  • Supporting sheet: right for most cases. List each contract with its payment amount, due month, and coverage period. The balance sheet row references the monthly total. This keeps renewals easy to update and gives a clear view of upcoming cash payments.
  • Directly on the balance sheet: right for simple single-contract setups.

​
Forecasting approaches

Set up the supporting sheetBuild the prepayments model on a supporting sheet. Group contracts by coverage period: one group for annual contracts, one for quarterly. Name the groups accordingly: Prepayments for annual, Quarterly prepayments for quarterly. Each group gets its own amortisation formula below it.Within each group, add one row per contract and hardcode the cash payment in the month it falls due.P&L recognizedAdd a P&L recognized row below each group. The formula takes the rolling sum of the last 12 months of annual payments and divides by 12:
= -sum("Prepayments" [-11:0]) / 12
For quarterly contracts, use the last 3 months divided by 3:
= -sum("Quarterly prepayments" [-2:0]) / 3
The formula is negative because it is an expense. When a new contract is added, the row picks it up in the current period and amortises it going forward. A 120k annual contract added in February produces a 10k monthly expense from that month onwards.Balance sheet roll-forwardThe balance sheet row references the P&L recognized row and the group directly:
= "Amount on BS"[-1]
+ "P&L recognized"[0]
+ "Prepayments"[0]
The group addition is positive (cash paid in); P&L recognized is negative (expense flowing out). Driver-based gives you scenario flexibility, auditability (every balance traces back to a cash payment and a coverage period), and automatic P&L and cash flow linkage.

​
FAQ

Add the renewal payment as a new cash paid addition in the month it falls due. Model the renewal as a fresh prepayment with its own total and coverage period rather than extending the existing amortisation schedule.