VBA function for annualized return (geometric mean)

[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

 

[Harlan Grove]

(e-mail address removed) wrote...

I am trying to write this fuction for calculating annualized return

....

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

 

[joeu2004]

apparently I can not add 1 to each element in the input array/range

It is unclear what you did to "add 1 to each element", but I had no
problem making the following change (inserting "1 +"):

a(i) = 1 + d.Cells(i)

I am getting an error.

It is unclear what you mean by "error". Do you mean an Excel
#VALUE or similar error? I do not get any such thing. Or do you
mean that the result is not what you expect -- perhaps always -1?

You have a programming error. By writing "ReDim a(d.Cells.Count)",
you implicitly set the lower bound to 0. But you fill only a(1)
through
a(10). Since you declare "dim a() as single", a(0) is zero, so
Product() always returns zero. You should write:

Redim a(1 to d.Cells.Count)

On the other hand, it is unclear what you pass to your ROR()
function. The wrong parameters for "d" or "n" could cause Excel
errors, of course.


----- original posting -----