How to Calculate CAGR in Excel

How do I calculate CAGR in Excel?

Whether you’re a business owner, investment manager, or financial assistant, knowing your Compound Annual Growth Rate is extremely useful. It allows you to get a compact overview of how your business has grown, which opens up another door of possibilities. Compare revenue in different years, see what you did right, and do it again.

This article focuses on calculating your CAGR with Microsoft Excel. While there’s no specific function to get this done, you can approach the calculation from a few different angles and get accurate results. Let’s get started!

What is Compound Annual Growth Rate?

The question is: What is the formula for calculating compound annual growth rate?

Below is an overview of how to calculate CAGR both by hand and by using Microsoft Excel.

What Is CAGR?

To put it simply, the Compound Annual Growth Rate (CAGR) is used to measure the return of investment over a specified amount of time. While it’s not an accounting term, it’s used by financial experts to analyze various situations and make better, more thought-out decisions.

Let’s use an example to put CAGR into action. In this example, we’ll look at the year-end prices for a stock. This value changes every day, often at an uneven rate. CAGR allows us to calculate the one rate that defines the return for the entire measurement period:

Stock price in 2018: $140
Stock price in 2019: $155
Stock price in 2020: $170

As you can see, the growth rate from year to year is inconsistent. However, we can use the CAGR formula to find a single growth rate for the whole time period.

How to calculate CAGR using Excel [CAGR formula in Excel]

At the time of writing, there’s no dedicated formula for calculating your Compound Annual Growth Rate (CAGR) in Excel. Luckily, there are some alternative formulas you can use for this purpose. We compiled two guides below, approaching the calculation from two different angles.

Video: How to Calculate CAGR using Excel

How is CAGR calculated?

The guides below were written using the latest Microsoft Excel 2019 for Windows 10. If you’re using a different version or platform, some steps may vary. Contact our experts if you need any further assistance.

Method 1. Calculate CAGR manually - CAGR formula in Excel

Knowing the generic CAGR formula, you can create a calculator within a matter of minutes. What you need to do is specify the following 3 values in your worksheet:

Launch Microsoft Excel and open the relevant document you want to work with or create a new one. Make sure you have data in your worksheet(s) before proceeding with the guide.
Select an output cell for your final CAGR value, and then input your formula. The basic formula for calculating your CAGR is as follows:

BV - Beginning value of the investment
EV - Ending value of the investment
n - Number of periods

Your formula should look like this: =(EV/BV)^(1/n)-1. In our example, the formula would be =(B2/B1)^(1/B3)-1.

CAGR formula example

Imgur link
Done! You can reuse this formula any time you need to calculate your Compound Annual Growth Rate.

Method 2. Use the RRI formula

Another way of calculating your CAGR in Excel is currently the RRI formula. It also has 3 core arguments: the number of periods, start value and end value. Once again, we’ve set up an example to show you how to use this function below.

Launch Microsoft Excel and open the relevant document you want to work with or create a new one. Make sure you have data in your worksheet(s) before proceeding with the guide.
Select an output cell for your final CAGR value, and then input your RRI formula:

nper - The total number of periods.
pv - The present value of the investment.
fv - The future value of the investment.

Your formula should look like this: =RRI(nper,pv,fv). In our example, the formula would be =RRI(B3,B1,B2).

Imgur link
Done!

Method 3. Use the CAGR calculator template

CAGR formula excel download template:

Thanks to this automated calculator, you never have to worry about calculating your CAGR again. With Vertex42’s Online CAGR Calculator and the corresponding Excel template, you can easily get this number without having to input formulas manually.

Use the CAGR calculator template

Tip: Want more amazing, time-saving Excel templates? Check out our Top 51 Excel Templates to Boost Your Productivity article.

Final thoughts

We hope this article helped you learn how you can calculate your CAGR using Excel’s formulas. Now, you’ll be able to see valuable information about your growth and make better decisions based on the results. We wish you the best on your journey! If you need any further help, don’t hesitate to get in touch with us.

Before you go

If you need any further help with Excel, don’t hesitate to reach out to our customer service team, available 24/7 to assist you. Return to us for more informative articles all related to productivity and modern-day technology!

Would you like to receive promotions, deals, and discounts to get our products for the best price? Don’t forget to subscribe to our newsletter by entering your email address below! Receive the latest technology news in your inbox and be the first to read our tips to become more productive.