Eu acho que você provavelmente está preso à solução que você 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 (notional ) depósito no início do mês. Infelizmente, acho que você está cometendo outro erro sutil: a TIR supõe fluxos de caixa em períodos regulares - não vejo que você tenha isso, a menos que o "0" seja necessário para manter períodos pares (semanalmente). Para calcular seu retorno se os fluxos de caixa não ocorrerem em intervalos regulares (especialmente se você estiver usando isso em períodos de tempo mais longos, como um ano ou mais), você terá que usar a função XIRR, que leva duas faixas de valor - a data do transação e fluxo de caixa.
* Atualização *
Eu sabia que tinha trabalhado em algo assim antes - só demorou um pouco para encontrá-lo! Dê uma olhada neste tópico do Excel. O take-away é que você carrega os fluxos de caixa e as datas, incluindo o valor e a data do saldo final, em dois arrays e, em seguida, avalia o XIRR desses dois arrays. A postagem com um UDF para fazer isso é sobre o terceiro na lista. O post imediatamente anterior afirma que o IRR aceitará um intervalo não contínuo - nunca testei isso, mas vale a pena tentar!