Calculating future revenue projection on recurring payments with various terms

Question

I have a service with various packages, that can all be purchased monthly, quarterly, semi-annually, and yearly.

Each package has a due_on date which I increment when someone renews.

It's easy to calculate how much revenue I can roughly expect this month, by checking who has a due_on date this month.

Where I'm running into trouble is calculating how much revenue I can expect each month over the next year. I can't base it on due_on, because some people will be paying 12 times in the next year, and some 6, etc.

What is the best way to do this? Note: for this purpose I am ignoring attrition. I am working in PHP and MySQL, but I'm asking for theory so that shouldn't matter too much.

Answer 1

Let's take the case of figuring out monthly revenue for the next 12 months.

I can see two basic ways to do it:

1. Create an array (or hash) of buckets for each of the next 12 months. Then, iterate through each active subscription and for each subscription add the expected revenue from that to every appropriate bucket. For example, if the terms are monthly, add that payment to every bucket; if the term is quarterly, add that payment to the current due_date month's bucket and the buckets for 3, 6, and 9 months after that; etc. This is fairly straightforward to code, but with enough customers generating the report could take some time.

2. Create a temporary table in your mysql database that has columns of (month, amount), fill it with a series of insert statements (there are a bunch; I'll get to them in a minute), and then do a report over that table.

   This is a bit more unusual, so I'm going to spell it out in more detail. I'm making some assumptions about your SQL table structure, but I'm trying to keep it as generic as possible.

   So first you need the temporary table, and we'll fill it initially with per-month revenue based on the current due_on date:

   CREATE TEMPORARY TABLE FutureRevenueReport
   SELECT
     CONCAT(YEAR(s.due_on), '-', MONTH(s.due_on)) as revenue_month,
     s.due_amount as revenue_amount
   FROM subscriptions s WHERE s.active = 'True';
   

   Now in the same mysql session, execute this series of insert statements to fill in future revenue:

   INSERT INTO FutureRevenueReport(revenue_month, revenue_amount)
   SELECT CONCAT(YEAR(s.due_on + INTERVAL 1 MONTH), '-', 
                 MONTH(s.due_on + INTERVAL 1 MONTH)), s.due_amount
   FROM subscriptions s WHERE s.active = 'True' AND s.term = 'MONTHLY';
   
   INSERT INTO FutureRevenueReport(revenue_month, revenue_amount)
   SELECT CONCAT(YEAR(s.due_on + INTERVAL 2 MONTH), '-', 
                 MONTH(s.due_on + INTERVAL 2 MONTH)), s.due_amount
   FROM subscriptions s WHERE s.active = 'True' AND s.term = 'MONTHLY';
   
   INSERT INTO FutureRevenueReport(revenue_month, revenue_amount)
   SELECT CONCAT(YEAR(s.due_on + INTERVAL 3 MONTH), '-', 
                 MONTH(s.due_on + INTERVAL 3 MONTH)), s.due_amount
   FROM subscriptions s WHERE s.active = 'True' AND s.term = 'MONTHLY';
   -- etc, up to + INTERVAL 11 MONTH
   
   -- Now the quarterly:
   INSERT INTO FutureRevenueReport(revenue_month, revenue_amount)
   SELECT CONCAT(YEAR(s.due_on + INTERVAL 3 MONTH), '-', 
                 MONTH(s.due_on + INTERVAL 3 MONTH)), s.due_amount
   FROM subscriptions s WHERE s.active = 'True' AND s.term = 'QUARTERLY';
   
   INSERT INTO FutureRevenueReport(revenue_month, revenue_amount)
   SELECT CONCAT(YEAR(s.due_on + INTERVAL 6 MONTH), '-', 
                 MONTH(s.due_on + INTERVAL 6 MONTH)), s.due_amount
   FROM subscriptions s WHERE s.active = 'True' AND s.term = 'QUARTERLY';
   -- And the same for 9 months
   -- Then do the same thing for SEMI-ANNUALLY and + INTERVAL 6 MONTH
   
   -- And now the report:
   SELECT revenue_month, sum(revenue_amount) as revenue from FutureRevenueReport
   GROUP BY revenue_month;