# 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.

Scroll to Top
Send this to a friend