CODEWITHSUNDEEP
vbaexcel
T.Jensen
T.Jensen

Reputation: 35

UDF to calculate CAGR from return data

I would like to make a UDF which will calculate the CAGR of a given range.

The data given to the function will typically be monthly return data, so i need to:

If i do it manually i would type in the formula =product(rng+1)^(1/(Count(rng)/12))-1 and array enter CTRL+Shift+Enter

I have tried two different path to getting this done. The first is to basically do the before mentioned formula in vba.

Option Explicit

Function CAGR(rng As Range) As Double

Dim total As Double
Dim n As Integer
Dim pwr As Double

Total = Application.FormulaArray="=Product(rng+1)"
n = Application.WorksheetFunction.Count(rng)
pwr = (1 / (n / 12))

CAGR = Application.WorksheetFunction.Power(total,  pwr) - 1

End Function

However the line

Total=Application.formulaArray="=Product(rng+1)"

Won't Work presumebly because i'm using the formulaArray function wrong...

The other approach i have tried is to make the function with a loop, where i would like to add 1 to each cell value in the range and multiply them. (not the entire function).

Option Explicit

Function CAGR2(rng As Range) As Double

Dim cell As Variant
Dim k As Double
Dim n As Integer

For Each cell In rng
    cell.Value = cell.Value + 1
    Next cell

k = Application.WorksheetFunction.Product(rng)

CAGR2 = k

End Function

All the CAGR functions i have found on the internet seems to be on Price data, so i would like to stress that this function should calculate on return data (1%, -2%, 3% and so on).

I have been struggeling with this problem for hours so any help would be greatly appriciated!

Many thanks

Upvotes: 1

Views: 267

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

use the evaluate function:

CAGR = ActiveSheet.Evaluate("Product(" & rng.address(1,1,xlA1,True) & "+ 1)^(1/(Count(" & rng.address(1,1,xlA1,True) & ")/12))-1")

Upvotes: 2

Matt Cremeens
Matt Cremeens

Reputation: 5151

I liked your loop idea and think you are on the right track. Building off of your second piece of code

Option Explicit

Function CAGR2(rng As Range) As Double

Dim cell As Variant
Dim n As Integer

CAGR2 = 1
n = 0
For Each cell In rng
    CAGR2 = CAGR2 * (cell.Value + 1)
    n = n + 1
Next cell

CAGR2 = CAGR2 ^ (1 / (n / 12))

End Function

Notice that I keep track of the number of cells in the range with n and we just accumulated the product of each cell with CAGR2. Once the product has fully accumulated, we then take it to the correct exponent. I'm not sure, but you may need to subtract one after it's all said and done.

Upvotes: 0

Related Questions