Calculating percentage growth rate from a cumulative figure

[Bruno]

Is there a function to calculate a linear percentage growth rate given an initial figure, a number a periods and a total over that period?

For example, given an initial sales figure of $300, and a 6-month sales period, by what percentage would sales have to increase every period such that, by the end of that 6-month period, cumulative sales for the period are $1,500?

It seemed a fairly straight forward question to be, but for the life of me I can't figure it out...

 

[joeu2004]

Is there a function to calculate a linear percentage growth
rate given an initial figure, a number a periods and a total
over that period?

For example, given an initial sales figure of $300, and a
6-month sales period, by what percentage would sales have
to increase every period such that, by the end of that
6-month period, cumulative sales for the period are $1,500?

I'm a little confused by your terminology and example.

First, if the "initial sales figure" is the initial __monthly__ sales, note
that 6 times $300 is $1800. Ergo, no increase(!) is needed to achieve
"cumulative sales" of $1500 for 6 months.

Second, by "linear percentage growth", I assume you mean non-compounding.
But "percentage [...] increase every period" sounds like a compounded growth
rate. Which do you really mean?

----- (linear growth)

Suppose the initial (average) monthly sales is $300, and you want the
cumulative sales over 6 months to be $2500 -- usually something larger than
$300 times 6 ($1800).

More generally, suppose A1 contains the initial (average) monthly sales, A2
contains the required cumulative sales, and A3 contains the number of
monthly periods.

Then the "linear growth" (not the linear percentage growth) can be computed
as follows (in A4):

=(A2-A1*A3)*2/A3/(A3+1)

You can confirm that result by entering =ROUND($A$1+$A$4*ROWS($B$1:B1),2)
into B1 and copying B1 down through A3 rows (e.g. through B6). Then
=SUM(B1:B6) should be about A2.

It might be off by as much as $0.01*A3 due to rounding. The use of
$A$4*ROWS($B$1:B1) instead of incremental addition to rounded figures is
intended to minimize rounding error.

Also note that A4 might be negative if A2 is less than A1*A3, as it is with
your example. That is, A4 might represent an incremental decrease as well
as an incremental increase.

You might express the linear growth as a percentage of initial (average)
monthly sales as follows:

=A4/A1

formatted as Percentage. But I think it is ambiguous and it might be
confusing to say that "11.11% percentage increase every month". I would
simply say "increase by $33.33 every month".

----- (compounded growth rate)

On the other hand, if you actually want the compounded growth rate -- the
"percentage [...] increase every period", you might simply do the following
(in A4):

=RATE(A3,-A1,0,A2,1)

formatted as Percentage. See the example above for the explanation for the
expected contents of A1, A2 and A3.

You can confirm that result by entering
=ROUND($A$1*(1+$A$4)^ROWS($B$1:B1),2) into B1 and copying B1 down through A3
rows (e.g. through B6). Then =SUM(B1:B6) should be about A2.

Again, it might be off by some relatively small amount due to rounding. The
use of (1+$A$4)^ROWS($B$1:B1) instead of incremental multiplication of
rounded figures is intended to minimize rounding error.

 

[Bruno]

Sorry about the confusion; that's what happens when I'm both frustrated ANDdon't have enough coffee first thing in the morning....

Anyway, thanks a lot for the answer; that's exactly what I was looking for:a method for calculating a constant, compounded rate of change given a beginning figure, a cumulative figure and a number of periods.

For the benefits of the multitudes who are sure to look for your answer farinto the future, it was aimed at the REVERSE of the simple situation: if your 1st month sales figure is $100 and your sales increase - on a month-over-month basis - by 10%, your sales for month 2 would be $110 and for month 3 $121, for a total 3-month cumulative sales figure of $331. Easy.

The reverse question is: if your 1st month sales figure is $100 and your 3-month cumulative sales figure is $331, by what constant percentage have your sales increased on a monthly basis?

And I now know how to calculate it. Thanks again!

 

[joeu2004]

it was aimed at the REVERSE of the simple situation:
if your 1st month sales figure is $100 and your sales
increase - on a month-over-month basis - by 10%, your
sales for month 2 would be $110 and for month 3 $121,
for a total 3-month cumulative sales figure of $331. [....]
The reverse question is: if your 1st month sales figure
is $100 and your 3-month cumulative sales figure is $331,
by what constant percentage have your sales increased
on a monthly basis?
And I now know how to calculate it. Thanks again!

You're welcome! But just to clarify, my RATE formula applied to your
example above assumes that $110 ($100 plus 10%) is the required 1st month
sales figure, not $100. For your clarified problem statement, you should
eliminate the last parameter for RATE (or change it to zero), to wit:

=RATE(A3,-A1,0,A2)

 

[bruno.lerer]

You're welcome! But just to clarify, my RATE formula applied to your

example above assumes that $110 ($100 plus 10%) is the required 1st month
sales figure, not $100. For your clarified problem statement, you should
eliminate the last parameter for RATE (or change it to zero), to wit:

=RATE(A3,-A1,0,A2)

Agreed; as a matter of good housekeeping, as it were, I prefer to set the [type]parameter to zero, so it looks like this:

=RATE(A3,-A1,0,A2,0)

This makes it clear that eliminating the last parameter was intentional, not an oversight.