Crear un rango de Excel a partir de celdas separadas

Question

Tengo una hoja de cálculo con mis cuentas de inversión, que muestra para cada mes los depósitos o retiros que se realizaron ese mes y el saldo de fin de mes.

Quiero utilizar la función TIR para calcular mi tasa de rendimiento. Puedo darle la columna de depósitos y retiros, y calculará el rendimiento suponiendo un saldo cero al final. Por ejemplo este:

Deposit 100
Withdraw 10

Mostrará una tasa de rendimiento del -90%. Sin embargo, si todavía tengo $95 en la cuenta, la tasa de rendimiento real es +5%.

Mi mesa se parece a esto:

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

Puedo agregar una columna a la derecha que es "Tasa de retorno", con una fórmula de"=IRR($A$2:A3)"

Sin embargo, nuevamente, esto me da datos inexactos porque la función TIR supone que el saldo final es cero y solo acepta un rango único. ¿Hay alguna manera de hacer algo como esto?

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

... para que vea un 'retiro' final igual a mi saldo actual?

Answer 1

Creo que probablemente esté atascado con la solución que identificó: mostrar el saldo final como un retiro (nocional) al final del mes (o período) y un depósito del saldo final del mes anterior como un depósito (nocional) en el comienzo del mes. Desafortunadamente, creo que está cometiendo otro error sutil: la TIR supone flujos de efectivo en períodos de tiempo pares; no veo que tenga eso, a menos que los totales "0" mantengan períodos pares (¿semanalmente?). Para calcular su rendimiento si los flujos de efectivo no ocurren a intervalos regulares (especialmente si está usando esto durante períodos de tiempo más largos, como un año o más), tendrá que usar la función XIRR, que toma dos rangos de valores: la fecha del transacción y el flujo de caja.

* Actualizar *

SABÍA que había trabajado en algo como esto antes, ¡solo que me tomó un tiempo encontrarlo! Echa un vistazo a estohilodel Sr. Excel. La conclusión es que se cargan los flujos de efectivo y las fechas, incluido el valor del saldo final y la fecha, en dos matrices, y luego se evalúa la XIRR de esas dos matrices. La publicación con una UDF para hacer eso es aproximadamente la tercera en la lista. La publicación inmediatamente anterior afirma que la TIR aceptará un rango no continuo; nunca lo he probado, ¡pero vale la pena intentarlo!