How To Calculate CAGR in Excel

Compound Yearly Progress Amount, CAGR, is your level of return for an financial commitment over a precise interval.

Domain & Hosting bundle deals!

Calculating CAGR by hand is a rather involved system, so under we’ll go around how you can speedily estimate CAGR in Excel.

Download 10 Excel Templates for Marketers [Free Kit]

CAGR Excel System

The method for calculating CAGR in Excel is:

=(Conclude Worth/Commencing Price) ^ (1/Range of Decades) – 1

The equation takes advantage of three various values:

  • Stop benefit, which is the total of revenue you’ll have soon after the period of time has handed.
  • Commencing benefit, which is the total of funds you commenced with.
  • Range of several years, which is the whole range of decades that have handed.

Below we’ll go in excess of an illustration of how to calculate CAGR for a 5 decades time body in Excel making use of the sample info set revealed down below:

sample data

1. Identify the figures you will use in your equation. Using the sample details set earlier mentioned,

  • The close worth is 2143 (in mobile B6).
  • The commencing price is 1000 (in cell B2).
  • The range of years is 5 (in mobile A6).

2. Enter your values into the formula. 

Excel offers lots of shortcuts, so you can merely enter the cell figures that contain every of your values into the equation. Using the sample info established above, the equation would be

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

This is what it seems like in my Excel sheet:

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

Observe that the equation changes colour to correspond with the cells you’re utilizing, so you can seem again and check out that your inputs are suitable before managing the equation.

cagr formula in excel using cell numbers

You can also enter true values into the system in its place of cell quantities. The equation would then glimpse like this:

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

3. Once you’ve entered your values, click enter and run the equation. Your outcome will appear in the mobile containing the equation, as proven in the picture underneath.

cagr equation calculation final result in excel

CAGR Method in Excel as a Proportion

Your default outcome will be demonstrated as a decimal. To see it as a percentage, suitable-simply click on the cell your consequence is in, decide on Structure Cells and then Proportion in the dialogue box.

Your result will be transformed to a share, as demonstrated in the impression under.

cagr forumla in excel expressed as a percentage

Now let us go above a shortcut for calculating CAGR in Excel employing the Fee perform.

How To Compute CAGR Using Fee Functionality

The Fee perform allows you work out the interest price on an financial commitment more than a time period of time.The formulation for calculating CAGR is: 

=Amount(nper,, pv, fv)

  • nper is the overall range of durations in the time frame you’re measuring for. Considering the fact that you’re calculating annual development amount, this would be 12.
  • pv is the existing benefit of your investment decision (have to always be represented as a unfavorable)
  • fv is long run benefit.

Take note that the standard Amount equation incorporates a lot more variables, but you only need to have the earlier mentioned 3 to determine your CAGR.

Let us operate an equation working with the sample table under exactly where nperi is 12, pv is 100, and fv is 500.

calculate CAGR using RATE formula in excel

1. In your sheet, pick out the cell that you want to comprise your CAGR. I chosen cell B5.

2. Enter the Price system and input your numbers. Note that you usually need to express your present value as a damaging, or you’ll obtain an error concept.

This is what my system seems like.

sample cagr equation in excel using rate formulaTake note that you can also basically enter the cell figures that your values are in. With my sample desk the formulation would search like this:

=Price(A2,,-B2,C2)

3. Simply click enter and operate your equation. Using the sample information, my CAGR is 14%.

Now you know how to quickly and easily calculate your CAGR in Excel, no hand calculations necessary.

excel marketing templates

You May Also Like