CAGR Formula

How to calculate CAGR in Excel - CAGR formula examples

In this tutorial, you will learn how to calculate CAGR in Excel, either using the RRI function or through manual calculation.

What is CAGR?

CAGR means Compound Annual Growth Rate. It shows how much an investment grows each year over a set time. CAGR assumes that profits are reinvested every year and interest grows yearly, and it calculates the growth rate from the starting amount to the final amount.

How to calculate CAGR in Excel?

  1. Select cell E4
  2. Type =(end/start)^(1/periods)-1
  3. Press Enter
  4. The formula will return the Compound Annual Growth Rate as a decimal value. Multiply by 100 if a percentage format is desired.

Compound Annual Growth Rate Example

In this example, we have a dataset in range B6:D14 tracking yearly investment values, percentage changes, and corresponding growth factors over time. The goal is to create a formula and calculate the compound annual growth rate (CAGR), which represents the average annual growth rate of the investment.

How to calculate CAGR in Excel - CAGR formula examples

The “Manual” method in cell H6 uses a formula based on the starting and ending values over the period, while the RRI function in cell H7 directly computes the CAGR. These methods validate the consistency of the calculated average compounding rate for the investment.

Explanation

The formula =((C14/C6)^(1/(B14-B6))-1) calculates the Compound Annual Growth Rate (CAGR) manually. Here’s a breakdown of its components:

  • C14/C6 divides the ending value (cell C14) by the starting value (cell C6), determining the overall growth factor over the entire period.
  • 1/(B14-B6) calculates the inverse of the total number of years between the starting and ending periods. (B14-B6) represents the time span.
  • (C14/C6)^(1/(B14-B6)) raises the overall growth factor to the power of the inverse period, giving the equivalent annual growth factor.
  • -1: Subtracting 1 converts the annual growth factor into the CAGR as a percentage increase.

The result represents the consistent annual growth rate required to grow the starting value (C6) to the ending value (C14) over the given period (B14-B6).

How to calculate the “Value” column?

In the value column, C6 is a constant (25000) representing the starting point and is not calculated using any formula in this table.

How to calculate the value column

The formula calculates the value of the investment for year 1 by applying the percentage change in D7 to the initial value in C6.

Calculate the Growth Factor using the following formula:

=1 + D7

This column contains the percentage change for the respective year. For example, in D7, the percentage change is 5%, or 0.05 in decimal form. The growth factor represents the multiplier applied to the previous year’s value to calculate the current year’s value. Adding 1 to the percentage change (D7) gives the growth factor. For D7 = 5%, the growth factor is: 1 + 0.05 = 1.05

Why use the Growth Factor?

The growth factor simplifies the calculation of the value in the next year. Instead of recalculating percentages directly, you multiply the previous year’s value by the growth factor:

Next year value = Current year value x Growth factor

RRI Formula to calculate Compound Annual Growth Rate

RRI stands for “Rate of Return for Investment”. It calculates an investment’s equivalent annual growth rate (or CAGR, Compound Annual Growth Rate) based on the number of periods, the starting value, and the ending value.

Configure the arguments:

  • 8: This is the nper, or the number of periods (in this case, 8 years).
  • C6: This cell contains the present value (PV), which in the provided data is 25000 (the investment’s starting value).
  • C14: This cell contains the future value (FV), which is 42677 (the investment’s value at the end of 8 years).

Formula:

=RRI(8,C6,C14)

This formula is useful for comparing the annual growth rates of different investments. Despite fluctuations in growth (as seen in column D with varying % Change values), RRI smoothens the data and provides a single, consistent annual growth rate.

Download the practice file.