Pinterest Pixel

2 Useful Methods for Calculating CAGR in Excel

CAGR stands for the Compound Annual Growth Rate. CAGR is used to calculate the rate of growth... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

2 Useful Methods for Calculating CAGR in Excel | MyExcelOnline 2 Useful Methods for Calculating CAGR in Excel | MyExcelOnline

CAGR stands for the Compound Annual Growth Rate. CAGR is used to calculate the rate of growth of an investment over a given time frame. CAGR is particularly helpful for comparing the performance of various investments and for assessing investment returns over a period of several years.

The CAGR can be calculated using a number of Excel functions and formulas. This blog post will go over the significance of CAGR and how to use CAGR in Excel to analyze investment returns –

Let’s dive in!

Download the Excel Workbook below to follow along and understand how to calculate CAGR in Excel –

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.

See also  SEARCH Formula in Excel

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.

2 Useful Methods for Calculating CAGR in Excel

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

2 Useful Methods for Calculating CAGR in Excel

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

=(B15/B2)

2 Useful Methods for Calculating CAGR in Excel

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

=(B15/B2)^

2 Useful Methods for Calculating CAGR in Excel

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
See also  VLOOKUP Function: Introduction

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

2 Useful Methods for Calculating CAGR in Excel

Be sure to keep the above equation in brackets.

Step 5: Subtract 1 from the entire function.

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

2 Useful Methods for Calculating CAGR in Excel

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

2 Useful Methods for Calculating CAGR in Excel

 

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(

2 Useful Methods for Calculating CAGR in Excel

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),

2 Useful Methods for Calculating CAGR in Excel

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,

2 Useful Methods for Calculating CAGR in Excel

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)

2 Useful Methods for Calculating CAGR in Excel

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

See also  Get the Day Name for a Date in Microsoft Excel - 3 Easy Ways

2 Useful Methods for Calculating CAGR in Excel

 

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%.

2 Useful Methods for Calculating CAGR in Excel

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.

See also  Create a Named Range in Excel

 

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!

If you like this Excel tip, please share it
2 Useful Methods for Calculating CAGR in Excel | MyExcelOnline 2 Useful Methods for Calculating CAGR in Excel | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...