Calculate the compound annual growth rate (CAGR)

The think-cell TCCAGR formula simplifies the calculation of the compound annual growth rate (CAGR). Use this function to quickly analyze business data over time. For more information on the CAGR, see Wikipedia.

Use the TCCAGR formula

1. In Excel, enter your data in a range of cells in a single row or column.

2. In another cell, type =TCCAGR(FIRST_CELL:LAST_CELL). Replace FIRST_CELL with the first cell in your range, and LAST_CELL with the last cell.

   Alternatively, to calculate the CAGR directly from the Excel ribbon, select the data range in a row, then select think-cell > Formulas > Insert CAGR . Currently, the Insert CAGR button only functions with a row of cells.

Note: The TCCAGR formula uses only the first and last cells in the range—the cells between the two do not affect the calculation. Furthermore, the formula requires positive values in both the first and last cells to return a meaningful result.

Represent a year in multiple cells

The TCCAGR formula has an optional second parameter to represent a year's data across multiple cells. To use this parameter, after the range, add the number of cells that represent a year. If you don't use this optional parameter, the TCCAGR formula takes each cell to represent one year by default.

For example: =TCCAGR(B1:B5) calculates the CAGR over a four-year period, with each cell representing one year. Using the optional parameter, =TCCAGR(B1:B9, 2) calculates the CAGR over a two-year period, with every two cells in the range representing one year.

Note: Comma is used as the delimiter in most versions of Excel. In some localized versions, however, comma is the decimal separator, so the semicolon is used instead of the comma for parameters. Make sure to use the correct delimiter for your Excel version.

Troubleshoot the TCCAGR formula

There are three possible error results you may come across when using the TCCAGR formula: #VALUE!, #DIV/0!, and #NUM!.

#VALUE!

The #VALUE! error may result from mistyped formulas or bad parameters. Make sure to use the correct punctuation for localized versions. For example, in the English versions of Excel, the formula looks like this: =TCCAGR(A1:A10, 2), but in the German version of Excel, the comma is replaced with a semicolon: =TCCAGR(A1:A10; 2).

The TCCAGR formula works in a range of cells arranged in a single row or column. Using the formula with cells that are not in a single row or column will result in a #VALUE! error.

Another reason for a #VALUE! error is having a negative value in either the first or the last cell in the range. The TCCAGR formula requires positive numbers in both cells to function.

#DIV/0!

If the value of the first cell in the range is zero, the formula will return a #DIV/0! error, as the CAGR calculation would involve division by zero.

#NUM!

If both the first and the last cell in the range are zero, the formula will return a #NUM! error, as the TCCAGR requires both cells to have positive numbers.