Cálculo del VPN en Excel. ¿Cómo se calcula la TIR? Cálculo de la TIR para flujos de efectivo mensuales.

NPV (abreviatura en inglés - Net Present Value), en ruso este indicador tiene varias variaciones del nombre, entre ellas:

valor actual neto (VAN abreviado) es el nombre y la abreviatura más común, incluso la fórmula en Excel se llama exactamente así;
valor actual neto (VAN abreviado): el nombre se debe al hecho de que los flujos de efectivo se descuentan y solo luego se suman;
valor actual neto (VAN abreviado): el nombre se debe al hecho de que todos los ingresos y pérdidas de las actividades debido al descuento se reducen, por así decirlo, al valor actual del dinero (después de todo, desde el punto de vista económico, si ganamos 1.000 rublos y luego recibimos menos que si recibiéramos la misma cantidad, pero ahora).

El VPN es un indicador de la ganancia que recibirán los participantes en un proyecto de inversión. Matemáticamente, este indicador se encuentra descontando los valores del flujo de caja neto (independientemente de si es negativo o positivo).

El valor actual neto se puede encontrar para cualquier período de tiempo del proyecto desde su inicio (durante 5 años, 7 años, 10 años, etc.), dependiendo de la necesidad de cálculo.

¿Para qué se necesita?

El VPN es uno de los indicadores de la eficiencia del proyecto, junto con la TIR, el período de recuperación simple y descontado. Es necesario:

comprender qué tipo de ingresos generará el proyecto, si en principio dará sus frutos o no será rentable, cuándo podrá dar sus frutos y cuánto dinero generará en un momento determinado;
comparar proyectos de inversión (si hay varios proyectos, pero no hay suficiente dinero para todos, se toman los proyectos con mayor oportunidad de ganar dinero, es decir, el VAN más alto).

Fórmula de cálculo

Para calcular el indicador se utiliza la siguiente fórmula:

CF: la cantidad de flujo de caja neto durante un período de tiempo (mes, trimestre, año, etc.);
t es el período de tiempo para el cual se toma el flujo de caja neto;
N es el número de períodos para los cuales se calcula el proyecto de inversión;
i es la tasa de descuento que se tiene en cuenta en este proyecto.

Ejemplo de cálculo

Para considerar un ejemplo de cálculo del indicador VAN, tomemos un proyecto simplificado para la construcción de un pequeño edificio de oficinas. Según el proyecto de inversión, se prevén los siguientes flujos de caja (miles de rublos):

Artículo	1 año	2 años	3 años	4 años	5 años
Inversiones en el proyecto.	100 000
Ingresos de explotación		35 000	37 000	38 000	40 000
Los gastos de explotación		4 000	4 500	5 000	5 500
Flujo de efectivo neto	- 100 000	31 000	32 500	33 000	34 500

La tasa de descuento del proyecto es del 10%.

Sustituyendo en la fórmula los valores de flujo de caja neto para cada periodo (donde se obtiene flujo de caja negativo, lo ponemos con signo menos) y ajustándolos teniendo en cuenta la tasa de descuento, obtenemos el siguiente resultado:

VPN = - 100.000 / 1,1 + 31.000 / 1,1 2 + 32.500 / 1,1 3 + 33.000 / 1,1 4 + 34.500 / 1,1 5 = 3.089,70

Para ilustrar cómo se calcula el VPN en Excel, veamos el ejemplo anterior ingresándolo en tablas. El cálculo se puede realizar de dos formas.

Excel tiene una fórmula VPN que calcula el valor actual neto; para ello debe especificar la tasa de descuento (sin el signo de porcentaje) y resaltar el rango del flujo de caja neto. La fórmula se ve así: = VAN (porcentaje; rango de flujo de caja neto).
Usted mismo puede crear una tabla adicional donde puede descontar el flujo de caja y resumirlo.

A continuación, en la figura, mostramos ambos cálculos (el primero muestra las fórmulas, el segundo los resultados del cálculo):

Como puede ver, ambos métodos de cálculo conducen al mismo resultado, lo que significa que dependiendo de con qué se sienta más cómodo, puede utilizar cualquiera de las opciones de cálculo presentadas.

TIR es Tasa Interna de Retorno, que se traduce al ruso como “tasa interna de rendimiento”. Este es el nombre de uno de los dos métodos principales para evaluar proyectos de inversión. Hay muchos artículos en Internet que brindan un breve resumen de este tema en los libros de texto de análisis financiero. Su desventaja común es que contienen demasiadas matemáticas y muy pocas explicaciones.

Este artículo proporciona no solo la fórmula y definición de TIR, sino que también hay ejemplos de cálculos de este indicador e interpretación de los resultados obtenidos.

TIR: ¿qué es? Fórmula TIR.

La TIR o tasa interna de rendimiento es la tasa de interés a la que el valor presente de todos los flujos de efectivo de un proyecto de inversión (es decir, VAN) es cero. Esto significa que a esta tasa de interés, el inversor podrá recuperar su inversión original, pero no más. En este artículo se describe con más detalle cómo utilizar el indicador TIR para aprobar proyectos de inversión. Primero, debe aprender a calcular la tasa interna de rendimiento TIR o, como también se la llama, la tasa interna de rendimiento.

Las matemáticas para calcular la TIR son bastante simples. Lo mejor es considerarlo con ejemplos elementales. Para calcular el indicador, uno de los artículos anteriores de este sitio utilizó los proyectos A y B con la misma cantidad de inversión inicial (10.000), pero con diferentes entradas de efectivo en los 4 años siguientes. Será conveniente utilizar estos ejemplos para estudiar la fórmula para calcular el indicador TIR.

El valor presente de todos los flujos de efectivo para proyectos de cuatro años se calculará mediante la fórmula:

donde NPV es el valor presente neto, CF son los flujos de efectivo, R es la tasa de porcentaje, el costo de capital, 0,1,2,3,4 es el número de períodos de tiempo a partir de hoy.

Si igualamos el VAN a cero, y en lugar de CF sustituimos los flujos de efectivo correspondientes a cada proyecto, entonces en la ecuación quedará una variable R. El tipo de interés, que será la solución a esta ecuación, es decir en el cual la suma de todos los términos será igual a cero, y se llamará TIR o tasa interna de retorno.

Para el proyecto A, la ecuación tomará la forma:

Para el proyecto B, puedes escribir una fórmula similar para calcular la TIR, solo que los flujos de efectivo serán diferentes:

Para hacerlo aún más claro, puedes trazar los flujos de efectivo del proyecto en una escala de tiempo y visualizar los descuentos. Digamos que, para el proyecto A, el cálculo de la tasa interna de rendimiento se puede presentar de la siguiente manera:

En general, para cualquier proyecto de inversión, la fórmula para calcular la TIR es la siguiente:

donde CF t - flujos de efectivo del proyecto en el momento t, n - número de períodos de tiempo, TIR - tasa interna de rendimiento. Tenga en cuenta que el concepto de TIR, a diferencia del VPN, tiene sentido sólo para un proyecto de inversión, es decir, cuando uno de los flujos de efectivo (normalmente el primero) es negativo. Este flujo de caja negativo será la inversión inicial. De lo contrario nunca obtendremos un VAN de cero.

Calcular la tasa interna de rendimiento usando Excel - ejemplos

