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
Baixe a planilha
Abraço
Marcos Rieper
