¿Alguna vez te has enfrentado a la complejidad de gestionar flujos de efectivo irregulares y has deseado contar con una herramienta que te simplifique el proceso? La función XIRR de Excel llega al rescate, convirtiéndose en tu aliada perfecta para calcular la Tasa Interna de Retorno (TIR) en situaciones donde los flujos de efectivo no ocurren en intervalos regulares. En este artículo, desglosaremos cómo utilizar esta poderosa función para transformar datos dispersos en decisiones financieras informadas y precisas. ¡Descubre cómo dominar la XIRR y optimiza la gestión de tus inversiones!
Excel es una herramienta poderosa que ofrece una amplia gama de funciones para simplificar nuestras tareas diarias. Una de esas funciones es la función XIRR, una herramienta que nos permite calcular la tasa interna de retorno (TIR) para flujos de efectivo no periódicos. Si alguna vez te has enfrentado a la tarea de calcular la rentabilidad de una inversión con pagos irregulares, entonces la función XIRR de Excel es tu solución ideal. En este artículo, exploraremos en detalle cómo utilizar esta fantástica función y cómo puede facilitar nuestra vida financiera. ¡Prepárate para descubrir cómo XIRR puede llevar tus análisis financieros al siguiente nivel!
El tutorial muestra cómo usar XIRR en Excel para calcular la tasa interna de rendimiento (TIR) para flujos de efectivo con tiempos irregulares y cómo crear su propia calculadora XIRR.
Cuando se enfrenta a una decisión que requiere mucho capital, es conveniente calcular la tasa interna de rendimiento porque permite comparar los rendimientos proyectados para diferentes inversiones y proporciona una base cuantitativa para tomar la decisión.
En nuestro tutorial anterior, vimos cómo calcular la tasa interna de rendimiento con la función TIR de Excel. Ese método es rápido y sencillo, pero tiene una limitación esencial: la función TIR supone que todos los flujos de efectivo ocurren en intervalos de tiempo iguales, como mensual o anualmente. Sin embargo, en situaciones de la vida real, las entradas y salidas de efectivo suelen ocurrir a intervalos irregulares. Afortunadamente, Microsoft Excel tiene otra función para encontrar la TIR en tales casos y este tutorial le enseñará cómo usarla.
Función XIRR en Excel
La función XIRR de Excel devuelve la tasa interna de rendimiento de una serie de flujos de efectivo que pueden ser periódicos o no.
La función se introdujo en Excel 2007 y está disponible en todas las versiones posteriores de Excel 2010, Excel 2013, Excel 2016, Excel 2019 y Excel para Office 365.
La sintaxis de la función XIRR es la siguiente:
XIRR(valores, fechas, [guess])
Dónde:
- Valores (obligatorio): una matriz o un rango de celdas que representan una serie de entradas y salidas.
- fechas (obligatorio) – fechas correspondientes a los flujos de efectivo. Las fechas pueden aparecer en cualquier orden, pero la fecha de la inversión inicial debe ser la primera en la serie.
- Adivinar (opcional): una TIR esperada proporcionada como porcentaje o número decimal. Si se omite, Excel utiliza la tasa predeterminada de 0,1 (10%).
Por ejemplo, para calcular la TIR para la serie de flujos de efectivo en A2:A5 y las fechas en B2:B5, usaría esta fórmula:
=XIRR(A2:A5, B2:B5)
>
Consejo. Para que el resultado se muestre correctamente, asegúrese de que Porcentaje El formato está configurado para la celda de fórmula.
6 cosas que debes saber sobre la función XIRR
Las siguientes notas lo ayudarán a comprender mejor la mecánica interna de la función XIRR y a utilizarla en sus hojas de trabajo de manera más eficiente.
Cálculo XIRR en Excel
La función XIRR en Excel utiliza un enfoque de prueba y error para encontrar la tasa que satisface esta ecuación:
>Dónde:
- P – flujo de caja (pago)
- d – fecha
- i – número de período
- n – períodos totales
Comenzando con la suposición, si se proporciona, o con el 10% predeterminado en caso contrario, Excel realiza iteraciones para llegar al resultado con una precisión del 0,000001%. Si después de 100 intentos no se encuentra una tasa precisa, el mensaje #NUM! se devuelve el error.
Para comprobar la validez de esta ecuación, probémosla con el resultado de la fórmula XIRR. Para simplificar nuestro cálculo, usaremos la siguiente fórmula matricial (recuerde que cualquier fórmula matricial debe completarse presionando Ctrl + Mayús + Intro):
=SUM(A2:A5/((1+$E$1)^((B2:B5-$B$2)/365)))
Dónde:
- A2:A5 son los flujos de efectivo
- B2:B5 son las fechas
- E1 es la tasa devuelta por XIRR
Como se muestra en la captura de pantalla siguiente, el resultado es muy cercano a cero. QED 🙂
>Cómo calcular XIRR en Excel – ejemplos de fórmulas
A continuación se muestran algunos ejemplos que demuestran los usos comunes de la función XIRR en Excel.
Fórmula XIRR básica en Excel
Suponga que invirtió $1000 en 2017 y espera recibir algunas ganancias en los próximos 6 años. Para encontrar la tasa interna de rendimiento de esta inversión, utilice esta fórmula:
=XIRR(A2:A8, B2:B8)
Donde A2:A8 son los flujos de efectivo y B2:B8 son las fechas correspondientes a los flujos de efectivo:
>Para juzgar la rentabilidad de esta inversión, compare la producción XIRR con la de su empresa. costo de capital promedio ponderado o tasa de rentabilidad. Si la tasa de retorno es mayor que el costo de capital, el proyecto puede considerarse una buena inversión.
Al comparar varias opciones de inversión, recuerde que la tasa de rendimiento proyectada es solo uno de los factores que debe estimar antes de tomar una decisión. Para obtener más información, consulte ¿Qué es la tasa interna de rendimiento (TIR)?
Forma completa de la función XIRR de Excel
En caso de que sepa qué tipo de rendimiento espera de tal o cual inversión, puede utilizar sus expectativas como una suposición. ¡Es especialmente útil cuando una fórmula XIRR obviamente correcta arroja un #NUM! error.
Para la entrada de datos que se muestra a continuación, una fórmula XIRR sin la estimación devuelve un error:
=XIRR(A2:A7, B2:B7)
La tasa de retorno anticipada (-20%) puesta en el adivinar El argumento ayuda a Excel a llegar al resultado:
=XIRR(A2:A7, B2:B7, -20%)
>Cómo calcular XIRR para flujos de efectivo mensuales
Para empezar, recuerde esto: independientemente de los flujos de efectivo que esté calculando, la función XIRR de Excel produce un tasa de rendimiento anual.
Para asegurarnos de esto, encontremos la TIR para la misma serie de flujos de efectivo (A2:A8) que ocurren mensual y anualmente (las fechas están en B2:B8):
=XIRR(A2:A8, B2:B8)
Como puede ver en la siguiente captura de pantalla, la TIR va del 7,68% en el caso de los flujos de efectivo anuales a aproximadamente el 145% para los flujos de efectivo mensuales. La diferencia parece demasiado grande para justificarla únicamente por el factor del valor del dinero en el tiempo:
>Para encontrar un aproximado TIR mensualpuede utilizar el siguiente cálculo, donde E1 es el resultado de la fórmula XIRR normal:
=(1+E1)^(1/12)-1
O puedes insertar XIRR directamente en la ecuación:
=(1+XIRR(A2:A8,B2:B8))^(1/12)-1
Como comprobación adicional, utilicemos la función TIR en los mismos flujos de efectivo. Tenga en cuenta que la TIR también calculará una tasa aproximada porque supone que todos los períodos de tiempo son iguales:
=IRR(A2:A8)
Como resultado de estos cálculos, obtenemos una XIRR mensual de 7,77%, que está muy cerca del 7,68% que produce la fórmula TIR:
>La conclusión: si busca una TIR anualizada para los flujos de efectivo mensuales, utilice la función XIRR en su forma pura; Para obtener una TIR mensual, aplique el ajuste descrito anteriormente.
Plantilla XIRR de Excel
Para obtener rápidamente la tasa interna de rendimiento de diferentes proyectos, puede crear una calculadora XIRR versátil para Excel. Así es cómo:
Flujo de caja:
=OFFSET(Sheet1!$A$2,0,0,COUNT(Sheet1!$A:$A),1)
Fechas:
=OFFSET(Sheet1!$B$2,0,0,COUNT(Sheet1!$B:$B),1)
Dónde Hoja1 es el nombre de su hoja de trabajo, A2 es el primer flujo de efectivo y B2 es la primera fecha.
Para obtener instrucciones detalladas paso a paso, consulte Cómo crear un rango dinámico con nombre en Excel.
=XIRR(Cash_flows, Dates)
¡Hecho! Ahora puede agregar o eliminar tantos flujos de efectivo como desee y su fórmula XIRR dinámica se volverá a calcular en consecuencia:
>XIRR frente a TIR en Excel
La principal diferencia entre las funciones XIRR y TIR de Excel es la siguiente:
- TIR Supone que todos los períodos de una serie de flujos de efectivo son iguales. Utilice esta función para encontrar la tasa interna de rendimiento de flujos de efectivo periódicos, como mensuales, trimestrales o anuales.
- XIRR le permite asignar una fecha a cada flujo de caja individual. Entonces, use esta función para calcular la TIR para flujos de efectivo que no son necesariamente periódicos.
Generalmente, si conoce las fechas exactas de los pagos, es recomendable utilizar XIRR porque proporciona una mayor precisión de cálculo.
Como ejemplo, comparemos los resultados de TIR y XIRR para los mismos flujos de efectivo:
Si todos los pagos ocurren en intervalos regulareslas funciones devuelven resultados muy cercanos:
>Si el momento de los flujos de efectivo es desigualla diferencia entre los resultados es bastante significativa:
>XIRR y XNPV en Excel
XIRR está estrechamente relacionada con la función XNPV porque el resultado de XIRR es la tasa de descuento que conduce a un valor presente neto cero. En otras palabras, XIRR es XNPV = 0. El siguiente ejemplo demuestra la relación entre XIRR y XNPV en Excel.
Supongamos que está considerando alguna oportunidad de inversión y desea examinar tanto el valor actual neto como la tasa interna de rendimiento de esta inversión.
Con los flujos de efectivo en A2:A5, las fechas en B2:B5 y la tasa de descuento en E1, la siguiente fórmula XNPV le dará el valor presente neto de los flujos de efectivo futuros:
=XNPV(E1, A2:A5, B2:B5)
Un VPN positivo indica que el proyecto es rentable:
>Ahora, encontremos qué tasa de descuento hará que el valor presente neto sea cero. Para ello utilizamos la función XIRR:
=XIRR(A2:A5, B2:B5)
Para comprobar si la tasa producida por XIRR realmente conduce a un VAN cero, colóquelo en el tasa argumento de su fórmula XNPV:
=XNPV(E4, A2:A5, B2:B5)
O incruste toda la función XIRR:
=XNPV(XIRR(A2:A5, B2:B5), A2:A5, B2:B5)
Sí, el XNPV redondeado a 2 decimales es igual a cero:
>Para mostrar el valor NPV exacto, elija mostrar más decimales o aplique el formato científico a la celda XNPV. Eso producirá un resultado similar a este:
>Si no estás familiarizado con la notación científica, realiza el siguiente cálculo para convertirla a un número decimal:
1.11E-05 = 1.11*10^-5 = 0.0000111
La función XIRR de Excel no funciona
Si ha tenido algún problema con la función XNPV en Excel, a continuación se detallan los puntos principales que debe verificar.
#¡NÚMERO! error
Puede producirse un error #NUM por los siguientes motivos:
- El valores y fechas los rangos tienen diferentes longitudes (diferente número de columnas o filas).
- El valores La matriz no contiene al menos un valor positivo y uno negativo.
- Cualquiera de las fechas posteriores es anterior a la primera fecha.
- No se encuentra ningún resultado después de 100 iteraciones. En este caso, pruebe con una suposición diferente.
Para obtener más detalles, consulte Cómo corregir el error #NUM en Excel.
#¡VALOR! error
Un error #VALOR puede deberse a lo siguiente:
- Cualquiera de los suministrados valores son no numéricos.
- Algunas de las fechas proporcionadas no pueden identificarse como fechas válidas de Excel.
Así es como se calcula XIRR en Excel. Para ver más de cerca las fórmulas analizadas en este tutorial, puede descargar nuestro libro de trabajo de muestra a continuación. ¡Te agradezco por leer y espero verte en nuestro blog la próxima semana!
Libro de práctica para descargar.
Plantilla XIRR Excel (archivo .xlsx)
Gegomooftoq: ¡Muy buen artículo! La función XIRR me salvó la vida cuando estaba tratando de analizar unos flujos de efectivo para un proyecto personal. Al principio pensé que sería un lío, pero con esta herramienta fue pan comido. ¡Gracias por compartir!
Kiezelwegsj: ¡Exacto! La función XIRR es súper útil. La usé una vez cuando tenía que hacer la contabilidad de un pequeño negocio que tenía y me ayudó a tener todo claro sin volverte loco con los números. Al principio no sabía ni por dónde empezar, pero gracias a esta función, pude determinar si estaba en números rojos o negros. ¡Buenísimo el artículo, muchas gracias!
Djdrifttp: ¡Completamente de acuerdo! La función XIRR es lo máximo, a mí también me ayudó un montón cuando estaba organizando mis inversiones. Pensaba que no iba a poder calcularlo bien, pero con esta función todo se hizo mucho más fácil. ¡Gran artículo, gracias por compartir!
XicletR: ¡Totalmente de acuerdo! La función XIRR es increíble, yo la usé cuando estaba planificando unas vacaciones y quería asegurarme de que podía cubrir todos los gastos. Nunca pensé que calcular el rendimiento de mis ahorros sería tan sencillo. ¡Gran artículo, gracias por la info!