Es imposible encontrar manualmente el valor de la TIR para los proyectos A y B usando una calculadora normal, porque en este caso se obtiene una ecuación de cuarto grado (contendrá un multiplicador de TIR 4, la tasa de interés elevada a la cuarta potencia). El problema de resolver una ecuación de este tipo de enésimo grado se puede eliminar utilizando una calculadora financiera o, más simplemente, utilizando la función incorporada en Excel. Esta función se encuentra en la sección Fórmulas -> Financiero y se llama TIR (tasa interna de rendimiento).

Para el proyecto A, el valor de la TIR, como se puede ver en la siguiente figura, será del 14,48%.

Para utilizar la función TIR, en la línea "valores" debe colocar enlaces a las celdas de la tabla con los montos de los flujos de efectivo. La celda "supuesto" se puede dejar en blanco; este argumento es opcional. El valor de salida 0,144888443 es la TIR deseada, es decir tasa interna de retorno de un proyecto determinado. Si convertimos este valor en porcentajes, entonces es igual a 14,48% con una precisión de dos decimales.

Para el proyecto B, el valor de la TIR según Excel es del 11,79%.

Daré explicaciones importantes sobre esta función desde la sección “ayuda” con mis adiciones:

Los valores deben contener al menos un valor positivo y uno negativo. De lo contrario, la función VSD devuelve el valor de error #¡NUM!. De hecho, si no hay flujo de caja negativo, entonces el VAN no puede ser igual a cero y, en este caso, la TIR no existe.
Para calcular la función TIR, el orden en que se reciben los fondos es importante. Por lo tanto, si los flujos de efectivo difieren en magnitud en diferentes períodos, lo que suele ocurrir, entonces deben ingresarse en la tabla de acuerdo con el momento en que ocurren.
Microsoft Excel utiliza el método de iteración para calcular la TIR. La función VSD realiza cálculos cíclicos a partir del valor del argumento "conjetura" hasta obtener un resultado con una precisión del 0,00001%. En la mayoría de los casos, no es necesario especificar un argumento de estimación para los cálculos que utilizan la función VSD. Si se omite, se supone un valor de 0,1 (10%).

En otras palabras, la función TIR del programa Excel buscará el valor de la TIR mediante selección, sustituyendo secuencialmente varias tasas porcentuales en la fórmula, comenzando con el valor en la celda "supuesto" o con el 10%. Si la función VSD no logra obtener un resultado después de 20 intentos, el valor de error #¡NUM! Por lo tanto, en algunos casos, por ejemplo, si va a calcular la TIR para los flujos mensuales durante varios años, es mejor poner la tasa de interés mensual esperada en la celda "conjetura". De lo contrario, es posible que Excel no pueda completar el cálculo en 20 intentos.

Método gráfico para calcular la TIR.

Antes de la llegada de las computadoras personales, se usaba comúnmente un método gráfico para determinar la TIR. A continuación se muestran gráficos de cambios en el VPN para los proyectos A y B dependiendo de la tasa de interés. Para construir gráficos, debe encontrar el valor del VPN sustituyendo diferentes valores de la tasa de descuento en la fórmula del VPN. Puedes leerlo en uno de mis artículos anteriores.

En la figura anterior, el gráfico azul es el proyecto A, el gráfico rojo es el proyecto B. La intersección de los gráficos con el eje X (en este punto el VAN del proyecto es cero) dará precisamente el valor de la TIR para estos proyectos. . Es fácil ver que el método gráfico proporciona un valor de TIR similar a los valores de la tasa interna de retorno que se encuentran en Excel para los proyectos A - 14,5% y B - 11,8%.

¿Cómo utilizar el indicador TIR para evaluar proyectos de inversión?

Cualquier proyecto de inversión implica una inversión inicial (salida de efectivo), que dará lugar a entradas de efectivo en el futuro (idealmente). ¿Qué muestra la tasa interna de retorno de un proyecto de inversión? Muestra la tasa de interés a la que no recibiremos una pérdida por nuestra inversión, es decir, el resultado de todas las entradas y salidas de efectivo será cero: ni ganancias ni pérdidas. En este caso, nuestra inversión en el proyecto se recuperará con los flujos de efectivo futuros del proyecto, pero al final no ganaremos nada.

Regla para evaluar proyectos de inversión:

Si la TIR del proyecto es mayor que el costo de capital de la empresa (es decir, WACC), entonces el proyecto debe aceptarse.

En otras palabras, si la tasa del préstamo es menor que la tasa de inversión (la tasa interna de rendimiento del proyecto), entonces el dinero prestado aportará valor agregado. Porque un proyecto de inversión de este tipo generará un porcentaje de ingresos mayor que el costo de capital requerido para la inversión inicial.

Por ejemplo, si solicita un préstamo bancario al 14% anual para invertir en un proyecto empresarial que le generará un ingreso anual del 20%, ganará dinero con este proyecto. Si sus cálculos resultan incorrectos y la tasa interna de rendimiento de su proyecto es inferior al 14%, tendrá que darle al banco más dinero del que recibe del proyecto. Es decir, incurrirá en una pérdida.

El propio banco hace lo mismo. Atrae dinero de la población, digamos, al 10% anual (tasa de depósito) y emite préstamos al 20% anual (una cifra tomada "de la nada"). Mientras la tasa de los depósitos aceptados por el banco sea menor que la tasa de los préstamos emitidos por el banco, el banco vivirá de esta diferencia.

Al calcular la TIR, descubrimos el nivel superior aceptable del costo del capital prestado que se supone que se debe invertir. Si el costo de capital (al que una empresa puede obtener recursos financieros) es mayor que la tasa interna de retorno (TIR) del proyecto, entonces el proyecto generará pérdidas. Si el costo de capital de la empresa es menor que la TIR del proyecto, entonces la empresa, en cierto sentido, funcionará como un banco: vivirá de la diferencia entre las tasas de interés de los préstamos bancarios y el retorno de la inversión.

Para aclarar aún más la lógica del cálculo de la TIR, daré varios ejemplos de la vida que una persona común y corriente puede encontrar (y encuentra).

Ejemplo 1: depósito a plazo en Sberbank

Digamos que tienes 6.000.000 de rublos disponibles. Ahora mismo puede realizar un depósito a plazo fijo en Sberbank, digamos, durante tres años. La cantidad es grande, por lo que necesitamos el banco más confiable de Rusia. Actualmente, Sberbank ofrece una tasa para depósitos de más de 2 millones de rublos durante tres años del 9,0% anual sin capitalización y del 10,29% anual con capitalización mensual. Puedes leer de qué se trata siguiendo el enlace.

Dado que retiraremos intereses al final de cada año, este será un depósito sin capitalización de intereses y la tasa será del 9% anual. Al final de cada año, será posible retirar una cantidad igual a 6.000.000 * 0,09 = 540.000 rublos. Al final del tercer año, el depósito se puede cerrar retirando los intereses del tercer año y el importe principal de 6 millones de rublos.

Un depósito bancario también es un proyecto de inversión, ya que primero se realiza una inversión inicial (flujo de caja negativo) y luego se recaudan las entradas de efectivo de nuestro proyecto. Un depósito bancario es un instrumento financiero y la forma más sencilla de invertir disponible para el ciudadano medio. Como se trata de un proyecto de inversión, podemos calcular su tasa interna de rendimiento. Probablemente muchos ya hayan adivinado a qué será igual.

