martes, 5 de octubre de 2010

Referencia circular

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.

Ajuste de los decimales

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.

Forward implícito

Puede descargar el archivo de Excel forward_implicito_01.xlsx



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

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 cupon 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 forwar implícito r12 que nos han pedido.

Método 2

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 3

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 forwar (r12). Ver la celda F34:

=+(1+F32)^2/(1+D32)-1

Renta valorada a dos tipos de interés

Puede descargar el archivo de Excel renta_a_tipo_variable_01.xlsx


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.


Audio

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.

Renta geométrica fraccionada con faltas

Puede descargar el archivo de Excel renta_geometrica_fraccionada_con_faltas_01.xlsm



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:

=+vfgeo(VF(C13;12;-1000)-1000*(1+C13)^5;C14;10;C12)

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.

Calculotes:


Audio

lunes, 4 de octubre de 2010

Mensualidad de un préstamo a tipo variable

Puede descargar el archivo de Excel prestamo_variable_01.xlsx


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:

  1. 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 €.
  2. 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.
  3. 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.

viernes, 1 de octubre de 2010

Cálculo de un tipo Forward utilizando Solver

Puede descargar el archivo de Excel forward_con_solver_01.xlsx


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).

Valoración con la ETTI

Puede descargar el archivo de Excel valora_con_ETTI_01.xlsx



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:


  1. Creamos una tabla con los años de cero a tres. El instante cero es el momento de adquisición, o momento actual.
  2. Ponemos o calculamos los flujos de caja de los bonos A, B y C.
  3. Ponemos los flujos de caja del bono D, salvo el precio de adquisición que es la incógnita
  4. 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.
  5. Creamos una columna auxiliar con el factor de descuento
  6. 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)
  7. 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%.

Equivalencia Financiera para calcular un capital

Puede descargar el archivo de Excel equivalencia_financiera01.xlsx


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.

100(1+0,1)^4+100(1+0,1)^3+100(1+0,1)=250(1+0,1)^2+X

Segundo

Despejamos la incógnita X.

X=100*1,1^4+100*1,1^3+100*1,1-250*1,1^2
X = 87,01 €

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.


Audio

jueves, 30 de septiembre de 2010

Calculadoras de Hipotecas

En las páginas web de algunos bancos existen calculadoras financieras que permiten a los usuarios calcular la cuota mensual (lo que nosotros llamamos término amortizativo).
Os dejo un ejemplo de un portal inmobiliario.



Otra página:



Pongamos un ejemplo:
  • Principal: 500.000 euros
  • 20 años
  • Tipo de interés: 6% todos los años
La cuota que nos da la web es: 3.582,16 euros.
Comprobarlo con Excel. Usar la función PAGO.
Observar que el 6% la web lo toma como un TIN. Esto es, para calcular el tanto mesual efectivo se ha de dividir entre 12.

La web es Hipotecas y Euribor y tienes diferentes tipos de simulaciones de hipotecas y en varios formatos (en html o excel).

Otra página de
CALCULADORA DE HIPOTECAS
que proporciona incluso el cuadro de amortización.

Os invito a que localicéis otras calculadoras financieras o calculadoras de hipotecas o préstamos que circulan por Internet.

Valor Actual y Valor Final de una renta

Puede descargar el archivo: va_vf.xlsx

Para calcular el Valor Actual (VA) de una renta se han de descontar hasta t=0, todos y cada uno de los flujos de caja de la renta. Se puede descontar de forma individual cada cuantía, y la suma de todos los valores actuales individuales constituirán el valor actual de la renta.

En la valoración de rentas siempre se utiliza la ley de capitalización compuesta para capitalizar, o la ley de descuento compuesto para descontar.

  • Ley de Capitalización Compuesta: Cn=Co(1+i)^n
  • Ley de Descuento Compuesto: Co=Cn(1+i)^-n
Como se puede ver ambas leyes en realidad se operan con la misma fórmula. La diferencia está en que al capitalizar calculamos el montante final Cn en función del capital inicial Co, y al descontar se efectúa la operación contraria, pero la fórmula utilizada es la misma y varía según despejemos Cn o Co.


