How To Calculate CAGR in Excel

Compound Annual Enlargement Fee, CAGR, is your charge of go back for an funding over a particular length.

Calculating CAGR by way of hand is a relatively concerned procedure, so underneath we’ll pass over how you’ll briefly calculate CAGR in Excel.

CAGR Excel Components

The formulation for calculating CAGR in Excel is:

=(Finish Price/Starting Price) ^ (1/Selection of Years) – 1

The equation makes use of 3 other values:

Finish price, which is the amount of cash you’ll have after the length has handed.
Starting price, which is the amount of cash you started with.
Selection of years, which is the full selection of years that experience handed.

Beneath we’ll pass over an instance of how you can calculate CAGR for a 5 years time period in Excel the usage of the pattern information set proven underneath:

1. Determine the numbers you’ll use for your equation. The use of the pattern information set above,

The top price is 2143 (in cellular B6).
The start price is 1000 (in cellular B2).
The selection of years is 5 (in cellular A6).

2. Enter your values into the formulation.

Excel provides many shortcuts, so you’ll merely enter the cellular numbers that comprise every of your values into the equation. The use of the pattern information set above, the equation can be

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

That is what it looks as if in my Excel sheet:

Be aware that the equation adjustments colour to correspond with the cells you’re the usage of, so you’ll glance again and take a look at that your inputs are proper sooner than operating the equation.

You’ll be able to additionally input precise values into the formulation as an alternative of cellular numbers. The equation would then seem like this:

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

3. If you’ve entered your values, click on input and run the equation. Your outcome will seem within the cellular containing the equation, as proven within the symbol underneath.

CAGR Components in Excel as a Proportion

Your default outcome can be proven as a decimal. To view it as a proportion, right-click at the cellular your result’s in, make a selection Layout Cells after which Proportion within the discussion field.

Your outcome can be transformed to a proportion, as proven within the symbol underneath.

Now let’s pass over a shortcut for calculating CAGR in Excel the usage of the Fee serve as.

How To Calculate CAGR The use of RATE Serve as

The RATE serve as is helping 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 full selection of sessions within the time period you’re measuring for. Because you’re calculating annual enlargement charge, this may be 12.
pv is the prevailing price of your funding (will have to at all times be represented as a damaging)
fv is long run price.

Be aware that the usual RATE equation comprises extra variables, however you most effective want the above 3 to calculate your CAGR.

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

1. On your sheet, make a selection the cellular that you need to comprise your CAGR. I decided on cellular B5.

2. Input the RATE formulation and enter your numbers. Be aware that you just at all times wish to specific your provide price as a damaging, otherwise you’ll obtain an error message.

That is what my formulation looks as if.

Be aware that you’ll additionally merely input the cellular numbers that your values are in. With my pattern desk the formulation would seem like this: