Cómo calcular la TIR en Excel: 4 mejores métodos en 2023

Si desea tomar decisiones informadas hacia planes de inversión sistemáticos (SIP) o invertir en proyectos comerciales como capitalista de riesgo, debe aprender a calcular la TIR en Excel.

Hay muchas formas de calcular el retorno de la inversión (ROI) de las inversiones realizadas en proyectos financieros o empresariales. Si bien un cálculo basado en una tasa fija es bastante fácil, no siempre es la herramienta elegida para el cálculo del ROI en grandes empresas o banca de inversión.

Digamos que invertirá una suma de dinero en un plan de inversión para los próximos 20 años. El plan dice que obtendrá una devolución cada trimestre. Ahora, si es inteligente invertir en un plan de este tipo o no, se puede decidir a partir del valor de la TIR del SIP. Cuanto mayor sea la TIR más lucrativo el plan de inversión. Lo mismo es cierto para todas las demás inversiones intensivas en capital en negocios, cuentas de ahorro bancarias, fondos mutuos, 401 (k), etc.

Para que no confíe en otros cuando se trata de invertir el dinero ganado con tanto esfuerzo, lo ayudo a aprender qué es la TIR en Excel, sus varias fórmulas modificadas y cómo calcular la TIR en Excel de varias maneras sin esfuerzo a continuación.

¿Qué es la Tasa Interna de Retorno (TIR)?

La TIR o tasa interna de retorno es una métrica común utilizada por los planificadores financieros para calcular o predecir la rentabilidad futura de un proyecto comercial potencial u otro tipo de inversiones. En términos financieros, se conoce popularmente como tasa de rendimiento económico o tasa de rendimiento de flujo de efectivo descontado.

La tasa TIR o tasa de descuento de inversión hace que todo el valor presente neto de todo tipo de flujos de efectivo, como salidas y entradas, sea igual a cero. Los profesionales financieros llaman a esta tasa interna de retorno porque este cálculo no tiene en cuenta otros factores que podrían tener un impacto positivo o negativo en el negocio en los próximos años.

Por ejemplo, la TIR no tiene en cuenta el coste del capital, la tasa de inflación a lo largo del tiempo, los riesgos financieros, etc., a la hora de predecir la rentabilidad de un proyecto empresarial o una inversión financiera.

En pocas palabras, cada vez que decida invertir por un período prolongado en cualquier proyecto comercial, fondo mutuo o plan de inversión para el retiro, averigüe la TIR de esas opciones de inversión. Luego elige el que te dé la TIR máxima entre todas las opciones.

¿Cuáles son las funciones de la TIR en Microsoft Excel?

Gracias a Microsoft, no necesita crear varias fórmulas ni realizar cálculos complejos para obtener la TIR de un plan de inversión. Excel tiene tres funciones dedicadas de cálculo de la tasa interna de retorno. Conoce estas funciones aquí:

1. TIR para Tasa Interna de Retorno Simple

La sintaxis de la TIR en Excel calcula la tasa de interés de una inversión en un proyecto empresarial o fondo mutuo o fondo de planificación de jubilación. La inversión debe incluir al menos un valor positivo y uno negativo. Además, el flujo de efectivo hacia el fondo debe ocurrir a intervalos regulares. Así es como se ve la fórmula:

= TIR (valores o rangos de celdas, [argumento de estimación])

• Valores es una entrada necesaria para la función TIR. Podría ser una serie de números, una matriz de números o un rango de celdas de referencia que consisten en flujos de efectivo.
• El argumento Guess es opcional. Si no proporciona un valor de Guess, entonces Excel lo considera una tasa interna de rendimiento de 0,1, 10 % o 10 puntos básicos requerida por el plan de inversión. Si proporciona un argumento Guess, Excel ejecutará los cálculos durante hasta 20 sesiones iterativas para encontrar un valor de TIR cercano al valor Guess.

2. XIRR para tiempos desiguales de devoluciones

Si necesita tener en cuenta el cronograma de flujos de efectivo, debe usar la función XIRR. La sintaxis es la siguiente:

= XIRR (valores o rangos de celdas, fechas, [argumento de estimación])

3. MIRR para reinversión de fondos de proyectos

