VBA function for annualized return (geometric mean)

  • Thread starter Thread starter ankurtyagi
  • Start date Start date
A

ankurtyagi

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
 
(e-mail address removed) wrote...
I am trying to write this fuction for calculating annualized return
Click to expand...
....

Why are you doing this? Why not use Excel's built-in GEOMEAN function?
 
Back
Top