La tasa interna de rendimiento (TIR de una inversión) de un depósito bancario es igual a la tasa de interés de este depósito, es decir 9%. Si heredó 6.000.000 de rublos después de impuestos, esto significa que el costo de capital para usted es cero. Por lo tanto, un proyecto de inversión de este tipo será rentable a cualquier tipo de depósito. Pero tomar un préstamo de 6 millones de un banco y depositar ese dinero en otro banco con una ganancia no funcionará: la tasa del préstamo siempre será obviamente más alta que la tasa de inversión. Este es el principio operativo del sistema bancario.

Ejemplo 2: comprar un apartamento para ganar dinero alquilándolo

Los fondos gratuitos se pueden utilizar de otra manera, es decir, comprar un apartamento en Moscú, alquilarlo durante tres años y, al final del tercer año, venderlo para recuperar el capital principal. Los flujos de efectivo de un proyecto de este tipo serán muy similares a los flujos de efectivo de un depósito a plazo fijo en un banco: supongamos que, para facilitar el cálculo, el inquilino del apartamento paga el alquiler inmediatamente para el año en al final de cada año, y el costo del apartamento en rublos después de tres años seguirá siendo el mismo que ahora. Estoy simplificando intencionalmente la situación; tú mismo puedes hacer cálculos más complejos.

Elegí el primer apartamento que encontré en Internet por 6 millones de rublos en el noroeste de Moscú. Alquilar un apartamento de una habitación de este tipo cuesta 30.000 rublos al mes. Por simplicidad, no se tienen en cuenta las consecuencias fiscales de estas transacciones.

Entonces, el alquiler anual será 30.000 * 12 = 360.000 rublos. Para que quede más claro, los flujos de efectivo de ambos proyectos (un depósito en Sberbank y el alquiler de un apartamento de una habitación en el noroeste de Moscú) se muestran juntos en la siguiente tabla:

Incluso sin calcular la TIR, está claro que ahora un depósito bancario es una opción más rentable. Es fácil demostrar esto si calcula la tasa interna de rendimiento para el segundo proyecto: será menor que la TIR del depósito. Al alquilar este apartamento de una habitación en Moscú durante tres años, siempre que se venda al final del tercer año, la TIR de la inversión será del 6,0% anual.

Si no tiene una herencia por valor de 6 millones de rublos, entonces no es prudente tomar este dinero a crédito para alquilar un apartamento, ya que la tasa de préstamo ahora es obviamente superior al 6,0% de la rentabilidad interna de este proyecto. Además, la TIR no depende del número de años que lleva alquilado el apartamento: la tasa interna de rendimiento seguirá siendo la misma si, en lugar de tres años, lo alquila durante 10 o 15 años.

Si tenemos en cuenta el aumento anual del precio de un apartamento como resultado de la inflación, la TIR de este proyecto será mayor. Por ejemplo, si en el primer año (2015) el coste en rublos de un apartamento aumenta un 10%, en el segundo (2016) un 9% y en el tercero (2017) un 8%, luego, al final del tercer año, se puede vender por 6.000.000*1,10*1,09*1,08 = 7.769.520 rublos. Este aumento del flujo de caja en el tercer año del proyecto produciría una TIR del 14,53%. Por lo tanto, si pudiéramos predecir los precios futuros de los apartamentos en rublos con gran precisión, entonces nuestro proyecto sería más realista. Pero todavía no es rentable en la situación actual, cuando la tasa de refinanciación del Banco Central es del 17% y, en consecuencia, todos los préstamos bancarios son demasiado caros.

Cálculo de la TIR para flujos de efectivo mensuales.

Con la función TIR, puede calcular la TIR de un proyecto de inversión con intervalos de tiempo iguales entre flujos de efectivo. El resultado de los cálculos será la tasa de interés para el período: año, trimestre, mes. Por ejemplo, si creyéramos que los pagos por el alquiler de un apartamento se realizan al final de cada mes (y no de año), entonces necesitaríamos hacer una tabla de Excel con 36 pagos de 30.000 rublos cada uno. En este caso, la función TIR daría el valor de la tasa interna de retorno del proyecto. por mes. Para nuestro proyecto, la TIR resultó ser del 0,5% mensual. Esto corresponde a una tasa de interés anual del 6,17% (calculada como (1+0,005) 12 -1), que no es mucho más que el 6,0% calculado anteriormente.

Si desea obtener este resultado usted mismo, asegúrese de completar la celda "Adivinar": coloque 0.03 allí; de lo contrario, obtendrá un error #¡NUM! en el resultado, porque Excel no tendrá suficientes 20 intentos para calcular la TIR.

Cálculo de la TIR para intervalos de tiempo desiguales entre flujos de efectivo

Excel brinda la capacidad de calcular la tasa interna de rendimiento de un proyecto incluso si los flujos de efectivo del proyecto llegan a intervalos irregulares. Para calcular la TIR de un proyecto de este tipo, debe utilizar la función NET IH y, como argumento, especificar no solo las celdas con flujos de efectivo, sino también las celdas con las fechas de sus recibos. Por ejemplo, si posponemos la venta de un apartamento junto con el último pago del alquiler hasta el final del cuarto año (del 31/12/17 al 31/12/18), y al final del tercer año no tenemos flujos de efectivo, entonces la TIR caerá del 6% al 4,53% anual. Tenga en cuenta que en este caso será posible calcular la tasa interna de rendimiento solo utilizando la función TIR NETA, porque la función TIR dará el mismo resultado que antes: 6%, es decir VSD no tendrá en cuenta los cambios en el período de tiempo.

"Adónde vamos Piglet y yo es un gran, gran secreto..."

La tasa de refinanciación actual del 17% está matando tanto a las empresas como a los bancos. Porque es difícil encontrar proyectos de inversión que rindan frutos a tales tipos de interés. ¿Cómo desarrollar un negocio en tales condiciones? El comercio de armas y drogas, por supuesto, será rentable en este caso, pero la mayoría de las empresas sobrevivirán en el mejor de los casos y, en el peor, irán a la quiebra.

¿Y cómo ganarán dinero los bancos si simplemente no existen proyectos de inversión con rendimientos tan altos? Y para poder pagarnos mayores intereses sobre los depósitos, los bancos deben ganar dinero en alguna parte para hacerlo.

Rusia podría soportar un tipo de cambio más bajo del rublo frente a las principales monedas, pero lidiar con una alta tasa de interés en la economía ya es demasiado.

En 2014, escuchamos repetidamente que el Banco Central de la Federación de Rusia estaba aplicando objetivos de inflación. Y esto se hizo con buenas intenciones: cuanto menor sea la inflación, más fácil será lograr un retorno de la inversión. Pero resulta que querían “lo mejor”, pero resultó “como siempre”. Con una moneda cara como la actual, Rusia podría desarrollar con éxito su propia producción y la sustitución de importaciones se convertiría en una realidad. Pero no, no buscamos caminos fáciles y lo peor es que no aprendemos de nuestros errores. Y vivimos, como en ese chiste:

“El año pasado sembramos 100 hectáreas de trigo. El hámster se lo comió todo... Este año vamos a sembrar 200 hectáreas de trigo. ¡Deja que el hámster se ahogue!

