Cálculo do PayBack - Excel VBA

Objetivo: Disponibilizar uma função para realizar o cálculo do retorno do investimento de forma automática.

Payback é o tempo decorrido entre o investimento inicial e o momento no qual o lucro líquido acumulado se iguala ao investimento.

Leia mais no Wikipedia sobre retorno de investimento.

No exemplo que consta na imagem inicial deste artigo temos uma lista de valores negativos, que representam os valores investidos. Em determinado momento estes valores começam a ser positivos, ou seja, o resultado começa a superar o valor investido, recuperando assim todo o investimento.

O cálculo do payback consiste em determinar o tempo exato para o retorno do investimento, no caso 16 meses e 19 dias.

Para o cálculo devemos contar quantos meses negativos temos até iniciar a série positiva, esta é a parte inteira dos meses, 16 no caso.

A quantidade de dias restantes para o período é calculado dividindo o último valor negativo pelo cálculo absoluto do último negativo menos o primeiro positivo,  tudo vezes 30 que é a quantidade de dias conforme a fórmula abaixo:

• Round(Abs((lUltNegativo / Abs(lUltNegativo - lPrimPositivo))) * 30, 0)

Este cálculo pode ser feito no Excel com o uso de uma coluna auxiliar, mas no caso utilizamos uma fórmula VBA  criada justamente para retornar a quantidade de meses e dias para o retorno do investimento.

Para usar a função faça o seguinte:Adicione a função abaixo conforme explicado no artigo http://guiadoexcel.com.br/criando-funcoes-proprias-globais.

Abaixo a função comentada:http://guiadoexcel.com.br/criando-funcoes-proprias-globais

'Função que calcula o payback
Public Function gfPayback(ByVal lRange As Range) As String
    Application.Volatile

    Dim lCel            As Range
    Dim lUltNegativo    As Double
    Dim lPrimPositivo   As Double
    Dim lContaPeriodo   As Long
    Dim lParcial        As Long

    'Se não houver nada selecionado retorna vazio
    If lRange Is Nothing Then
        gfPayback = ""
    Else
        'Passa por todas as células selecionadas
        For Each lCel In lRange
            lContaPeriodo = lContaPeriodo + 1
            'Se o valor for menor que 0 a variável do último valor negativo é atualizada
            If lCel.Value < 0 Then
                lUltNegativo = lCel.Value
            Else
                'Se o valor for positivo atualiza o primeiro positivo
                lPrimPositivo = lCel.Value
                'Calcula a parte parcial do retorno do investimento
                lParcial = Round(Abs((lUltNegativo / Abs(lUltNegativo - lPrimPositivo))) * 30, 0)

                'Retorna o cálculo do payback
                gfPayback = CStr(lContaPeriodo - 1) & " meses e " & lParcial & " dias"
                Exit For
            End If
        Next lCel
    End If
End Function

Abraço

Marcos Rieper