A função XTIR é uma das ferramentas mais precisas do Excel para calcular a taxa de retorno de investimentos com fluxos de caixa irregulares. Mas ela também é uma das que mais gera dúvidas e erros quando não está configurada corretamente. Um pequeno erro na estrutura dos dados pode fazer a XTIR retornar um valor completamente errado ou um código de erro que parece impossível de resolver. Neste artigo iremos mostrar os erros mais comuns que as pessoas cometem ao usar a função XTIR no Excel e como corrigir cada um deles de forma prática e definitiva.
Erro #VALOR!: o problema das datas como texto
O erro #VALOR! é o mais frequente ao usar a XTIR, e a causa quase sempre é a mesma: as datas na coluna de datas estão armazenadas como texto em vez de como datas reais do Excel. Isso acontece muito quando os dados vêm de exportações de sistemas, planilhas compartilhadas ou quando as datas foram digitadas em um formato que o Excel não reconheceu automaticamente como data.
Para diagnosticar esse problema, selecione uma das células de data e formate-a como “Geral”. Se o valor que aparecer for um número inteiro grande, como 45678, a data está correta — esse é o número interno que o Excel usa para representar datas (contando os dias desde 01/01/1900). Se o valor que aparecer for o texto da data, como “15/03/2024”, a data está armazenada como texto e precisa ser convertida.
Para corrigir datas armazenadas como texto, use a função DATEVAL em uma coluna auxiliar:
=DATEVAL(A2)
Onde A2 contém a data em formato de texto. Depois copie os resultados, cole como valores (Ctrl+Alt+V > Valores) sobre a coluna original e delete a coluna auxiliar. Outra alternativa é selecionar a coluna com as datas de texto, ir em Dados > Texto para Colunas, e seguir o assistente selecionando o formato de data correto. Depois que todas as datas estiverem no formato correto, o erro #VALOR! da XTIR desaparecerá.
Erro #NÚM!: quando a função não consegue convergir
O erro #NÚM! na XTIR acontece em situações onde a função não consegue encontrar uma taxa que resolva matematicamente o problema. As causas mais comuns são três: todos os fluxos de caixa têm o mesmo sinal (todos positivos ou todos negativos), a taxa de retorno é tão extrema que está fora da capacidade de cálculo do Excel, ou a estimativa inicial fornecida está muito longe do resultado real e a função não consegue convergir.
Se todos os valores são positivos ou todos são negativos, verifique se o investimento inicial está sendo registrado como negativo. Lembre-se: qualquer saída de caixa precisa ser negativa. Se você está calculando a rentabilidade de uma carteira, o saldo atual precisa aparecer como positivo na última linha, representando o valor que você receberia ao resgatar tudo hoje.
Se o problema for de convergência, tente fornecer uma estimativa inicial diferente no terceiro parâmetro da função. O valor padrão é 10% (0,1), mas você pode tentar valores diferentes:
=XTIR(A1:A10; B1:B10; 0,5) ← tenta com estimativa de 50% =XTIR(A1:A10; B1:B10; -0,5) ← tenta com estimativa negativa =XTIR(A1:A10; B1:B10; 2) ← tenta com estimativa de 200%
Experimentar diferentes estimativas geralmente resolve o problema de convergência. Se nenhuma estimativa funcionar, revise os dados porque pode haver um erro estrutural no fluxo de caixa.
Resultado inconsistente: número de valores diferente do número de datas
Embora esse erro às vezes resulte em #VALOR! e às vezes em resultados incorretos sem nenhum aviso de erro, ele é um dos mais perigosos porque pode passar despercebido. Se o intervalo de valores tiver 10 células e o intervalo de datas tiver 9 ou 11 células, a XTIR pode retornar um resultado sem dar erro, mas esse resultado estará calculado com dados incorretamente pareados.
Para prevenir esse problema, crie uma célula de verificação que confira se os tamanhos dos dois intervalos são iguais:
=SE(LINS(A1:A10)=LINS(B1:B10); "OK"; "INTERVALOS DIFERENTES - VERIFICAR")
Mantenha essa verificação sempre visível próxima à fórmula da XTIR como um controle de qualidade. Qualquer discrepância no tamanho dos intervalos é sinalizada imediatamente, evitando que você trabalhe com um resultado incorreto sem perceber.
Resultado negativo inesperado: quando a XTIR retorna uma taxa negativa
Às vezes a XTIR retorna um resultado negativo, como -15% ou -32%, e o analista fica confuso, pois esperava um retorno positivo. Uma taxa negativa na XTIR não é necessariamente um erro de cálculo — pode ser simplesmente o reflexo de um investimento que está destruindo valor. Se o total dos retornos recebidos até agora (trazidos a valor presente) for menor do que o total investido, a taxa de retorno é de fato negativa.
Porém, se você esperava um resultado positivo e obteve negativo, verifique os sinais dos seus fluxos. O erro mais comum nesse caso é inverter os sinais: colocar os aportes (investimentos) como positivos e os resgates ou retornos como negativos, quando deveria ser o contrário. Na convenção correta da XTIR, investimentos são negativos (saída de caixa do investidor) e retornos são positivos (entrada de caixa para o investidor).
Se os sinais estiverem corretos e o resultado ainda for negativo, a interpretação é que o investimento está, neste momento, rendendo abaixo de zero em termos anualizados. Isso pode ser temporário — se o investimento tiver expectativa de geração de valor no futuro, inclua esses fluxos futuros projetados no cálculo para ver a XTIR completa do horizonte de análise.
Resultado absurdamente alto: taxa de centenas ou milhares por cento
O oposto do resultado negativo também acontece: a XTIR retorna uma taxa de 500%, 1.000% ou até mais, o que obviamente não reflete a realidade do investimento. Isso geralmente acontece quando os fluxos de caixa positivos são muito maiores e muito próximos no tempo dos fluxos negativos, ou quando há um fluxo positivo muito grande muito próximo (em dias) de um fluxo negativo pequeno.
Esse problema também pode ocorrer quando as datas estão erradas. Por exemplo, se o investimento está datado de 15/01/2024 e o primeiro retorno está datado de 15/01/2024 (mesma data), a XTIR vai calcular um retorno infinito porque há um ganho sem passagem de tempo. Verifique se nenhuma data de retorno é igual ou muito próxima (em dias) à data de investimento.
Outra causa é quando as datas estão em um formato errado que o Excel interpretou incorretamente. Por exemplo, se uma data foi interpretada como 15/01/1924 em vez de 15/01/2024, o intervalo de tempo calculado pela XTIR seria de 100 anos, gerando resultados absurdos. Sempre visualize as datas formatadas para garantir que o Excel as interpretou corretamente.
XTIR com fluxos de caixa não convencionais: múltiplos resultados
Assim como a TIR, a XTIR pode ter múltiplas soluções matemáticas quando o fluxo de caixa não é convencional, ou seja, quando há mais de uma mudança de sinal (de negativo para positivo ou vice-versa) ao longo da série. Por exemplo, se você investe, recebe retornos, precisa reinvestir no meio do projeto e depois recebe mais retornos, há duas mudanças de sinal e podem existir duas taxas que matematicamente satisfazem a equação da XTIR.
Nesse caso, a XTIR vai retornar apenas um resultado — aquele que está mais próximo da estimativa inicial fornecida. Mas esse resultado pode não ser o economicamente relevante para a sua análise. Para verificar se esse problema existe, plote o VPL do projeto para diferentes taxas de desconto e veja se a curva cruza o eixo zero mais de uma vez. Se cruzar, você tem múltiplas taxas e precisa usar a MTIR (TIR Modificada) em vez da XTIR.
Boas práticas para evitar erros na XTIR
A melhor forma de evitar erros na XTIR é adotar um conjunto de boas práticas desde a montagem da planilha. Sempre insira as datas diretamente nas células usando o formato de data do Excel, nunca como texto. Sempre verifique que o número de valores e datas é idêntico. Sempre inclua pelo menos um fluxo negativo (o investimento) e pelo menos um fluxo positivo (o retorno ou o saldo atual).
Crie uma célula de verificação que calcula a soma algébrica de todos os fluxos: se a soma for positiva, o projeto retorna mais do que foi investido e a XTIR deve ser positiva. Se for negativa, a XTIR deve ser negativa. Se a XTIR for positiva mas a soma for negativa (ou vice-versa), algo está errado nos dados.
Também é uma boa prática adicionar uma célula com o VPL do projeto usando a mesma taxa da XTIR calculada. O resultado do VPL deve ser zero (ou muito próximo de zero, considerando arredondamentos), pois por definição a TIR e a XTIR são as taxas que zeram o VPL. Se o VPL calculado com a taxa da XTIR não for próximo de zero, há um erro no cálculo que precisa ser investigado.
Se você curtiu esse artigo onde mostramos os erros mais comuns na função XTIR do Excel e como corrigir cada um deles, compartilhe com as suas redes sociais e não se esqueça de deixar um comentário aqui embaixo caso você tenha ficado com alguma dúvida.