How to Calculate CAGR in Google Sheets

5/5 - (1 vote)

The Compound Annual Growth Rate (CAGR) is a useful measure to understand the growth of an investment over time. How do I calculate CAGR in Google Sheets? 👇

To calculate CAGR in Google Sheets, use the following formula:
(Ending Value/Starting Value)˄(1/Number of Years) - 1

Step 1: Input Your Start and End Values

In column A, input the periods over which you want to calculate the CAGR of your investment. In column B, input your investment value at the end of each period.

For example: 

Step 2: Use the CAGR Formula

The CAGR formula (Ending Value/Starting Value)˄(1/Number of Years) - 1 calculates the rate at which an investment grows over time.

Specifically, I used this formula to calculate the compound annual growth rate over 3-1=2 years:

=(B4/B2)^(1/(A4-A2))-1

Where:

  • B4 is the cell with the ending value of your investment.
  • B2 is the cell with the starting value of your investment.
  • A4 and A2 are the cells with the end and start periods, respectively. You can also put in the number of years right away.

Step 3: Format as Percentage

After entering the formula, the result is not yet displayed as a percentage. To format it, click on the CAGR result cell and click Format > Number > Percent.

The resulting cell looks like this:

Voilà. You’ve successfully calculated the CAGR in your Google spreadsheet and formatted it as a percentage value. ✅🎯