Cálculo do PayBack – Excel VBA

Gráfico de Gantt no Excel - Gráfico de Barra Empilhada
Gráfico de Gantt no Excel – Gráfico de Barra Empilhada
30 de junho de 2011
Google +1
Google +1
2 de julho de 2011

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

7 Comments

  1. ++RiptoR++ disse:

    òtimo tutorial. Sua explicação sobre cada agrupamento do código VBA é o que o deixa rico.
    Acredito que uma explicação mais detalhada poderia estimular a compreensão e até a ‘vontade de programar em VBA’ dos iniciantes e intermediários, como eu.

    Ex: Ao invés de detalhar cada agrupamento do código, informar a forma de ler e a funcionalidade de cada linha de código.

    Muitos parabéns, seu blog merece “+999” em recomendação Google. 🙂

  2. Gustavo disse:

    Ótimo tutorial, me deu um norte!
    Porém, não consigo fazer quando é em anos. Tem como você dar uma luz?
    Abraços,

    Gustavo

  3. adriano disse:

    Bom dia, otima materia, mais no meu caso o empreendimento tem entradas antes mesmo de efetuar as saidas necessarias para o empreendimento, então o caixa fica positivo mais ainda não foram recuperadas as despesas com o projeto, como faço?
    Desde já agradeço sua atenção.

  4. Ivo disse:

    Bom dia,

    Excelente codigo, e acima de tudo excelente site 😀 aprendo muito aqui 😀

    Tenho apenas uma duvida aqui no payback :S, se eu quiser calcular em anos e meses numa tabela semelhante a esta mas somente anos não existe meses.
    Eu para calcular os anos “Anos” nem preciso mexer, mas para calcular os meses, como faço?multiplico por 12? ou apenas retiro os 30?

    cumprimentos

  5. rogerio disse:

    boa noite, se eu quiser calcular o payback descontado, no qual aplica-se uma certa taxa mínima de atravidade (TMA), como devo proceder

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.