MIRR proporciona valores IRR altamente confiables en comparación con la función IRR simple. La TIR no considera el costo del capital que invierte. Por lo tanto, el valor final de la TIR no es exacto. En su lugar, puede usar la función MIRR para incluir el costo de un factor de capital en el cálculo de la TIR. Por lo tanto, la sintaxis queda como se muestra a continuación:

=MIRR(valores o rangos de celdas, tasa_financiación, tasa_reinversión)

• Valores es el estado de flujo de efectivo que consta de al menos uno de cada uno de los flujos de efectivo positivos y negativos.
• Finance_rate es el interés que paga al prestamista por el dinero prestado para un proyecto empresarial.
• Reinvest_rate es la tasa de interés que recibe su negocio o fondo de inversión sobre los flujos de efectivo al reinvertir.

Cómo calcular la TIR en Excel: usando la sintaxis de la TIR

Antes de que pueda comenzar a calcular la TIR para varios planes o proyectos de inversión, debe organizar los datos en Excel. Así es como se hace:

• Abra una hoja de cálculo de Excel y cree tres encabezados de columna: Período , Descripción y Flujos de efectivo .
• Complete los datos que desea que se calculen debajo de los encabezados de las columnas respectivas.

Usando la fórmula de la TIR en Excel

• La primera inversión debe tener un valor negativo, ya que le está pagando a un fondo mutuo oa un empresario para que utilice el dinero.
• Si obtendrá ingresos regulares después de un mes o un año, entonces esos ingresos deben agregarse como valores positivos.

Aprenda a calcular la TIR en Excel usando la sintaxis de la TIR

• Ahora, use la siguiente fórmula en una celda donde desea que Excel calcule el valor de la TIR:

=TIR(C2:C8)

• Presiona Enter para obtener el ROI de una posible inversión.

Este cálculo de la TIR se basa en un argumento Guess predeterminado del 10 %. Digamos que está buscando un retorno del 12%. Así es como debe ingresar el valor de estimación:

=TIR(C2:C8,30%)

• En la tabla anterior, puede ver que el último flujo de caja es negativo. Por tanto, el flujo de efectivo debe cambiar de positivo a negativo o de negativo a positivo.

Vea cómo calcular la TIR en Excel usando el valor estimado de la TIR

• En este escenario, utilice Guess para encontrar el resultado exacto ya que la TIR producirá dos resultados.

Cómo calcular la TIR en Excel cuando se usa un valor Guess negativo

• El argumento Guess debería ser el ROI de inversión que espera.

Cómo calcular la TIR en Excel: usando la sintaxis XIRR

Cuando obtiene rendimientos en diferentes fechas después de la inversión inicial, el uso de la sintaxis de la TIR dará resultados defectuosos. En este escenario, debe utilizar la sintaxis XIRR. Estos son los pasos que puede probar en su propia hoja de cálculo de Excel:

• Cree un encabezado de columna para la Fecha de devoluciones en el lado derecho de la columna Flujos de caja .
• Ahora, ingrese la fecha de inversión en la parte superior y luego ingrese la fecha esperada de interés o créditos de devolución.

Descubra cómo calcular la TIR en Excel usando la sintaxis XIRR

• Una vez hecho esto, use la siguiente sintaxis XIRR en cualquier celda para obtener la tasa interna de retorno de la inversión con un programa de pago de retorno irregular:

=XIRR(C2:C8,D2:D8)

Cómo calcular la TIR en Excel: usando la sintaxis MIRR

Para que el valor de la TIR sea más realista, puede utilizar la fórmula MIRR. Aquí, puede incluir dos factores obligatorios: costo de capital y tasa de reinversión. Encuentre a continuación los pasos para usar MIRR en Excel:

• En la hoja de trabajo actual, agregue dos filas de información como se muestra en la imagen.

Usando la función MIRR en Excel

• Estos son el Costo de capital y la tasa de Reinversión .

Aprenda a calcular la TIR en Excel usando la sintaxis MIRR

• En sus respectivas celdas, ingrese los valores también.
• Ahora, use la siguiente fórmula para obtener una tasa interna de retorno realista:

=ESPEJO(C2:C8,G2,G3)

Cómo calcular la TIR en Excel: usando Goal Seek Excel

