How To Calculate CAGR in Excel

Compound Annual Progress Fee, CAGR, is your fee of return for an funding over a particular interval.

Calculating CAGR by hand is a fairly concerned course of, so beneath we’ll go over how one can rapidly 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 sum of money you’ll have after the interval has handed.
• Starting worth, which is the sum of money you started with.
• Variety of years, which is the overall variety of years which have handed.

Beneath we’ll go over an instance of the right way to calculate CAGR for a 5 years time-frame in Excel utilizing the pattern information set proven beneath:

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

• The tip 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 presents many shortcuts, so you possibly can 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:

Observe that the equation adjustments shade to correspond with the cells you’re utilizing, so you possibly can look again and verify that your inputs are right earlier than working the equation.

You may also enter precise values into the formulation as an alternative of cell numbers. The equation would then appear to be 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 beneath.

CAGR Components in Excel as a Proportion

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

Your consequence shall be transformed to a share, as proven within the picture beneath.

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

How To Calculate CAGR Utilizing RATE Operate

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

=RATE(nper,, pv, fv)

• nper is the overall variety of durations in the timeframe you’re measuring for. Because you’re calculating annual development fee, this may be 12.
• pv is the current worth of your funding (should all the time be represented as a adverse)
• fv is future worth.

Observe 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 beneath the place nperi is 12, pv is 100, and fv is 500.

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

2. Enter the RATE formulation and enter your numbers. Observe that you simply all the time 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.

Observe which you could additionally merely enter the cell numbers that your values are in. With my pattern desk the formulation would appear to be this:

=RATE(A2,,-B2,C2)

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

Now you know the way to rapidly and simply calculate your CAGR in Excel, no hand calculations required.