TIR con flujos no periódicos

Calcular la TIR de una inversión con los siguientes flujos de caja. Desembolso inicial de 200.000 € y tres recuperaciones de importes 90.000 €, 50.000 € y 100.000 € en los instantes 1, 1.67 y 2 años respectivamente.

---

La dificultad de este ejercicio residen en interpretar el dato que nos dan para el momento de vencimiento del la segunda recuperación que nos dicen que vence a los 1.67 años.

El primer flujo de caja positivo vence al año y el tercer flujo de caja positivo vence a los dos años exactos. Lo que nos desconcierta es ver que el segundo flujo de caja positivo vence en un número fraccionario de años. Esto imposibilita aplicar la TIR en años ya que no podemos establecer un flujo de caja entre dos celdas si trabajáramos en años.

Para resolver este caso lo que hacemos es darnos cuenta que 1.67 es una forma de redondear el número 1.66666666.... que se corresponde con un año completo más 3/4 partes de otro año. O dicho de otra forma, 1.67 años equivale a decir 1 años + 9 meses.

Esto nos permite utilizar los siguientes métodos.

Método 1

Método 1. Trabajando en trimestres. El dato de 1.67 años equivale a 1 años y 3 trimestres.

Método 2

Método 2. Trabajando en meses. El dato de 1.67 años equivale a 1 años y 9 meses.

TIR de bisiestos

Hoy es 29 de febrero de 2016, estamos en año bisiesto y realizamos el desembolso inicial de una inversión por importe de 770.000 €. Se obtienen 5 recuperaciones de 310.228 € cada una que vencen en los próximos 29 de febrero de los próximos años bisiestos. Calcular la TIR de la operación.

---

Préstamo al consumo

Un consumidor adquiere un bien cuyo precio figura en la etiqueta por importe de 1.000 €. Puede optar por pagarlo de dos formas. Si elige pago al contado obtendrá un descuento del 15,0%. Si elige pago aplazado podrá pagarlo mediante 12 mensualidades prepagables todas ellas del mismo importe, y sin pago de intereses. En el caso de pago aplazado ha de hacer frente a unos gastos de tramitación del préstamo de 40 € euros al inicio. Calcular la TIR.

---

TIR de una compra con descuento

Puede descargar el archivo EjerciciosVarios.xls

El gerente de una empresa necesita adquirir 4 palés de un producto para su fábrica. Necesita un palé al mes durante 4 meses, al inicio de cada mes. El coste de cada palé es de de 500 € y se paga al contado. También dispone de otra opción que consiste en comprar al inicio los 4 palés por importe de 2.000 €, también con pago al contado, pero en este caso recibiría un descuento por volumen de compra del 5%. Determinar si le resulta rentable la adquisición en dos supuestos: a. Que no exista coste de almacenamiento. b. Que el coste de almacenamiento sea de 10 € por palé al mes. Los palés se consumen al inicio de cada mes. Calcule también cuál debéría ser el coste de almacenaje mensual de cada palé, para que ambas opciones resultasen equivalentes.

---

Si no existe coste de almacenamiento la TIR en términos anuales resulta ser del 51,64%. Con costes de almacenaje de 5 €/mes por cada palé, la TIR baja hasta el 17,91%.

El punto de equilibrio nos arroja que el coste e almacenaje es de 16,67 €/mes por palé, lo que haría que el coste de almacenaje consumiera el beneficio que se obtiene por el descuento por volumen de compra.

TIR para operaciones simples

La TIR es la Tasa Interna de Rentabilidad, o también se la denomina Tasa Interna de Retorno.

En inglés se denomina IRR (Internal Rate of Return).

Posteriormente estudiaremos la TIR para operaciones complejas, con varios capitales. En esta ocasión, vamos a efectuar un primer acercamiento a la TIR y la aplicaremos únicamente para operaciones simples. Recuerde que una operación simple es aquella que únicamente dispone de un capital para la prestación (Co) y un único capital para la contraprestación (Cn). La TIR determina la rentabilidad de la operación trabajando en capitalización compuesta.

Para un caso tan sencillo como este, con solo dos capitales, Co y Cn, aplicar la TIR equivale a despejar i de la ley de capitalización compuesta.

Cn=Co(1+i)ⁿ     →   i=(Cn/Co)^(1/n)-1

Aplicada la TIR a una operación simple, también equivale a utilizar la función TASA, donde no hay ningún pago por no tratarse de una renta.

=TASA(nper;pago;va;vf;tipo;estimar)

