How to Calculate CAGR in Google Sheets

The CAGR or compound annual growth rate is a helpful metric in investment analysis that gives the real average rate of annual return for an investment.

The CAGR formula finds a growth rate that, if it had been the growth rate for each successive year, would produce the current total.

This formula is much more accurate than getting the average of successive growth rates. For example, if we have three successive annual growth rates of 25%, 15%, and 18%, we would come up with an average of 19.3%. 

This value, however, does not take into consideration the compounding effects of investments. In real life, the growth rate is applied to the principal amount and any interest gained during the year. With the CAGR formula, we would instead get a real average rate of 19.26%.

The formula for CAGR is the following:

B / A ^ (1/n) -1

B refers to the end value, and A refers to the starting value. The value of n corresponds to the number of periods for which you are calculating the average. 

The ‘^’ symbol calls the power function. The formula effectively raises the ratio of B and A to the power of (1/n). We then subtract one from that result to achieve the final output.

Let’s begin with a quick use case of the CAGR formula.

You are investing in a few companies in the stock market. You want to find out which stock, on average, has the highest annual rate of return. We have a record of the initial investment of each stock, as well as the current value of each investment.

With the CAGR formula function, it becomes quite easy to determine the real average rate of return of an investment per period for each of your stock investments.

This financial use case is just one of the many applications of the CAGR function in Google Sheets. This function can be applied to many fields, including economics, biology, and statistics.

Let’s learn how to write the CAGR function ourselves in Google Sheets and later test out the function with actual numerical values.

 

 

A Real Example of Using CAGR

Let’s look at a real example of CAGR being used in a Google Sheets spreadsheet.

In the example below, we’ve set up a simple CAGR calculator to find the average growth rate of a certain investment. The investment grew from $1000 to $1696.25 in three years. Using the CAGR formula, we now know that, on average, our investment grew by 19.26% each year.

Calculate CAGR in Google Sheets with a CAGR calculator

 

To get the value in cell B5, we just need to add the following formula:

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

In the example below, we’re illustrating how the CAGR formula gives a more accurate annual growth rate than using the average of each annual growth rate.

showing the difference between CAGR and the usual average

 

We can also use the CAGR formula to determine which stock has the best average annual return. In the next section, we’ll show you how to make a sheet similar to this one.

Calculate CAGR in Google Sheets to compare stock rate of return

 

You can make your own copy of the examples above using the spreadsheet link attached below. 

If you’re ready to try out the CAGR function in Google Sheets, let’s start writing it ourselves!

 

 

How to Calculate CAGR in Google Sheets

In this section, we will go through each step needed to start using the CAGR in Google Sheets. This guide will show you how we compared the annual rate of return of multiple investments.

Follow these steps to start using the CAGR formula:

  1. First, select the cell where we will type out the CAGR formula. In this example, we’ll be writing the formula in row 6.
    Select the cell to start calculating CAGR in Google Sheets
  2. Next, we’ll type out the CAGR formula described earlier, substituting our variables with cell references.
    write formula used to calculate CAGR in Google Sheets
  3. Hit the Enter key on your keyboard to return the final result. In this example, we now know that the TSL stock has an average annual growth rate of 86.3%. But how does it fare with the other stocks?
    hit Enter to return the result
  4. To compare all stocks, we’ll have to apply the formula to all the columns. Drag the formula to the right to find the average rate of return of each stock.
    fill the rest of the row to compare each stock

 

 

Frequently Asked Questions (FAQ)

  1. Are there other ways to calculate CAGR in Google Sheets?
    You may also use the POW function instead of the ‘^’ operator. For example, a formula for CAGR using the POW function may look like this:  =POW(C4/C3, 1/C5)-1. We can also use the RRI function to return a similar result since that function also computes compound growth rate. 
  2. In which cases should I use the CAGR function?
    You must use CAGR when dealing with investments or values that compound over time. Compounding refers to interest credited to an existing principal amount and to interest already paid in a previous period.
    Essentially, this is interest on previous interest. If your investment is linear or has a fixed payout, then the CAGR function is not needed.

That’s all you need to remember to calculate CAGR in Google Sheets. This step-by-step guide shows how easy it is to find the average growth rate of an investment per time period.

The CAGR formula is just one example of how Google Sheets can help you with your financial trackers and spreadsheets. With so many other Google Sheets functions out there, you can indeed find one that best suits your use case. 

Are you interested in learning more about what Google Sheets can do? Don’t miss out on our newsletter to be the first to know about the latest guides and tutorials from us. 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like