Hay muchas fórmulas financieras en Excel. Veamos las fórmulas más importantes que te permitirán calcular:

(Valor Actual Neto) o valor actual neto;
TIR de un proyecto de inversión(Tasa Interna de Retorno) o tasa interna de retorno;

También consideraremos algunos de los matices y trucos del uso de estas fórmulas. Todos los cálculos se pueden encontrar en el archivo adjunto. La atención se centra principalmente en las funciones de Excel.

Cómo calcular el VPN en Excel

Consideremos un ejemplo condicional: hay un proyecto que generará 250.000 rublos al año durante 5 años. Su implementación requiere 1.000.000 de rublos. - 10%.
Si los flujos de efectivo reducidos al período actual son mayores que el dinero invertido (NPV>0), entonces el proyecto es rentable. De otra manera no.

La fórmula del VPN se ve así:

Si los flujos de efectivo reducidos al período actual son mayores que el dinero invertido (NPV>0), entonces el proyecto es rentable. De otra manera no.

Para calcular el VAN, necesitaremos hacer lo siguiente en Excel:

Sumemos los números de serie de los años: 0 – año de inicio, los flujos se reducen a él.
1, 2, 3, etc – estos son los años de implementación del proyecto. La fórmula de la figura realiza las acciones escritas arriba después del signo de suma (Σ).

Dividimos el flujo de caja del período por la cantidad de 1 y la tasa de descuento elevada a la potencia del año correspondiente. La línea calculada representa el flujo de caja descontado. Para obtener el valor VPN, basta con encontrar el importe total de toda la línea.

Resulta "-52 303". El proyecto no es rentable.

Leer también:

¿A quién le resultará útil?: Supongamos que su empresa planea invertir en un proyecto que parece prometedor a primera vista. Tiene un buen valor actual neto (VAN) y una buena tasa interna de rendimiento (TIR). Estos indicadores parecen completamente estándar y generalmente aceptados. Pero hay muchas sutilezas en sus cálculos que inciden en la cifra final y, en ocasiones, en la decisión que se toma. Lea el artículo para comprender las características de la evaluación de proyectos analizándola utilizando diferentes técnicas.

Calcular el VPN usando la fórmula del VPN

Para calcular el VPN en Excel, no es necesario preparar dicha tabla. Simplemente use la fórmula VPN en Excel. Donde VPN es la tasa de descuento; rango de valores descontados. Es decir, basta con indicar una celda con intereses y flujos de efectivo. Pero al utilizar esta fórmula por costumbre, los financieros suelen cometer un error:

De hecho, los resultados deberían ser los mismos. ¿Por qué hay diferentes significados aquí? El hecho es que el VPN en Excel comienza a descontarse desde el primer valor. Es decir, busca valor presente. Y la inversión inicial hay que quitarla después. La forma correcta de la fórmula en nuestro caso será la siguiente:

Las inversiones iniciales se “eliminan” más allá del rango de descuento (rojo) y se suman (en realidad, se restan: dado que las inversiones iniciales ya son menos, es necesario sumar D8) por separado. Ahora el resultado es el mismo.

Más sobre el tema:

¿Cómo ayudará?: La tasa interna de rendimiento modificada, en comparación con la tasa interna de rendimiento convencional, permite una evaluación más precisa de la eficacia de los proyectos de inversión en los que el flujo de caja neto cambia de signo varias veces durante el ciclo de vida. En esta solución se describe cómo calcular este indicador.

¿Cómo ayudará?: para elegir el más rentable de dos proyectos de inversión, es necesario evaluar la eficacia de cada uno de ellos según uno o más indicadores: período de recuperación, tasa de rendimiento, valor actual neto y tasa interna de rendimiento. En esta solución se pueden encontrar más detalles sobre cómo compararlos si los proyectos en sí no son comparables.

Cómo evaluar la efectividad de un proyecto de inversión en Excel usando TIR

La TIR es otro indicador para evaluar proyectos de inversión. La TIR responde a la pregunta: ¿cuál debería ser la tasa para que el VPN sea = 0?

Fórmula TIR en Excel

Si la tasa de descuento< IRR, то проект стоит принять, если нет – отказаться. Как рассчитать IRR в Excel? Очень просто: Подставляем в функцию ВСД итоговый денежный поток.

La TIR resultó ser menor que la tasa de rendimiento. El proyecto no es rentable (misma conclusión que con el VPN).

El VPN y la TIR se consideran, con razón, los principales criterios económicos. Se utilizan tanto para la evaluación de inversiones de proyectos como para evaluar el valor de negocios existentes. En particular, el indicador EVA (Valor Económico Agregado) se considera un buen criterio, ya que si se calcula correctamente es igual al VPN.

¿Dónde más puedes utilizar los cálculos de VAN y TIR en Excel?

VPN y TIR Los especialistas financieros pueden utilizarlo en asuntos más aplicados. Por ejemplo, al resolver la cuestión con los bancos sobre el tipo de interés real de los préstamos. El caso es que al conceder un préstamo, los bancos calculan el importe de la anualidad o, en otras palabras, un pago fijo. Para planificar los pagos de un préstamo, es importante comprender cómo se calcula la anualidad.
Digamos que va a solicitar un préstamo de 1.000.000 de rublos a 5 años al 10% anual. Pagarás una vez al año en cuotas iguales. No presentaremos aquí la fórmula para calcular la anualidad de un libro de texto sobre gestión financiera. Aquí está la fórmula de Excel:

PMT – tasa de descuento; número de períodos; el monto del préstamo que solicita.

Hay dos puntos opcionales más en la fórmula. La cantidad que debe quedar (cero por defecto) y cómo calcular la cantidad: al comienzo del mes - establezca 1, al final del mes - cero. En la mayoría de los casos, estos elementos no son necesarios, por lo que pueden omitirse. El monto total de la anualidad se determina de la siguiente manera:

El monto del pago anual es inmediatamente negativo. Esta cantidad debe pagarse al banco cada año. Contiene dos partes:

Pago del préstamo: tomamos el 10% (interés del préstamo) del monto de la deuda al comienzo del período.
El cuerpo del préstamo es la diferencia entre el pago anual y el pago de intereses (en Excel puedes encontrar fórmulas que calcularán estos pagos por ti).

La deuda al final del mes se calcula como la diferencia entre la deuda al principio y el pago del préstamo. Si los pagos no son anuales, sino mensuales o trimestrales, entonces se deberá ajustar la tasa y el período a estos valores. Entonces, si tuviéramos un pago todos los meses, la fórmula para calcular la anualidad quedaría así:

Dividiríamos la tasa anual por 12 (lo que daría como resultado una tasa mensual) y tomaríamos no 5 períodos, sino 5 · 12 = 60 meses. Recibimos un pago mensual equivalente a 21.247 rublos.

Cómo comprobar la honestidad de los bancos calculando el VPN y la TIR en Excel

Cualquier flujo de pagos de préstamos implica que todas las salidas de dinero se reducen a entradas al tipo de interés de los préstamos. En otras palabras, si construimos el flujo de caja del préstamo que recibimos y los pagos de anualidades posteriores, podemos calcular el VAN y la TIR a partir de ellos. El VPN debería tomar valor cero y la TIR debería mostrarnos la tasa de interés real.

Cuando el préstamo y sus pagos se calculan correctamente, el VAN tomado a la misma tasa de interés es cero. Y la TIR muestra la misma tasa. Cuando el banco nos haga una oferta imposible de rechazar y que aumentará el tipo del préstamo “sólo” en un determinado porcentaje, no lo crea y vuelva a calcular. Daré un ejemplo de cómo calcular la TIR y comprender el interés real de un préstamo.

El banco ofrecía un seguro a “sólo” el 2% del importe del préstamo al año. ¿Crees que esto es un aumento de sólo el 2%? No. El hecho es que el crédito real disminuye al comienzo de cada año:

El resultado muestra que el VPN no es cero. Pero el porcentaje real no es el 10, sino el 12,9%. Tenga en cuenta que el monto del sobrepago ha aumentado aquí. Si esto le confunde, es posible que le ofrezcan "condiciones aún más favorables": pague el sobrepago ahora y el resto más tarde, en pagos más pequeños o, como en nuestro ejemplo, simplemente pague más y luego menos. El monto del pago en exceso no cambiará, pero el porcentaje cambiará significativamente.

¿Qué se ha hecho aquí? De cada pago posterior se dedujo la cantidad de 43.797 rublos y se añadió al primer pago. Si para el sector real la matemática financiera sobre “dinero ayer, dinero mañana” parece algo lejana de la vida, para los bancos es un beneficio real. Por lo tanto, ya están cargando el primer pago. Y con la ayuda de fórmulas sencillas se puede realizar un seguimiento y preparar la base para futuras negociaciones.

PD No olvides que, cuando se trata de pagos mensuales, multiplica por 12.

Excel se creó originalmente para facilitar los cálculos en muchas áreas, incluida la empresarial. Utilizando sus capacidades, puede realizar rápidamente cálculos complejos, incluida la previsión de la rentabilidad de determinados proyectos. Por ejemplo, Excel facilita bastante el cálculo de la TIR de un proyecto. Este artículo le dirá cómo hacer esto en la práctica.

¿Qué es la TIR?

Esta abreviatura denota la tasa interna de rendimiento (TIR) de un proyecto de inversión específico. Este indicador se utiliza a menudo para comparar propuestas sobre las perspectivas de rentabilidad y crecimiento empresarial. En términos numéricos, la TIR es la tasa de interés a la que el valor presente de todos los flujos de efectivo necesarios para implementar un proyecto de inversión (denominado VAN o VAN) se fija en cero. Cuanto mayor sea el INB, más prometedor será el proyecto de inversión.

como evaluar

Una vez conocida la TIR del proyecto, puedes decidir si lanzarlo o abandonarlo. Por ejemplo, si va a iniciar un nuevo negocio y planea financiarlo con un préstamo de un banco, calcular la TIR le permitirá determinar el límite superior aceptable de la tasa de interés. Si una empresa utiliza más de una fuente de inversión, comparar el valor de la TIR con su costo le permitirá tomar una decisión informada sobre la viabilidad de lanzar un proyecto. El costo de más de una fuente de financiamiento se calcula mediante la denominada fórmula del promedio aritmético ponderado. Se denomina “Costo de Capital” o “Precio del Capital Avanzado” (denominado CC).

Usando este indicador tenemos:

TIR > CC, entonces se puede lanzar el proyecto;
TIR = СС, entonces el proyecto no generará ni ganancias ni pérdidas;
TIR< СС, то проект заведомо убыточный и от него следует отказаться.

Cómo hacerlo manualmente

Mucho antes de la llegada de las computadoras, el INB se calculaba resolviendo una ecuación bastante compleja (ver más abajo).

Incluye las siguientes cantidades:

CFt: flujo de efectivo para el período t;
IC - inversiones financieras en la etapa de lanzamiento del proyecto;
N es el número total de intervalos.

Sin programas especiales, se puede calcular la TIR de un proyecto mediante el método de aproximaciones sucesivas o iteraciones. Para hacer esto, primero es necesario seleccionar las tasas de barrera de tal manera que se encuentren los valores mínimos del VPN en módulo y realizar la aproximación.

Solución por el método de aproximaciones sucesivas

En primer lugar, tendrás que cambiar al idioma de funciones. En este contexto, se entenderá por TIR el valor de rendimiento r en el que el VPN, siendo función de r, se vuelve igual a cero.

En otras palabras, TIR = r tal que cuando se sustituye en la expresión VAN(f(r)) se vuelve cero.

Ahora resolvamos el problema formulado utilizando el método de aproximaciones sucesivas.

Se suele entender por iteración el resultado de la aplicación repetida de una u otra operación matemática. En este caso, el valor de la función calculada en el paso anterior se convierte en su argumento durante el siguiente.

El cálculo del indicador TIR se realiza en 2 etapas:

cálculo de la TIR para valores extremos de rendimientos normales r1 y r2 tales que r1< r2;
cálculo de este indicador para valores de r cercanos al valor de la TIR obtenido como resultado de cálculos anteriores.

Al resolver el problema, r1 y r2 se eligen de tal manera que NPV = f (r) dentro del intervalo (r1, r2) cambie su valor de menos a más o viceversa.

Por tanto, tenemos una fórmula para calcular el indicador TIR en la forma de la expresión que se presenta a continuación.

De esto se deduce que para obtener el valor de la TIR, primero es necesario calcular el VAN para diferentes valores de la tasa de interés.

Existe la siguiente relación entre los indicadores VAN, PI y CC:

si el valor del VPN es positivo, entonces TIR > CC y PI > 1;
si VPN = 0, entonces TIR = CC y PI = 1;
Si el valor del VPN es negativo, entonces la TIR< СС и PI< 1.

Método gráfico

Ahora que sabes qué es la TIR y cómo calcularla manualmente, vale la pena familiarizarte con otro método para resolver este problema, que era uno de los más populares antes de que aparecieran las computadoras. Estamos hablando de una versión gráfica de la determinación de la TIR. Para construir gráficos, debe encontrar el valor VAN sustituyendo diferentes valores de la tasa de descuento en la fórmula para calcularlo.

Cómo calcular la TIR en Excel

Como puede ver, encontrar la TIR manualmente es bastante difícil. Esto requiere ciertos conocimientos matemáticos y tiempo. Es mucho más fácil aprender a calcular la TIR en Excel (vea el ejemplo a continuación).

Para ello, el conocido procesador de hojas de cálculo de Microsoft tiene incorporada una función especial para calcular la tasa de descuento interna, la TIR, que proporciona el valor TIR deseado en términos porcentuales.

Sintaxis

Echemos un vistazo más de cerca a su sintaxis:

Por valores nos referimos a una matriz o referencia a celdas que contienen números para los cuales es necesario calcular la TIR, teniendo en cuenta todos los requisitos especificados para este indicador.
La estimación es un valor que se sabe que está cerca del resultado de la TIR.

En Microsoft Excel, el método de iteración descrito anteriormente se utiliza para calcular el VSD. Comienza con un valor Guess y recorre los cálculos hasta obtener un resultado con una precisión del 0,00001%. Si la función VSD incorporada no produce un resultado después de realizar 20 intentos, entonces el procesador de la mesa produce un valor de error indicado como “#¡NÚMERO!”.

