A subscription business such as software-as-a-service (Saas) business is one in which customers subscribe for products or services by paying a monthly fee. This monthly recurring revenue calculator can be used by such a business to forecast the recurring revenue it will have for each of the next 60 months. The revenue is based on the number of customers the business has at the end of each month.
Using the Monthly Recurring Revenue Calculator
By entering details relating to the subscription business model the monthly recurring revenue calculator can be used to estimate the following.
- The monthly recurring revenue at the end of a particular month in the future.
- The month in which the business will reach a target level of monthly recurring revenue.
In addition, the calculator provides a graph showing the monthly recurring revenue in comparison to target revenue for the next 60 months.
The monthly recurring revenue calculator is used as follows.
1. Enter the Starting Number of Customers
Enter the number of customers at the start of period 1. In the case of a new subscription based business the number of customers at the start will be zero. For an established business enter the current number of customers.
2. Enter the Churn Rate
The churn rate is the rate per month at which customers cancel their subscription service. Either enter an estimate based on industry comparisons or if historical information is available, calculate the rate using the following formula.
So for example, if the cancellations during the month chosen are 40 and the customers at the start of the month are 1,000, then the churn rate is calculated as follows.
Churn rate = Cancellations during the month / Customers at the start of the month Churn rate = 40 / 1,000 = 4%
3. Enter the Customer Additions
Enter the number of new customers added each month.
4. Enter the Customer Additions Growth Rate
The customer growth rate is the percentage by which the additions entered in #3. above grows each month.
For example, if the monthly additions entered are 400, and they are expected to grow at the rate of 1% each month, the calculator will calculate the following months additions as 400 x 101% = 404, and the next month as 404 x 101% = 408 and so on.
Average Revenue / Customer
5. Enter the Average Revenue / Customer
Enter the amount of recurring revenue paid each month by a customer. For example, if the current monthly subscription for the service is 25.00, enter 25.00. If there is more than one level of subscription then enter the average revenue for all subscriptions, sometimes referred to as the average revenue per user or ARPU.
6. Enter the Revenue Growth Rate
The revenue growth rate is the percentage by which the monthly revenue subscription entered in #5. above grows each month. The growth can be for a number of reasons, but typically reflects unit subscription price increases or perhaps a change of mix in the subscriptions offered causing the average unit price to increase.
For example, if the starting average customer revenue is entered as 25.00 and is expected to grow at the rate of 1.50% each month, the calculator will calculate the following months revenue as 25.00 x 101.50% = 25.38, and the next month as 25.38 x 101.50% = 25.76 and so on.
Monthly Recurring Revenue (MRR)
7. Enter the Month Number
Enter a month number between 1 and 60 (5 years).
The monthly recurring revenue calculator calculates the recurring revenue based on the the number of customers at the end of the specified month.
Monthly Recurring Revenue Target
8. Enter the Target MRR
Enter a target monthly recurring revenue required. If the target revenue is not achievable within 5 years (60 months), the calculator will show a message indicating this fact.
The monthly recurring revenue calculator calculates the month at which the customer MRR reaches the target level of revenue.
Finally the calculator provides a graph showing the monthly recurring revenue based on customers at the end of each month for the next 60 months (blue line), compared to the target level of revenue (red line).
The point at which the lines intersect is the month in which the customer MRR reaches the target level of revenue. If the lines do not intersect, it means that the target level of revenue cannot be met within the 60 month period, and either the subscription business model inputs or the target level needs to be changed.
Monthly Recurring Revenue Calculator Download
The monthly recurring revenue calculator is available for download in Excel format by following the link below.
Users use this monthly recurring revenue calculator at their own risk. We make no warranty or representation as to its accuracy and we are covered by the terms of our legal disclaimer, which you are deemed to have read. This is an example of a calculator that you might use when estimating how to calculate monthly recurring revenue for a subscription business financial projection. It is purely illustrative of monthly recurring revenue Excel spreadsheet software. This is not intended to reflect general standards or targets for any particular company or sector. If you do spot a mistake in the monthly recurring revenue model calculator, please let us know and we will try to fix it.
About the Author
Chartered accountant Michael Brown is the founder and CEO of Plan Projections. He has worked as an accountant and consultant for more than 25 years and has built financial models for all types of industries. He has been the CFO or controller of both small and medium sized companies and has run small businesses of his own. He has been a manager and an auditor with Deloitte, a big 4 accountancy firm, and holds a degree from Loughborough University.