La TIR y sus fórmulas modificadas realizan 20 cálculos iterativos antes de mostrar un resultado. Si necesita una precisión mayor que esta, puede usar la función de búsqueda de objetivos de Excel para calcular la TIR. En este método, Excel realiza hasta 32 000 cálculos iterativos antes de generar un valor de retorno. Aquí están las instrucciones para usar Goal Seek y calcular la TIR en Excel:

• Configure la TIR al 11% o cualquier otra cosa manualmente.

Cálculo del VAN en Excel

• Calcule el VAN por debajo de la TIR usando esta fórmula:

=VAN(C9,C3:C8)+C2

• Ahora, haga clic en la pestaña Datos y luego seleccione Análisis hipotético en el grupo de comandos Pronóstico en el menú de cinta de Excel.
• Seleccione Goal Seek en el menú contextual que aparece.
• Aquí, la celda Establecer debe ser el valor NPV. Entonces, el valor Hasta debe ser 0.

Realice Goal Seek para calcular la TIR en Excel

• Establezca Cambiando de celda al valor de la TIR.
• Solo necesita ingresar las referencias de celda como se muestra en la imagen.

Resultados de la búsqueda de objetivos para el cálculo de la TIR en Excel

• Ahora, haga clic en Aceptar para calcular la TIR a través de Goal Seek.
• Según el conjunto de datos actual, el VAN será 0,00 si la TIR es del 12 %.

En el método Goal Seek, en realidad no es necesario calcular la TIR a partir del flujo de caja y el monto de la inversión. En su lugar, establece una TIR esperada en la celda de la TIR y la usa para calcular el VAN utilizando el flujo de efectivo y el monto de la inversión. Luego, utiliza la capacidad de cálculo iterativo de Excel para predecir cuándo el NPV se convierte en 0,00 y en qué valor de la TIR.

Consejos para calcular la TIR en Excel correctamente

Recuerda estos consejos y datos cuando vayas a calcular la TIR en Excel para obtener porcentajes de TIR fiables:

• El argumento de la parte Valores debe contener un ingreso (flujo de caja positivo) y un desembolso (flujo de caja negativo) para que la función funcione con precisión.
• La función TIR solo procesará números en el argumento Valores. Ignorará cualquier otro elemento como valores lógicos, celdas vacías y textos.
• Debe haber intervalos regulares de flujo de efectivo como semanal, mensual, trimestral, anual, etc. No es obligatorio que los flujos de efectivo sean iguales en valor en dólares.
• Asegúrese de preparar la tabla de flujo de caja en orden cronológico. Por ejemplo, si está pagando $100 cada mes, organice los valores del flujo de caja y sus fechas de enero, febrero, marzo, etc.
• En cálculos simples de TIR, no es obligatorio ingresar un argumento Guess.
• Al usar un valor de Guess, la fórmula de la TIR mostrará un valor más cercano al argumento de Guess. Si muestra #NUM! Error, cambie el valor del argumento Guess.

¿Cuáles son las limitaciones de la TIR en Excel?

Antes de que pueda aplicar el valor de la TIR calculado en Excel en situaciones del mundo real, también debe conocer las limitaciones inherentes de la fórmula de la tasa interna de rendimiento en Excel:

• TIR es una expresión del ROI en porcentaje. No considera el valor absoluto. Por lo tanto, puede mostrar un mayor porcentaje de rendimiento para un proyecto de bajo valor en dólares. Sin embargo, los capitalistas de riesgo y los inversionistas individuales buscan proyectos de mayor valor en dólares, aunque el porcentaje de ROI es menor que los proyectos pequeños.
• La fórmula de la TIR devuelve un valor considerando que el proyecto reinvertirá su capital o flujo de efectivo a la misma tasa que la TIR calculada. Esto no siempre es posible porque las tasas de retorno fluctúan mucho en el mercado de capitales.
• Si el plan o proyecto de inversión implica la alternancia de flujos de efectivo positivos y negativos, es posible que obtenga más de una TIR para el mismo proyecto.

Conclusión

Ahora sabe cómo calcular la TIR en Excel usando varias sintaxis de TIR dedicadas. Además, aprendió a usar Excel Goal Seek para calcular la TIR en Excel. Si me perdí algún otro método, no olvide mencionarlo en el cuadro de comentarios a continuación.

A continuación,  cómo usar la fórmula IF-THEN de Excel .