Table of Contents
Calculating compound annual growth rate (CAGR) in Google Sheets is a fairly straightforward process. First, you will need to calculate the total return by subtracting the initial investment from the ending value, and then dividing the total return by the initial investment. Next, you will need to calculate the annual growth rate by taking the nth root of the total return (where n is the number of years), and then subtracting one. Finally, you will need to multiply the annual growth rate by 100 to get the CAGR. All of these calculations can easily be done using the Google Sheets formula bar.
The acronym CAGR stands for compound annual growth rate, which is the average annualized revenue growth rate during a certain time period.
The formula to calculate CAGR is as follows:
CAGR = (future value / present value)1/periods – 1
The following examples show two equivalent ways to calculate CAGR in Google Sheets.
Method 1: Calculate CAGR Manually
We can use the following formula to calculate CAGR manually in Google Sheets:
=(ENDING_VALUE/STARTING_VALUE)^(1/PERIODS)-1
The following screenshot shows how to use this formula to calculate CAGR for an investment that started at $1,000 and ended at $5,000 after 9 investment periods:
The CAGR is 19.58%. This represents the compound annual growth rate of the investment during these 9 investment periods.
We can confirm this answer is correct by calculating the growth of an initial $1,000 investment if it grew consistently at 19.58% each year for 9 years:
Method 2: Calculate CAGR Using RRI Function
Another way to calculate CAGR in Google Sheets is by using the RRI function, which uses the following syntax:
RRI(number of periods, starting value, ending value)
The following screenshot shows how to use this function in practice:
This matches the value that we calculated manually using the previous method.