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

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á un rendimiento 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 dependas de otros a la hora de invertir el dinero que tanto te ha costado ganar, te 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.

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 considera 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.

Lea también:Las mejores plantillas de presupuesto de 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í:

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.

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])

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.

Lea también:Cómo usar la fórmula de resta de Excel

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 Flujo de caja.
• Complete los datos que desea que se calculen debajo de los encabezados de las columnas respectivas.

• La primera inversión debe tener un valor negativo, ya que le está pagando a un fondo mutuo o a 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.

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

=TIR(C2:C8)

• Golpear Ingresar para obtener el ROI de una inversión potencial.

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.

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

• El Adivinar El argumento debe ser el ROI de la inversión que espera.

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 el Fecha de Devoluciones al lado derecho de la Flujo de caja columna.
• 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.

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

=XIRR(C2:C8,D2:D8)

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.

• Estos son los Costo de capital y Tasa de reinversión.

• 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)

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:

• Establezca la TIR en 11% o cualquier otra cosa manualmente.

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

=VAN(C9,C3:C8)+C2

• Ahora, haga clic en el Datos pestaña y luego seleccione la Y si el análisis en el Pronóstico grupo de comandos en el menú de cinta de Excel.
• Seleccionar Buscar objetivo del menú contextual que aparece.
• Aquí el Establecer celda debe ser el valor VPN. Entonces el Valorar debería ser 0.

• Colocar Al cambiar de celda al valor de la TIR.
• Solo necesita ingresar las referencias de celda como se muestra en la imagen.

• Ahora, haz clic DE ACUERDO 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 necesita calcular la TIR a partir del flujo de efectivo 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.

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 efectivo en orden cronológico. Por ejemplo, si está pagando $100 cada mes, organice los valores del flujo de efectivo 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.

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.

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 de Excel IF-THEN.