How to calculate Compound Annual Growth Rate (CAGR) in Excel?

Compound Annual Growth Rate in Excel is a vital tool for assessing investment performance, business growth, and financial projections. By mastering this calculation method, you can accurately measure the average annual growth rate of an investment or business over a specified period. With Compound Annual Growth Rate in Excel, you can make informed decisions about investments, set realistic business goals, and track progress towards financial objectives. Whether you’re analyzing stock performance, evaluating marketing campaigns, or projecting future revenues, understanding and calculating compound annual growth rate in Excel is essential for effective financial planning and decision-making. Embrace this powerful feature to gain insights into your financial performance and drive success in your investments and business endeavors.

This Tutorial Covers:

1. What is Compound Annual Growth Rate?
2. CAGR formula
3. How to calculate CAGR in Excel
   • CAGR Formula 1: Simple Excel formula for calculating CAGR
   • CAGR formula 2: RRI function
   • CAGR formula 3: POWER function
   • CAGR formula 4: RATE function
   • CAGR formula 5: IRR function
4. Things to Remember

1. What is Compound Annual Growth Rate?

The Compound Annual Growth Rate (CAGR) is a financial metric that calculates the average annual growth rate of an investment over a specific period of time, considering the effect of compounding. It is a useful tool for measuring the performance of an investment over a longer time period and provides a single, easily comparable number that represents the investment’s overall growth rate. CAGR is commonly used in finance to compare the returns of different investments and to forecast future performance.

For example, if you bought land in 2015 worth $2000 and $5000 in 2023, CAGR is how this investment has grown yearly.

2. CAGR formula:

The following is the general CAGR formula used in business, finance, and financial analysis:

 (EV/BV)^(1/n)-1

Where,

EV, Ending Value: This is the final value of the investment at the end of the period for which you want to calculate the CAGR.

BV, Beginning Value: This is the initial value of the investment at the beginning of the period for which you want to calculate the CAGR.

N, Number of Years: This is the length of the investment period, measured in years. It is calculated by subtracting the start date from the end date and dividing by 365 (or 366 for leap years) to get the number of years.

Return Value:

The return number will be numerical, and since the CAGR works best when expressed as a percentage, we can convert it into a percentage.

3. How to calculate CAGR in Excel?

Let’s look at how to compute compound annual growth rate in your Excel worksheets now that you have a basic understanding of what it is. There are five different methods to construct an Excel formula for CAGR.

1. Using generic CAGR formula
2. Using POWER function
3. Using RRI function
4. Using RATE function
5. Using IRR function

• CAGR Formula 1: Simple Excel formula for calculating CAGR:

It takes only a few minutes, if not seconds, to create a CAGR calculator in Excel if you are familiar with the general CAGR formula described above.

Simply enter the values listed below in your worksheet:

BV – Beginning value of the investment

EV – Ending value of the investment

n – Number of periods

Then, in a blank cell, write the CAGR formula as follows:

=(EV/BV)^(1/n)-1

In this example, BV is in cell A2, EV in B2, and n in C2. So, we enter the following formula in B4:

=(B2/A2)^(1/C2)-1

Your CAGR formula can be made more flexible if all investment values are enumerated in a single column, and the number of periods can be determined automatically.

The following formula can be used to compute CAGR in our example worksheet in this situation:

=(B10/B2)^(1/(9-1))-1

Note: It should be noted that the above formula uses B10 as the cell with the end value, B2 as the cell with the beginning value, and 8-1 as the interval between the two. You can alter these variables to suit your needs.

The computation result might not always be formatted as a percentage. Please maintain choosing the calculation result.

Also Read: How to Calculate Standard Deviation in Excel?

• CAGR formula 2: RRI function:

Using Excel’s RRI function, which determines an equivalent interest rate on a loan or investment over a given period based on the present value, future value, and the total number of periods, is the simplest method to determine compound annual growth rate:

=RRI(nper, pv, fv)

Where,

Nper stands for the total amount of periods.

Pv stands for the investment’s present value.

Fv stands for the investment’s future value.

The steps to calculate CAGR in Excel using RRI Function are described below:

Step 1: In a blank cell, write the CAGR formula as follows:

=RRI(C2,A2,B2)

• CAGR formula 3: POWER function:

Excel’s POWER function, which returns the result of raising a number to a specific power, can be used to quickly and easily compute CAGR.