Como muestra la práctica, en la mayoría de los casos no es necesario establecer un valor para el valor "Supuesto". Si se omite, el procesador lo considera igual a 0,1 (10%).

Si la función VSD incorporada devuelve el error "#¡NÚMERO!" o si el resultado obtenido no cumple con las expectativas, entonces se pueden realizar los cálculos nuevamente, pero con un valor diferente para el argumento “Supuesto”.

Soluciones en Excel: opción 1

Intentemos calcular la TIR (ya sabes qué es y cómo calcular este valor manualmente) usando la función TIR incorporada. Digamos que tenemos datos para los próximos 9 años, que se ingresan en una hoja de cálculo de Excel.


Período (año) T	Costos iniciales	Ingresos en efectivo	Gasto en efectivo	Flujo de fondos

La fórmula “=VSD (E3:E2)” se ingresa en la celda con dirección E12. Como resultado de su aplicación, el procesador de hojas de cálculo arrojó un valor del 6%.

Soluciones en Excel: opción 2

Utilizando los datos proporcionados en el ejemplo anterior, calcule la TIR utilizando el complemento "Búsqueda de soluciones".

Le permite buscar el valor óptimo de TIR para VAN=0. Para hacer esto, necesita calcular el VAN (o VAN). Es igual a la suma del flujo de caja descontado a lo largo de los años.


Período (año) T	Costos iniciales	Ingresos en efectivo	Gasto en efectivo	Flujo de fondos	Flujo de caja con descuento

El flujo de caja descontado se calcula utilizando la fórmula “=E5/(1+$F$11)^A5”.

Luego, para el VPN se obtiene la fórmula “=SUM(F5:F13)-B7”.

A continuación, debe encontrar, basándose en la optimización utilizando el complemento "Búsqueda de soluciones", un valor de la tasa de descuento de la TIR al que el VAN del proyecto sea igual a cero. Para lograr este objetivo, debe abrir la sección "Datos" en el menú principal y encontrar allí la función "Búsqueda de soluciones".

En la ventana que aparece, complete las líneas "Establecer celda objetivo", indicando la dirección de la fórmula de cálculo del VPN, es decir, +$F$16. Entonces:

seleccione el valor para esta celda “0”;
en la ventana “Cambios de celda” se ingresa el parámetro +$F$17, es decir, el valor de la tasa interna de retorno.

Como resultado de la optimización, el procesador de la tabla llenará la 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%, lo que coincide completamente con el cálculo del mismo parámetro obtenido mediante la fórmula incorporada en Excel.

ESPEJO

MIRR - tasa interna de retorno del proyecto de inversión;
COFt es la salida de fondos del proyecto durante los períodos de tiempo t;
CIFt - entrada financiera;
r es la tasa de descuento, que es igual al costo promedio ponderado del capital invertido WACC;
d - % tasa de reinversión;
n - número de períodos de tiempo.

Calcular MIRR en un procesador de mesa

Una vez que se familiarice con las propiedades de la TIR (qué es y cómo calcular su valor gráficamente, ya lo sabe), podrá aprender fácilmente cómo calcular la tasa interna de rendimiento modificada en Excel.

Para ello, el procesador de mesa dispone de una función MVSD especial integrada. Tomemos el mismo ejemplo ya comentado. Ya se ha discutido cómo calcular la TIR. Para MIRR la tabla se ve así.


Monto del préstamo en porcentaje
Nivel de reinversión
Período (año) T	Costos iniciales	Ingresos en efectivo	Gasto en efectivo	Flujo de fondos

En la celda E14 ingrese la fórmula para MIRR “=MIRR(E3:E13;C1;C2)”.

Ventajas y desventajas de utilizar el indicador de tasa interna de retorno

El método para evaluar las perspectivas de proyectos calculando la TIR y comparándola con el costo de capital no es perfecto. Sin embargo, tiene ciertas ventajas. Éstas incluyen:

La capacidad de comparar varios proyectos de inversión según el grado de atractivo y la eficiencia del uso del capital invertido. Por ejemplo, se puede hacer una comparación con el rendimiento de los activos libres de riesgo.
La capacidad de comparar diferentes proyectos de inversión con diferentes horizontes de inversión.

Al mismo tiempo, las deficiencias de este indicador son obvias. Éstas incluyen:

la incapacidad del indicador de tasa interna de retorno para reflejar el monto de la reinversión en el proyecto;
la dificultad de pronosticar los pagos en efectivo, ya que su valor está influenciado por muchos factores de riesgo, cuya evaluación objetiva es muy difícil;
no reflejar la cantidad absoluta de ingresos (ingresos) del tamaño de la inversión.

¡Nota! El último inconveniente se resolvió manteniendo el indicador MIRR, que se describió en detalle anteriormente.

Cómo la capacidad de calcular la TIR puede resultar útil para los prestatarios

Según los requisitos del Banco Central de Rusia, todos los bancos que operan en la Federación de Rusia deben indicar el tipo de interés efectivo (EPR). Cualquier prestatario puede calcularlo de forma independiente. Para hacer esto, deberá usar un procesador de hojas de cálculo, por ejemplo, Microsoft Excel y seleccionar la función VSD incorporada. Para ello, se debe multiplicar el resultado de la misma celda por el periodo de pago T (si son mensuales, entonces T = 12, si son diarios, entonces T = 365) sin redondear.

Ahora bien, si sabe qué es la tasa interna de rendimiento, si alguien le dice: “Para cada uno de los siguientes proyectos, calcule la TIR”, no enfrentará ninguna dificultad.

a la página anterior - al desarrollo de modelos financieros en EXCEL

A los efectos de este artículo, entenderemos por inversión o inversiones de inversión la dirección del capital en forma de dinero hacia la infraestructura productiva con el objetivo de crear en el futuro una producción en masa de un nuevo producto o ampliar la producción para crear un volumen mayor. de productos, y mediante cuya venta se espera recuperar los fondos de inversión. Donde, a su vez, como infraestructura de producción consideraremos todos los componentes del ciclo de creación de un producto de consumo desde las ideas, la investigación científica y el trabajo de diseño hasta el desarrollo, construcción, implementación y lanzamiento de instalaciones de producción y ventas en masa.

Como es habitual, publicamos inmediatamente para su descarga el correspondiente modelo financiero del proyecto de inversión (modelo de inversión) en forma de archivo EXCEL:

La leyenda del proyecto de inversión, cuyo modelado se presenta en el archivo EXCEL, es la siguiente. Los fondos de inversión se invierten en la creación de la producción de ciertos bienes, en el proyecto se denominan productos terminados, cuyo beneficio de cuya venta se utiliza para saldar la deuda de la empresa de producción con los inversores. El período de inversión se considera de 10 años, los cálculos del modelo financiero se realizan mensualmente, el usuario puede establecer él mismo el inicio del período a partir de una lista desplegable de los primeros números de diferentes años.

Desde el punto de vista de las características específicas de los objetos de gasto de capital, el modelo financiero distingue únicamente que la infraestructura de producción se compra con fondos de inversión, sin especificar el método de adquisición, ya sea la construcción desde cero o la compra de instalaciones de producción ya preparadas (edificios, estructuras, equipos, etc.), incluido en sí mismo un almacén de materias primas y productos terminados, así como áreas para el personal, incluidos espacios de oficinas para el personal directivo, es decir. No hay planes de alquilar oficinas, así como tampoco hay inversiones de capital en infraestructura comercial y logística - no hay tiendas ni vehículos propios - logística subcontratada.

