Excel fue creado originalmente paraFacilitación de asentamientos en muchas áreas, incluidas las empresas. Usando sus capacidades, puede realizar rápidamente cálculos complejos, incluso para pronosticar la rentabilidad de ciertos proyectos. Por ejemplo, Excel le permite calcular fácilmente la TIR de un proyecto. Cómo hacer esto en la práctica, este artículo lo dirá.
Esta abreviatura se refiere a la norma interna.rentabilidad (INB) de un proyecto de inversión específico. Este indicador se usa a menudo para comparar propuestas para la perspectiva de rentabilidad y crecimiento empresarial. En términos numéricos, la TIR es la tasa de interés a la que se cancela el valor presente de todos los flujos de efectivo necesarios para la implementación del proyecto de inversión (denotado por VPN o VPN). Cuanto mayor sea el INB, más prometedor es el proyecto de inversión.
Habiendo descubierto el INB del proyecto, puede decidir sobre sulanzarlo o abandonarlo. Por ejemplo, si tiene la intención de abrir un nuevo negocio y se planea financiarlo con un préstamo tomado de un banco, el cálculo de la TIR le permite determinar el límite superior aceptable de la tasa de interés. Si la empresa utiliza más de una fuente de inversión, la comparación del valor de la TIR con su valor permitirá tomar una decisión informada sobre la factibilidad de lanzar un proyecto. El costo de más de una fuente de financiamiento se calcula de acuerdo con la denominada fórmula de la media aritmética ponderada. Recibió el nombre "Costo de capital" o "Precio de capital avanzado" (denotado por SS).
Usando este indicador, tenemos:
Si:
Mucho antes del advenimiento de las computadoras, el INB se calculó resolviendo una ecuación bastante complicada (ver más abajo).
Incluye los siguientes valores:
Sin programas especiales calcule la TIR del proyectoutilizando el método de aproximación o iteraciones sucesivas. Para hacer esto, primero debe seleccionar las tasas de barrera de tal manera que encuentre los valores mínimos del módulo de VPN y realice la aproximación.
En primer lugar, debe cambiar al idioma de las funciones. En este contexto, la TIR se entenderá como un valor de rentabilidad r en el que el VPN, en función de r, es igual a cero.
En otras palabras, IRR = r tal que cuando se sustituye en la expresión NPV (f (r)), se pone a cero.
Ahora resolvemos el problema formulado por el método de aproximaciones sucesivas.
La iteración se entiende comúnmente como el resultado.uso repetido de una operación matemática particular. En este caso, el valor de la función, calculado en el paso anterior, durante el siguiente se convierte en su propio argumento.
El cálculo del indicador IRR se realiza en 2 etapas:
Al resolver el problema, se eligen r1 y r2 para que NPV = f (r) dentro del intervalo (r1, r2) cambie su valor de menos a más o viceversa.
Por lo tanto, tenemos la fórmula para calcular el indicador IRR en la forma de la expresión a continuación.
De esto se deduce que para obtener el valor IRR, primero se debe calcular el VPN para diferentes valores de la tasa de porcentaje.
Entre los indicadores NPV, PI y SS existe la siguiente relación:
Ahora que sabes qué es la TIR y cómoPara calcularlo manualmente, vale la pena familiarizarse con otro método para resolver este problema, que fue uno de los más populares antes de que aparecieran las computadoras. Esta es una versión gráfica de la definición de TIR. Para trazar, es necesario encontrar el valor de VPN sustituyendo varios valores de la tasa de descuento en la fórmula para calcularlo.
Como puede ver, encontrar manualmente el GNI es bastante difícil. Esto requiere algo de conocimiento matemático y tiempo. Es mucho más fácil aprender a calcular la TIR en Excel (ver el ejemplo a continuación).
Para este propósito, el famoso procesador de tablas de Microsoft tiene una función incorporada especial para calcular la tasa de descuento interna: la TIR, que proporciona el valor de TIR deseado en términos porcentuales.
La TIR (qué es y cómo calcularla es necesario conocer no solo especialistas, sino también prestatarios comunes) en Excel se designa como VVD (valores; suposición).
Consideremos su sintaxis con más detalle:
En Microsoft Excel, para calcular la TIR, utilizaEl método de iteración anterior. Comienza con el valor "Suposición" y realiza cálculos cíclicos, hasta que se obtiene el resultado con una precisión del 0,00001%. Si la función VSD incorporada no produce un resultado después de hacer 20 intentos, el procesador de la tabla genera un valor de error, designado como "# ¡NÚMERO!".
Como muestra la práctica, en la mayoría de los casos no es necesario establecer un valor para el valor de "Asunción". Si se omite, entonces el procesador lo considera igual a 0.1 (10%).
Si la función VSD incorporada devuelve un error"# ¡NÚMERO!" O si el resultado no cumple con las expectativas, puede volver a realizar los cálculos, pero con un valor diferente para el argumento "Asunción".
Tratemos de calcular la TIR (qué es y cómoya conoce este valor manualmente) utilizando la función IRR incorporada. Supongamos que tenemos datos con 9 años de anticipación que se enumeran en una hoja de cálculo de Excel.
Un | En el | C | D | E | |
1 | Período (año) T | Costo inicial | Ingresos en efectivo | Gastos en efectivo | Flujo de caja |
2 | 0 | 200 000 p. | - p. | 200000 p. | 200000 p. |
3 | 1 | - p. | 50,000 p. | 30000 p. | 20,000 p. |
4 | 2 | - p. | 60,000 p. | 33000 p. | 27000 p. |
5 | 3 | - p. | 45000 p. | 28000 p. | 17000 p. |
6 | 4 | - p. | 50,000 p. | 15000 p. | 35000 p. |
7 | 5 | - p. | 53000 p. | 20,000 p. | 33000 p. |
8 | 6 | - p. | 47000 p. | 18000 p. | 29000 p. |
9 | 7 | - p. | 62000 p. | 25000 p. | 37000 p. |
10 | 8 | - p. | 70,000 p. | 30 000 p. | 40,000 p. |
11 | 9 | - p. | 64000 p. | 33000 p. | 31000 p. |
12 | TIR | 6% | |||
La fórmula "= VSD (E3: E2)" se ingresa en la celda con la dirección E12. Como resultado de su uso, el procesador de tablas produjo un valor del 6%.
De acuerdo con los datos del ejemplo anterior, calcule la TIR a través del complemento "Buscar soluciones".
Le permite buscar el valor óptimo de la TIR para VPN = 0. Para esto, es necesario calcular el VPN (o VPN). Es igual a la cantidad de flujo de efectivo descontado a lo largo de los años.
Un | En el | C | D | E | F | |
1 | Período (año) T | Costo inicial | Ingresos en efectivo | Gastos en efectivo | Flujo de caja | Descuento de flujo de caja |
2 | 0 | 200000 p. | - p. | 200000 p. | 200000 p. | |
3 | 1 | - p. | 50,000 p. | 20 000 p. | 20,000 p. | 20,000 p. |
4 | 2 | - p. | 60,000 p. | 20,000 p. | 27000 p. | 27000 p. |
5 | 3 | - p. | 45000 p. | 20,000 p. | 17000 p. | 17000 p. |
6 | 4 | - p. | 50,000 p. | 20,000 p. | 35000 p. | 35000 p. |
7 | 5 | - p. | 53000 p. | 20,000 p. | 33000 p. | 33 000 p. |
8 | 6 | - p. | 47000 p. | 20,000 p. | 29000 p. | 29000 p. |
9 | 7 | - p. | 62000 p. | 20,000 p. | 37000 p. | 37000 p. |
10 | 8 | - p. | 70,000 p. | 20,000 p. | 40,000 p. | 40,000 p. |
11 | 9 | - p. | 64000 p. | 20,000 p. | 31000 p. | 31000 p. |
12 | VPN | 69000 p. | ||||
TIR | ||||||
El flujo de caja descontado se calcula mediante la fórmula "= E5 / (1 + $ F $ 11) ^ A5".
Entonces para NPV obtenemos la fórmula "= SUMA (F5: F13) -B7".
A continuación, debe buscar en función de la optimizacióna través del complemento "Búsqueda de decisiones", el valor de la tasa de descuento de TIR a la que el VPN del proyecto se convierte en cero. Para lograr este objetivo, debe abrir la sección "Datos" en el menú principal y encontrar allí la función "Buscar soluciones".
En la ventana que aparece, complete las líneas "Establecer celda objetivo", que indica la dirección de la fórmula de cálculo de VPN, es decir, + $ F $ 16. Entonces:
Como resultado de la optimización, el procesador de mesaRellene una celda vacía con la dirección F17 con el valor de la tasa de descuento. Como se puede ver en la tabla, el resultado es 6%, que coincide completamente con el cálculo del mismo parámetro obtenido usando la fórmula integrada en Excel.
En algunos casos, necesitas calculartasa de rendimiento interna modificada. Refleja el INB mínimo del proyecto en caso de reinversión. La fórmula para calcular MIRR es la siguiente.
donde:
Después de familiarizarse con las propiedades de la TIR (qué es y cómo calcular su valor gráficamente ya lo sabe), puede aprender fácilmente cómo calcular la tasa de rendimiento interna modificada en Excel.
Para hacer esto, el procesador de mesa proporcionafunción incorporada especial del Ministerio del Interior. Tome el mismo ejemplo ya en consideración. Ya se ha considerado cómo calcular la TIR en él. Para MIRR, la tabla es la siguiente.
Un | En el | C | D | E | |
1 | El monto del préstamo como porcentaje | 10% | |||
2 | Tasa de reinversión | 12% | |||
3 | Período (año) T | Costo inicial | Ingresos en efectivo | Gastos en efectivo | Flujo de caja |
4 | 0 | 200000 p. | - p. | 200000 p. | 200000 p. |
5 | 1 | - p. | 50,000 p. | 30000 p. | 20,000 p. |
6 | 2 | - p. | 60,000 p. | 33000 p. | 27000 p. |
7 | 3 | - p. | 45000 p. | 28000 p. | 17000 p. |
8 | 4 | - p. | 50,000 p. | 15000 p. | 35000 p. |
9 | 5 | - p. | 53000 p. | 20,000 p. | 33000 p. |
10 | 6 | - p. | 47000 p. | 18000 p. | 29000 p. |
11 | 7 | - p. | 62000 p. | 25000 p. | 37000 p. |
12 | 8 | - p. | 70,000 p. | 30000 p. | 40,000 p. |
13 | 9 | - p. | 64000 p. | 33000 p. | 31000 p. |
14 | MIRR | % | |||
La fórmula para MIRR "= MVSD (E3: E13; C1; C2)" se ingresa en la celda E14.
El método para evaluar las perspectivas de los proyectos mediante el cálculo de la TIR y la comparación con el valor del costo de capital no es perfecto. Sin embargo, tiene ciertas ventajas. Estos incluyen:
Al mismo tiempo, las desventajas de este indicador son obvias. Estos incluyen:
¡Presta atención! El último inconveniente se resolvió manteniendo el indicador MIRR, que se describió en detalle anteriormente.
De acuerdo con los requisitos del Banco Central de Rusia, todos los bancos,que operan en el territorio de la Federación de Rusia deben indicar la tasa de interés efectiva (EPS). Cualquier prestatario puede calcularlo independientemente. Para hacer esto, tendrá que usar un procesador de tabla, por ejemplo, Microsoft Excel, y seleccionar la función IRR incorporada. Para hacer esto, el resultado en la misma celda debe multiplicarse por el período de pago T (si son mensuales, entonces T = 12, si son diarios, luego T = 365) sin redondear.
Ahora, si sabe cuál es la tasa interna de rendimiento, por lo tanto, si le dicen: "Para cada uno de los proyectos enumerados a continuación, calcule la TIR", no tendrá ninguna dificultad.
