Criando um intervalo do Excel a partir de células disjuntas

Question

Tenho uma planilha com minhas contas de investimento, mostrando para cada mês os depósitos ou saques ocorridos naquele mês e o saldo do final do mês.

Quero usar a função TIR para calcular minha taxa de retorno. Posso fornecer a coluna de depósitos e saques e ele calculará o retorno assumindo um saldo zero no final. Por exemplo isto:

Deposit 100
Withdraw 10

Mostrará uma taxa de retorno de -90%. No entanto, se eu ainda tiver US$ 95 na conta, a taxa real de retorno é de +5%.

Minha mesa é mais ou menos assim:

Deposits  Balance
100       100
-10       95
0         103
0         98
100       215

Posso adicionar uma coluna à direita que é "Taxa de Retorno", com uma fórmula de"=IRR($A$2:A3)"

No entanto, novamente, isso me dá dados imprecisos porque a função TIR assume que o saldo final é zero e aceita apenas um único intervalo. Existe uma maneira de fazer algo assim:

=IRR(ConcatenateRange($A$2:A3, B3))

... para que veja um 'retirada' final igual ao meu saldo atual?

Answer 1

Acho que você provavelmente está preso à solução alternativa que identificou - mostrar o saldo final como uma retirada (nocional) no final do mês (ou período) - e um depósito do saldo final do mês anterior como um depósito (nocional) em o início do mês. Infelizmente, acho que você está cometendo outro erro sutil: a TIR assume fluxos de caixa em períodos pares - não vejo que você tenha isso, a menos que os inteiros "0" sejam para manter períodos pares (semanalmente?). Para calcular o seu retorno se os fluxos de caixa não ocorrerem em intervalos regulares (especialmente se você estiver usando isso durante períodos mais longos, como um ano ou mais), você terá que usar a função XIRR, que assume dois intervalos de valores - a data do transação e o fluxo de caixa.

* Atualizar *

EU SABIA que já tinha trabalhado em algo assim antes - só demorou um pouco para encontrá-lo! Veja issofiodo Sr. Excel. A conclusão é que você carrega os fluxos de caixa e as datas, incluindo o valor e a data do saldo final, em duas matrizes e, em seguida, avalia o XIRR dessas duas matrizes. A postagem com uma UDF para fazer isso é a terceira da lista. A postagem imediatamente anterior afirma que a TIR aceitará um intervalo não contínuo - nunca testei isso, mas vale a pena tentar!