How to Easily Calculate CAGR (Using Excel Formulas)

How do you assess the annual growth of a business over a certain time (say 5 years 10 years or even more)?

It is simple to tell that an investment grew by say 10% since the last year. But as the time horizon increases beyond a year, it no longer remains that simple since the growth rate for each year varies 🚀

So how do you define the annual growth rate of an investment for a given period of time?

The answer is, through CAGR 💡

In this tutorial, I will teach you the concept of CAGR from scratch, how it works, and how you can calculate it in Microsoft Excel using a variety of functions.

Get your free sample practice workbook, and come along with me.

Table of Contents

What is CAGR

CAGR stands for Compound Annual Growth Rate.

It defines how an investment has grown over time and yields the annual growth rate for that investment.

The Concept of CAGR

The easiest way to understand CAGR is to evaluate it in terms of an investment.

So, let’s say you invested $1000 in an automobile business on 01 January 2018. And here’s how your business value has grown over the years from 2018 to 2024.

I can see the business value has grown from $1000 to $2200 in these 6 years. Visually, this is what the growth of this business looks like.

The graph shows steep ups and downs which is understandable.

A business won’t just grow in its very first year, then there were COVID impacts in 2019 until 2022 when the business value started declining. And then soon thereafter in 2023, the business saw a boom.

But how do you define this growth in a percentage? Talking about the year-over-year growth, it looks like this:

10% in the first year, no growth in the second year, 27% decline in the third and so on.

However, so many varying annual growth percentages make things complicated 🤯

This is where CAGR helps.

CAGR smooths out these varying growth rates into a single annual compound growth rate to tell how the business has annually performed over the 6-year period.

Instead of so many year-over-year growth rates, the simple CAGR for this business is 14.04% (see the calculation below).

The Formula for CAGR

The formula for CAGR is as follows:

• Ending value (EV) is the value of the business as of the end ($2200 on 31 December 2024 in our example).
• Beginning value (BV) is the value of investment in the beginning ($1000 on 01 January 2018)
• N defines the number of years over which the growth occurred (6 years in our example)

Calculate CAGR through its formula (Arithmetic CAGR Calculator in Excel)

Let’s now put this formula together in Excel to calculate CAGR.

I have divided $2200 (the ending value) by $1000 (the beginning value) and raised it to the inverse power of 6. And then deducted 1 from it.

Turns out that the CAGR for this business is 14.04% ⚖

Easy to calculate, right?

Pro Tip!

Be careful while you count the number of years for CAGR. The above data has 7 dates in total, so you might conclude the years (n) to be 7.

But that’s not true. The first date is the investment date, and the years (n) start there onwards.

Count the years passed from 2018 to 2024, this makes 6 years only.

Inputting the wrong number of years can distort the results for CAGR.

You can also make an arithmetic calculator in Excel to automate your calculations in CAGR.

All you need is three input values i.e., the Ending Value, the Beginning value, and the Number of years.

Now, write up the following formula in a cell by referencing to the input values.

And that’s it. Now whatever values you enter in the above input cells, Excel will automatically calculate the CAGR for that.

For example, for an investment of $1000 that grew to $5000 in 8 years, the CAGR is 22.28% 🧮

Useful enough.

Interpreting CAGR

The CAGR for this business is 14.04%.

In other words, if a 14.04% compound annual growth rate is applied to an investment of $1000 for 6 years straight, the investment would grow to $2200 by the end of Year 6.

This means that if we start compounding $1000 by 14.04%, this is what happens.

Again, compound the first year’s closing value of $1140 by 14.04% as below 👇

Dragging it down until the end of the period, the value of investment reaches $2200 (our ending value).

That’s how CAGR works.

It gives us one smoothened-out annual growth rate for the business. With CAGR, the grumpy bumpy graph of business growth (shown above) turns to a smooth line of 14.04% annual growth.

Pro Tip!

We can also find an annualized growth rate for this business by finding the simple average of the year-over-year growth rates. This rate is called the Average Annual Growth Rate (AAGR).