Si la renta es unitaria, pospagable su valor actual es:

Vo=(1+i)^-1+(1+i)^-2+(1+i)^-3+....+(1+i)^-n


Cada uno de los anteriores sumandos es el valor actual de cada uno de los euros que vencen en:
t=1, 2, 3,...n respectivamente.


Los sumando de la expresión anterior surgen al descontar el número de periodos necesarios cada uno de los euros que constituyen los términos de la renta.

  • El primer sumando (1+i)^-1 surge de descontar un euro durante 1 periodo
  • El segundo sumando (1+i)^-2 surge de descontar un euro durante 2 periodos
  • El tercer sumando (1+i)^-n surge de descontar un euro durante 3 periodos
  • ...
  • El n-ésimo sumando (1+i)^-n surge de descontar un euro durante n periodos


Valor Actual de una renta Unitario y Pospagable

Para calcular el Valor Final (VF) de la renta podemos llevar cada uno de esos euros hasta el final de la renta, hasta t=n. El Valor Final es:

Vn=1+(1+i)+(1+i)^2+(1+i)^3+....+(1+i)^(n-1)

Cada uno de los sumandos anteriores es el valor final de cada uno de los euros que vencen en:
t=n, (n-1), (n-2), (n-3), .... , 1 respectivamente.


Los sumando de la expresión anterior surgen al capitalizar el número de periodos necesarios cada uno de los euros que constituyen los términos de la renta.
  • El primer sumando 1 surge al capitalizar un euro durante 0 periodos, ya que el euro que vence en n no se mueve.
  • El segundo sumando (1+i) surge al capitalizar un euro durante 1 periodo
  • El tercer sumando (1+i)^2 surge al capitalizar un euro durante 2 periodos
  • El cuarto sumando (1+i)^3 surge al capitalizar un euro durante 3 periodos
  • ...
  • El n-ésimo sumando (1+i)^(n-1) surge al capitalizar un euro durante n-1 periodos


Valor Final de una renta Unitaria y Pospagable

Aunque para calcular el Vn, conocido el Vo, lo mejor es capitalizar n periodos al tanto i.

Vn=Vo*(1+i)^n

Siempre se cumple que: El valor final de una renta es igual a su valor actual capitalizado n periodos.

No confundamos la palabra "renta" con la palabra "término". La renta es el conjunto de capitales distribuido a lo largo del tiempo, y es la que pretendemos valorar, normalmente en el origen (Valor Actual), o en el final de la renta (Valor Final).

Los términos de la renta, son cada uno de los capitales que la componen. Capitalizando o descontando cada uno de esos capitales, y luego sumando ese resultado, es como obtenemos el valor financiero de la renta.


Un ejemplo de cálculo del VA y VF por varios métodos lo encontrará en el archivo va_vf.xlsx cuyo enlace está al inicio de este post.

En ese fichero se muestra como calcular el VA y el VF de rentas PRE y POSpagables.


Audio

Incrustar imágenes

Al crear un Blog como éste podemos incrustar imágenes con las herramientas propias del Blog. Otro sistema para incrustar imágenes es acudir a un 'almacen' externo en el que podamos subir nuestras imágenes y luego incrustarlas en nuestro Blog. Este 'almacen' externo nos proporciona una dirección URL (una dirección web) que nos permitirá enlazar la imagen en el Blog o en un FORO. Y esta es la idea, poder crear entradas en un Foro insertando imágenes.

Los 'almacenes' de imágenes más importantes de la web están relacionados en este Post:

Best Free Image Hosts (HotLinking allowed, No Bandwidth Limits…)

De los mencionados en ese artículo a mi me funciona muy bien ImageShack.


Te puedes registrar o no.

La página es: http://imageshack.us



