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
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
3. Convert the decimals into percentages by clicking on the % icon on the home tab under the number group
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
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
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.
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:
=CAGR(A2, B2, C2)
Here, A2 is the initial value, B2 is the final value, and C2 is the number of years.
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.