El modelo financiero le permite establecer el nivel de producción máxima de productos terminados por mes, mientras que el plan de ventas en términos de cantidad de productos terminados se establece independientemente de la producción máxima. Por tanto, el modelo de inversión prevé la posibilidad de ampliar o escalar la producción mediante inversiones de capital en módulos de producción adicionales similares al original, cuya fuente de financiación también son las inversiones. Es decir, se supone que si en cualquier período futuro, para cumplir el plan de ventas, es necesario producir productos en un volumen que exceda la producción máxima de un módulo de producción, entonces el modelo financiero calcula automáticamente los costos de capital de inversión para el creación de capacidades de producción adicionales, formando así el flujo de inversión del modelo.

El archivo EXCEL con el modelo de inversión consta de 10 pestañas, la primera de las cuales, en la pestaña "tabla de contenido", contiene una lista que indica el contenido de las 9 pestañas restantes y hipervínculos a ellas (el archivo del modelo financiero brinda la posibilidad de moverse entre pestañas usando hipervínculos a través de la tabla de contenido), brevemente se ve así:

En la hoja de “condiciones”, el usuario del modelo financiero establece las condiciones iniciales básicas para modelar el proyecto de inversión. Todo comienza configurando la fecha de inicio del proyecto en la celda M11 de la lista desplegable de fechas (el conjunto de fechas en esta lista se puede cambiar en la pestaña "estructura", columna D). Luego de lo cual, en las líneas 7, 8 y 9, el período de inversión se divide automáticamente en 10 años posteriores.

La línea 13 de las “Condiciones” establece los costos de capital anuales totales en miles de rublos para la infraestructura de un módulo de producción, a partir del cual el proyecto comienza a funcionar.

Las líneas 15 a 48 proporcionan un desglose mensual de los gastos de capital totales de la línea 13. Aquí se proporcionan los siguientes tres métodos de división, cuya selección se realiza a través de la lista desplegable de la celda M11 con los siguientes valores (cuyos nombres se pueden cambiar en la pestaña "estructura" en la columna G):

Uniformemente durante todo el año;

Uniformemente de... a...;

A mano.

Si selecciona el método “uniformemente durante todo el año” en las líneas 35 a 46, los montos totales de la línea 13 simplemente se dividen por 12 para cada mes del año correspondiente. Al elegir el método “uniformemente desde... hasta...” en las líneas 17 y 18, debe seleccionar de la lista los números de los meses de inicio y fin de las inversiones de capital de un año determinado, después de lo cual el modelo financiero distribuirá automáticamente los volúmenes totales de inversiones de capital de manera uniforme durante los meses seleccionados en el bloque de líneas del 35 al 46. Finalmente, al elegir el método “manual”, se le pedirá en las líneas 21 a 32 que simplemente establezca la distribución porcentual por mes del año de los montos totales de gastos de capital.

En la línea 48, se especifica el número del mes de puesta en servicio de los objetos de gastos de capital, y si este número no coincide con el número del mes de los últimos volúmenes mensuales distintos de cero de gastos de capital, entonces la puesta en servicio se calculará en dos etapas: primero, se pondrá en funcionamiento todo el volumen hasta el mes especificado, incluido él, luego, en el último mes de volúmenes distintos de cero, se pondrá en funcionamiento el volumen restante de activos no corrientes.

La celda Q50 especifica el número de años de depreciación de los activos de producción.

En las líneas 52 a 56, se establecen restricciones a la capacidad de producción y almacenamiento de un módulo de producción: la producción mensual máxima de productos terminados se establece en la celda Q52, el volumen del propio almacén de productos terminados se establece en la celda Q54, como porcentaje de la producción máxima mensual, volumen propio almacén de materias primas e insumos - en la celda Q56 (en términos específicos por unidad de producto terminado).

En el bloque de líneas 58 a 77, el plan de ventas se establece en el número de unidades de productos terminados, como producto del número de clientes/compradores potenciales en la región de ventas (línea 58), la participación objetivo prevista del mercado de ventas que la empresa planea ocupar en la región (línea 60) y el número promedio de unidades que comprará el cliente (línea 62).

El ciclo operativo de nuestra empresa consta de un ciclo de producción y un ciclo comercial, el primero de los cuales comienza con la compra de materias primas y materiales y finaliza con la liberación de los productos terminados y su colocación en nuestro propio almacén. El ciclo comercial comienza con la colocación de los productos terminados en el almacén de la empresa y finaliza con su venta a los clientes. Los períodos de rotación correspondientes o la duración de los ciclos especificados se especifican en las líneas 79 y 81.

Indicadores como el presupuesto para la compra de materias primas y materiales, el presupuesto para la producción de productos terminados, los saldos al final del período de inventarios de materias primas y materiales, así como de productos terminados, se calculan en el estado financiero. modelo considerado desde el punto de vista del enfoque clásico, a través de ratios de rotación, a partir del presupuesto de ventas.

PG = PG 1 - PG 0 + S,

donde GP 1 y GP 0 son los saldos de productos terminados al final y al inicio del período, respectivamente, S – ventas de productos terminados para el período.

En este caso, el volumen de productos terminados de GP 1 al final del período se calcula mediante la siguiente fórmula (se puede encontrar en cualquier libro de texto):

GP 1 = [ 2 * S * P sobre (GP) / número de días del período ] - GP 0 ,

que se desprende de la definición clásica del coeficiente y el período de rotación de los inventarios de productos terminados, donde P ob (GP) es el período de rotación de los productos terminados especificado en la línea 79 de la pestaña "condiciones".

Naturalmente, al establecer el volumen de reservas de GP 0 para el primer período de nuestro modelo de inversión en cero, aplicamos secuencialmente las fórmulas anteriores para cada período siguiente, y en nuestro caso estos son meses (ver la pestaña "mensual"), obtener volúmenes de producción para el período y saldos de reservas de GP al final del período.

De igual forma, los volúmenes de compras de materias primas e insumos para el período, así como los inventarios al final de los períodos de materias primas y materiales, se calculan mediante un plan precalculado para la producción de productos terminados y el período de rotación de inventarios. de materias primas y suministros en relación con la producción de productos terminados especificados en la línea 81.

Además, no describiremos con tanto detalle la metodología de la estructura técnica del archivo con el modelo financiero, ya que creemos que sería justo ofrecer al usuario interesado en esto pagar 750 rublos por una metodología de modelado detallada. Puede solicitar una metodología de modelado de inversión paga a través de sus comentarios o llamándonos por teléfono; consulte los contactos en la esquina superior derecha de la página.

Solo notamos que utilizamos fórmulas con la rotación de inventarios y productos terminados en el modelo financiero para las ventas expresadas en valores naturales, es decir. en el número de unidades de productos terminados, luego de lo cual al establecer en las líneas 83, 85 y 104, respectivamente, el costo de producción planificado, la inflación proyectada dentro del costo de materias primas y materiales, así como el margen de ventas, obtenemos un presupuesto de adquisiciones, plan de producción y saldo de inventarios de materias primas y materiales y productos terminados en dinero, en nuestro caso en miles de rublos.

