Cálculo del VAN y TIR con Excel

Existen varios programas especializados en llevar a cabo tareas relacionadas con el análisis de inversiones. Si bien Microsoft Excel no es específico para el análisis de inversiones, es uno de los más utilizados debido a su difusión y a que cuenta con diversas funciones específicas para el análisis financiero de proyectos de inversiones.

En primer lugar, debemos saber que las funciones para el análisis de inversiones están agrupadas bajo la categoría “financieras” dentro de las funciones. Las funciones mas utilizadas son “TIR” y “VAN”, las cuales son desarrolladas en este artículo. Se adjunta una hoja de calculo que desarrolla los casos explicados. Descargar Hoja de Cálculo Excel

Nota para OpenOffice Calc
Con el programa OpenOffice Calc las funciones son exactamente iguales, por lo que el ejemplo explicado en este artículo se aplica también a OpenOffice Calc.

Descargar Hola de Cálculo OpenOffice Calc

Función TIR

La función TIR devuelve la tasa interna de retorno de una serie de flujos de caja.

Sintaxis:

=TIR(matriz que contiene los flujos de caja)

Debido a que Excel calcula la TIR mediante un proceso de iteraciones sucesivas, opcionalmente se puede indicar un valor aproximado al cual estimemos que se aproximará la TIR, si no se especifica ningún valor, Excel utilizará 10%.

=TIR(matriz que contiene los flujos de caja;valor estimado de la TIR)


Ejemplo:

Fórmula para el cálculo de la TIR con Excel

Función VAN

En Excel la función para el cálculo del VAN se llama VNA. Esta función devuelve el valor actual neto a partir de un flujo de fondos y de una tasa de descuento. Vemos que esta función tiene un argumento mas que la función para el cálculo de la TIR, la tasa de descuento.

Se debe tener en cuenta, que excel tiene en cuenta los pagos futuros como ocurridos al final de cada período, por lo que el primer valor que se indique en la matriz de pagos será actualizado a la tasa de interés que indiquemos. Por esto no se debe incluir a la inversión inicial en esta matriz, sino que la matriz debe incluir sólo los pagos futuros.

Sintaxis:

=VNA(tasa de descuento;matriz que contiene el flujo de fondos futuros)+ inversión inicial

Ejemplo:
Fórmula para el cálculo del VAN con Excel

Notas:

Como habrá observado en el flujo de fondos, en el período inicial se incluye un flujo de caja negativo. Esta salida de fondos representa la inversión inicial, y debe incluirse en la fórmula del TIR ya que si todos los flujos de fondos son positivos, la TIR tiende a infinito.

Si la inversión inicial se ingresó con valor positivo, la fórmula del VAN debe incluirla con signo negativo.

=VNA(tasa de descuento;matriz que contiene el flujo de fondos futuros)- inversión inicial

Autor: Federico Anzil

Login or register to view attached files

Comentarios

Comentario

La opcion que se entrega en esta pagina es buena, por lo menos uno puede comprobar con la tasa que señala excel si es correcta o no los calculos que se han obtenido con el trabajo manual.

Correccion calculo VAN en Excel

Este metodo de calculo del VAN es erroneo!!!!

La formula descrita en excel es el VNA la que es distinta del VAN:

VNA = Valor Neto Actualizado
En cambio,
VAN = Valor Actual Neto

Tomando por ejemplo una planilla con inversión inicial en C7 y con los flujos en D7;E7;F7, con taza 10% o sea 0,1 :

Columnas
C (Inversión)
D Flujo 1
E Flujo 2
F Flujo 3

La formula para calcular el VAN quedaría:

=VNA(0,1;D7:F7)+C7 donde C7 es un valor(-) generalmente

De esta manera se calcula el VNA y se suma (resta)la inversión, obteniendo el VAN.

Siguiendo con el ejemplo, el calculo de la TIR es el mismo expuesto:

=TIR(C7:F7)

Espero les sirva la aclaración.

Suerte a Todos

Antonio VR
Ingeniero Industrial
CHILE

Corrección VAN

Estimado Antonio,

muchas gracias por la correción. He modificado el artículo y el archivo para que ahora la fórmula sea la correcta. La diferencia surge porque excel considera que el pago se realiza al final del período. Efectivamente, el resultado es distinto.

Saludos,

Federico

Federico

Estimado Federico, Disculpa

Estimado Federico,

Disculpa por haber criticado el articulo, solo quería aclarar ese punto...

Este foro económico es excelente, tiene mucha información y sirve para superar bastantes dudas.

Muchas Gracias,

Atte.,

Antonio

Todo bien

Toda crítica constructiva es bien recibida. Gracias a la aclaración, la información es ahora correcta. Saludos, Federico

Federico

TIR ?

Hola que tal primero decirte que es muy bueno el excel que tienes para calcular la TIR y el motivo de mi mensaje es saber si puede haber una TIR de 115.87% ya que es lo que me sale cuando meto los flujos de efectivo en la tabla ojala puedas contestarme lo mas pronto posible de ante mano gracias

TIR

Si el calculo lo requieres para efectos académicos, matemáticamente si es posible calcular ese % de TIR. No hay problema ya que es teórico.

Si lo quieres para un proyecto real, dudo que alcances esos niveles... Si es así revisa bien el Analisis de Flujo de Caja.

Suerte

Antonio VR

Hola, yo no soy experto en

Hola, yo no soy experto en el tema, pero talvez le sirva a Nestor: matemáticamente es posible que te de ese valor de TIR (por eso excel lo calcula), pero en la realidad no, te recomiendo que revises tu flujo de fondo, porque la TIR tiende a ser alta cuando tenes muchos gastos en este. espero que te sirva.

Muy interesante la ayuda de

Muy interesante la ayuda de obtencion del van y tir

Duración de la inversion

Hola, enhorabuena por la interesante hoja de calculo creada para el cálculo del VAN y la TIR.

Se me plantea una duda: si la inversion tiene una vida superior a lo 10 años que figuran en la hoja excel ¿como puede modificarse o ajustarse para recoger los flujos de caja a partir de año decimo?

muchas gracias

javier gallego