CAGR Formula Google Sheets: Step-by-Step Guide
The CAGR (Compound Annual Growth Rate) formula in Google Sheets calculates the average annual growth rate of an investment over a specified period, assuming the value grows at a steady rate each year. You enter the starting value, ending value, and the number of periods (usually years), and the formula returns a percentage that represents the annualized return. The core formula is =(EV/BV)^(1/N)-1, where EV is the ending value, BV is the beginning value, and N is the number of periods.
What Is CAGR and Why Use It?
CAGR is a useful financial metric because it smooths out the volatility of year-to-year returns into a single, comparable annual rate. Unlike an average of annual returns (which can be misleading due to compounding), CAGR shows the actual growth rate needed to go from the starting value to the ending value over the given time. It is commonly used to compare the performance of investments, business revenue growth, or any metric that changes over time.
For example, if an investment grows from $1,000 to $2,000 over 5 years, the CAGR is approximately 14.87%. This means that if the investment grew at a constant 14.87% each year, it would reach $2,000 after 5 years. The actual yearly returns might have been 20%, -5%, 30%, 10%, and 15%, but CAGR gives you a single number to compare against other investments.
How to Write the CAGR Formula in Google Sheets
To calculate CAGR in Google Sheets, you can use the basic formula or the RRI function. The basic formula is:
=(Ending Value / Beginning Value)^(1 / Number of Periods) - 1
If your data is in cells, for example, B2 = beginning value, B3 = ending value, and B4 = number of years, you would enter:
=(B3/B2)^(1/B4)-1
Then format the result as a percentage by clicking the percent icon in the toolbar. Alternatively, you can use the built-in RRI function:
=RRI(B4, B2, B3)
This function takes the number of periods, present value, and future value as arguments and returns the equivalent interest rate. It’s a cleaner way if you prefer using built-in functions.
Worked Example with Default Values
Let’s use a concrete example. Suppose you invested $1,000 and after 5 years it is worth $1,500. What is the CAGR?
- Beginning Value (BV) = 1000
- Ending Value (EV) = 1500
- Number of Years (N) = 5
Using the formula: =(1500/1000)^(1/5)-1
First, divide 1500 by 1000 to get 1.5. Then raise 1.5 to the power of 1/5 (0.2). 1.5^0.2 ≈ 1.08447. Subtract 1 to get 0.08447, which is 8.45% when formatted as a percentage. So the CAGR is approximately 8.45%.
Using the RRI function: =RRI(5,1000,1500) returns the same 8.45%.
How to Choose Between the Basic Formula and RRI
The basic formula and the RRI function produce the same result, so the choice is mostly about readability and personal preference. The basic formula is transparent—you can see exactly what is being calculated. The RRI function is shorter and less prone to typos. If you are sharing the sheet with others, the basic formula might be easier for them to understand. If you are working with many calculations, RRI saves space. Both handle fractional periods (e.g., 2.5 years) correctly.
Common Mistakes When Calculating CAGR
Several errors can lead to incorrect CAGR values:
- Using the wrong number of periods: If you are measuring from the start of year 1 to the end of year 5, that is 5 periods, not 4. Always count the number of intervals.
- Not formatting as a percentage: The raw result is a decimal (e.g., 0.0845). If you don’t format it as a percentage, it looks like 0.08, which is misleading.
- Using negative values: CAGR assumes positive beginning and ending values. If you have a loss, the formula may produce an error or a nonsensical result. In that case, consider using a different metric.
- Mixing up units: Ensure that the beginning and ending values are in the same currency and that the number of periods is in years (or the same time unit as the data).
When to Use CAGR and Its Limitations
CAGR is best used for comparing the historical growth of investments, company revenue, or other metrics over a defined period. It is not a forecast; it only describes the past. It also assumes a smooth growth rate, which is rarely true in reality. For volatile assets, CAGR can hide the risk involved. Additionally, CAGR does not account for additional contributions or withdrawals during the period—it only looks at the start and end values. For those cases, you would need a more complex calculation like XIRR.
Frequently Asked Questions
Can I calculate CAGR for quarterly or monthly data?
Yes. Just ensure the number of periods matches the frequency. If you have monthly data over 3 years, you have 36 periods. The result will be a monthly growth rate. To annualize it, raise the result to the power of 12 (or use the formula with N as the number of years). For example, if the monthly CAGR is 1%, the annual CAGR is (1+0.01)^12 – 1 ≈ 12.68%.
What if my beginning value is zero?
If the beginning value is zero, the formula returns a division by zero error. CAGR is not defined when the starting value is zero because you cannot calculate a growth rate from nothing. In such cases, consider using a different measure or adjusting the starting point to when the value first became positive.
How do I format the result as a percentage?
Select the cell with the formula, then click the percent symbol (%) in the toolbar. You can also go to Format > Number > Percent. This multiplies the value by 100 and adds the % sign.
Does Google Sheets have a built-in CAGR function?
There is no function named “CAGR,� but the RRI function does exactly that. It returns the interest rate required for an investment to grow from a present value to a future value over a specified number of periods. So you can use =RRI(periods, present_value, future_value).
What is the difference between CAGR and average annual return?
Average annual return is the simple arithmetic mean of yearly returns. For example, if returns are 10%, -5%, and 20%, the average is (10-5+20)/3 = 8.33%. CAGR, on the other hand, accounts for compounding and is the geometric mean. In the same example, the CAGR would be ((1.10*0.95*1.20)^(1/3))-1 ≈ 7.66%. CAGR is generally lower when returns are volatile, and it gives a more accurate picture of actual growth.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.