# Excel - Calculating percentage growth rate from a cumulative figure

Asked By Bruno on 14-Jun-12 08:37 AM
```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 cannot figure it out...```

joeu2004 replied to Bruno on 14-Jun-12 01:23 PM
```I am 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

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

It might be off by as much as \$0.01*A3 due to rounding.  The use of
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
(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 replied to joeu2004 on 14-Jun-12 03:12 PM
```Sorry about the confusion; that is what happens when I am both frustrated AND=
do not have enough coffee first thing in the morning....

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

For the benefits of the multitudes who are sure to look for your answer far=
into the future, it was aimed at the REVERSE of the simple situation: if y=
our 1st month sales figure is \$100 and your sales increase - on a month-ove=
r-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 you=
r sales increased on a monthly basis?

And I now know how to calculate it. Thanks again!```
joeu2004 replied to Bruno on 14-Jun-12 03:20 PM
```[....]

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.lere replied to joeu2004 on 14-Jun-12 05:26 PM
```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.```