Chitosunday posted some code a while back called PAYBACK, which I have inserted below. It helps to determine the number of periods it takes to recoup an initial investment. I have a similar use for the function, but have a little twist: My future periods array (finflow) is in non-contiguous cells. The code as written works fine when these cells all run together, but when I break them up -- as I need to do to separate years -- the code factors in the cells between the defined ranges and returns an incorrect value. This problem only occurs when the code has to jump from one block of the array to the next. I need to know if there is a way to have the code recognize only the defined cell array for the future period data. Help please?
Function Payback(invest, finflow)
x = Abs(invest)
i = 1
c = finflow.Count
Do
x = x - v
v = finflow.Cells(i).Value
If x = v Then
Payback = i
Exit Function
ElseIf x < v Then
P = i - 1
Z = x / v
Payback = P + Z
Exit Function
End If
i = i + 1
Loop Until i > c
Payback = "> " & c
End Function