Los pasos son:
  1. Primero debes tener la imagen que quieras subir almacenada en tu ordenador. Si quieres hacer una captura de pantalla, la puedes obtener, por ejemplo, con un magnífico programa llamado SnagIt. Luego puedes reducir su tamaño con un estupendo programa gratuito llamado Fotosizer.
  2. Vas a la página de ImageShack y pulsas sobre el botón Browse. Esto te permitirá navegar por las carpetas de tu ordenador hasta llegar a seleccionar la imagen que quieres subir.
  3. Pulsas sobre el botón Upload Now, o si lo tienes puesto en español, veras que el botón se denomina: Cargar ahora.
  4. De todos los enlaces que aparecen elige el que pone: Direct LinkLo seleccionas y lo copias con Control+C. De esta forma ya tienes copiado el enlace en tu portapapeles. Si quieres puedes hacer la prueba de pegar esa dirección URL en la barra de navegación de tu navegador (con Control+V) y verás en tu navegador la imagen con la que estas trabajando.
  5. Copiar el Enlace directo
  6. Ahora debes ir donde quieras pegar la imagen, por ejemplo, en el Foro. Normalmente los foros permiten no solo escribir texto sino también insertar imágenes. Para ello debes elegir el botón que permita no solo escribir texto puro sino también introducir código HTML. Pulsa sobre el botón que pone: Editor de HTML
  7. Pulsar sobre el botón: Editor de HTML
  8. Ahora podrás ver un Editor con más posibilidades, y entra ellas está la posibilidad de insertar imágenes. Esto se hace pulsando en el icono denominado: Insertar imagen.


    Pulsar sobre el icono: Insertar imagen
  9. Surge una ventana  en la que debemos pegar (con Control+V) el enlace directo que habíamos capturado de la página de Direct Link Donde pone Origen se ha de añadir la URL o dirección web que tenemos anotada en el portapapeles. Esto se hace pulsando Control+V.
  10. Donde pone Origen es donde se ha de añadir la URL
  11.  De esta forma conseguiremos incrustar imágenes en un mensaje que dejemos en el Foro. Esto es muy enriquecedor al intercambiar ideas en un foro ya que una imagen vale más que mil palabras.

miércoles, 29 de septiembre de 2010

Capturas de Pantalla

Cuando se utiliza Solver o Buscar Objetivo en la celda resultante no queda ninguna fórmula. Lo que queda es un valor numérico, generalmente con muchos decimales.
Para demostrar/comprobar que ese valor numérico proviene del uso de una de estas herramientas se ha de realizar una captura de pantalla, que es como una foto que tomamos de la pantalla.

Captura de Pantalla

Se hace pulsando la techa "Imprimir Pantalla" del teclado. Normalmente está en la parte superior derecha del teclado, y en abreviatura suele poner "Impr Pant" o si está en inglés puede que ponga "Print Screen" o una abreviatura.
Esto lleva la imagen (la captura de pantalla) al portapapeles, y luego lo que debes hacer es pegarla en la hoja de Excel. Para ello te sitúas en una celda vacía, y en una zona vacia de la hoja de cálculo y pegas el contenido del portapapeles, por ejemplo con Control + V.
Lo malo de esto es que se copia el contenido de la pantalla completa. Aunque podemos copiar únicamente el contenido de la ventana activa.

Captura de pantalla de la Ventana Activa

Para calturar únicamente la ventana activa se ha de pulsar la tecla Alt junto con la tecla "Impr Pant":
Alt + Print Screen
Esto es lo que debemos hacer al capturar la imagen de SOLVER. Cuando tengamos activa la ventana de Solver con todas las variables y parámetros colocados correctamente, enconces es cuando debemos pulsar: Alt + Imp Pant.
Y luego pegar la captura con Ctrl + V.

Para recortar la imagen

El propio Excel dispone de la posibilidad de recortar una imagen incrustada. Si tienes la versión de Excel 2003 pincha con el botón derecho en la imagen y en Herramientas de imagen pon "Recortar" y así la puedes reducir todo lo que quieras.
En la versión 2007 con hacer doble click en la imagen se te habilita la barra de herramientas de la imagen y seleccionas "Recortar".

Capturas de pantalla profesionales

Existe un maravilloso programa que hace muy versátiles las capturas de pantalla.  Se llama SnagIt. Muy recomendable.

martes, 28 de septiembre de 2010

Bibliografía


En el campo de las Matemáticas Financieras existen infinidad de libros en español. Uno recomendable para seguir nuestros casos prácticos es el siguiente.

