Using CAGR to project sales

Joe_Fossella1005 

For quicker dispositive answers, provide concrete examples.

If first year data are in B2 and C2, and the expected annual growth rate (CAGR) is D2, the following demonstrates how to forecast the next 5 years.

Formulas:

B3: =B2*(1+$D$2)

C3: =C2*(1+$D$2)

Copy B3:C3 into B4:C7

Joe_Fossella1005 

Calculate a compound annual growth rate (CAGR)

A compound annual growth rate (CAGR) measures the rate of return for an investment — such as a mutual fund or bond — over an investment period, such as 5 or 10 years. The CAGR is also called a "smoothed" rate of return because it measures the growth of an investment as if it had grown at a steady rate on an annually compounded basis. To calculate CAGR, use the XIRR function.

Calculation of the growth rate with Excel

Here are two formulas that can be used alternatively and deliver the same result. Put the formula in a cell. You can either enter the values for the start value, end value and number of year changes directly into the formula or link them from other cells. You can also try both and check them each.

Example of Excel formula:

=(POWER((FINAL VALUE/INITIAL VALUE),(1/N))-1)x100

or

=((FINAL VALUE/INITIAL VALUE)^(1/N)-1)x100

Excel only offers the square root (n=2) as a pure square root function, so you have to think a bit outside the box. In most cases, this is not sufficient as a number for the turn of the year in the period under consideration. This takes advantage of the fact that the nth root in a calculation corresponds to the reciprocal (i.e. reversed) power. This means that if n=5, the calculation "high" ^1/5 or ^1/n can also be used. This procedure also works with pocket calculators.

Determine CAGR or annual growth rate using an online calculator.

Hope I was able to help you with this info.

NikolinoDE

I know I don't know anything (Socrates)