May 22, 2024

Phidiastavern

Marketing Needs Experts

How To Calculate CAGR in Excel

Compound Annual Expansion Level, CAGR, is your level of return for an financial commitment in excess of a unique period of time.

Calculating CAGR by hand is a relatively involved system, so down below we’ll go more than how you can promptly determine CAGR in Excel.

Download 10 Excel Templates for Marketers [Free Kit]

CAGR Excel Formulation

The components for calculating CAGR in Excel is:

=(Conclusion Benefit/Starting Price) ^ (1/Quantity of Many years) – 1

The equation employs a few distinct values:

  • Finish price, which is the amount of money you’ll have after the period of time has passed.
  • Beginning price, which is the total of revenue you commenced with.
  • Variety of several years, which is the overall variety of yrs that have passed.

Under we’ll go around an illustration of how to determine CAGR for a 5 a long time time frame in Excel utilizing the sample knowledge set demonstrated underneath:

sample data

1. Identify the quantities you’ll use in your equation. Applying the sample data established earlier mentioned,

  • The stop value is 2143 (in mobile B6).
  • The commencing benefit is 1000 (in cell B2).
  • The amount of decades is 5 (in cell A6).

2. Enter your values into the formulation. 

Excel offers many shortcuts, so you can just input the cell numbers that comprise just about every of your values into the equation. Applying the sample info established higher than, the equation would be

=(B6/B2) ^ (1/A6) – 1

This is what it seems to be like in my Excel sheet:

calculate cagr in excel step 2: input your cell numbers into the equation

Observe that the equation changes color to correspond with the cells you’re employing, so you can appear back again and examine that your inputs are accurate ahead of jogging the equation.

cagr formula in excel using cell numbers

You can also enter actual values into the system as an alternative of mobile numbers. The equation would then glance like this:

=(2143/1000) ^ (1/5) – 1

3. When you have entered your values, click on enter and run the equation. Your final result will seem in the mobile containing the equation, as shown in the impression beneath.

cagr equation calculation final result in excel

CAGR System in Excel as a Percentage

Your default outcome will be demonstrated as a decimal. To view it as a share, suitable-click on on the mobile your result is in, pick out Format Cells and then Share in the dialogue box.

Your end result will be converted to a percentage, as shown in the impression below.

cagr forumla in excel expressed as a percentage

Now let’s go around a shortcut for calculating CAGR in Excel utilizing the Rate functionality.

How To Calculate CAGR Making use of Fee Function

The Rate function can help you compute the fascination level on an financial commitment more than a interval of time.The system for calculating CAGR is: 

=Charge(nper,, pv, fv)

  • nper is the complete number of durations in the time body you’re measuring for. Due to the fact you’re calculating yearly growth charge, this would be 12.
  • pv is the present price of your financial commitment (have to generally be represented as a unfavorable)
  • fv is long term price.

Note that the common Fee equation involves extra variables, but you only will need the previously mentioned a few to compute your CAGR.

Let us run an equation using the sample desk beneath in which nperi is 12, pv is 100, and fv is 500.

calculate CAGR using RATE formula in excel

1. In your sheet, select the cell that you want to have your CAGR. I selected mobile B5.

2. Enter the Charge method and input your numbers. Take note that you normally need to express your present value as a detrimental, or you’ll acquire an mistake information.

This is what my formula seems to be like.

sample cagr equation in excel using rate formulaTake note that you can also simply enter the mobile numbers that your values are in. With my sample table the formulation would search like this:

=Charge(A2,,-B2,C2)

3. Simply click enter and run your equation. Working with the sample knowledge, my CAGR is 14%.

Now you know how to immediately and simply compute your CAGR in Excel, no hand calculations necessary.

excel marketing templates