Calculate unit sales with recurring billing AND growth rate.



Hi folks,

(I posted this a few days ago to the worksheetfunctions group, but it
appears to be pretty quiet over there...)

I'm building a worksheet to forecast unit sales. Our business does
recurring billing monthly (on a subscription), so I need to be able to
plug in a growth rate and have each month show a sum that includes in
the growth while also adding in all existing subscriptions. In other
words, I want to be able to show, say, a 2% growth rate each month for
new subscriptions, while still adding in existing ones into the total.

So, for instance, if I had a 10% monthly growth rate, and I started in
month 1 with 25 subscriptions, my monthly totals would look like:

1 2 3 4 5 6 <-- Month
25 53 83 116 153 193 <-- Subscription sales (rounded)

I can do this using two cells for each month (one to calculate growth,
the other to add all the months, including the current month,
together), but I'd like to find a formula that will allow me to
calculate and show each month's total in a single cell.

I'm sure there's a nice easy formula/function, and I'm just missing






previous months sales * (1 + growth rate) = new months sales

am i missing something

Sandy Mann

I don't know about you missing something but I deffnitely am.

With the OP's 25 in A1, =ROUND(A1+A1*1.1,0) will give the second value of 53
and =ROUND(A1+B1*1.1,0) will give the 83 third value but extending the logic
to =ROUND(A1+B1+C1*1.1,0) returns 169 instead of the 116 required value?

To the OP:
What formulas have you been using in the two cell for each month?




Hi Sandy and Duane,

Thanks very much for your responses. I was originally calculating
things the way you are, then realized that this meant that growth was
being calculated on both new sales and existing (recurring) sales. I
need the growth to be based only on the previous month's NEW sales,
without including the existing sales in the growth calculation. Once
I've done that, I can then add in the recurring sales. I'm just
wondering if there's a way to do all this in a single cell
calculation. Currently, I'm doing it in two cells. Here's another,
clearer example (again assuming a 10% growth on for new sales):

1 2 3 4 5 6 <-- Month
25 28 30 33 37 40 <-- New Sales (based on 10% growth)
0 25 53 83 116 153 <-- Recurring sales (total of all
------------------------------- previous sales)
25 53 83 116 153 193 <-- Total New & Recurring Sales

Hope this makes more sense, and explains how I'm arriving at my

Thanks again for your help!


Sandy Mann


With your starting value of 25 in cell B21, try


in C1 and copy across.

By the way the way that I think your groth rate is 110% not 10%






Thanks, Sandy! I'll give your solution a go.

I'm pretty sure what I've got is a 10% growth rate, not 110%. At 110%,
you'd see the new sales more than double each month, right? Now, we
could say that this month's new sales are 110% of last month's new
sales, which means we grew by 10%. I think it's a semantics thing
(unless I'm very confused, here!).



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question