How to Change Payback Macro to Payback Function

[Barklie]

Hi all,

I have made a macro for calculating payback for an investment using a one dimensional array of cash flows. The problem is I would like this to be a function where the user can just select an array and the macro will run automatically using the range that user has provided and output the payback for them.

I am attaching the macro I currently use, where I have to manually set the range. If anyone knows anyway this can be made into a function, please let me know. Thanks!

Sub Payback()

On Error Resume Next

Dim R As Range

Set R = Range("I2:I15")

CumCF = 0
i = 0

For Each C In R
    If CumCF <= 0 Then
        CumCF = CumCF + C.Value
        Pybck = i + 1 - (CumCF / R.Cells(i + 1))
    End If
    i = i + 1
Next

Range("C3") = Pybck

End Sub

 

[JoeMo]

I don't know what your input data look like so there's no error trapping in this. This can be used directly in a worksheet cell or called from a macro.

Function Payback(R As Range)
Dim C As Range
CumCF = 0
i = 0
For Each C In R
    If CumCF <= 0 Then
        CumCF = CumCF + C.Value
        Pybck = i + 1 - (CumCF / R.Cells(i + 1))
    End If
    i = i + 1
Next
Payback = Pybck
End Function

 

[Barklie]

Worked like a charm. Thank you Joe.

 

[JoeMo]

Worked like a charm. Thank you Joe.

You are welcome - thanks for the reply.