Ejemplo 1

Calcular la rentabilidad de una operación simple pactada a interés compuesto donde el capital inicial es de 1.000 euros, y el capital final a los tres años es de 1.331 euros.

Los datos que nos dan son:

• Co=1.000 €
• Cn= 1.331 €
• n=3 años

Nos piden el tanto i de la ley de capitalización compuesta.

Método 1

Aplicando la ley de la capitalización compuesta.

Cn=Co(1+i)ⁿ     →   1331=1000(1+i)³ 

 i=(Cn/Co)^(1/n)-1     →    i=(1331/1000)^(1/3)-1 =0,1=10% anual

Método 2

Con la función TASA.

=TASA(3;;-1000;1331)

Obtenemos un 10%. Observe que aparecen dos signos de punto y coma seguidos (;;) lo que indica es que en el argumento 'pago' no se ha puesto nada. También podríamos haber puesto el pago como cero (0). Observe que el valor actual (va) lleva signo negativo.

Método 3

Apliquemos la función TIR a este caso de operación simple. Lo primero que debemos hacer es escribir los flujos de caja en la hoja de cálculo tal y como se indica en la siguiente imagen.

En las celdas amarillas hemos de colocar los datos imprescindibles que luego se utilizaran en la función TIR. En la celda verde calculamos la TIR señalando el rango de los flujos de caja. Es necesario que el capital inicial se ponga negativo ya que constituye el desembolso y la recuperación se ha de poner positiva, ya que se sigue el criterio de caja.

Criterio de caja

Los cobros se anotan con signo positivo y los pagos con signo negativo.

Dicho en otros términos:

Las entradas de tesorería son positivas y las salidas negativas.

Y expresado más informalmente:

"Lo que entra a mi bolsillo es positivo y lo que sale de mi bolsillo es negativo".

Sintaxis de la función TIR

La función TIR tiene la siguiente sintaxis.

=TIR(valores;estimar)

donde:

• valores son todos los flujos de caja de la operación. Desde el flujo de caja en t=0, que por tratarse del desembolso inicial se ha de escribir negativo, hasta la recuperación final en t=n, que es positiva.
• estimar es un argumento optativo que en el caso de una operación simple, con solo dos capitales, Co y Cn, nunca se utiliza. Ya se explicará este argumento más adelante cuando tratemos la TIR en operaciones complejas

Periodicidad de los flujos de caja

Los flujos de caja que utilizamos en el cálculo de la TIR han de ser periódicos. La TIR que se obtenga con la función TIR de Excel expresará la rentabilidad de la operación referida a la unidad temporal utilizada en el rango de valores.

• Si los valores tienen periodicidad anual, entonces la TIR resultante será un tanto efectivo anual
• Si los valores tienen periodicidad mensual, entonces la TIR resultante será un tanto efectivo mensual
• En general la TIR será un tanto efectivo de la misma periodicidad con la que vengan expresados los flujos de caja en las celdas de Excel

Importante: rellenar con ceros

Un aspecto importante es que debemos rellenar con ceros las celdas de la hoja de cálculo en las que no vence ninguna cuantía. Si no se hace así, la función TIR no da error, sino que proporciona resultados erróneos.

TIR con Fechas

Una operación de inversión supone un desembolso de 9.400 euros el 18 de febrero de 2010. Veinte días más tarde se recuperan 1.000 €. Transcurridos cincuenta días más se recuperan 1.500 €. Transcurridos cien días más se recuperan 2.000 €. Transcurridos trescientos días más se recuperan 2.500 €. Y finalmente transcurridos diez días más se recuperan 3.000 €. La duración completa de la operación es de 480 días. Calcular la TIR.

---

Ver el siguiente Post:

VAN y TIR NO PERIODICOS


Si en tu Excel no ves las funciones VNA.NO.PER y TIR.NO.PER es porque no las tienes habilitadas. Esto se hace activando "Herramientas para análisis" tal y como se hizo al instalar Solver. Esto se cuenta en el siguiente Post:

Solver y Buscar Objetivo


Método 1

La forma de ir sumando días a una fecha es simplemente sumarlos. Así, la celda C16 es:
=C15+B16

El motivo es que Excel asigna a cada fecha un número que comienza el día 1-1-1900 y así cada día más es  un número más. Por eso para saber los días transcurridos entre dos fechas lo único que se ha de hacer es  restar ambas fechas, y al resultado ponerle formato general.

