Para calcular a Taxa Interna de Retorno de um fluxo de caixa com datas irregulares, use =XTIR(valores; datas; [suposição]), informando os valores do fluxo (com pelo menos um negativo, o investimento) e as datas correspondentes de cada pagamento — diferente da TIR comum, a XTIR não exige intervalos regulares entre os pagamentos.
A função XTIR é uma função financeira do Excel que nos permite calcular, com base em um fluxo de caixa não periódico, um percentual hipotético que indicará se um investimento ou projeto é rentável ou não.
Entretanto, antes de nos aprofundarmos na função XTIR no Excel, devemos nos ater a algumas definições.
O que é TIR?
Taxa Interna de Retorno ou simplesmente TIR, é um percentual de desconto hipotético que, através do Valor Presente Líquido (VPL) indica, levando em consideração o valor de um investimento e despesas, dentro de determinado fluxo de caixa, se há equivalência entre os dois.
Em outras palavras, a Taxa Interna de Rentabilidade é uma taxa de juros que nos indica se um investimento vale ou não a pena com base em sua rentabilidade.
Vale ressaltar que no Excel temos duas maneiras de calcular a Taxa Interna de Retorno.
TIR: a Função do Excel que Calcula a Taxa Interna de Rentabilidade de Intervalo Regulares
Sintaxe da função TIR
A sintaxe da Função TIR é:
- TIR(valores, [suposição])
Para entender a Função TIR, vamos imaginar que André tenha investido R$ 146.000,00, no ano de 2017, para fundar uma startup.
Seu retorno em anos posteriores foi positivo.
Observe que na composição da sintaxe da Função TIR estão presentes os argumentos valores e suposição.
- Valores são o argumento obrigatório para calcular a Taxa Interna de Rentabilidade, sendo obrigatório a inclusão de um valor negativo para que o cálculo seja viável.
- Ao incluir somente valores positivos em planilha, aplicando a fórmula TIR, o Excel retornará o resultado #NÚM!. Deve haver ao menos um argumento positivo, mais negativo, para que seja calculado a rentabilidade,
- Quaisquer que sejam as movimentações, é necessário saber que inseri-las em ordenamento é obrigatório para que a fórmula cumpra o seu papel.
- Valores lógicos, texto, matrizes em branco, ou até mesmo caracteres especiais, serão ignorados, se presentes em planilha.
- Estimativa é um argumento obrigatório, pode ser uma previsão do retorno esperado e quando omitida, na grande maioria dos casos, terá o percentual de 10% atribuído a fórmula.
Para além da Taxa Interna de Rentabilidade(TIR)
Existem outros dois conceitos que precisam estar claros para compreender a utilidade da TIR. São eles: VPL e TMA.
Ambos compõem a análise financeira da porcentagem de segurança de um investimento em um negócio.
- Taxa Mínima de Atratividade: feito o próprio nome já sugere, a TMA é uma taxa de juros mínima, ou até mesmo máxima em alguns casos, que um investidor julga como aceitável para se expor ao risco do investimento. Nela deve-se considerar a liquidez, o risco e a remuneração que o ativo retornará.
- Valor Presente Líquido: informa em seu cálculo o valor presente de um fluxo de caixa, levando em consideração as entradas e saídas de valores em recursos financeiros, menos o investimento inicial.
Para criar e avaliar sua Taxa Mínima de Atratividade deve-se calcular o Valor Presente Líquido de um investimento e sua Taxa Interna de Rentabilidade.
Tais métricas financeiras são um conjunto e devem ser usadas como tal. De nada vale calcular seu VPL, se não calcular a sua TIR. Sem ambos não há TMA.
Função XTIR no Excel: Calcula a Taxa Interna de Rentabilidade de Intervalo Irregulares
Após entendermos os conceitos anteriores, retornamos à função XTIR. No início de nosso artigo atribuímos a XTIR seguinte definição:
A XTIR é uma função financeira do Excel que nos permite calcular um percentual hipotético que indicará se um investimento ou projeto é rentável ou não levando em consideração um fluxo de caixa não periódico.
Isto significa que XTIR calculará a Taxa Interna de Rentabilidade levando em consideração intervalos irregulares.
Sintaxe da função XTIR no Excel
A sintaxe da função TIR tem os argumentos a seguir:
- XTIR(valores; datas; [suposição])
Em outras palavras no caso de intervalos irregulares, além de valores e suposição, iremos considerar datas que servirão de base para o resultado final.
- Valores: argumento obrigatório. E apesar de abordado anteriormente, o argumento valores em XTIR, possui suas especificidades. Os valores são sequências de pagamento de um fluxo de caixa. Isso significa que o valor correspondente ao investimento ou despesa é opcional e que os pagamentos poderão integrar em totalidade a equação. Vale ressaltar que todos os valores serão descontados em um ano base de 365 dias.
- Datas: argumento obrigatório faz referência ao cronograma de pagamentos dentro do fluxo de caixa. Apesar de sua ordem não possuir relevância, devem ser inseridas com a função DATA ou enquanto resultado de outras funções.
- Estimativa: argumento obrigatório, definido e aprofundado anteriormente e que quando ignorado, passa a corresponder ao percentual de 10% ou 0,1.
Exemplo da função XTIR no Excel
No exemplo abaixo, temos uma despesa inicial de R$ 258.550.00 em janeiro de 2022.
No decorrer do ano houveram pagamentos associados a suas respectivas datas, períodos irregulares.
Perceba que ao calcular XTIR de janeiro a dezembro o Excel retorna uma alíquota, que em um primeiro momento será decimal.
Ao utilizarmos a função porcentagem, teremos a TIR anual correspondente ao período relacionado.
Lembre-se que em valores serão o intervalo de 365 dias para o cálculo que mensura a TIR.
=XTIR(C4:C15;B4:B15), em resultado, 2,924564958 ou 292,46%.
Entretanto, sabemos que é uma porcentagem irreal e, para descobrirmos de fato a TIR desse investimento, devemos aplicar a seguinte fórmula:
=(1+XTIR(C4:C15;B4:B15))^(1/12)-1
A Taxa Interna de Rentabilidade é elevada ao quadrado e dividida por 12 meses, correspondentes ao intervalo de um ano.
Logo temos o valor da TIR mensal relativa ao intervalo irregular presente na planilha, exatos 12,07%.
Perceba que nesse exemplo a estimativa foi omitida, em caso assim a função XTIR atribui a fórmula uma porcentagem de 10% enquanto estimativa automaticamente.
Erro #NÚM! na função XTIR: causas mais comuns
O erro #NÚM! é o mais frequente de quem começa a usar a XTIR, e geralmente tem uma de três causas. A primeira é ter apenas valores positivos ou apenas valores negativos no intervalo — a XTIR precisa de pelo menos um valor negativo (representando o investimento ou a saída de caixa) e pelo menos um valor positivo (o retorno), senão o Excel não consegue calcular uma taxa de desconto que zere o fluxo. A segunda causa é ter datas duplicadas ou incoerentes com os valores. E a terceira, mais sutil, é quando a estimativa inicial está muito distante do resultado real: nesses casos, informe manualmente o terceiro argumento da XTIR com um valor mais próximo do esperado, em vez de deixar o Excel usar os 10% padrão.
XTIR versus TIR: qual usar em cada situação
Já explicamos que a TIR calcula a rentabilidade de fluxos de caixa com intervalos regulares (mensais, anuais), enquanto a XTIR calcula fluxos com datas irregulares. Na prática, se as suas movimentações não caem sempre no mesmo dia do mês, ou se você tem pagamentos e recebimentos espalhados de forma imprevisível ao longo do ano, a XTIR é sempre a escolha mais precisa, porque ela usa as datas reais no cálculo, e não apenas a posição da célula no intervalo. Usar a TIR em um fluxo irregular pode gerar um resultado distorcido, já que ela assume implicitamente que todos os períodos têm a mesma duração. Se você quiser se aprofundar nessa comparação com mais exemplos, veja também o nosso guia sobre XTIR versus TIR no Excel.
Outro conceito que anda lado a lado com a TIR e a XTIR é o VPL (Valor Presente Líquido), que traz todos os valores futuros de um fluxo de caixa para o valor de hoje, usando uma taxa de desconto definida por você. Enquanto a XTIR calcula qual seria essa taxa para que o VPL do fluxo seja exatamente zero, o VPL tradicional parte de uma taxa já definida (geralmente a sua TMA) e informa se o projeto vale a pena com base nela. Veja também o nosso guia completo de VPL no Excel para entender como as duas métricas se complementam na análise de um investimento.
Perguntas frequentes
Por que a função XTIR retorna o erro #NÚM!?
Geralmente porque o intervalo de valores tem só números positivos ou só negativos — a XTIR precisa de pelo menos um valor negativo (investimento) e um positivo (retorno). Também pode acontecer se a estimativa (terceiro argumento) estiver muito distante do resultado real; nesse caso, informe um valor de estimativa manual mais próximo.
A ordem das datas importa na função XTIR?
As datas não precisam estar em ordem cronológica na tabela, mas cada valor precisa estar corretamente pareado com a sua data correspondente na mesma linha. O Excel identifica a ordem cronológica sozinho a partir das datas informadas, então o que realmente importa é a correspondência correta entre valor e data.
XTIR e TIR podem dar resultados diferentes para o mesmo conjunto de dados?
Sim, e é esperado que isso aconteça sempre que os intervalos entre os pagamentos não forem exatamente iguais. A TIR assume que cada período tem a mesma duração, enquanto a XTIR calcula com base no número real de dias entre as datas — por isso a XTIR costuma ser mais precisa quando o fluxo de caixa não é perfeitamente periódico.
O que você achou do conteúdo?
Esse conteúdo te ajudou? Preparado para criar sua planilha e calcular sua Taxa Interna de Rentabilidade?
Em caso de dúvidas deixe um comentário logo abaixo, que iremos lhe responder.
Até a próxima.