Hi,

I have attempted to write a basic vba code for calculating payback (the time it takes to get back your original investment using a stream of cash flows). Using some referenced code and writing some myself I have been unable to get it to output correctly. I have minimal experience in VBA so any help would be appreciated!


Public Function mypayback(cashflow As Range)

Dim csum As Single
Dim time As Integer
Dim total As Single
Dim rangesum As Single

total = 0

For Each cell In cashflow
total = total + cell.Value
Next cell

rangesum = total

If cashflow(1) >= 0 Or rangesum < 0 Then
mypayback = "No Value"
Exit Function

Else
csum = 0
For time = 1 To cashflow.Cells.Count
csum = csum + cashflow(time)
If csum > 0 Then
Exit For
End If
Next time

csum = csum - cashflow(time)

mypayback = Period - 2 - csum / cashflow(time)

End If


End Function


I think everything above the bold is correct but I am having difficulty understanding and following the bold part. I feel as though there must be an easier way to write it.

Thanks!