How to Calculate CAGR in Excel

Excel does not feature an inbuilt CAGR function. Luckily, there are various ways you can use to get a solution. For instance, you can customize your own formula or combine inbuilt existing formulas to achieve the same results. 

This article is a step-by-step guide on calculating CAGR in Excel. 

What is CAGR?

Compound Annual Growth Rate (CAGR) is a measure used to represent the steady rate of return of an investment over a specific period, usually longer than one year. It provides a smoothed-out annualized growth rate, taking into account the effects of compounding. 

CAGR considers the initial and final values of the investment and calculates the rate of return that would be required for the investment to grow from the initial value to the final value over the specified period, assuming steady growth each year.

CAGR Syntax

The formula for Compound Annual Growth Rate (CAGR) is:

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

Where:

Ending Value is the final value of the investment.

Beginning Value is the initial value of the investment.

Number of Years is the total number of years over which the investment has grown. is the total number of years over which the investment has grown.

3 ways to calculate CAGR in Excel

Simple Method

Let’s take into consideration the data in Excel below. It shows an investment that grows from $10,000 to $20,000 in a period of 5 years

Table showing Years and amount invested

1. To calculate the CAGR of the above example, you need to type the following formula in an empty cell. 

=(C7/C3)^(1/5 )-1

2. Press enter to apply the formula

How to apply simple CAGR formula

3. Convert the decimals into percentages by clicking on the % icon on the home tab under the number group

How to get CAGR in percentage

As you can see from the image above the CAGR is 15%.

Using Rate Function

You can use some sections of the Rate function to calculate CAGR in Excel. First, let’s gather the necessary information:

Initial investment value (PV): The starting value of the investment.

Future value (FV): The ending value of the investment.

Number of periods (N): The total number of compounding periods.

1. Enter the Formula:

In an empty cell, type the following formula:

=RATE(N,,-PV,FV)

  • The first argument (N) is the number of periods.
  • The second argument is left blank (,,) as it is not used in CAGR calculation.
  • The third argument is the initial investment (PV).
  • The fourth argument is the future value (FV).

2. Press Enter:

After entering the formula, press Enter. The cell will now display the CAGR as a decimal.

3. Convert to Percentage:

To express the CAGR as a percentage, multiply the result by 100:

=RATE(Nper,,-PV,FV)*100

Let’s see how you can calculate CAGR in Excel using a real example. Consider an investment with the following details:

Initial Investment (PV): $10,000

Future Value (FV): $15,000

Number of Periods (N): 5 years

1. First, set up your spreadsheet like in the image below

How to set up excel for CAGR

2. Now type the following formula in cell C6. Note that C6 is the cell where you want to display your results.

=RATE(A3,,-B3,C3)

3. Click enter to apply the formula and get the CAGR

Formula to calculate CAGR in Excel

As you can see from the image above the CAGR in this Example is 8%

By creating your own CAGR function using VBA

Excel does not have an inbuilt CAGR function. Luckily you can build one by following these simple steps.

1. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

2. Insert a new module by right-clicking on any item in the Project Explorer, choose Insert, and then select Module.

How to create CAGR Function using VBA code

3. Copy and paste the VBA code below into the module.

Function CAGR(initialValue As Double, finalValue As Double, years As Integer) As Double
    ' Calculate Compound Annual Growth Rate (CAGR)
    
    If initialValue <= 0 Then
        CAGR = CVErr(xlErrNum)
        Exit Function
    End If
    
    If finalValue < 0 Then
        CAGR = CVErr(xlErrNum)
        Exit Function
    End If
    
    If years <= 0 Then
        CAGR = CVErr(xlErrNum)
        Exit Function
    End If
    
    CAGR = (finalValue / initialValue) ^ (1 / years) - 1
End Function

The syntax for the new custom function that we have created is: =CAGR(initial value, final value, number of years)

4. Close the VBA editor.

Now you can use the CAGR function in your Excel worksheet like any other Excel function. For example:

Custom CAGR function in Action

=CAGR(A2, B2, C2)

Here, A2 is the initial value, B2 is the final value, and C2 is the number of years.

Results for using the custom formula built with VBA

Make sure to replace A2, B2, and C2 with the actual cell references or values you want to use in your calculation.

Download the practice template used in this tutorial

Frequently Asked Questions

1. What is the formula to calculate CAGR in Excel?

The formula to calculate the Compound Annual Growth Rate (CAGR) using Excel involves the use of the RATE function. The general form of the formula is:

=RATE(N,,-PV,FV)

Where:

N: Number of periods.

PV: Initial investment (Present Value).

FV: Future value.

For example:

=RATE(B6,, -B2, C6)

2. How do you calculate a 5-year CAGR in Excel?

To calculate a 5-year CAGR in Excel, you can use the RATE function. Assuming your data is in cells B2 (Initial Investment), B6 (Number of periods), and C6 (Future Value), the formula would be:

=RATE(B6,, -B2, C6)

This formula calculates the CAGR over 5 periods, using the initial investment and future value as inputs.

3. How do I calculate yearly growth rate in Excel?

To calculate the yearly growth rate in Excel, you can use the following formula:

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

For example:

=((C6 / B2)^(1/5)) – 1

This formula calculates the annual growth rate over a 5-year period based on the initial and ending values.

4. What is the formula for CMGR in Excel?

The formula for Cumulative Monthly Growth Rate (CMGR) in Excel involves using the following formula:

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

For example:

=((C13 / B2)^(1/60)) – 1

This formula calculates the cumulative monthly growth rate over 60 months based on the initial and ending values. Note that CMGR is often used for monthly growth calculations, especially in scenarios where data is available on a monthly basis.

Leave a Reply

Videos

Discover more from Excel Wizard

Subscribe now to keep reading and get access to the full archive.

Continue reading