Por ejemplo, al 18 de febrero de 2010, le corresponde el valor 40227. Ver en la imagen la celda C15. Eso se sabe escribiendo la fecha en una celda y luego dándole formato de celda GENERAL. O al contrario, escribe en una celda el número 37500 y al darle formato de FECHA verás que se trata del día: 1-sep-2002.

Método 2

---

En finanzas trabajamos con flujos de caja. No es como en contabilidad que trabajan con el criterio del devengo.

Ejemplo: En contabilidad dan como ingreso una factura en el momento en el que nuestro cliente reconoce nuestro derecho de cobro, aunque se pague a 180 días. En finanzas hasta que no se produce el pago (a los 180 días) no se tiene en cuenta ese importe. Por tanto distinguimos entre COBRO e INGRESO. Igualmente hemos de distinguir entre PAGO y GASTO. Las finanzas se mueven por la corriente de tesorería, por lo que a nosotros nos interesan sólo los Cobros y Pagos.

---

Para valorar Rentas únicamente utilizamos una ley financiera:

Cn=Co(1+i)^n (para capitalizar)

y ella misma despejando Co:

Co=Cn(1+i)^-n (para descontar)

El valor actual de una renta es:

VA=C1(1+i)^-1+C2(1+i)^-2+...+Cn(1+i)^-n

¿Qué pasa cuando trabajamos con fechas?

A nivel de Excel es muy fácil ya que se trabaja con VNA.NO.PER o con TIR,NO.PER. Si trabajamos con fórmulas la cosa se complica algo. Debemos trabajar con días.

Supongamos que queremos calcular el VA de una renta cuyas cuantías C1, C2, ... vencen en los instantes d1, d2, d3, ... estando estos expresados en días a contar desde el instante t=0 que es donde se desea calcular el VA.

VA=C1(1+i)^-(d1/365)+C2(1+i)^-(d2/365)+...+Cn(1+i)^-(dn/365)

Con esta fórmula y con Solver se podría calcular al TIR. Lo que tendríamos que hacer es pedir a Solver que haga el VAN igual a cero, y para eso que despeje el tanto i que hace eso posible. Ese tanto sería la TIR.

Pudieras encontrar alguna pequeña variación respecto al valor TIR.NO.PER, por lo de los bisiesto, por ejemplo. Yo lo he probado y normalmente coinciden.

Vídeo

Actualización

En la versión de Excel 2010, con las nuevas actualizaciones, ya no es necesario cargar Herramientas para análisis para tener disponible todas las funciones de que dispone Excel. Ahora este complemento, si se carga en Excel 2010, lo que permite es disponer de más herramientas de tipo estadístico en el menú Datos. Concretamente si cargamos el complemento Herramientas para análisis dispondremos en el menú Datos de una nueva opción a la derecha denominada Análisis de datos que nos permite realizar interesantes análisis estadísticos.

TIR incluyendo gastos mensuales

Puede descargar el archivo de Excel tir_con_gastos.xlsx

---

Una operación de inversión tiene una duración de 8 años. Tiene un desembolso inicial de 50.000 €, y recuperaciones de 12.000 € cada semestre. A los dos meses de realizado el desembolso inicial se han de pagar otros 2.000 euros en concepto de gastos. Calcular la TIR de la operación incluyendo los gastos.

---

En finanzas es fundamental establecer los flujos de caja con su signo en el momento justo en que vencen. Los pagos son negativos y los cobros positivos.

Si usamos la función TIR los flujos de caja han de tener una periodicidad constante. En este caso elegimos la periodicidad mensual debido a que los gastos se pagan a los dos meses del desembolso inicial. El resultado de aplicar la función TIR nos dará la TIR anual, que luego hemos de anualizar.

En la celda F13 escribimos la fórmula de la TIR con una estimación del 0%:

=+TIR(C13:C109;0)

Si no se pone ninguna estimación la función TIR de Excel utiliza un algoritmo que comienza a buscar entorno al 10%. Si la TIR fuera muy pequeña, y el algoritmo no encuentra su valor entorno al 10%, la función arroja un error. Por este motivo es muy aconsejable utilizar la estimación del 0%, cuando se trata de una TIR muy pequeña, que se puede dar con más facilidad al usar una frecuencia de cobros pagos inferior al año.

TIR incluyendo gastos

Una operación de inversión tiene una duración de 10 años. Tiene un desembolso inicial de 50.000 €, y recuperaciones de 8.000 € cada año. A los dos meses de realizado el desembolso inicial se han de pagar otros 2.000 euros en concepto de gastos. Calcular la TIR de la operación incluyendo los gastos.

