Podemos usar as fórmulas PMT, IPMT e IF do Excel para criar um cronograma de dívida. Primeiro, precisamos configurar o modelo inserindo algumas premissas de dívida. Neste exemplo, assumimos que a dívida é de $ 5.000.000, o prazo de pagamento é de 5 anos e a taxa de juros. Taxa de juros Uma taxa de juros refere-se ao valor cobrado por um credor a um tomador por qualquer forma de dívida, geralmente expressa como uma porcentagem do principal. para ser 4,5%.
1. O saldo inicial em nosso cronograma de dívida é igual ao valor do empréstimo de $ 5 milhões, então, na célula E29, inserimos = B25 para vinculá-lo à entrada da suposição. Então, podemos usar a fórmula PMT para calcular o pagamento total para o primeiro período = PMT ($ B $ 27, $ B $ 26, $ B $ 25) . A fórmula calcula o valor do pagamento usando o valor, o prazo e a taxa de juros do empréstimo declarados na seção de premissas.
2. Na célula E28, insira o período em que estamos, que é 1. Na célula E29, insira = E28 + 1 e preencha a fórmula à direita. A seguir, use a fórmula IPMT para descobrir o pagamento de juros para o primeiro período = IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25) .
3. O pagamento do principal é a diferença entre o pagamento total e o pagamento de juros, que é = E30-E31 . O saldo final é o saldo inicial mais o pagamento do principal sendo feito, que é = E29 + E32 . O saldo inicial do período 2 é o saldo final do período 1, que é = E33 .
4. Copie todas as fórmulas da célula E29 a E33 para a próxima coluna e copie tudo para a direita. Verifique se o saldo final para o período 5 = 0 para garantir que fórmulas e números corretos estão sendo usados.
5. Observe que há algumas mensagens de erro a partir do período 6 porque o saldo inicial é 0. Aqui podemos usar a função IF para limpar os erros. Na célula E30, digite = IF (E29> 0, PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) . A fórmula afirma que se o saldo inicial for menor que 0, o valor total do pagamento será mostrado como 0.
6. Na célula 31, digite = IF (E29> 0, IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25), 0) . Essa fórmula é semelhante à anterior, que afirma que se o saldo inicial for menor que 0, o pagamento de juros será mostrado como 0.
7. Copie as células E30 e E31, pressione SHIFT + seta para a direita e CTRL + R para preencher à direita. Você deve ver que todas as mensagens de erro agora são mostradas como 0.
XNPV e XIRR com funções DATE e IF
Podemos calcular o NPV e a TIR com base em datas específicas usando as funções do Excel XNPV e XIRR com as funções DATE e IF.
8. Vá para a célula E6 e digite = DATA (E5,12,31) para exibir a data. Copie à direita. Você verá o #VALUE! mensagem após 2021. Podemos corrigir isso usando a função IFERROR = IFERROR (DATE (E5,12,31), ””) .
9. Agora podemos começar a calcular o VPL e a TIR. Primeiro, precisamos inserir os valores do fluxo de caixa livre. Assumimos que os valores FCF do período 1 a 5 são -1.000, 500, 600, 700, 900. Na célula C37, inseriremos uma taxa de desconto de 15%. Na célula B37, calcule o NPV usando a fórmula XNPV = XNPV (C37, E35: I35, E6: I6) .
10. Na célula B38, calcule a TIR usando a fórmula XIRR = XIRR (E35: I35, E6: I6) .
Adicionando OFFSET ao XNPV e XIRR
Podemos mudar para as fórmulas XNPV e XIRR para fazer fórmulas mais dinâmicas usando a função OFFSET.
11. Na célula B42, altere a fórmula para = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) . A fórmula é mais dinâmica porque se o número de períodos aumentar, os períodos de fluxo de caixa livre também aumentarão. Não precisamos alterar a fórmula do VPL se o período de previsão for mais longo. Para a função IRR, mude para = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) .
12. Depois de ajustar a fórmula para o número de períodos, devemos compensar as datas. Na célula B42, altere a fórmula para = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) . Isso permite que as fórmulas NPV e IRR obtenham o número certo de fluxo de caixa livre com a mudança no número de períodos.
Resumo das principais fórmulas do cronograma de dívidas
- Fórmula PMT para calcular o valor do pagamento da dívida: = PMT (taxa de juros, número de termos, valor presente)
- Fórmula IPMT para calcular o pagamento de juros: = IPMT (taxa de juros, período, número de termos, valor presente)
- Fórmula XNPV para encontrar o valor presente líquido: = XNPV (taxa de desconto, fluxos de caixa livres, datas)
- Fórmula XIRR para encontrar a taxa interna de retorno: = XIRR (fluxos de caixa livres, datas)
- Fórmula OFFSET para calcular NPV dinâmico: = XNPV (taxa de desconto, 1º FCF: OFFSET (1º FCF, 0, # períodos - 1), 1ª data: OFFSET (1ª data, 0, # períodos - 1))
- Fórmula OFFSET para calcular IRR dinâmico: = XIRR (1º FCF: OFFSET (1º FCF, 0, # períodos - 1), 1ª data: OFFSET (1º data, 0, # períodos - 1))
Outros recursos
Obrigado por ler o guia de Finanças sobre Cronograma de Dívida com as fórmulas PMT, IPMT e IF. Para continuar aprendendo e progredindo em sua carreira, os seguintes recursos financeiros serão úteis:
- Fórmulas básicas do Excel Fórmulas básicas do Excel Dominar as fórmulas básicas do Excel é fundamental para que os iniciantes se tornem proficientes em análise financeira. O Microsoft Excel é considerado o software padrão da indústria para análise de dados. O programa de planilhas da Microsoft também é um dos softwares preferidos dos banqueiros de investimento
- Práticas recomendadas de modelagem financeira Práticas recomendadas de modelagem financeira Este artigo fornece aos leitores informações sobre as práticas recomendadas de modelagem financeira e um guia passo a passo fácil de seguir para construir um modelo financeiro.
- Lista de funções de funções do Excel Lista das funções mais importantes do Excel para analistas financeiros. Esta folha de dicas cobre centenas de funções essenciais para se conhecer como analista do Excel
- Visão geral dos atalhos do Excel Visão geral dos atalhos do Excel Os atalhos do Excel são um método esquecido de aumentar a produtividade e a velocidade no Excel. Os atalhos do Excel oferecem ao analista financeiro uma ferramenta poderosa. Esses atalhos podem executar muitas funções. tão simples quanto navegar na planilha para preencher fórmulas ou agrupar dados.
