You can use VBA code to calculate compound annual growth rate in Excel. Let’s start by looking at the following code.
Function CAGR(startValue As Double, endValue As Double, periods As Double) As Double
CAGR = (endValue / startValue) ^ (1 / periods) - 1
End Function
To use the function in a worksheet, enter =CAGR(start_value, end_value, periods)
in a cell, where start_value
is the initial value, end_value
is the final value, and periods
is the number of periods (e.g. years) over which the growth occurs.
The VBA code I provided creates a user-defined function called CAGR
that calculates the compound annual growth rate. Here’s a step-by-step explanation of how the function works:
- The function takes three arguments:
startValue
(the initial value),endValue
(the final value), andperiods
(the number of periods over which the growth occurs). - The compound annual growth rate is calculated using the formula
(endValue / startValue) ^ (1 / periods) - 1
. This formula raises the ratio of the final value to the initial value to the power of1 / periods
to determine the growth rate per period, and then subtracts 1 to get the growth rate as a percentage. - The function returns the calculated compound annual growth rate as its result.
To use the CAGR
function in a worksheet, you would enter the following formula in a cell: =CAGR(start_value, end_value, periods)
, where start_value
is the initial value, end_value
is the final value, and periods
is the number of periods (e.g. years) over which the growth occurs.
Note that you’ll need to open the VBA editor (press Alt + F11
) and paste the code into a module before you can use the CAGR
function in your worksheet.