Amortizaciones anticipadas periódicas y coste financiero

Se concede un préstamo francés de 600.000 €, a 15 años, con términos mensuales, a un tanto del 4,20% nominal anual. El banco permite que el prestatario efectúe amortizaciónes anticipadas al final de cualquier mes, sin aplicar comisión. El prestatario ha venido aportando 1.000 € adicionales al final del tercer mes de cada año.
Calcular:
1. La última mensualidad
2. El coste financiero del préstamo (TAE)

---

Co = 600.000
n =180
TIN = 4,20%
AA = 1.000

---

Hola,

Puede ser:

a176 = 1.527,71 €
TAE = 4,2818%
?

Un saludo y gracias.

---

Hola Javi.

La resolución que propones destina las aportaciones de la Amortización Anticipada (AA) a reducir la duración total del préstamo. De forma que ya no se amortizará en 180 meses sino en menos.

Según tu sistema, pagamos una mensualidad constante a = 4.498,50 € y después de pagar cada importe adicional (AA=1.000 ), seguimos pagando la misma mensualidad.

El sistema que yo había utilizado consistia en que despues de pagar cada aportación adicional de 1.000 euros, recalculamos el préstamo para que al final la duración total continue siendo de 180 meses. Al recalcular el préstamo, sin variar la duración total, lo que varía es la mensualidad. De forma que cada nueva mensualidad será inferior a la anterior, ya que esos 1.000 euros se destinan a reducir el Capital Vivo.

En realidad no es necesario hacer una fórmula distinta para calcular la mensualidad, despues de cada aportación adicional de 1.000 euros. Se hace una fórmula que sirve para toda la columna de la mensualidad, y se copia hacia abajo.

Para llegar a obtener esta fórmula te sugiero que veas el Post siguiente, junto con su video:

Préstamo Francés recalculado cada año


Por cierto, la TAE la tienes bien, ya que financieramente el coste de ambos métodos coincide. Además, se podría incluso calcular sin hacer el cuadro de amortización. Ya que la TAE, en este caso al no existir costes externos, se obtienen simplemente anualizando el tanto mensual efectivo (i12).

i=(1+i12)^12-1

La TIR anual te tiene que dar lo mismo que si haces esto:

TAE=i=(1+0,0035)^12-1

Esto es así debido a que en este caso no existen costes externos. Esto es, costes que

pague el prestatario y reciban otras personas ajenas al prestamista. Es lo que en finanzas

se denominan 'externalidades'.

Se comporta igual que los préstamos a tipo variable, donde partes de un tipo francés

como idea originaria, pero al variar el tipo de interés luego te cambia el término

amortizativo, y entonces ya no se cumple lo de que a=cte.Y como verás la mensualidad va disminuyendo.

En esto tipos de préstamos sucede lo mismo. Yo inicialmente firmo un préstamo francés,

y si no realizo amortizaciones anticipadas (AA=0), entonces se comporta como un

préstamo francés puro, toda la vida del préstamo, ya que además supongo que el tipo de

interés es fijo (i=cte). Pero como me dejan amortizar anticipadamente, entonces al

realizar el pago adicional he de racalcular, al igual que he de recalcular en el caso de un

préstamo a tipo variable.

En un préstamo a tipo variable yo no conozco como variará el Euribor en el futuro, y por

tanto, cada vez que cambia he de recalcular la mensualidad. Y aquí sucede lo mismo,

cuando yo firmo el préstamo no se lo que aportaré en forma de Amortización Anticipada

en el futuro. De esta forma, cada vez que entrego un pago adicional, he de realcualar, y

si no cambia el plazo, entonces necesariamente ha de disminuir la mensualidad.

¿Cómo hacer la serie de la Amortización Anticipada?

Aprovechando este correo explicaremos como hacer la columna de Amortización Anticipada sin tener que ir poniendo a mano los importes de 1.000 euros en el tercer mes de cada año.

Primero

Haces a mano los 12 primeros meses. Puedes poner ceros o dejar las celdas vacías, salvo la del tercer mes, donde van los 1.000 euros.

Segundo

Seleccionas con el ratón las celdas correspondientes a los 12 primeros meses. Pones el cursor del ratón en la esquina inferior derecha de ese rango que tienes seleccionado. Pulsas la tecla CONTROL, verás que junto al cursor aparece un signo +. Sin soltar la tecla CONTROL arrastra hacia abajo, hasta llegar al final del cuadro. Y ya está.

Resolución