How To Calculate CAGR in Excel

Compound Annual Development Price, CAGR, is your charge of return for an funding over a particular interval.

Calculating CAGR by hand is a somewhat concerned course of, so under we’ll go over how one can shortly calculate CAGR in Excel.

CAGR Excel Components

The formulation for calculating CAGR in Excel is:

=(Finish Worth/Starting Worth) ^ (1/Variety of Years) – 1

The equation makes use of three totally different values:

• Finish worth, which is the amount of cash you’ll have after the interval has handed.
• Starting worth, which is the amount of cash you started with.
• Variety of years, which is the entire variety of years which have handed.

Under we’ll go over an instance of the best way to calculate CAGR for a 5 years timeframe in Excel utilizing the pattern information set proven under:

1. Establish the numbers you’ll use in your equation. Utilizing the pattern information set above,

• The top worth is 2143 (in cell B6).
• The start worth is 1000 (in cell B2).
• The variety of years is 5 (in cell A6).

2. Enter your values into the formulation. 

Excel affords many shortcuts, so you may merely enter the cell numbers that include every of your values into the equation. Utilizing the pattern information set above, the equation can be

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

That is what it appears to be like like in my Excel sheet:

Word that the equation modifications coloration to correspond with the cells you’re utilizing, so you may look again and test that your inputs are appropriate earlier than operating the equation.

You can too enter precise values into the formulation as a substitute of cell numbers. The equation would then appear like this:

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

3. When you’ve entered your values, click on enter and run the equation. Your consequence will seem within the cell containing the equation, as proven within the picture under.

CAGR Components in Excel as a Proportion

Your default consequence will probably be proven as a decimal. To view it as a proportion, right-click on the cell your result’s in, choose Format Cells after which Proportion within the dialogue field.

Your consequence will probably be transformed to a proportion, as proven within the picture under.

Now let’s go over a shortcut for calculating CAGR in Excel utilizing the Price operate.

How To Calculate CAGR Utilizing RATE Operate

The RATE operate helps you calculate the rate of interest on an funding over a time period.The formulation for calculating CAGR is: 

=RATE(nper,, pv, fv)

• nper is the entire variety of intervals in the timeframe you’re measuring for. Because you’re calculating annual development charge, this could be 12.
• pv is the current worth of your funding (should at all times be represented as a adverse)
• fv is future worth.

Word that the usual RATE equation consists of extra variables, however you solely want the above three to calculate your CAGR.

Let’s run an equation utilizing the pattern desk under the place nperi is 12, pv is 100, and fv is 500.

1. In your sheet, choose the cell that you just wish to include your CAGR. I chosen cell B5.

2. Enter the RATE formulation and enter your numbers. Word that you just at all times want to specific your current worth as a adverse, otherwise you’ll obtain an error message.

That is what my formulation appears to be like like.

Word you could additionally merely enter the cell numbers that your values are in. With my pattern desk the formulation would appear like this:

=RATE(A2,,-B2,C2)

3. Click on enter and run your equation. Utilizing the pattern information, my CAGR is 14%.

Now you understand how to shortly and simply calculate your CAGR in Excel, no hand calculations required.