Don’t forget to share this post

Google Sheets is the go-to solution to perform financial calculations. Apart from the fact that it’s free, this cloud-based spreadsheet app makes it easy to store, calculate, and analyze any type of data.

Whether you’re looking to manage your personal finances or business operations, Google Sheets includes a wide range of financial functions that help you calculate key information such as compound interest or the compound annual growth rate, aka CAGR. This is the best formula to evaluate how different investments have performed over a period of time.

In this article, you’ll learn what CAGR is, how to write the formula syntax, how to calculate CAGR, as well as how to use two alternative formulas, POW and RRI, to calculate CAGR in Google Sheets.

What is the CAGR Formula?

CAGR stands for compound annual growth rate and is a concept used to assess the performance of various investments over a specific period. For example, as an investor, the CAGR formula can help you compare stocks within a peer group or against a market index.

CAGR Standard Formula Syntax

CAGR is not a built-in formula in Google Sheets but as you’ll see, the syntax is not complex at all:

CAGR = EV / BV ^ (1/n) – 1
  • EV - Refers to the ending value
  • BV - Refers to the beginning value
  • n - Represents the number of years

To understand fully understand the formula, these are the steps taken to calculate CAGR:

  1. 1. EV / BV is the value of an investment at the end of the period divided by the value at the beginning of the period.
  2. 2. The result obtained from EV / BV is raised to an exponent of one divided by n, the number of years.
  3. 3. Subtract 1 from the result obtained from EV / BV ^ (1/n)
  4. 4. To obtain a percentage, multiply the result by 100

How to calculate CAGR in Google Sheets?

Now that you know what CAGR is and what it’s used for, you’ll learn three ways to calculate it in your Google Sheets. Alternatively, you can also an online CAGR calculator. This is how you can calculate CAGR manually in your Google Sheets.

How to calculate CAGR manually?

To calculate CAGR, you need to make sure that your spreadsheet contains the following values: ending value, beginning value, and the number of periods/years.

  1. 1. Open the Google Sheets where you’d like to calculate the CAGR. Check that you have the required values.
How To Calculate CAGR In Google Sheets CAGR main values
How To Calculate CAGR In Google Sheets - CAGR main values
  1. 2. Type in an equal sign, followed by an opening parenthesis, to trigger the formula function in Google Sheets. Then, select the cell containing the ending value, add the division sign “/”, select the beginning value, and close the parenthesis.
How To Calculate CAGR In Google Sheets EVBV
How To Calculate CAGR In Google Sheets - EV/BV
  1. 3. Add the exponent sign “^” followed by “1”, the division sign “/”, and select the cell corresponding to the number of periods. Once again, this should be placed between parentheses.
How To Calculate CAGR In Google Sheets 1n
How To Calculate CAGR In Google Sheets - 1/n
  1. 4. Add a minus sign and “1” to subtract one from the result.
How To Calculate CAGR In Google Sheets Subtract one
How To Calculate CAGR In Google Sheets - Subtract one
  1. 5. To format the result as a percentage, add the multiplication sign “*” and then “100”. To obtain the result, press “Enter”.
How To Calculate CAGR In Google Sheets Format as percentage
How To Calculate CAGR In Google Sheets - Format as percentage
  1. 6. You should now see the result of the CAGR as a percentage.
How To Calculate CAGR In Google Sheets CAGR result
How To Calculate CAGR In Google Sheets - CAGR result
Google Sheets FORECAST Function Examples
Google Sheets FORECAST Function (+ Examples)

The Google Sheets FORECAST function predicts future values based on your data. Here’s how to use the FORECAST function step-by-step, with examples.

READ MORE

How to calculate CAGR with the POW function?

You can calculate the CAGR in Google Sheets using the POW function. Opposite to CAGR, the POW function is built in Google Sheets. Nonetheless, here’s the syntax for the POW function.

POW Function Syntax