The AAGR comes out as 18%.

It is easier to calculate AAGR – however, it is not an accurate measure for measuring the growth of a business as it tunes out the effect of compounding.

This is the reason why the AAGR (18%) exceeds the CAGR (14%).

How to calculate CAGR in Excel

There are many ways to calculate CAGR in Excel.

One of these ways is to manually write the formula for CAGR in Excel (as we did in the above section). Another way is to use Excel functions.

There are so many functions that you can use to calculate CAGR in Excel. Let’s explore these together 🏍

RRI function

The first and the easiest function to calculate CAGR is the RRI function. It returns the interest rate that applies to a loan to equate its present value to its future value.

Interestingly, that’s exactly what CAGR does. To find CAGR using the RRI function.

Step 1) Write the RRI function as below.

• The first argument is nper> the number of years. We will set it to 6.
• The second argument is pv > the starting value of the investment. That’s $1000 (in Cell B2).
• The third argument is fv > the ending balance of the investment. That’s $2200 (in Cell B8).

Makes it:

Step 2) All arguments are done. Hit enter to have the CAGR calculated.

Easy, quick, and precise.

POWER function

The second is the POWER function. This function raises a number to a given power.

Alright, that is what CAGR does as well. It raises the (EV/BV) to the inverse power of number of years (1/n).

To find CAGR using the Power function.

Step 1) Write the Power function as below.

• The first argument is number > our number will be (EV/BV) > ($2200 / $1000) > (B8/B2)
• The second argument is power > the power we want is (1/n) > (1/6)
• We will then deduct 1 from the whole function as done in CAGR’s formula.

Makes it:

Step 2) Type it up and hit enter to have the CAGR calculated.

This was quick too. And the answer is a precise match to the CAGR calculated previously.

IRR function

The IRR function is meant to calculate the rate at which an investment equals the annual discounted cashflows generated by it at regular intervals. The internal rate of return.

However, to use the IRR function to calculate the CAGR, you must rearrange your data as follows:

The IRR function assumes that you invested $1000 in year 1 (so it must appear with a negative sign since it is a cash outflow).

And that it generated a cash inflow of $2200 in year 6 (should come with a positive sign since it is a cash inflow). No other cashflows occurred during this period and should be set to zero.

Step 1) Write the IRR function as below.

Step 2) ress Enter to have the CAGR calculated.

IRR makes CAGR calculation easy once you’ve arranged the dataset 🤩

Rate function

Then we have the RATE function.

It finds the rate at which a given number of equal periodical payments (occurring at regular intervals) equate to their present value today.

We can use it to find CAGR if we populate its arguments as below.

Step 1) Begin writing the RATE function as below.

Here’s what the arguments require:

• NPER – The number of periods over which the payments occur. This will be equal to the investment period i.e., 6.
• PMT – the amount of payment made each year. Since we are not making any payments, we will set it to zero. Instead of this argument, we will supply the value for [fv] which is an optional argument.
• PV – the present value of the investment. This will be our initial investment enclosed in a negative sign since it’s a cash outflow.
• FV – the future value of the investment. This is an optional argument and if skipped, its value is assumed 0. This will be the ending value of our investment.
• Type – an optional value that tells when payments are made. If omitted, it is set to 0 (that assumes payments are made at year-end), whereas 1 assumes that payments are made at year start.

Makes the RATE function:

Step 2) Press Enter.

Viola! We’ve once again reached the CAGR that’s as accurate and precise as found through all other methods. 💪

Conclusion

CAGR helps you come down to a single rate that defines the annual compound growth rate for a business for any given number of years.

It is massively used by investment portfolio managers and financial analysts for evaluating investments, comparing the growth of different investments and financial modeling 📈

I hope you enjoyed learning how this superhero financial metric works. And by now, you know plenty of ways to calculate it yourself.

I’d also recommend you read my Excel tutorials on calculating compound interest in Excel and using the IRR function of Excel for learning more about doing finances in Excel.