¿Podéis enseñarme a calcular el TIR con el Excel?

Saludos!

La ayuda del Exel es poco clara. No sé si se refiere a productos de renta fija como los bonos o a algo más general(*)

El caso es que supongamos que compro un bono el día 5/1/2010 con cupón corrido; que el cupón sea un 6% anuan pagadero los dias 15 de enero, 15 de abril, 15 de julio y 15 de octubre; que lo compre al 92% y que la fecha de vencimiento sea el 15 de julio del 2013. ¿Cómo meto los datos en el Excel para calcular el TIR?

____________

(*) TIR(valores;estimar)

Valores es una matriz o referencia a celdas que contengan los números para los cuales se desea calcular la tasa interna de retorno.

El argumento valores debe contener al menos un valor positivo y uno negativo para calcular la tasa interna de retorno.

TIR interpreta el orden de los flujos de caja siguiendo el orden del argumento valores. Asegúrese de introducir los valores de los pagos e ingresos en el orden correcto.

Si un argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, esos valores se pasan por alto.
Estimar es un número que el usuario estima que se aproximará al resultado de TIR.

Microsoft Excel utiliza una técnica iterativa para el cálculo de TIR. Comenzando con el argumento estimar, TIR reitera el cálculo hasta que el resultado obtenido tenga una exactitud de 0,00001%. Si TIR no llega a un resultado después de 20 intentos, devuelve el valor de error #¡NUM!

En la mayoría de los casos no necesita proporcionar el argumento estimar para el cálculo de TIR. Si se omite el argumento estimar, se supondrá que es 0,1 (10%).

Si TIR devuelve el valor de error #¡NUM!, o si el valor no se aproxima a su estimación, realice un nuevo intento con un valor diferente de estimar.

Observaciones

TIR está íntimamente relacionado a VNA, la función valor neto actual. La tasa de retorno calculada por TIR es la tasa de interés correspondiente a un valor neto actual 0 (cero). La fórmula siguiente demuestra la relación entre VNA y TIR:

VNA(TIR(B1:B6),B1:B6) es igual a 3,60E-08 [Dentro de la exactitud del cálculo TIR, el valor 3,60E-08 es en efecto 0 (cero).]

Ejemplos

Supongamos que desea abrir un restaurante. El costo estimado para la inversión inicial es de 70.000 $, esperándose el siguiente ingreso neto para los primeros cinco años: 12.000 $; 15.000 $; 18.000 $; 21.000 $ y 26.000 $. El rango B1:B6 contiene los siguientes valores respectivamente: 70.000 $, 12.000 $, 15.000 $, 18.000 $, 21.000 $ y 26.000 $.

Para calcular la tasa interna de retorno de su inversión después de cuatro años:

TIR(B1:B5) es igual a -2,12 por ciento

Para calcular la tasa interna de retorno de su inversión después de cinco años:

TIR(B1:B6) es igual a 8,66%

Para calcular la tasa interna de retorno de su inversión después de dos años, tendrá que incluir una estimación:

TIR(B1:B3;-10%) es igual a -44,35 por ciento

Pues yo aplicaria el método más sencillo:

TAE = (C'/C)^(1/t)-1

Donde t es el tiempo expresado en años desde el origen de la operación hasta el vencimiento. Simplemente pon laformula en el excel y pon una casilla donde puedas introducir el tiempo y ya está.

Un saludo,

¿Qué son C y C', os precios de compra y venta? ¿No tienes en cuenta el cupón? Para el ejemplo propuesto:

supongamos que compro un bono el día 5/1/2010 con cupón corrido; que el cupón sea un 6% anual pagadero los dias 15 de enero, 15 de abril, 15 de julio y 15 de octubre; que lo compre al 92% y que la fecha de vencimiento sea el 15 de julio del 2013.

¿Cuál es la rentabilidad de este producto bien en TAE o en TIR?

A la hora de calcular la TAE, la C' es el capital final y C el capital inicial. Evidentemente al tener en cuenta estos dos parametros estoy teniendo en cuenta el o los cupones intermedios.
Si quieres calcular la TIR, deberias plantearte que diferencias hay entre la TAE y la TIR??? Si calculas la TIR con los datos que me planteas, tendrias que ser consecuente de que los flujos netos de caja intermedios, llamese cupones en tu caso se reinvierten a la misma tasa de descuento, es decir a la TIR. Esto viene a decir algo asi como que la TIr de tu inversión te da un 7% en los proximos 3 años, implica que los cupones los vas a reinvertir a ese 7%, cosa que en la práctica es sumamente por no decir practicamente inviable. Si calculas la TAE, con la formula que te he comentado estaras realmente comparando lo que has invertido y lo que has recuperado, y obtendras realmente la rentabilidad real de tu inversión. Lo demás es un efecto ilusorio como lo que hace ing que te dice que te pagan el 3% TAE cuando realmente solo te estan pagando el 2,96% nominal. Si tengo un momento te expondré un ejemplo donde puedas ver todo esto muy claro y compruebes por ti mismo.

Estos ejemplos, quizás puedan orientarte un poco.
http://excelavanzado.com/xls/Bonos.xls

Saludos,
Valentin

...pero, para calcular la TAE, ¿no deberíamos también considerar que los cupones los reinvertimos? Si no hacemos esto, creo que lo que calculamos se parece más al interés nominal de un depósito que paga los intereses en periodos que son fracciones del año (meses, bimestres, o trimestres, cuatrimestres).

En cualquier caso, creo que sería fácil desarrollar una fórmula para calcular algo parecido al TAE de un bono.

Veamos si este ejemplo es cierto y así iré acercándome a lo que busco y a lo que realmente me interesa:

Si un depósito a plazo fijo paga tiene una duración de dos años y paga un interés del 4% anual nominal, lo que paga es un 1% trimestral y su TAE será la mitad (la inversa de periodo en años) de

1,01^(7/8) + 1,01^(6/8) + 1,01^(5/8) + 1,01^(4/8) 1,01^(3/8) + 1,01^(2/8) + 1,01^(1/8) + 1,01^(0/8) = 8,035% que dividido entre los 2 años da 4,0175%.

Pero con los bonos es más difícil por tres motivos:

1) Si compro un bono con descuento y paga un cupon tengo que equilibrarlo; por ejemplo, si compro un bono al 85% y el cupon es un 4% lo que recibo yo es 4*100/85 = 4,706%
2) Si lo compro entre dos fechas de pago de cupón deben descontarme del primer cupón la parte prorcional al tiempo trascurrido desde el pago anterior.
3) Si yo compre, por ejemplo 8.500 euros y en la fecha del vencimiento recibo 10.000 tengo que incrementarlo a las cantidades percibidas repartiéndolas entre los años que he tenido el cupón y teniendo en cuenta que esa diferencia de 1.500 euros no es lineal sino acumulativa con los años.

La cuenta de la vieja es la más practica: Compro por tanto y recibo un cupón de cuanto, por lo que la rentabilidad es tal.

Lo de la TIR son historias matemáticas para los que necesitan una precisión infinitesimal.

Saludos.

...y si además lo compara con la rentabilidad de un depósito que es un concepto muy sencillo mejor.

En el ejemplo anterior, si considero un bono comprado por ejemplo al 90% que tiene una duración de dos años y paga un interés del 4% anual nominal cada tres meses, lo que paga es un 1% trimestral

Por una parte tenemos el rendimiento del cupón. Aquí habrá que considerar

100/90 · [1,01^(7/8) + 1,01^(6/8) + 1,01^(5/8) + 1,01^(4/8) 1,01^(3/8) + 1,01^(2/8) + 1,01^(1/8) + 1,01^(0/8)] = 8,9278 y dividirlo entre el número de años que nos dará 4,4639%

Por otra parte, habrá que añadir el rendimiento de recibir el 100% cuando se pagó el 90%, es decir la raiz con índice igual al número de años (en este caso dos, o sea, cuadrada) de 100/90, restarle la unidad y multiplicarlo por 100. Eso nos dará en este caso 5,4093

Si sumamos ambas cantidades, el TAE de este bono será 9,87% aprox.

Para corregir el cupón corrido en lugar de utilizar la fracción de años utilizaremos deberemos pasar los intervalor temporales a días.