=POW (base, exponent)
  • base - refers to the number raised to the power of
  • exponent - the number the base is raised to
  1. 1. Type in an equal sign and “POW” to trigger the POW formula in Google Sheets.
How To Calculate CAGR In Google Sheets CAGR result 2
How To Calculate CAGR In Google Sheets - CAGR result
  1. 2. Open parenthesis and add the ending value, the division sign, and the beginning value. Then, separate this division by a comma “,” and add the second part of the CAGR formula, “1”, the division sign, select the number of periods value, and close parenthesis before subtracting by one, “-1”.
How To Calculate CAGR In Google Sheets POW
How To Calculate CAGR In Google Sheets - POW
  1. 3. To obtain the same result as before, as a percentage, add extra parentheses and include “*100” at the very end of the formula, as shown below.
How To Calculate CAGR In Google Sheets Format as percentage 2
How To Calculate CAGR In Google Sheets - Format as percentage
  1. 4. Press “Enter” to obtain the same result using the POW function.
How To Calculate CAGR In Google Sheets POW and CAGR same results
How To Calculate CAGR In Google Sheets - POW and CAGR same results

How to calculate CAGR with the RRI function

The RRI function can also be used in Google Sheets to calculate the CAGR. The RRI formula in Google Sheets calculates the interest rate you need for an investment to reach a value within a specified number of periods. Let’s see what the RRI formula looks like.

RRI Formula Syntax

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.

How To Import Yahoo Finance Data Into Google Sheets

You want to analyze Yahoo Finance data in Google Sheets but are still using copy-paste? Here’s how to import Yahoo Finance data into Google Sheets instead.

READ MORE
How To Import Yahoo Finance Data Into Google Sheets
  1. 1. Type in an equal sign and “RRI” to trigger the RRI formula in Google Sheets.
How To Calculate CAGR In Google Sheets Enter RRI formula
How To Calculate CAGR In Google Sheets - Enter RRI formula
  1. 2. Open parenthesis, and select the cell containing the number of periods to include it in the RRI formula. Follow with a comma.
How To Calculate CAGR In Google Sheets Include number of periods
How To Calculate CAGR In Google Sheets - Include number of periods
  1. 3. Select the cell corresponding to the beginning value to include in the formula.
How To Calculate CAGR In Google Sheets Select beginning value
How To Calculate CAGR In Google Sheets - Select beginning value
  1. 4. Add a comma and then select the cell corresponding to the ending value. Close parenthesis.
How To Calculate CAGR In Google Sheets Select ending value
How To Calculate CAGR In Google Sheets - Select ending value
  1. 5. To format as a percentage, multiply by 100 by adding “*100” as shown below.
How To Calculate CAGR In Google Sheets Format as percentage 3
How To Calculate CAGR In Google Sheets - Format as percentage

How to manage Google Sheets data with Layer?

Layer adds productivity features to your Google Sheets. Share parts of your spreadsheet, request input, and accept or reject changes to make collaboration seamless and more efficient while keeping full control over your data.

Using Layer, you can:

  • Manage Access: Give spreadsheet access to relevant stakeholders on a tab or cell level.
  • Review & Track: Consolidate input and easily track changes.
  • Collaborate: Define, assign, and automate tasks and set deadlines.

Sign up for early access and start automating your Google Sheets workflows with Layer!

Conclusion

As you can see, Google Sheets has become a truly versatile spreadsheet app for many business people since it allows users to perform basic to advanced financial calculations. Not only this, but it also offers a wide variety of formulas to import financial data, such as the GOOGLEFINANCE function or the YahooF function via Google Apps Script.

You should know now what the CAGR is, how it’s expressed, and the two alternative ways to calculate it in Google Sheets using the POW function and RRI function. Learning how to calculate the CAGR manually is important so you can fully understand the data needed. However, the RRI function is the most suitable if you prefer a quicker method.

If you want to learn about more financial formulas in Google Sheets and Excel, check out any of the articles below:

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jul 27 2022, Updated Aug 2 2022