Cómo calcular el VAN y la TIR de un proyecto en Excel

El Valor Actual Neto (VAN) y la Tasa Interna de Retorno (TIR) son dos conceptos de matemática financiera que permiten evaluar proyectos de inversión. Por su lado, el VAN surge de actualizar los flujos de caja que genera un proyecto a lo largo del tiempo a una tasa K, que es el coste del proyecto. Por otro lado, la TIR, es la tasa a la que debemos actualizar los flujos de caja para que el VAN sea 0. El criterio que se sigue con este método es el siguiente:

• Si el VAN > 0, el proyecto se acepta porque k < TIR, es decir, el coste del proyecto es inferior a la TIR.
• Si el VAN <0, el proyecto no se acepta porque k>TIR, es decir, el coste es superior al coste máximo aceptado para el proyecto o TIR.

Lo vamos a ver con un caso simulado. Te recordamos que tienes disponible la plantilla excel VAN y TIR en la sección de finanzas, que es la que vamos a utilizar para llevar a cabo este ejemplo práctico.

Imaginemos que queremos evaluar el siguiente proyecto de inversión. A una empresa le han pedido la fabricación y suministro de componentes durante 2 años a razón de 1.000 unidades mensuales por un precio de 20 euros la unidad. Para acometer el proyecto la empresa debe adecuar sus instalaciones y adquirir una nueva máquina para producirlos. Los costes en los que incurre son:

• Adecuación de instalaciones para instalar la nueva máquina: 6.000 euros.
• En materia prima incurre en un coste de 6,25 euros la unidad, es decir, 6.250 euros al mes y 150.000 euros en toda la vida del proyecto.
• El consumo eléctrico y la parte proporcional de alquiler de las instalaciones ascienden a 2.000 euros mensuales, es decir, 48.000 euros en total.
• Para llevar a cabo la producción se requieren dos empleados cuyo coste total es de 5.000 euros mensuales, es decir, 120.000 euros.
• Para producir los componentes la empresa adquiere una máquina cuyo coste es de 120.000 euros y que financia completamente con un préstamo a 24 meses a un tipo de interés del 3%. 
• Suponemos una tasa k del 5% mensual.

Antes de utilizar la plantilla, debemos tener claro el flujo de ingresos y gastos y la frecuencia con la que suceden. Los resultados los tenemos en la siguiente tabla de datos.

Este es un caso bastante sencillo porque la mayor parte de ingresos y gastos son mensuales y encajan todos con la misma periodicidad de ingresos del proyecto. Tenemos, por un lado, 6.000 euros iniciales de adecuación de instalaciones que es el único concepto que tiene sólo un periodo, el resto, son todos mensuales.

La única dificultad radica en calcular la cuota mensual del préstamo, en este caso 5.157,75€, que puedes obtener aplicando la fórmula de un préstamo francés, o bien con la calculadora de préstamos que tenemos en la sección de finanzas.

Teniendo claros estos dos puntos tendremos unos gastos iniciales de 6.000 euros y un gasto recurrente durante los 24 meses de 18.407,75€. En cuanto a los ingresos no hay duda, son 20.000 euros mensuales durante 24 periodos.  Ahora sí, con la ayuda de Excel, obtendremos el VAN y la TIR de este proyecto. Los flujos de caja derivados de este proyecto son estos:

Para obtener la TIR sólo tenemos que aplicar la fórmula TIR de Excel sobre el rango que contiene el beneficio de cada periodo de este proyecto. El resultado es de 26.44%. Hay que tener en cuenta que esta TIR es mensual, puesto que los flujos de caja son mensuales. Para obtener el resultado anualizado o Tasa Anual Equivalente podemos utilizar la fórmula de interés compuesto:

TAE= ((1+TIR mensual)^12)-1

La Tasa Anual Equivalente, en este caso 1.569,98%, nos permite comparar la rentabilidad de dos proyectos con periodicidad distinta. Por comparación, puesto que la TIR es superior a la tasa K, ya podemos decir que este proyecto es apto para invertir y que tendrá un VAN superior a cero. Pero vamos a calcularlo para salir de dudas.

En este caso, siguiendo con la plantilla excel, utilizaremos un par de columnas adicionales, una donde veremos el resultado acumulado, sólo por ver cómo evoluciona el beneficio del proyecto, y otra donde calcularemos el VAN.

Para calcularlo debemos actualizar, con la tasa k, cada uno de los flujos de caja teniendo en cuenta su temporalidad, y hacer la suma total de flujos actualizados, en este caso la suma de la cuarta columna. Por ejemplo, el VAN de los flujos de los periodos 1, 2 y 24 respectivamente se calculan:

En total tenemos un VAN de 15.970,89€, positivo como decíamos. Además, el beneficio absoluto obtenido del proyecto es de 32.214€. Desde el punto de vista del VAN es un proyecto apto para invertir, aunque siempre es necesario contrastar los resultados con otros métodos de valoración como por ejemplo el payback.