Skip to Content

How to Calculate CAGR in Google Sheets

Google Sheets is a versatile application that allows users to perform many types of calculations.

Some people use Google Sheets to perform financial calculations such as calculating CAGR (compound annual growth rate).

In this tutorial, I will show you how to calculate CAGR in Google Sheets. There are a few different formulas we can use to do this, which I will show in this post.

Table of Contents

What is CAGR?

CAGR stands for compound annual growth rate. This is a kind of formula that is commonly used in investment analysis to determine returns on an investment over time.

The CAGR is a number that represents the rate an investment would have grown if profits were reinvested at the end of each year and the investment grows at the same rate each year.

The standard CAGR formula is:

CAGR = EV / BV ^ (1/n) – 1.

  • EV = ending value
  • BV  = beginning value
  • n = number of years

The basic steps for performing this calculation are:

  1. Take the value of an investment at the end of the period and divide it by its value at the beginning of the period
  2. Take the result and raise it to an exponent of one divided by the number of years
  3. Take the result and subtract one
  4. To change it to a percentage you can multiply this by 100

Calculating CAGR

Calculating CAGR Manually

Now that we’ve gone over the basic steps for CAGR and how to calculate it, let’s take a look at how we would actually set this formula up in our spreadsheet.

First, you need to have the data points that will be used to calculate the CAGR. The ending value, beginning value, and number of periods/years.

cagr-formula-1

Once you have your data in the spreadsheet you can follow the steps for the formula we covered above:

  1. Take the value of an investment at the end of the period and divide it by its value at the beginning of the period
  2. Take the result and raise it to an exponent of one divided by the number of years
  3. Take the result and subtract one
  4. In Google Sheets, you will want the cell where the formula is calculated to be formatted as a percentage

Here is how this will look with my example data:

cagr-formula-2

The formula used in the above example is:

=(B2/B1)^(1/B3)–1

The cell where the formula is calculated is formatted as a percentage.

RRI Function to Calculate CAGR

You can also use the RRI function to calculate the CAGR.

The RRI function returns the interest rate needed for an investment to reach a certain value in a given number of periods.

The syntax of the RRI function is:

RRI(number_of_periods, present_value, future_value)

  • number_of_periods – the number of periods
  • present_value – the current value of the investment
  • future_value – the future value of an investment

To use this function to calculate CAGR with our previous example data the formula we will use is:

=RRI(B3,B1,B2)

RRI-CAGR

Closing Thoughts

In this post, I have now shown two methods for calculating the CAGR. There are certainly other ways of performing this calculation in your spreadsheet, but we think these are the best methods.

It’s good to learn how to calculate it manually first, just so you understand the data needed to calculate it, but using the RRI function is definitely the faster method.

We hope you found this tutorial helpful!

More Google Sheets Tutorials:
How to Use the NPV Function
How to use the PMT Function
How to Calculate Compound Interest