VBA function for annualized return (geometric mean)
 

Hi Guys,

I am trying to write this fuction for calculating annualized return
from the monthly returns I have. If I use, (1+Rm) as my range input, I
can calculate the annualized return. But when I am trying to add 1 to
monthly returns within the function and get the answer, I am getting an
error. (apparently I can not add 1 to each element in the input
array/range).
Can somebody please help! (thanks in advance)

Function ROR(d As Range, n As Integer)

Dim a() As Single
Dim f As Integer

'f = Application.Count(d)

c = d.Rows.Count
e = d.Columns.Count
If c e Then
f = c
Else
f = e
End If

ReDim a(d.Cells.Count)
For i = 1 To UBound(a)
a(i) = d.Cells(i)
Next i


prod = Application.WorksheetFunction.Product(a())


k = n / f

ROR = prod ^ k - 1


End Function

VBA function for annualized return (geometric mean)
 

VBA function for annualized return (geometric mean)