The POWER function has the following syntax:

=POWER(number, power)

Where,

Power is the exponent to increase the base number to, and number is the base number.

Using the POWER function, create an Excel CAGR calculator by defining the parameters as follows:

Number – This is the value of calculation’s ending value (EV) / beginning value (BV)

Power – 1/number of periods (n)

=POWER(EV/BV, 1/n)-1

Here is our strong CAGR formula operates:

=POWER(B10/B2,1/(9-1))-1

• CAGR formula 4: RATE function:

Excel’s RATE function, which gives the interest rate per period of an annuity, can also be used to calculate CAGR.

The RATE function has the following syntax:

=RATE(nper, pmt, pv, [fv], [type], [guess])

Where:

nper: Required. The entire number of payment intervals in an annuity.

pmt: Required. The payment made each period. Over the course of the annuity, this amount won’t alter.

pv: Required. The value of a sequence of future payments as a lump sum at this moment, or the present value.

fv: Optional. The future value, or the cash balance that you want to attain after the last payment is made.The default number is 0 if it is omitted.

type: Optional. Indicates when payments are due. Use 0 for payments at the end of the period or 1 for payments at the beginning of the period. The default number is 0 if it is omitted..

guess: Optional. An estimate for the rate. If omitted, Excel uses the default value of 0.1 (10 percent).

You must provide the first (nper), third (pv), and fourth (fv) parameters in the following manner to convert the RATE function into a CAGR calculation formula:

=RATE(n,,-BV, EV)

Where,

BV – Beginning value of the investment

EV – Ending value of the investment

n – Number of periods

Note: The beginning value (BV) must be specified as a negative integer; otherwise, your CAGR formula will produce the #NUM! error.

The following method can be used to determine the compound growth rate in this example:

=RATE(8,,-B2,B10)

You can use the ROW function to have the ROW function calculate the number of periods for you in order to save yourself the bother of doing it manually:

=RATE(ROW(B10)-ROW(B2),,-B2,B10)

Also Read: How to Calculate Internal Rate of Return in Excel?

• CAGR formula 5: IRR function:

For a sequence of cash flows that happen at regular intervals, Excel’s IRR function returns the internal rate of return. (i.e., days, months, quarters, years, etc.).

It has the following syntax:

=IRR(values, [guess])

Where:

values: Required. An array or range of cash flows that represent the series of payments and income from an investment or project. The array must contain at least one positive and one negative value, and the first value must be negative to represent an initial investment.

guess: Optional. A guess at the internal rate of return (IRR). If omitted, Excel uses 0.1 (10 percent).

You would have to reshape the initial data in this manner because the Excel IRR function is not specifically made for calculating compound growth rate:

• Enter a negative figure for the investment’s starting value.
• The investment’s ending value is a positive value.
• The numbers in the middle are all zeros.

After rearranging your primary data, use the following straightforward formula to determine CAGR in Excel:

=IRR(B2:B10)

Where B2 is the beginning value and B10 is the ending value of investment:

4. Things to Remember:

• The Compound Annual Growth Rate, or CAGR, value is returned by the Microsoft Excel CAGR formula from the collection of supplied values.
• CAGR calculates the return value on an investment over a specific time frame and measures it.
• Excel CAGR formula can be used to determine how much the investment should yield annually at a constant growth rate.
• The #VALUE! mistake is most likely to be present if you encounter any Excel CAGR formula errors.

Application of Compound Annual Growth Rate in Excel

• Investment Analysis: Calculate the compound annual growth rate in Excel to assess the performance of investments over time, aiding in investment decision-making.
• Financial Forecasting: Use compound annual growth rate to project future revenues, expenses, and profits, facilitating strategic financial planning.
• Business Growth: Evaluate the growth rate of sales, customer base, or market share using compound annual growth rate in Excel, guiding business expansion strategies.
• Performance Comparison: Compare the growth rates of different assets, products, or business units to identify high-performing areas and areas for improvement.
• Risk Assessment: Assess the volatility and stability of investments or business performance by analyzing the consistency of compound annual growth rates.
• Benchmarking: Use compound annual growth rate as a benchmark to evaluate performance against industry averages or competitors, informing strategic decision-making.

For ready-to-use Dashboard Templates:

1. Financial Dashboards
2. Sales Dashboards
3. HR Dashboards
4. Data Visualization Charts