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.
Puedes ver un ejemplo aquí:
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:
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.
Vídeo
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.
Vídeo
Hola Adolfo,
ResponderEliminarhe tenido que cambiar otra vez de ordenador (esto es un desastre ya). He vuelto a programar todas las funciones, solver, VAgeo,VFgeo... pero no encuentro la de BUSCARV y ni tampoco ningun link que lo enseñe.
¿me podrías echar un cable?
Hola Sofi.
ResponderEliminarLa función BuscarV no es necesario instalarla. La tienes en la categoría de Búsqueda y Referencia.
Para aprender a manejarla mira este link:
Función BUSCARV
Vale gracias¡¡ Por si acaso lo había hecho sin esa función pero la necesitaba para otros.
ResponderEliminarOtra cuestión, a veces al desplazar una celda con referencias hacia abajo, para desarrollarla, me da problemas y no me dan los valores correctos si no valores mucho mayores. Lo que me sorprende es que sólo a veces pasa, otras veces dan los resultados correctos, y siempre lo hago de la misma forma (control + click en la esquina inferior derecha de la celda a desarrollar)
¿Cómo pueden salir cosas distintas utilizando el mismo sistema? Siempre compruebo los símbolos de dólares, las referencias, etc.
Hola Sofi.
ResponderEliminarCuando tienes dos números (sin fórmula) en dos celdas, una debajo de otra, y seleccionas ambas y con el ratón arrastras hacia abajo tirando desde la equina inferior derecha, siempre te hará una SERIE.
Ejemplo: 100, 102, 104, 106, 108,....
Pero si en alguna de las dos celdas no existe un número sino una fórmula, lo que obtienes ya no es una serie.
Posiblemente te pase eso, ya que dices que unas veces te funciona bien y otras veces no.
Un saludo.
hola me podría explicar
ResponderEliminarcomo se realizaría a mano?
gracias
Hola Cris.
ResponderEliminarEste problema se podría hacer a mano pero no está pensado para ello, ya que nos piden la mensualidad del mes 122 y para ello tendríamos que recalcular el préstamo cada vez que cambia el tipo de interés y cada vez que se produzca una amortización anticipada.