Here is a Visual Basic for Application (Microsoft Excel) function to calculate the payback time in years.

Function PayBack(Rango As Range, Optional Inversion As Double = 0, Optional Tasa As Double = 0) As Double

‘This is a Visual Basic for Application (Microsoft Excel) function to calculate the payback time in years.
‘The result is in years.
‘The parameters are:
‘Rango: the cash flow to analyze
‘Inversion: is the year where the investment ends.
‘This is an optional parameter for cash flows
‘where the initial down payment is in the firsts years
‘and make some year cash flow positive in excess
‘before the payback is done.
‘Tasa: here goes a rate to discount the cash flow.
‘This is an optional parameter.
‘
‘Copyright: pepemosca
’2007-04-26

Dim SumaParcial, Siguente, CantidadAnos As Double
SumaParcial = 0
Siguente = 0
CantidadAnos = 0

For Each Celda In Rango
SumaParcial = SumaParcial + Celda.Value / (1 + Tasa) ^ CantidadAnos

If SumaParcial >= 0 And CantidadAnos >= Inversion Then
Siguiente = Celda.Value / (1 + Tasa) ^ CantidadAnos
Exit For
End If

CantidadAnos = CantidadAnos + 1
Next Celda

PayBack = CantidadAnos – (SumaParcial – Siguiente) / Siguiente
End Function