El tutorial muestra cómo usar XIRR en Excel para calcular la tasa interna de retorno (TIR) para flujos de efectivo con tiempos irregulares y cómo hacer su propia calculadora XIRR.
Cuando se enfrenta a una decisión de capital intensivo, es deseable calcular la tasa interna de rendimiento porque le permite comparar los rendimientos proyectados para diferentes inversiones y brinda una base cuantitativa para tomar la decisión.
En nuestro tutorial anterior, vimos cómo calcular la tasa interna de retorno 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 asume que todos los flujos de efectivo ocurren en intervalos de tiempo iguales, como mensual o anual. 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 retorno para una serie de flujos de efectivo que pueden o no ser periódicos.
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 caja. Las fechas pueden ocurrir en cualquier orden, pero la fecha de la inversión inicial debe ser la primera en la matriz.
- Adivinar (opcional): una TIR esperada proporcionada como un porcentaje o un 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 el Porcentaje se establece el formato 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 usarla en sus hojas de trabajo de manera más eficiente.
- XIRR en Excel está diseñado para calcular la tasa interna de retorno para flujos de efectivo con tiempos desiguales. Para flujos de efectivo periódicos con fechas de pago exactas desconocidas, puede utilizar la función TIR.
- El rango de valores debe contener al menos un valor positivo (ingresos) y uno negativo (pago saliente).
- Si el primer valor es un desembolso (inversión inicial), debe representarse con un número negativo. No se descuenta la inversión inicial; los pagos subsiguientes se retrotraen a la fecha del primer flujo de caja y se descuentan sobre la base de un año de 365 días.
- Todas las fechas se truncan a números enteros, lo que significa que se elimina la parte fraccionaria de una fecha que representa el tiempo.
- Las fechas deben ser fechas válidas de Excel ingresadas como referencias a celdas que contienen fechas o resultados de fórmulas como la función FECHA. Si las fechas se ingresan en formato de texto, pueden ocurrir problemas.
- XIRR en Excel siempre devuelve un TIR anualizada incluso al calcular los flujos de efectivo mensuales o semanales.
Cálculo XIRR en Excel
La función XIRR en Excel utiliza un método 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 – periodos totales
Comenzando con la conjetura si se proporciona o con el 10 % predeterminado si no, Excel pasa por 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 #NUM! se devuelve el error.
Para comprobar la validez de esta ecuación, vamos a probarla con el resultado de la fórmula XIRR. Para simplificar nuestro cálculo, usaremos la siguiente fórmula de matriz (recuerde que cualquier fórmula de matriz debe completarse presionando Ctrl + Shift + Enter):
=SUM(A2:A5/((1+$E$1)^((B2:B5-$B$2)/365)))
Dónde:
- A2:A5 son los flujos de caja
- B2:B5 son las fechas
- E1 es la tasa devuelta por XIRR
Como se muestra en la captura de pantalla a continuación, 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 básica XIRR en Excel
Suponga que invirtió $1,000 en 2017 y espera recibir alguna ganancia en los próximos 6 años. Para encontrar la tasa interna de retorno de esta inversión, utilice esta fórmula:
=XIRR(A2:A8, B2:B8)
Donde A2:A8 son 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 salida XIRR con el costo de capital promedio ponderado o la tasa crítica de rentabilidad de su empresa. 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 una tasa de retorno 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 retorno (TIR)?
Formulario completo de la función XIRR de Excel
En caso de que sepa qué tipo de rendimiento espera de esta o aquella inversión, puede utilizar su expectativa 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 conjetura devuelve un error:
=XIRR(A2:A7, B2:B7)
La tasa de retorno anticipada (-20 %) puesta en el argumento de suposición 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 Excel XIRR 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 alrededor del 145 % para los flujos de efectivo mensuales. La diferencia parece demasiado alta para ser justificada solo por el factor del valor del dinero en el tiempo:
Para encontrar un aproximado XIRR mensualpuede usar el siguiente cálculo, donde E1 es el resultado de la fórmula XIRR normal:
=(1+E1)^(1/12)-1
O puede incrustar XIRR directamente en la ecuación:
=(1+XIRR(A2:A8,B2:B8))^(1/12)-1
Como verificación adicional, usemos la función TIR en los mismos flujos de efectivo. Tenga en cuenta que la TIR también calculará una tasa aproximada porque asume 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 de la TIR:
La conclusión: si está buscando una TIR anualizada para flujos de efectivo mensuales, use 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 retorno de diferentes proyectos, puede crear una versátil calculadora XIRR para Excel. Así es cómo:
- Ingrese los flujos de efectivo y las fechas en dos columnas individuales (A y B en este ejemplo).
- Cree dos rangos dinámicos definidos, denominados Cash_flows y Dates. Técnicamente, eso se llamará fórmulas:
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)Donde Sheet1 es el nombre de su hoja de cálculo, 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.
- Proporcione los nombres dinámicos definidos que ha creado para la fórmula XIRR:
=XIRR(Cash_flows, Dates)
¡Hecho! Ahora puede agregar o eliminar tantos flujos de efectivo como desee, y su fórmula XIRR dinámica se recalculará en consecuencia:
Descargar Plantilla Excel XIRR
XIRR frente a TIR en Excel
La principal diferencia entre las funciones Excel XIRR y IRR es esta:
- TIR supone que todos los periodos de una serie de flujos de efectivo son iguales. Utilice esta función para encontrar la tasa interna de rendimiento de los flujos de caja periódicos, como mensual, trimestral o anual.
- 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 caja:
Si todos los pagos ocurren en intervalos regulareslas funciones devuelven resultados muy parecidos:
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 relacionado 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 caja en A2:A5, las fechas en B2:B5 y la tasa de descuento en E1, la siguiente fórmula XNPV le dará el valor actual neto de los flujos de caja 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 esto, usamos la función XIRR:
=XIRR(A2:A5, B2:B5)
Para verificar si la tasa producida por XIRR realmente conduce a un NPV cero, póngalo en el argumento de tasa 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 VPN exacto, elija mostrar más lugares decimales o aplique el formato científico a la celda XNPV. Eso producirá un resultado similar a este:
Si no está familiarizado con la notación científica, realice el siguiente cálculo para convertirlo a un número decimal:
1.11E-05 = 1.11*10^-5 = 0.0000111
La función XIRR de Excel no funciona
Si se ha encontrado con un problema con la función XNPV en Excel, a continuación se encuentran los puntos principales que debe verificar.
#NUM! error
Un error #NUM puede ocurrir debido a las siguientes razones:
- Los rangos de valores y fechas tienen diferentes longitudes (diferente número de columnas o filas).
- La matriz de valores no contiene al menos un valor positivo y uno negativo.
- Cualquiera de las fechas posteriores es anterior a la primera fecha.
- No se encuentra un resultado después de 100 iteraciones. En este caso, intente una conjetura diferente.
#¡VALOR! error
Un error #VALOR puede deberse a lo siguiente:
- Cualquiera de los valores proporcionados no es numérico.
- Algunas de las fechas proporcionadas no se pueden identificar como fechas de Excel válidas.
Así es como calculas XIRR en Excel. Para ver más de cerca las fórmulas discutidas en este tutorial, puede descargar nuestro libro de trabajo de muestra para Función XIRR de Excel. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!