What is the CAGR formula?

CAGR (compound annual growth rate) is an investment’s RoR (rate of return) when you reinvest the profits each year. To calculate the CAGR of an investment, you must use the CAGR formula. There’s a CAGR formula in Excel that can do the work for you if you input the data into a table and break the calculations out line by line. Here’s how to perform the manual formula.

• Take the value of an investment at the end of the investment period (EV) and divide it by the value of the investment at the beginning of the investment period (BV).
• Take the result of the last equation and raise it to the exponent of 1, divided by the number of years (n) of the life of the investment.
• Subtract 1.
• Multiply the final number by 100 to convert it into a percentage.

How to use the CAGR formula

Say you have an initial investment of $10,000 on January 1, 2024; by the end of the year, it grew to $13,000 or 30%. From January 1, 2025, to January 1, 2026, the investment jumped to $14,000 or 7.69%. In the final year, from January 1, 2026, to January 1, 2027, the investment grew to $19,000 or 35.71%. Your CAGR formula would look like this:

CAGR = [(EV$/BV$) ^ (1/n)] – 1 x 100

or

CAGR = [($19,000/$10,000) ^ (⅓)] – 1 x 100 = 23.86%

What does the CAGR formula tell you?

The example above shows that the investment grew $9,000 over three years, but the RoR is only 23.86%. The compound annual growth rate isn’t the actual return rate. It represents a figure that describes the rate of return if the investment grew at the same rate every year, with all profits reinvested each year. It doesn’t show how volatile the highs and lows were at the ends of each year. Smoothing out the fluctuations makes it easier to understand.

A CAGR of 23.86% over three years can help you compare alternatives for future investments. For example, suppose you compare the performances of two uncorrelated investments, such as stocks to bonds or emerging markets to real estate investments. In that case, one may fall while the other rises. Smooth out the ups and downs using the CAGR formula to form an easier comparison. One drawback of using the CAGR formula is that the smoothed-out return version doesn’t tell you how risky or volatile the investment was.

Understanding the CAGR formula

Investors who understand the CAGR formula for past rates of returns also know that they can use the same formula to estimate future profits. You can manipulate the CAGR formula algebraically into a formula that can find the future or present value of money or calculate a hurdle rate (the minimum acceptable rate of return or MARR). For example, you’ll need $50,000 for your child’s post-secondary education in 18 years. With your initial investment of $15,000, what average rate of return do you require to reach your objective? Use the CAGR formula as follows:

[(EV/BV) ^ (1/n)] – 1 x 100 = required return

or

[($50,000/$15,000) ^ (1/18)] -1 x 100 = 6.9%

This example rearranges EV and BV to the equation’s future and present value figures. You can also determine your initial investment if you require $50,000 for your child’s education and are willing to accept a reasonable CAGR of 8%.

[(50,000/X) ^ (1/18)] -1 x 100 = 8.0%

What is X? 

 X = $12,500 

In this example, you need to invest $12,500 to achieve your objective of $50,000 in 18 years with a CAGR of 8%.

Adapting the CAGR formula

Rarely will you invest on the first day of the year and sell the investment on the last day of a future year. For instance, on June 1, 2010, you invested $10,000 into a stock and sold it for $16,897.14 on September 9, 2015. You want to calculate the CAGR of that investment, but you’ll need the fractional remainder of n (the life of the investment). Break down the value into days to get your fractional remainder and divide by 365. Using the above example:

• 213 days in 2010
• 365 in 2011
• 365 in 2012
• 365 in 2013
• 365 in 2014
• 251 in 2015

Totalling 1,924 days. Divide 1,924 days by 365 days, and the entire holding period of the investment was 5.271 years, which will take its place in the CAGR formula as follows:

[($16,897.14/$10,000) ^ (1/5.271)] -1 x 100 = 10.46%

The compound annual growth rate of that investment was 10.46%.

IRR vs. CAGR

IRR (the internal rate of return) estimates the return on an investment. The higher your IRR, the better return on investment. Since the same calculation applies to varying investments, you can rank all your company investments to determine which ones are best. The IRR measures investment performance just as the CAGR does; however, the IRR is more flexible. Another crucial distinction is that CAGR is straightforward enough to calculate manually. In contrast, using IRR to calculate complicated projects or investments or those with varying cash inflows is best.

CAGR formula in Excel

When working with the built-in formula for CAGR in Excel, you still need to enter a beginning value, an ending value, and the investment period. Best practices for financial modelling require all calculations to be auditable and transparent. Set up one table for all your data, then break out the calculations line by line. This method helps you see which numbers belong where, and which are hard-coded or user-inputted.

CAGR’s benefits

CAGR serves as a way to forecast the rate at which your investment may grow if it maintains a steady growth rate over the entire forecast period. Calculating CAGR offers additional benefits, such as:

Performance tracking

Organizations use the CAGR formula to track performance through various business aspects or compare themselves to other companies. For example, a large department store’s market share CAGR was 2.41%, but its customer satisfaction CAGR was -0.49% over the same period. When comparing CAGRs of measure within an organization, you can detect strengths and weaknesses.

Detect strengths and weaknesses

CAGR helps you detect strengths and weaknesses throughout your organization. But you can also use this strategy to monitor your competitors, avoid factors that cause weakness, and mimic activities that add strength. In the example above, the large department store’s customer satisfaction CAGR was -0.49%, which may not seem so low compared to a competitor’s CAGR of -4.92%. Research what they’re doing or not doing and use the insights to your advantage. Also, find a competitor with a high CAGR for customer satisfaction to give you more options to improve your score.

CAGR’s limitations

There are some challenges when using the CAGR formula.

• Since CAGR smooths out the growth rate, it ignores volatility, which implies the growth was steady.
• The CAGR formula doesn’t account for added funds or withdrawals during the measured period. Deposited funds would show growth and inaccurately inflate the CAGR; the reverse is true for withdrawals.
• Investors can’t rely on historical results, especially if the analysis period is short. The shorter the time frame, the less likely the actual CAGR will meet the expected rate.

Tips for calculating CAGR

Here are some tips to keep in mind when calculating CAGR.

Date mistakes

A common mistake when calculating CAGR is incorrectly counting the investment period. You must carefully read the dates to avoid accidentally adding or subtracting a year. Let’s use year-end stock prices as an example:

• 2020 = $100
• 2021 = $120
• 2022 = $125

Using a beginning and ending value and the number of investment years for the entire term, your calculation would look like this:

[($125/$100) ^ (½)] – 1 x 100 = 11.8%

When comparing annual sales figures to investment returns, the period often includes a partial year. A common mistake is choosing, for example, three years for the entire term when the correct value is two years. According to the above example, there are only two full years’ worth of year-end data.

Percentages

You must convert annual price data percentages into dollars to use the formula correctly. For example:

• 2020 = 10%
• 2021 = 15%
• 2022 = 4%

Convert the percentages to dollars and notice that you’re reading percentages for the entire yearly return, making the number of years in the formula three. This would be an excellent time to use the formula for CAGR in Excel because adding a helper column for percentage conversions to dollar values makes the calculation easier.