Wall Street Prep

Compound Annual Growth Rate (CAGR): Formula & Example Calculation

CAGR Formula and Definition Explained with Calculation Example (Excel Template Included)

Learn Online Now

Compound Annual Growth Rate (CAGR): Formula & Example Calculation

Key Takeaways
  • What is the formula used to calculate the CAGR metric?
  • In practice, what can the CAGR be used for?
  • What are the benefits and downsides of the CAGR?

CAGR Formula

CAGR is the rate of return required for the value of an investment or financial metric to grow from its beginning value to its ending value between two dates, and the main inputs necessary to compute the CAGR are listed below:

  1. Beginning Value
  2. Ending Value
  3. Number of Periods (n)

The CAGR formula involves dividing the ending value by the beginning value, raising that amount to the inverse number of periods (1 / # of periods), and finish by subtracting one to make the rate a percentage.

CAGR Excel Formula

The CAGR answers: “At what growth rate must the metric grow at each [Period] to reach [Ending Value] from [Beginning Value] under the time frame of [Number of Periods]?”

As an example, let’s say there’s a company with revenue of $20m at the end of the current period (Year 0). Five years from the present date, the company’s revenue is projected to reach $32.5m (Year 5).

As a side note, we exclude Year 0 when counting the number of periods because we are counting only the periods when the revenue is compounding – i.e., we subtract the beginning period number from the ending period number (in this case, Year 5 minus Year 0 = 5 Years).

We would enter the following figures into the CAGR formula:

  • Beginning Value = $20m
  • Ending Value = $32.5m
  • Number of Periods = 5 Years

In the first part of the formula, the ending value of $32.5m is divided by the beginning value of $20m. This figure must then be annualized by raising it to the power of 1 divided by the 5 periods. Lastly, once we subtract 1 from the return value, and we are left with 10.2% as the CAGR.

CAGR Example

CAGR Benefits/Downsides

The primary benefit of CAGR is that the metric can serve as a quick and informative growth measure of anything that rises (or falls) in value. Because the CAGR can confirm whether the projections are in line with industry averages and/or historical growth, CAGR can also be useful as a sanity check.

Let’s suppose a company is projected to grow at a CAGR of 20.0% but the company’s closest comparables are expected to grow around 5.0% while the collective industry is being forecasted to grow 3.0% across the same periods. If this were the case, the company’s growth assumptions likely warrant some adjustments, or at the very least, a closer look into whether the numbers are reasonable or not.

Additionally, CAGR can be useful and have wide applicability for purposes of comparability. Since annualized growth metrics remove the fluctuations of year-over-year growth rates, this helps facilitate the comparisons of CAGRs over time between two companies or investments which would otherwise be very challenging to compare.

The main drawback to the CAGR is that fails to take into account the volatility associated with the underlying asset. Thus, the growth metric becomes vulnerable to misinterpretations as the actual growth rate experienced year-over-year may vary.

For instance, a company’s revenue growth could in reality be disproportionate with positive growth being front-ended in the earlier periods and ultimately tapering off or even flattening. Without taking a closer look, the CAGR can be misleading by erroneously portraying the company as having consistent positive growth potential.

Excel Template Download

Now that we’ve explained the concept of CAGR and the formula used to calculate the rate, we’re ready to move onto an example exercise in Excel. To get started, fill out the form below to access the spreadsheet.

dl
Submitting ...

CAGR Example Calculation

In our hypothetical scenario, we are tasked with calculating the CAGR of a company’s revenue. At the end of the current period, the company has generated $100m in revenue – and this figure is anticipated to grow by $10m each year.

By the end of Year 5, the company’s revenue grows to $150m. Just like the previous example, we are going to input the following assumptions into the CAGR formula:

  • Beginning Value = $100m
  • Ending Value = $150m
  • Number of Periods = 5.0 Years

The row below the revenue figures calculates the year-over-year growth rates of revenue. Here, we see how revenue begins growing at 10.0% before slowing down to 7.1% in the final period of the forecast.

Our CAGR formula will divide $150m (ending value) by $100m (beginning value), and then raise it to 1 divided by 5 (number of periods). In the subsequent step, we subtract by 1 to get 8.6% as the CAGR.

CAGR Formula

Alternatively, the “RATE” function in Excel could be used. The COUNTA function can count the number of years (nper) for the first input, and we can hardcode a zero for the next input (pmt).

In the subsequent steps, we can enter -$100m for the present value (pv), and $150m for the future value (fv). Notice we must enter a negative sign for either the present or future value.

CAGR Alternative Formula

Note that the CAGR is not as simple as averaging the YoY growth rates.

Instead, given the beginning value and ending value and date parameters, the CAGR metric assumes the profits are reinvested each period and that interest is compounded annually.

A screenshot of the finished CAGR calculation output is shown below.

To ensure that you understand the concept of CAGR, we have also computed the implied revenue, which we link to the $100m assumption for Year 0 and then grow it by the CAGR of 8.4%. At the end of Year 5, we can see how by growing revenue by the constant rate of 8.4% each year, the company’s revenue reaches $150m.

CAGR Finished

Annualized metrics like CAGR should not just be taken at face value to minimize the risk of understating (or overstating) the future growth potential without fully understanding the volatility risks and core drivers of performance. While the CAGR can be a useful tool for better decision-making, CAGR as a standalone metric does not reveal the entire story.

Comments
guest
0 Comments
Inline Feedbacks
View all comments
Learn Financial Modeling Online

Everything you need to master financial and valuation modeling: 3-Statement Modeling, DCF, Comps, M&A and LBO.

Learn More
X

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.