2 Useful Methods for Calculating CAGR in Excel

• What is CAGR?
• Examples of applying CAGR in Excel
• Using CAGR to analyze different investments
• Constraints of CAGR
• Conclusion

 

download excel workbookCAGR-in-Excel.xlsx

 

What is CAGR?

CAGR measures the rate of growth of an investment over time while accounting for the impact of compounding. The average yearly growth rate of investment throughout the given time period is expressed as a single number.

For instance, if you invested $100 in a company in 2010 and by 2020, the value of the investment had increased to $250, The CAGR of the investment would be:

CAGR = (Ending Value / Beginning Value)^(1 / Number of Years) – 1
= ($250 / $100)^(1/10) – 1
= 9.6%

This means that the investment grew at an average annual rate of 9.6% over the 10-year period.

The use of CAGR in the area of entrepreneurship and startup businesses is another intriguing application. A startup’s growth rate over time may be measured using CAGR, which is helpful for attracting investors and assessing the likelihood of future growth.

CAGR is a tool that can be used to define attainable financial objectives in addition to analyzing investment results. CAGR, for instance, can be used to calculate the yearly growth rate necessary to meet a goal return that an investor intends to attain over a given time period.

 

Examples of applying CAGR in Excel

Let us explore the different ways to apply the CAGR formula in EXCEL

Example 1

As we have used the formula to calculate CAGR above, we can do the same in EXCEL. In this example, we have changing values of our investment over a time period. Let us see how to calculate the CAGR here.

Here we have used the same formula explained above:

= (Ending Value / Beginning Value)^(1 / Number of Years) – 1

Step 1: Enter the ending value or the future value of the investment. Here that is in cell B15.

=(B15

Step 2: Divide the above by the beginning or current value of the investment. Here that is in cell B2.

=(B15/B2)

Step 3: Make sure to keep the above equation in brackets. Now enter the “^” sign for exponential calculations.

=(B15/B2)^

Step 4: Enter 1 divided by the number of periods the investment was made for. Here we have used the count function to calculate the number of years.

• Enter the COUNT function
• Select the entire column of values. Here A2:A15
• Close the COUNT Function

=(B15/B2)^(1/COUNT(A2:A15))

Be sure to keep the above equation in brackets.

Step 5: Subtract 1 from the entire function.

=(B15/B2)^(1/COUNT(A2:A15))-1

The CAGR function will produce the rate at which your investment has grown or needs to grow to achieve your target value.

 

Example 2

The RRI function can be used to calculate CAGR in Excel. The syntax for CAGR has the following arguments –

=RRI(Nper, Pv, Fv)

• Nper Required. Nper is the number of periods for the investment.
• Pv Required. Pv is the present value of the investment.
• Fv Required. Fv is the future value of the investment.

RRI returns the interest rate given nper (the number of periods), pv (present value), and fv (future value).

 

Step 1: In Cell E1, enter the RRI function.

=RRI(

Step 2: We are going to count the number of years by using the COUNT function.

• Enter the COUNT function
• Select the entire column of values. Here A2:A15
• Close the COUNT Function

=RRI(COUNT(A2:A15),

Step 3: Select the present value of our investment. We can also interpret this as the original value of our investment. Here, it is B2

=RRI(COUNT(A2:A15),B2,

Step 4: Select the future value of our investment. We can also interpret this as an increased value of investment or simply a future target value you wish to achieve. Here, it is B15

=RRI(COUNT(A2:A15),B2,B15)

As we can see, this formula returns the exact same result.

 

Using CAGR to analyze different investments

By contrasting the growth rates of several investments over the same time period, CAGR may be used to analyze investment returns. When comparing investments with various compounding frequencies or assessing the profitability of assets over extended periods of time, this can be especially helpful.

Consider examining two equities, A and B, for a period of ten years. Stock A started out with a $100 value and increased to $250, while Stock B increased from its initial $100 worth to $200. Here’s how Excel might be used to determine the CAGR for each stock:

CAGR for Stock A is ($250/$100) x (1/10) – 1 = 9.6%.

CAGR for Stock B is ($200/$100) x (1/10) – 1 = 7.2%.

CAGR can also be useful for forecasting future investment returns. By using historical CAGR data, investors can estimate the future growth rate of an investment and make informed decisions about buying, selling, or holding onto that investment.

 

Constraints of CAGR

CAGR has various drawbacks despite being a helpful indicator for assessing investment returns. First of all, it makes the unrealistic assumption that the investment will increase at a steady pace for the whole time frame. Second, it doesn’t account for any swings in the investment’s value over the time period in question.

It’s also important to keep in mind that there are other metrics to consider when assessing investment returns in addition to CAGR. When making investing decisions, investors need also to take other aspects into account, such as risk, liquidity, and diversity.

 

Conclusion

In conclusion, CAGR is a useful financial measure that can be applied to assess investment returns over a given time frame. Excel is a quick and simple tool for financial analysis because it has several functions and formulae that can be used to compute CAGR. Investors can analyze the performance of various investments using CAGR and decide whether to buy, sell, or keep their investments with knowledge.

When assessing investment returns, it’s crucial to keep in mind that CAGR has its limitations and should be combined with other indicators.

Click here to learn about other financial formulas available in Excel!