Python and a "time value of money" problem.

Question

(I asked this question earlier today, but I did a poor job of explaining myself. Let me try again)

I have a client who is an industrial maintenance company. They sell service agreements that are prepaid 20 hour blocks of a technician's time. Some of their larger customers might burn through that agreement in two weeks while customers with fewer problems might go eight months on that same contract. I would like to use Python to help model projected sales revenue and determine how many billable hours per month that they'll be on the hook for.

If each customer only ever bought a single service contract (never renewed) it would be easy to figure sales as monthly_revenue = contract_value * qty_contracts_sold. Billable hours would also be easy: billable_hrs = hrs_per_contract * qty_contracts_sold. However, how do I account for renewals? Assuming that 90% (or some other arbitrary amount) of customers renew, then their monthly revenue ought to grow geometrically. Another important variable is how long the average customer burns through a contract. How do I determine what the revenue and billable hours will be 3, 6, or 12 months from now, based on various renewal and burn rates?

I assume that I'd use some type of recursive function but math was never one of my strong points. Any suggestions please?

Edit: I'm thinking that the best way to approach this is to think of it as a "time value of money" problem. I've retitled the question as such. The problem is probably a lot more common if you think of "monthly sales" as something similar to annuity payments.

Answer 1

If you want to consider the problem in terms of present value of future revenue (that's what "time value of money" implies to me), then you have the following parameters: discount rate D (on a monthly basis for convenience), time T a customer will take to exhaust their prepaid hours, likelihood L that they will renew when their prepaid hours are up, dollar amounts for first sale F and renewal R. This has several assumptions of course (maybe the customers who consume support faster are more likely to renew, for example -- this model doesn't account for that) but it may still be a useful first approximation.

So making a sale today is worth: F immediately for sure; plus, in T months, R more with probability L; plus, in 2T months, R more with probability L*L; and so on. So the worth of that sale is F + R*L / (D**T) + R*L*L / (D**2T) + ... = F + (R*L / D**T ) * (1 + L/D*T + L*2/(D*T)*2 + ...).

The series converges to 1 / (1 - L/(D**T)), so the overall formula in closed form (shifting to Python;-):

def salesworth(D, T, L, F, R):
  return F + (R * L) / (D**T * (1 - L / (D**T))) 

Expected billable hours can be had with the same formula, just using for F and R the number of hours in a first sale and renewal, and (if the discount rate concept does not apply to billable hours) a D of 1 (so T doesn't actually matter, as 1**T == 1 for any T;-).

Answer 2

Thanks for the assistance even though my requirements were a bit vague. After consulting someone who is extremely versed in financial mathematics, I determined that a simple formula was not an appropriate solution.

What I ended up doing is "exploding" the months into the component days using xrange() and iterating over each day. When evaluating each day, I determined whether a new contract was signed on that day, and if so, which dates in future the contract would need to be renewed. I pushed those renewal dates into a list and then summed the values.