Summing compounded growth rate periods

[Guest]

Hi,

I've got a worksheet in which I have a starting sales volume in month 1. I expect that sales volume to grow monthly at a rate of, say, 3%. How can I calculate what my TOTAL sales for months 1-3 will be? Or months 1-6? I'd like to do this without having to create a separate cell for each month.

Thanks!

 

[Ron Rosenfeld]

Hi,

I've got a worksheet in which I have a starting sales volume in month 1. I expect that sales volume to grow monthly at a rate of, say, 3%. How can I calculate what my TOTAL sales for months 1-3 will be? Or months 1-6? I'd like to do this without having to create a separate cell for each month.

Thanks!

If I understand you correctly, the following *array-entered* formula should
give you the total of your expected sales between any two months.


=SUM(Volume*(1+Rate)^(ROW(INDIRECT(startper&":"&endper))-1))

To *array-enter* a formula, after typing or pasting it in, hold down
<ctrl><shift> while hitting <enter>. XL will place braces {...} around the
formula.

In the above:

Volume = starting sales volume
Rate = monthly growth rate
startper = the first month to look at
endper = the last month to look at.

These can all be named ranges, or refer to specific cells.

For example, if:

Volume = 100
Rate = 3%

startper = 1
endper =3

The formula --> 309.09 is the sum of sales from month 1 to month 3

startper = 5
endper = 9

597.5479128 sales total from month 5 through month 9



--ron

 

[GB]

Hi,

I've got a worksheet in which I have a starting sales volume in month 1. I

expect that sales volume to grow monthly at a rate of, say, 3%. How can I
calculate what my TOTAL sales for months 1-3 will be? Or months 1-6? I'd
like to do this without having to create a separate cell for each month.

Thanks!

Use:

Starting sales * FV(3%,6,-1,0,0) to get the total figure for the first
6 mths (for example)

Geoff

PS: This assumes 3% compound growth