Todos los cálculos del modelo de inversión se realizan en detalle mensualmente en la pestaña "mensual", después de lo cual los datos ingresan en las hojas de nuestro archivo EXCEL con los formularios finales de informes de gestión financiera, consulte la tabla de contenido arriba en el texto.

La cuenta de pérdidas y ganancias o, como se suele decir ahora, “el informe de resultados financieros (P&L) en el modelo financiero se presenta en dos versiones, a saber, en forma de P&L marginal y P&L funcional: el “PL_m” y Pestañas “PL_f”.

En el estado de resultados marginal, todos los gastos se dividen en variables y fijos, es decir, para aquellos rubros que dependen de determinados indicadores de ingresos y para aquellos rubros que no dependen directamente de ellos. La plantilla/formato de Margen P&L se puede encontrar a continuación:

En un estado de resultados funcional, todos los gastos se dividen según el principio de afiliación funcional, es decir La división de las partidas de gastos se produce de acuerdo con la estructura organizativa aprobada de la empresa; la plantilla/formato de las pérdidas y ganancias funcionales se puede encontrar a continuación:

La plantilla/formato del estado de flujo de efectivo utilizado en el modelo financiero es el siguiente:

No publicaremos el formato del balance, porque si el lector ya está interesado en nuestro modelo financiero del proyecto de inversión, probablemente lo descargará y se familiarizará con él (con el formato/plantilla del balance).

Pasaremos a la pestaña “VAN”, en la que se realiza el análisis del VPN mediante fórmulas EXCEL, donde se calculan indicadores como:

VAN de las inversiones (Inv);

VAN del flujo DS (CF);

Retorno de la inversión;

PP - período de recuperación;

VAN CF después del período de inversión;

PI del período de inversión;

VPN completo;

PI lleno;

ROI del período de inversión;

El retorno de la inversión está completo;

Tasa de descuento promedio para un proyecto de inversión;

TIR - tasa interna de rendimiento.

El formato del informe con análisis del VPN del proyecto de inversión es el siguiente:

Entonces estamos mirando la pestaña VAN. En la línea 12 de esta pestaña, desde el informe de Flujo de Caja de la pestaña "CF", utilizando fórmulas EXCEL, ingresamos datos sobre el flujo de inversiones en el proyecto para gastos de capital para crear la cantidad de módulos de producción que son necesarios de acuerdo con el plan de ventas del producto terminado.

En la línea 14, los factores de descuento se calculan para cada año con base en los datos de la línea 188 de la pestaña “condiciones”, donde el usuario, a su vez, ingresa manualmente las tasas de descuento de los flujos financieros previstos por separado para cada año del proyecto de inversión; le recordamos que estas tasas en general pueden variar de un año a otro.

El valor presente de las inversiones se calcula en la línea 16 sumando los sucesivos cocientes de división para cada año de la línea 12 con los volúmenes nominales de las inversiones por los factores de descuento correspondientes de la línea 14.

Además, en las líneas 20 y 24, los valores actuales de las entradas y salidas de fondos del proyecto se calculan de manera similar, sin tener en cuenta el reembolso de los préstamos de inversión, pero teniendo en cuenta las actividades financieras de la empresa, si la alta dirección de este último tuvo que atraer préstamos para reponer el capital de trabajo de las actividades operativas.

El modelo considerado supone que las inversiones están dirigidas únicamente a la infraestructura de producción, y para mantener las actividades operativas en caso de falta de efectivo, la alta dirección de la empresa debe encontrar por sí misma la oportunidad de atraer préstamos y empréstitos adecuados, cuyos volúmenes nuestros financieros El modelo calcula automáticamente a través del modelo de préstamos para sobregiros y toma en cuenta estas operaciones dentro del bloque con flujo de caja para actividades financieras en el informe de Flujo de Caja.

Es decir, en la línea 26 se calcula el flujo financiero reducido para las actividades principales y financieras o, lo que es lo mismo, el flujo financiero para las actividades operativas de la empresa, que es exactamente lo que hay que comparar con el flujo de inversión reducido de línea 16, que se realiza en la celda M29, en la que se calcula la diferencia especificada. Usando el formato condicional EXCEL, la celda M29 se colorea de verde si su valor es mayor que cero, es decir el proyecto es solvente (el flujo financiero normalizado de las actividades operativas es mayor que el flujo de inversión normalizado), y se colorea en rojo; si, por el contrario, el proyecto no es rentable, en igualdad de condiciones, desde el punto de vista del inversor.

En las celdas M31 y M33 se calculan los valores de los indicadores PI y ROI:

PI - Índice de rentabilidad - Índice de rentabilidad de las inversiones - igual a la relación entre el flujo financiero reducido de las actividades operativas y el valor actual de las inversiones de inversión - obviamente, debe ser mayor que uno para que el proyecto se proponga para su consideración;

ROI - Retorno de la inversión - Retorno de la inversión - es igual a la relación entre el VAN y el valor actual de la inversión.

En la línea 35 se calcula el flujo de retorno de la inversión y, por lo tanto, el indicador se calcula en la celda M39.

PP - Período de recuperación: el período de recuperación o recuperación de la inversión es igual al número de años, ponderado por el monto de la recuperación, después de los cuales el inversionista recibirá todo el dinero invertido en el proyecto, teniendo en cuenta el nivel de descuento especificado. .

A continuación, en el bloque de líneas 41 a 53, se calculan todos los indicadores de efectividad del proyecto de inversión dados anteriormente para el caso de que el inversor decida no abandonar el proyecto, sino dejarlo para sí mismo “para siempre”, ver las fórmulas correspondientes en la primera parte de esta sección. Aquí, el flujo financiero promedio calculado para el período posterior al período de recuperación PP se considera el flujo financiero anual para el período posterior a los 10 años de existencia del proyecto.

Finalmente, en la celda M56, y de hecho en el bloque de líneas del 61 al 160, aplicando el método de dicotomía, como señalamos anteriormente, se calcula el valor aproximado del indicador de eficiencia del proyecto de inversión TIR - tasa interna de retorno. Y para tener con qué compararlo, si de año en año el usuario del modelo establece diferentes tasas de descuento, presentamos en la celda M56, con cálculo en el bloque de líneas del 161 al 260, la tasa de descuento promedio unificada para cada año del proyecto de inversión.

En conclusión, observamos que fuera del material presentado en este artículo, excluimos conscientemente la discusión de conceptos tan importantes como EBITDA y EVA, así como el valor de mercado de los activos netos, cuya filosofía forma una comprensión adecuada de lo que es una gestión eficaz. es desde el punto de vista del sistema de aceptación de inversiones, y no sólo de soluciones. También observamos que la visión de la gestión a través de estos conceptos es similar, por ejemplo, al sistema de gestión llamado "fabricación ajustada", que se formó en Japón. En este caso, no estamos hablando solo de fórmulas mediante las cuales la persona promedio recibe formalmente una idea de estos indicadores y conceptos relacionados, estamos hablando de cómo implementar sistemas de gestión apropiados y altamente efectivos en la vida real de una empresa. que es el punto de partida básico para cualquier inversor a la hora de formar un equipo de altos directivos para gestionar el proyecto. Publicaremos este material en un futuro próximo en la sección dedicada a las leyes de una gestión eficaz.