Un préstamo de principal 10.000 € se contrata a tipo de interés fijo del 11,5% anual, amortizable mediante pagos anuales constantes de 1.647 € los s primeros años y b € los restantes. Si el capital vivo al cabo de s años es de 3.997,51 €, calcular b sabiendo que la duración total del préstamo es de 20 años.
Estudiar el problema 3.2 del libro de Cálculo financiero.
Método 1
Método 2
Vídeo
Método manual
Planteamiento
Primero dibujas el préstamo y ves que el principal Co se paga en base a dos rentas de términos anuales, durante 20 años, una de importa a=1.647 € durante los s primeros años y luego de importe b los restantes. Te piden calcular b, y te dan el tipo de interés i.
Montas una ecuación donde la incógnita es s.
Despejas s, tomando logaritmos y da como resultado que s=8
Ahora montas otra ecuación pensando en la última renta de importe b que se pagará durante 20-8=12 años, y que su valor actual ha de ser igual a C8 que es un dato conocido 3.997,51 €.
La amortización anticipada de un préstamo supone la entrega adicional de un cierto capital, que inicialmente no estaba previsto, en un momento intermedio de la vida del préstamo que se destina exclusivamente a la reducción del capital vivo existente en ese momento. Al reducirse la deuda pendiente que existe con el banco existen dos posibilidades, o bien se reduce el término amortizativo a pagar desde ese momento, o bien, se reduce la duración total del prestamo.
Caso 1. Reducción del término amortizativo
Sea un préstamo francés con las siguientes características.
Principal 1.000.000 €
Duración 10 años
Término amortizativo mensual constante
TIN 6% anual
Transcurridos 24 meses se efectúa una entrega de 200.000 € en concepto de amortización anticipada que reducirá la mensualidad pero no el plazo.
Calcular la nueva mensualidad.
Caso 2. Reducción del plazo
Continuando con los datos anteriores ahora vamos a suponer que la entrega de los 200.000 € se destinará a reducir el plazo del préstamo permaneciendo constante la mensualidad entregada.
Tendremos que calcular n que es el número de meses que quedan a contar desde el final del mes 24. Si los datos no se han establecido con intencionalidad lo más probable es que n sea un número fraccionario. Mucha casualidad sería que n resultase un número entero, sin decimales.
Tomaremos la parte entera de n. No se redondea, sino que se toma la parte entera, esto es, se truncan decimales. En nuestro caso n saldrá 68,78... meses. La parte entera es 68 meses. Pero si únicamente pagamos 68 mensualidades constantes de importe a no lograremos amortizar completamente el préstamo. Por este motivo, se acuerda con el banco que se realizarán los 68 pagos de importe a constantes más un pago adicional, un mes más tarde, con el importe X necesario para amortizar completamente el préstamo.
Se pide calcular X.
Para despejar n realizamos el siguiente procedimiento. Hay un paso en el que metemos logaritmo neperiano (ln) en ambos lados de la igualdad para que ésta no varíe y para luego poder despejar n.
Si quieres que las funciones VAgeo y VFgeo estén siempre disponibles en tu ordenador consulta cómo lograrlo introduciendo la macro en el libro PERSONAL. En el siguiente post se explica con un vídeo:
Function VAgeo(C, q, n, i)
If q = 1 + i Then
VAgeo = C * n / (1 + i)
Else
VAgeo = C * (1 - (q / (1 + i)) ^ n) / (1 + i - q)
End If
End Function
Function VFgeo(C, q, n, i)
VFgeo = VAgeo(C, q, n, i) * (1 + i) ^ n
End Function
Solver es un complemento de Excel que nos permite optimizar: calcular máximos, mínimos y llegar a valores objetivo. Nosotros lo utilizaremos de una forma similar a 'Buscar Objetivo', pero permite mucha mayor precisión. Esta precisión será necesaria cuando trabajemos con la función TIR.
Pasos
Archivo
Opciones
Complementos
Botón "Ir..."
Marcamos Solver
También es aconsejable marcar "Herramientas para análisis". Con esto triplicaremos el número de funciones disponibles. En finanzas es imprescindible para usar funciones tan útiles como TIR.NO.PER
También aprovechamos a marcar "Herramientas para el euro". Así conseguiremos disponer del icono de los euros (€).
Aceptamos
Ahora ya podemos ver Solver en el menú Datos
Estos pasos únicamente se han de hacer una vez. En las siguientes ocasiones ya estará disponible Solver en el menú Datos.
Solver es una aplicación desarrollada por una empresa ajena a Microsoft. En la foto puedes ver el magnífico lago que se ve desde el pueblecito donde esta ubicada esta empresa de software.
Una renta diferida es aquella en la que el punto de valoración es anterior al origen de la renta.
Supongamos una renta unitaria (de cuantía constante 1 €), de n términos. Deseamos valorarla en el instante t=α que es anterior al origen de la renta en k periodos. Valoramos a un tanto constante i.
El valor de la renta en t=α será igual al valor de la renta en t=0 descontado k periodos.
Por tanto la expresión que nos permite calcular el valor en alpha en función del valor de la renta unitaria pospagable es la siguiente:
Ejemplo
Supongamos una renta de 7 términos anuales constantes de 100 euros, valorada al 5% anual. Deseamos calcular la renta en t=0 y sabemos que la primera cuantía de 100 euros vence en el instante t=4.
En este caso podemos considerar la renta como pospagable y su valor actual quedaría en t=3. Luego tendríamos que descontar 3 años. El valor en t=0 será:
También podríamos considerar la renta como prepagable. En ese caso su valor actual quedaría valorado en t=4 y tendríamos que descontar 4 años.
En este caso el valor de la renta en t=0 se obtendría a partir de la renta prepagable.
Observar que al quitar los dos puntitos de la renta prepagable y pasar a una renta pospagable se ha de multiplicar por (1+i). Operando, "la misma base se suman los exponentes", obtenemos la misma expresión que antes. Por tanto, es indiferente que consideremos la renta como una pospagable y descontemos tres años, o que la consideremos como una prepagable y la descontemos 4 años. Al final llegamos al mismo valor de la renta en t=0.
Esto es así porque realmente podemos establecer el instante t=0 del eje de tiempos donde realmente nos resulte más conveniente.
En el gráfico anterior utilizamos dos ejes de tiempo. El segundo eje, en color verde, establece el instante t=0 donde el primer eje tenía el instante t=3. Con este segundo eje, se ve mejor que la renta es una renta que estamos considerando como pospagable y que luego se ha de descontar tres años, ya que es una diferida.
Supongamos una renta de 7 términos anuales constantes de 100 €, valorada al 5% anual. La primera cuantía de 100 € vence justo a los 4 años del momento actual (en t=4). Nos piden calcular el Valor Actual de la renta.
El valor de la renta en t=0 puedes calcularlo de estas dos formas:
=VA(5%;7;-100)/1,05^3 (suponemos la renta pos)
=VA(5%;7;-100;;1)/1,05^4 (aquí la renta es pre)
VA=499,85 €.
Vencimiento de la primera cuantía en las rentas pospagables y prepagables
Cuando tenemos una renta pospagable conviene observar que el instante t=0 no se corresponde con el vencimiento de la primera cuantía. En las rentas pospagables la primera cuantía está un periodo más allá del instante de valoración.
Por el contrario, en las rentas prepagables el vencimiento de la primera cuantía cae justo en el instante de valoración.
En las rentas pospagables, la primera cuantía vence en t=1, y su valor actual se calcula en t=0, por tanto ente ambos hay un periodo de diferencia.
Por el contrario, en las rentas prepagables el primer término vence en t=0 y su valor actual se calcula justamente en t=0.
Sea un préstamo a tipo variable con las siguientes características:
* Principal 900.000 euros
* Duración 12 años
* Tipo variable: Euribor + 0,75%, con revisión anual.
* El Euribor el primer año ha sido del 2% y luego ha crecido 0,1% cada año.
Al final del año 8 se entregan 100.000 euros, y al final del mes 122 se entregan 50.000 euros, en concepto de amortización anticipada.
Calcular la mensualidad del mes 144.
Método 1
Hemos creado un nombre de rango. Se denomina tabla y es el rango: L23:O34.
La celda C30 se copia hacia abajo y es:
=C18+1
La celda D18 es:
=BUSCARV(C18;tabla;4)
siendo el rango tabla el L23:O34. Si no nos gusta nombrar rango debemos cambiar la fórmula anterior por la siguiente:
=BUSCARV(C18;$L$23:$O$34;4)
sin olvidar los dólares, ya que esta expresión se ha de copiar hacia abajo.
La mensualidad es E18:
=PAGO(D18;144-B17;-H17)+J18
observar que se copia hacia abajo y va sin dólares.
Los intereses también van sin dólares. La celda F18 es:
=H17*D18
Método 2
El método 2 no necesita utilizar BUSCARV para obtener la columna del tipo de interés mensual.
BuscarV
La función BUSCARV se utiliza para hacer una búsqueda vertical en una tabla. Revisa este Post:
En nuestro caso la utilizamos para obtener la columna del tipo de interés mensual correspondiente a cada año. Para ello se hace una tabla con el Euribor de cada año, el Euribor más el diferencial, y el Tanto mensual equivalente. Esta tabla del Euribor hemos de nombrarla, con un nombre de rango.
Si ves que te da error, posiblemente sea porque no has nombrado el rango, que en este caso se llama: tabla
El rango tabla es: L23:O34
Así la fórmula de la celda D139 te funcionará bien:
=BUSCARV(C139;tabla;4)
Si no te gusta nombrar rangos puedes cambiar la fórmula anterior por esta:
=BUSCARV(C139;$L$23:$O$34;4)
No olvides los dólares, ya que tendrás que copiar esta fórmula a toda su columna, para obtener la tasa mensual de todos los periodos.
Pregunta
Una pregunta operativa,
Si le doy un nombre a un conjunto de celdas (tabla), igual que hacemos con la tabla de
intereses, y veo que me he equivocado, ¿cómo puedo quitar el nombre a esa tabla?.
Supongamos que ya he guardado el fichero y no puedo deshacer la operación.
Respuesta
Si tienes la versión 2007, para gestionar los nombres de rango debes ir a:
Fórmulas
Administrador de nombres
Donde tienes las opciones: Nuevo, Editar y Eliminar
Si tienes la versión de Excel 2003 o anteriores, puedes llegar a una ventana similar a ésta, donde podrás gestionar los nombres de rango. Está en: Insertar, Nombre, Definir.
Sea un préstamo a tipo fijo con las siguientes características:
* Principal 700.000 euros
* Duración 8 años
* TIN 6%
Al final del mes 30 se entregan 100.000 euros, en concepto de amortización anticipada que permite reducir la duración del préstamo. Calcular la última mensualidad.
Se elabora el cuadro de amortización con la mensualidad inicialmente calculada. Al final del mes 30 se añaden los 100.000 euros en concepto de amortización anticipada. Para considerar este aporte adicional hemos creado una columna adicional y la fórmula de la mensualidad suma el importe de esta columna. Así, la celda D17 es:
=PAGO($L$19;$L$17;-$L$15)+I17
Esta fórmula se copia hasta el final de su columna.
Al añadir los 100.000 euros de amortización anticipada observamos que al final comienzan a producirse capitales vivos negativos. Eliminamos todas las filas donde se encuentran los capitales vivos negativos, salvo la primera de ellas. En este caso resulta ser la fila del mes 82.
Ahora debemos ajustar los valores del mes 82 para lo cual hacemos coincidir la cuota de amortización A82 con el capital vivo del periodo anterior. Por tanto, la celda F98 es:
=+G97
Finalmente hacemos que D98 sea:
=+F98+E98
ya que la última mensualidad debe cumplir, al igual que todas las demás, que es igual a la Cuota de Intereses más la Cuota de Amortización.
Observar como al efectuarse el pago de los 100.000 € en concepto de Amortización Anticipada no se modifica la mensualidad posteriormente. Sigue siendo de 9.199 €.
Al no modificarse la mensualidad lo que sucede es que se acorta la duración del préstamo. Ya no es necesario ir hasta los 96 meses para amortizar completamente el préstamo. Observamos que en el mes 82 conseguimos obtener un capital vivo incluso negativo. Por eso es necesario ajustar el último mes. Para ello hacemos que la última cuota de amortización coincida con el capital vivo del mes 81. Esto es, hacemos iguales las dos celdas verdes.
Finalmente ajustamos la celda de color naranja que es la última mensualidad y hacemos que sea igual a la suma de la cuota de intereses más la cuota de amortización.
Veamos las fórmulas del cuadro de amortización. La clave está en la fórmula del término amortizativo.
Método 2
En la celda F116 calculamos la mensualidad constante a, con la función PAGO.
En F117 calculamos el Capital Vivo C30 por el método prospectivo, con la función VA.
Si quisiéramos utilizar el método retrospectivo la expresión empleada debiera ser la de la celda G127:
=+C116*(1+C120)^30-VF(C120;30;-F116)
En F119 calculamos n con la expresión:
=+NPER(C120;F116;-F118)
Tomamos la parte entera que es 51.
Calculamos el Capital Vivo en t=51, que es C51, por el método retrospectivo. Eso se calcula en la celda F21 con la siguiente expresión:
=+F118*(1+C120)^F120-VF(C120;F120;-F116)
La última mensualidad a52, en F122, se obtiene capitalizando el capital vivo anterior.
Vídeo
.
Para calcular n hemos de despejar tomando logaritmos. Veamos cómo se consigue despejar.
