Use VBA code to calculate Compound Annual Growth Rate

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:

1. The function takes three arguments: startValue (the initial value), endValue (the final value), and periods (the number of periods over which the growth occurs).
2. 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.
3. 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.