Loop expression to calculate return

[Jason]

Can anyone tell me how to create a vba function (I
believe I need some sort of loop expression) to 1)add 1
to each number and then 2) multiply each of the resulting
values and then 3) subtract 1. I am trying to create a
user-defined function to calculate the cumulative return
of a series of numbers.

For example: if the range of (4 in this case) numbers
was .0291, -.0021, .0032 and .0337, then the answer to
the function would be .07559 (1.0291*.9979*1.0132*1.0337 -
1) = .07559).

Thank you.

 

[Robin Hammond]

Jason,

this should do it.

Public Function CRetn(rngValues As Range) As Double
Dim rngCell As Range

CRetn = 1
For Each rngCell In rngValues
CRetn = CRetn * (1 + rngCell.Value)
Next rngCell

CRetn = CRetn - 1
End Function

Robin Hammond
www.enhanceddatasystems.com