more-arw search

Q&A Forum

Excel formula for subscription revenue recognition

C. Baylis's Profile

accrued revenue vs deferred revenue calculation in ExcelI am building an excel spreadsheet to recognize subscription revenue (including annual renewals) for use in a forecast model.  Our subscriptions are generally annual, and are at various points throughout the month and the year (ie not just on the first day of the month).  Does anyone have an excel formula they would like to share with me?  I am fairly excel-friendly but am hoping to not have to recreate the wheel.  Thank you very much.



Robert Honeyman
Title: CFO
Company: Advanced Predictive Analytics
(CFO, Advanced Predictive Analytics) |

You must be talking about forecasting accretion of revenue for contracts sitting in deferred revenue on your balance sheet. The approach I used was to create a three-tabbed spreadsheet with the following tabs:

Amortization Schedule
Cumulative Amortization

The amortization schedule yields period revenue. Reconciliation yields balance sheet deferred revenue. Cumulative amortization controls for ensuring things zero out properly.

Columns in the amortization schedule should include start date, end date, as well as the amount. You're probably better off calculating a daily amortization, since that eases the computational complexities of the starting month and ending month. Other than that, it's pretty straight-forward.


Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email [email protected] to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.