Vamos a explicar lo que es una referencia circular en Excel. Y lo mejor es poner un ejemplo.
Sigue estos pasos:
Abre un Libro nuevo.
El la celda A1 escribe un 1
En la celda A2 escribe la fórmula: =A1+1. Verás que el resultado es igual a 2.
En la celda A3 escribe la fórmula: =A2+1. Verás que el resultado es igual a 3.
Ahora en la celda A1 vamos ha sutituir el 1 que teníamos escrito por la fórmula: =A3+1. Verás que al pulsar INTRO entras en una referencia circular.
Dependiendo de la versión de Excel que tengas te avisa del error de una forma más o menos insistente. En versiones antiguas, te avisaba del error y si insistias en aceptarlo, veias abajo que ponia la palabra: CIRCULAR. Lo cual te indicaba que estabas en este caso.
En Excel 2007, los avisos son muy insistentes para que te des cuenta del error. Obtienes este mensaje de advertencia.
Una referencia circular se produce cuando en una fórmula se escribe una referencia a una celda que a su vez hace referencia a la celda que estamos escibiendo. A nivel ilustrativo equivale a intentar subirse uno mismo tirando de las solapas de la chaqueta, cosa que solo se sonsigue en los dibujos animados. Ya que una fórmula de una celda no puede dar un resultado que a su vez depende del propio resultado que proporciona esa misma celda. Podríamos decir que es 'la pescadilla que se muerde la cola'.
Cuando os salga un error de estos repasar las fórmulas y encontrareis el origen de fallo por algún lado.
Para ajustar los decimales podemos utilizar unos iconos con los que puedes meter y sacar decimales.
Excel siempre redondea, y hay que tener cuidado con creer que el resultado numérico que te esta dando es el correcto, ya que puede ser un simple redondeo. Por ejemplo, si el resultao es 6,51%, redondeando a cero decimales tu ves en la celda que pone 7%. Cuidado con este tema.
En el mercado están disponibles los siguientes bonos:
Bono A: Letra del Tesoro de duración un año y precio de adquisición 933 €
Bono B: Bono cupón explícito del 7% y duración 2 años que se adquiere con un descuento del 2% sobre el nominal.
Determinar el tipo de interés forward implícito para el periodo que se inicia en t=1 y finaliza en t=2 años.
Método 1
La clave está en la celda F21, en la que multiplicamos los flujos de caja de los bonos A y B en cruz para conseguir un flujo de caja igual a CERO. Luego copiamos la fórmula de esa celda hacia abajo.
En esta ocasión multiplicamos en cruz para conseguir anular el flujo de caja inicial del bono C. La fórmula de la celda F21, que luego copiaremos hacia abajo, es la siguiente:
=$E$21*D21-$D$21*E21
Observar que los dólares van en el sitio adecuado para conseguir que al copiar hacia abajo siempre estemos tomando una cierta combinación de bonos A y B, comprados y vendidos, con el fin de anular el flujo de caja inicial del bono C.
Método 2
Combinando los bonos A y B construimos el bono D que es un bono cupón cero a dos años, cuya TIR es r02. Luego aplicamos la fórmula:
(1+r02)^2=(1+r01)(1+r12)
Y despejamos r12 que es la incógnita.
Aplicamos la fórmula que relaciona el tipo forward con los tipos spot a un año y a dos años.
Creamos el bono D mediante réplica del bono cupón cero a dos años, empleando para ello la combinación adecuada de bonos A y B, según se ve en la fórmula de la celda F29:
=$D$29*E29-$E$29*D29
La TIR del bono D es el tipo spot (de contado) a dos años (r02). Luego simplemente hemos de despejar el tipo forward (r12). Ver la celda F34:
=(1+F32)^2/(1+D32)-1
Método 3
Consiste en inventarte el nominal del bono B, ya que el enunciado solo te dice el cupón 7% y el precio 98% ambos sobre el nominal que quieras. Eligiendo bien el nominal podemos conseguir que al restar los flujos de caja de los bonos A y B en el instante t=1, el resultado sea CERO. Este es nuestro objetivo, ya que pretendemos conseguir un bono C cuyo flujo de caja en t=0 sea cero, por tratarse de un forward r12.
Se crea un bono cupón cero que se compra en t=1 y se amortiza en t=2. Es decir, un bono (C) suma de los bonos A y B tal que el flujo en t=0 sea nulo. El forward solicitado (r12) será la TIR de ese bono.
Del bono A nos dan el precio de adquisición (933 €), y sabemos que el nominal es de 1.000 € por tratarse de una Letra del Tesoro.
En el bono B no nos dicen quién es el nominal, por lo que podemos suponer el importe que más nos interese. En este caso vamos a considerar un nominal que sea justo el necesario para que luego el precio de adquisición sea el mismo que el del bono A. Esto nos conviene para luego poder combinar cómodamente los bonos A y B y formar el bono C, de forma que el flujo de caja del bono C sea cero en t=0.
Como el nominal del bono B podemos inventárnoslo, podemos poner el que anule el flujo en t=1 del bono A (933€) (para crear el bono C). Como el precio de compra del bono B es el nominal con un descuento del 2%, si decidimos que el nominal del bono B sea el flujo que queremos hacer cero (933€) aumentado un 2%, ya tenemos lo que queríamos.
Explicándolo con números:
BONO C = BONO B - BONO A y en t =0 el flujo del BONO C tiene que ser 0.
En t = 0: Flujo en C = Flujo en B - Flujo en A = 0 (*)
Flujo en A = -933 € Flujo en B = Precio de compra del bono B = Nominal-(2%·Nominal) = Nominal·(1-0,02) = Nominal·0,98
Sustituyendo en (*): Nominal·0,98 - (-933 €) = 0, por tanto:
Nominal = -933 € / 0,98.
Por este motivo, hacemos que la celda E17 sea:
=-D13/0,98
Lo que nos da un nominal de 952,04 €, que luego en la celda E13, al calcular el precio del bono B, podremos convertir en 933 €, utilizando la fórmula:
=-0,98*E17
Con estos valores para el bono A y B es muy sencillo calcular el bono C, de forma que sea un forward r12, esto es, con un flujo de caja cero en t=0. Esto se consigue simplemente con la fórmula de la celda F13, que es una mera resta de los flujos de los bonos A y B, y luego copiada hacia abajo. La fórmula es:
=E13-D13
Se puede interpretar diciendo que si compramos un bono B y vendemos (o emitimos) un bono A, obtendremos una cartera que es la representada por el bono C.
Finalmente calculamos la TIR del bono C que coincide con el forward implícito r12 que nos han pedido.
En el caso de una renta en la que el tipo de interés varía a lo largo del tiempo, se ha de trasladar financieramente cada cuantía al tipo de interés vigente durante el tiempo que la cuantía atraviesa en su camino hacia el origen, si calculamos el valor actual, o hacia el fina, si calculamos el valor final. Por ejemplo, el último importe de 500 euros que vence en t=10, al llevarle hasta t=0 se ha de descontar durante 10 años, de los cuales durante 6 años lo hará al 7% y durante 4 años lo hará al 5%.
En la celda C22 calculamos el Valor Actual por el método 1 con la expresión:
=-(VA(0,05;4;500)+VA(0,07;6;500)*1,05^-4)
Lo que hacemos es utilizar la función VA por tramos.
En la celda D22 calculamos el Valor Final por el método 1 con la expresión:
=-(VF(0,05;4;500)*1,07^6+VF(0,07;6;500))
Observar que si la renta no fuera de cuantía constante no podríamos utilizar VA ni VF ya que estas funciones valoran rentas de cuantía constante.
El método 2 es válido para el caso de rentas de cuantía constante o variable. Para utilizar este método necesitamos unas columnas auxiliares:
Columna Factor. El factor es simplemente (1+i)
Columna Valor Actual: Calcula el valor actual de cada término de la renta. Para ello, divide la cuantía de cada término entre el producto de los factores necesarios para poder descontar esa cuantían hasta el origen de la renta. La celda F19 es: =+C19/PRODUCTO($E$10:E19). Es importante poner con dólares E10, pero no E19, para luego copiar la fórmula al resto de la columna.
Columna Valor Final: Calcula el valor final de cada término de la renta. Para ello, multiplica la cuantía de cada término entre el producto de los factores necesarios para poder capitalizar esa cuantían hasta el final de la renta. La celda G10 es: =C10*PRODUCTO(E11:$E$19). Es importante poner con dólares E19, pero no E11, para luego copiar la fórmula al resto de la columna.
Para calcular el VA y el VF por el método 2 únicamente nos queda sumar las columnas F y G, para así tener el VA y el VF acumulado de todos los términos de la renta, respectivamente.
Comprobación
Hemos efectuado dos comprobaciones de que el valor final es igual al valor actual capitalizado durante 10 años, de los cuales 4 trabajamos al 5%, y 6 al 7%.
La celda G23 es:
=+C22*1,05^4*1,07^6=D22
La celda G24 es:
=+C23*PRODUCTO(E10:E19)=D23
En la valoración de rentas a tipo de interés variable utilizaremos la función PRODUCTO aplicada a los factores (1+i), lo que nos permitirá capitalizar o descontar los términos de la renta aplicando en cada tramo el tanto que corresponda.
La primera renta de 4 términos anuales se capitaliza a su valor final hasta el instante t=4, y luego se capitaliza hasta t=10 lo cual supone multiplicar por (1+0,07) elevado a 6.
La segunda renta de 6 términos anuales se capitaliza hasta su valor final con lo que queda ya valorada en t=10.
Observe que todas las cuantías han quedado valoradas en t=10 y que cuando han atravesado la zona donde está vigente el tipo i=5% anual se han capitalizado a este tipo, y cuando han atravesado la zona donde está vigente el tipo i'=7% anual se han capitalizado a este otro tipo.
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.
Calcular el valor final de una renta pospagable, mensual, de 10 años de duración, que experimenta incrementos acumulados cada año del 5%, y que dentro de cada año es constante. La primera mensualidad es de 1.000 €. La séptima mensualidad de cada año no se entrega. Valorar al 10% efectivo anual.
Creamos a mano las cuantías de los 12 primeros meses. Para el mes trece generamos la cuantía con fórmula. La celda C30 es:
=+C18*$C$14
La idea es tomar el importe pagado 12 meses antes y multiplicarlo por la razón con dólares. De esta forma conseguiremos que todos los meses sean iguales al que se pagó justo 12 meses antes pero incrementado en un 5%. La salvedad del mes 7 del primer año se repetirá todos los años.
Una vez que hemos conseguido crear la columna C con los términos de la renta el cálculo del valor final se puede realizar por varios métodos.
Método 1
En la celda F13 utilizamos la fórmula:
=+VNA(C13;C18:C137)*(1+C12)^10
Lo que hacemos es calcular el valor actual con VNA y luego capitalizar 10 años al tanto efectivo anual.
Método 2
Requiere el uso de la función programada por el usuario que denominamos VFgeo. La celda F14 es:
La función VFgeo se aplica sobre una renta geométrica de términos anuales. Este es el motivo por el que es necesario anualizar los términos mensuales. Los pagos mensuales se llevan a final de año.
La anualidad del primer año es:
=VF(C13;12;-1000)-1000*(1+C13)^5
Este valor se denomina C, que corresponde a la primera cuantía anual de la renta geométrica de 10 años.
En este préstamo no cambia el tipo de interés cada año, sino que cambia un par de veces. Esto permite calcular la mensualidad del mes 60 sin necesidad de hacer todo el cuadro de amortización, ya que hasta el mes 60 únicamente cambia el tipo de interés en una ocasión.
Los pasos a seguir son estos:
Primero calculamos la primera mensualidad (a) sabiendo que el tipo de interés nominal será del 6%, con una duración de 10 años y sobre un principal de 500.000 €.
Calculamos el capital vivo o saldo financiero del préstamo al final del tercer año (mes 36). Calculamos C36 por el método prospectivo.
Calculamos la nueva mensualidad (a') que se comienza a pagar en el mes 37 y que será también la del mes 60. Para ello consideramos que debemos amortizar C36 en 7*12 meses a un tanto nominal anual del 8,10%.
Posteriormente podemos confeccionar el cuadro de amortización para comprobar resultados.
En la fila 10 y con color gris ponemos una celdas auxiliares, que inicialmente tomarán los valores que queramos, por ejemplo: 1, 2 y 3. Representan la ponderación con la que participará cada uno de los bonos A, B y C respectivamente. En esas celdas grises encontraremos al menos una con signo positivo y otra con signo negativo. El signo positivo indica número de veces que se ha de comprar el bono correspondiente y el signo negativo indica el número de veces que se ha de vender (o emitir) el bono correspondiente.
En este caso el ponderador del bono A es cero (celda C10) lo que indica que no es necesario utilizar el bono A para obtener el Forward que nos han pedido. El bono D se puede obtener comprando un cierto número de veces el bono B (celda D10) y vendiendo un cierto número de veces el bono C (celda E10).
En un mercado de renta fija cotizan los siguienes bonos
Bono A: es un bono cupón cero a un año que se adquiere por 100 y se amortiza por 110
Bono B: es un bono cupón cero a dos años con TIR del 9% y precio de adquisición de 500 €
Bono C: es un bono cupón cero a tres años con TIR del 8% y nominal de 1.000 €
Calcular el precio de un bono a tres años de cupón anual de 70 € y nominal de 1.000 €.
En color rosa ponemos los datos del problema y en color naranja la solución.
El procedimiento de resolución es el siguiente:
Creamos una tabla con los años de cero a tres. El instante cero es el momento de adquisición, o momento actual.
Ponemos o calculamos los flujos de caja de los bonos A, B y C.
Ponemos los flujos de caja del bono D, salvo el precio de adquisición que es la incógnita
Creamos la columna de la ETTI. Los valores de la ETTI son las rentabilidades de los bonos cupón cero a 1, 2 y 3 años.
Creamos una columna auxiliar con el factor de descuento
Calcular el precio del bono D se puede resolver con la función SUMAPRODUCTO. La fórmula de la celda F11 es la siguiente: =-SUMAPRODUCTO(F12:F14;H12:H14)
Si lo deseamos podemos comprobar los precios ya conocidos de los bonos A, B y C utilizando también la ETTI con la función SUMAPRODUCTO. Esto se hace en la fila 18.
El motivo de que utilicemos la ETTI es que el precio del bono se calcula descontando cada flujo de caja al tanto que opera en el mercado para cada plazo. Así, el primer flujo de caja de 70 € se descontará durante un año al 10%, el segundo flujo de caja de 70 € se descontará durante 2 años al 9%, y el tercer flujo de caja de 1.070 € se descontará durante 3 años al 8%.
La mayoría de los problemas en los que interviene una operación financiera se resuelven efectuando la denominada EQUIVALENCIA FINANCIERA. Ésta consiste en igualar los capitales de la prestación con los capitales de la contraprestación valorados todos ellos en el mismo instante del tiempo.
[Prestación]en t=T=[Contraprestación]en t=T
En nuestro ejemplo la corriente de pagos A constituye la Prestación, debido a que contiene el pago que se realiza en primer lugar. Y la corriente de pagos B constituye la Contraprestación.
Podemos representar el gráfico anterior en una sola recta, dotando de diferente signo a la prestación y a la contraprestación.
Si afrontamos el problema utilizando lápiz y papel podríamos hacer lo siguiente.
Primero
Elegimos el instante de valoración. Por comodidad puede ser t=0 o t=n=4. En este caso vamos a valorar el final de la renta, en t=4.
Este valor de X es el que hace prestación y contraprestación se encuentren equilibradas valoradas ambas en el mismo instante.
El método manual que hemos visto no es muy aconsejable si en lugar de manejar 3 capitales de la prestación y dos de la contraprestación su número fuera mucho mayor. Para una caso más complejo deberíamos recurrir a un método automatizado utilizando la hoja de cálculo. Esto es lo que se hace en el fichero anterior mediante dos métodos.
Método 1 (con VNA)
Planteamos una tabla con los periodos y los flujos de caja de A y B. La celda D16 permanece vacía ya que es la incógnita y aún no conocemos su valor.
En C17 y D17 calculamos el VAN de ambas corrientes monetarias, valoradas ambas al 10%. Si la operación estuviera equilibrada ambos VAN coincidirían. En este caso no coinciden ya que falta cuadrar la operación con el importe X que no conocemos. La diferencia entre ambos VAN será el valor de ese descuadre valorado en t=0 que es donde deja valorados los flujos la función VNA. Para calcular X, que vence en t=4, simplemente hemos de capitalizar ese descuadre (la diferencia de los dos VAN) hasta el instante t=4. Por tanto, capitalizamos al 10% durante 4 años la diferencias de los VAN de las corrientes A y B. El resultado obtenidio vuelve a ser X = 87,01 €.
Método 2 (con Solver)
Creamos una tabla similar a la del caso anterior, pero en la celda I16 nos inventamos un valor de la incógnita X, por ejemplo 100.
Calculamos los VAN en las celdas H17 e I17, y su diferencia en la celda J17. La diferencia inicialmente no es es cero, lo que indica que la operación no esta equilibrada, ya que nos hemos inventado el valor de X. Para conseguir calcular X, pedimos a Solver que haga que la celda J17 sea cero, y así despejar la celda I17 que nos dará el valor de X que estamos buscando.
Segunda parte del Problema
Calcular la reserva matemática por la derecha y por la izquierda en todos los instantes, representando gráficamente su evolución.
Podemos calcular la reserva matemática por la derecha (R+) y por la izquierda (R-) por los tres métodos:
Método recurrente
Método prospectivo
Método retrospectivo
Podemos seguir este ejemplo en el siguiente vídeo.