Título: Cálculo Financiero. Teoría y problemas.
Autores: Adolfo Aparicio, Rocío Gallego, Antonio Ibarra, José Ramón Monrobel
Editorial: Paraninfo
Edición: 3ª edición
Tema: Finanzas
Páginas: 294
Formato: 17 X 24 cms
ISBN: 9788428340342
Año: 2017

Índice


1. Conceptos básicos. Operaciones financieras simples
2. Rentas financieras. Operaciones financieras compuestas
3. Operaciones de amortización. Préstamos
4. Activos de renta fija.
Apéndice A. Cálculo del VAN y de la TIR en Excel.
Apéndice B. Cuentas corrientes bancarias.
Apéndice C. Sucesiones y series geométricas
Bibliografía

Enlaces a librerías que disponen del texto.

    Formación básica en Excel

    Para acometer nuestra tarea de valoración financiera con Excel necesitamos una formación básica en esta popular Hoja de Cálculo.

    Es recomendable disponer de un libro o tutorial que nos permita esta formación previa. En la biblioteca de tu facultad tienes seguramente bastantes libros de Excel, o bien en la biblioteca más cercana a tu casa (por ejemplo, alguna municipal).

    Utilizaremos preferiblemente la versión 2010. También esta bien la versión 2007. Si tienes la versión 2003 o anteriores, piensa que es hora de actualizarte, ya que la interface de usuario ha cambiado mucho.

    Últimamente con los ordenadores nuevos regalan la versión 2010 Starter, esta versión no nos vale, ya que no dispone de muchísimas herramientas y menús de Excel, es pomo más que un visualizador de ficheros.

    Os animo a ir mirando aspectos de Excel, ya que para nuestros cálculos financieros utilizaremos intensivamente esta herramienta. En concreto es necesario conocer los temas más básicos y luego ir mirando algún otro de nivel algo mayor.

    En cuento a FUNCIONES:

    1. SUMA
    2. multiplicar con el asterisco: *
    3. elevar con el acento circunflejo francés: ^
    4. PRODUCTO
    5. BUSCARV
    6. REDONDEAR
    7. ENTERO
    8. LN
    9. ALEATORIO
    10. SUMAPRODUCTO
    11. PROMEDIO
    12. MEDIA.GEOM
    13. MIN
    14. MAX
    15. concatenar con &
    16. SI
    17. Y
    18. O
    19. NO
    20. RESIDUO
    21. ESERROR
    22. ESERR
    Otros temas que es conveniente conocer:
    1. Los Dólares en Excel
    2. Solver y Buscar Objetivo
    3. Formato condicional
    4. Insertar comentarios
    5. Copiar y pegar con pegado especial valores
    6. Crear Rangos y usarlos en las fórmulas
    7. Manejar bien el Formato de Celda

    Instalar los Complementos:
    1. Herramientas para análisis
    2. Solver
    Por Internet circulan infinidad de manuales de Excel, por ejemplo éste: Biblia Excel 2007.

    jueves, 24 de junio de 2010

    ¿Qué signo pongo en los argumentos de las funciones financieras?

    Función VA

    En la función VA deben tener el mismo signo el argumento 'pago' y el argumento 'vf'.

    =VA(tasa;nper;pago;vf;tipo)


    Ejemplo

    Calcular el principal de un préstamo contratado al 10% anual, pagos anuales de 1.000 euros durante 10 años. Al final del décimo año el capital vivo es de 20.000 euros.

    =+VA(10%;10;1000;20000)

    Esto da un número negativo: -13,855.43 €

    Para que de positivo tienes dos alternativas:
    • Primera: =-VA(10%;10;1000;20000)
    • Segunda: =+VA(10%;10;-1000;-20000)

     Como ves lo importante es que tengan el mismo signo los argumentos 'pago' y 'vf'.



      
    Función VF

      

    Para la función VF sucede lo mismo. Lo importante es que los argumentos 'pago' y 'va' tengan el mismo signo.


    =VF(tasa;nper;pago;va;tipo)


    Ejemplo

    Se quiere constituir un montante M aportando 1.000 euros al final de cada año, durante 10 años, más una aportación al inicio (en t=0) de 20.000 euros. Calcular M valorando al 10% anual.



     Si utilizas la expresión:


     =+VF(10%;10;1000;20000)


     obtendrás un valor negativo: -67,812.27 €


      
    Para que de positivo tienes dos alternativas:
    • Primera: =-VF(10%;10;1000;20000)
    • Segunda: =+VF(10%;10;-1000;-20000)

      
    Como ves lo importante es que tengan el mismo signo los argumentos 'pago' y 'va'.

    Función PAGO

      

    Para la función PAGO, los argumentos 'va' y 'vf' deben tener signo contrario.


     =PAGO(tasa;nper;va;vf;tipo)


      
    Ejemplo


    Calcular la anualidad de un préstamo de principal: Co = 13,855.43 €, valorado al 10%, del que sabemos que a los 10 años el capital vivo aún es de 20.000 €.


    =PAGO(10%;10;-13855,43;20000)


    El resultado es: 1.000,00 €


    En este caso lo importante es que los argumentos 'va' y 'vf' tengan distinto signo.



    Función TASA

      

    Para la función =TASA, los argumentos 'pago' y 'va' deben tener distinto signo, y si existe un valor para el argumento 'vf' éste debe tener el mismo signo que 'pago'.


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


    Ejemplo


    Calcular el tipo de interés anual al que se pactó un préstamo cuyo principal es de 13.855,43. Sabemos que tras efectuar 10 pagos de 1.000 € anuales, aún queda un capital vivo de 20.000 € al final del décimo año.

    =TASA(10;1000;-13855,43;20000)

      
    Esta expresión da como resultado: 10%



    Otra forma de obtener ese 10% es así:

    =TASA(10;-1000;13855,43;-20000)

    Lo importante es que los argumentos 'pago' y 'vf' tengan el mismo signo, y que éste sea distinto del signo del argumento 'va'.


      
    Como podemos ver no existe una regla común para todas las fórmulas, y cada una tiene sus peculiaridades. El consejo es que resuelvas los problemas por varios métodos y no lo fies todo a una sola fórmula.

    miércoles, 26 de mayo de 2010

    Capital Vivo de un préstamo francés

    Se contrata un préstamo francés de 200.000 €, a 10 años, al 6% TIN, con pagos mensuales. Calcular el capital vivo al final del tercer año.

    Co=200.000 €
    n= 120 meses
    i12=6%/12=0,5% efectivo mensual
    C36=?

    Primero calculamos la mensualidad constante (a)

    =PAGO(0,005;120;-200000)

    a=2.220,41 €

    Método 1

    Reserva matemática por el método prospectivo:

    =VA(0,005;120-36;-2220,41)

    C36=151.993,82 €

    Método 2

    Reserva matemática por el método retrospectivo:

    =200000*1,005^36-VF(0,005;36;-2220,41)

    C36=151.993,82 €

    Método 3

    Método alternativo con VF

    =+VF(0,005;36;2220,41;-200000)

    Este método es curioso

    viernes, 21 de mayo de 2010

    Cálculo del precio de un bono con la ETTI

    Puede descargar el archivo de Excel precio_etti.xlsx

    La ETTI es la Estructura Temporal de los Tipos de Interés,o también denominada Curva de Tipos.

    Nos proporciona los tipos de interés a los diferentes plazos. Se forma con las rentabilidades de los Bonos Cupón Cero a esos plazos.

    Ejemplo

    En un mercado de bonos la ETTI a un año es del 8%, a dos años del 9%, y a tres años del 10%. Calcular el precio de un bono cupón explícito del 5%, de nominal 1.000 €, que madura a los 3 años.

    La ETTI viene dada por la siguiente tabla.



    Y su gráfico pudiera ser el siguiente.



    Podemos representar gráficamente el bono con sus flujos de caja y los tantos de valoración de la ETTI.


    El cálculo del precio del bono se realiza con la siguiente operación.


    Para resolver este caso en Excel seguimos estos pasos:


    1. Necesitamos una columna con los años, desde el instante t=0 hasta t=3. Columna B
    2. Escribimos todos los flujos de caja de los instantes t=1, 2 y 3. La celda correspondiente al instante t=0 es nuestra incógnita. Esa celda (de color amarillo) será la que contenga el precio del bono.
    3. Creamos una columna con los valores de la ETTI, desde t=1 hasta t=3
    4. Creamos una columna adicional con el denominado Factor de Descuento. La celda E6 es: =+(1+D6)^-B6. Y se copia hacia abajo
    5. El precio se calcula en la celda amarilla con la expresión: =-SUMAPRODUCTO(E6:E8;C6:C8)
    6. La TIR se calcula con la expresión: =TIR(C5:C8)

    Cálculo del Precio de un Bono con la TIR

    El precio de un bono es el valor actual de los flujos de caja que promete el bono a futuro, descontados a su TIR.

    Un Bono paga el cupón periódico y al final, junto con el último cupón, nos abona el nominal.

    Ejemplo

    Calcular el precio de un bono a 3 años, que proporciona un cupón anual del 5%, y su nominal es de 1.000 €, sabiendo que su TIR en ese momento es del 10%.


    Se trata de calcular el Valor Actual de los flujos de caja que el bono paga durante estos tres años. La tasa de descuento utilizada será la TIR del bono: r=10%.


    En Excel podemos calcular el precio con la función VNA y con VA:

    =VNA(10%; Flujos)

    =VA(10%;3;-50;-1000)

    martes, 18 de mayo de 2010

    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.

    lunes, 17 de mayo de 2010

    Capital vivo del penúltimo periodo

    Un préstamo de 800.000 €, que se contrató al 8% nominal anual, debe reembolsarse mediante n entregas trimestrales constantes (a). El Capital Vivo del trimestre n-2 asciende a 56.780,17. Calcular a y n.





    Para calcular a con Excel podemos emplear la función PAGO y para calcular n podemos utilizar NPER.

    Italiano más Francés

    Se concede un préstamo  de 900.000 €, al 6% nominal anual, durante 25 años, con pagos mensuales. Los 10 primeros años se caracterizan por tener una cuota de amortización constante (A), y el resto del tiempo el término amortizativo es constante (a). Calcular A sabiendo que el capital vivo al final del año 20 es de 100.000 €.

    viernes, 14 de mayo de 2010

    Número de periodos de un francés

    Un préstamo francés de n términos anuales se emite al 6% anual. Conocemos la cuota de amortización del 6º año que es de 1.007,79 €, y el saldo financiero al inicio del último año, que es de 1.702,64 €.
    Calcular n.

    jueves, 13 de mayo de 2010

    Cuota de amortización del último periodo

    Un préstamo italiano se concedió a 15 años a un tipo fijo del 10% nominal anual con pagos trimestrales. La última trimestralidad es de 1.000 €.
    Calcular el principal.

    miércoles, 12 de mayo de 2010

    Precio de un bono conocida la ETTI



    En el mercado cotizan los siguientes bonos:


    Bono A. Bono cupón cero a un año. TIR del 3%


    Bono B. Bono cupón cero a dos años. TIR del 4%


    Bono C. Bono cupón cero a tres años. Se adquiere por 800 € y se amortiza por 920


    Bono D. Bono cupón explícito a tres años del 10% anual. Nominal 1.000 €


    Calcular el precio del bono D.

    martes, 11 de mayo de 2010

    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


    Dos capitales Vivos de un Préstamo Francés

    Se concede un préstamo francés de términos amortizativos mensuales, al 4,20% nominal anual. Conocemos los capitales vivos al final de los meses 40 y 100, que son 497.212,076531941 y 313.412,818130387.
    Calcular:

    1. El importe de la mensualidad constante
    2. El principal del préstamo
    3. La duración del préstamo



    TIN = 4,20%
    C40  = 497.212,076531941
    C100 = 313.412,818130387

    Solución


    dos_cap_vivos.xlsx


    sábado, 24 de abril de 2010

    Francés con pocos datos

    Puede descargar el archivo de Excel frances_escaso.xlsx


    Este es un ejemplo que permite comprobar la enorme utilidad de Solver en el campo financiero. Con escasos datos conseguimos completar el cuadro de amortización de un préstamo francés.

    Esta es una solución analítica: frances_con_pocos_datos.pdf