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), and`periods`

(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 of`1 / 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.