Solicitamos un préstamo en nuestra entidad bancaria y deseamos calcular un préstamo con tabla de amortización TAE, de las siguientes caracteristicas:
Tipo de interés variable; Euribor más 0,5%; El Euribor del primer año es del 2% y en años sucesivos se ha incrementado en 0,2%; Revisión anual de intereses.
| A | B | |
|---|---|---|
| 1 | Valor préstamo: | |
| 2 | Diferencial: | |
| 3 | Duración: | |
| 4 | Euribor año 1: | |
| 5 | Incremento Euribor: |
Ir a: Celda B1
Escribir: 100.000. Como el valor del préstamo.Ir a: Celda B2
Escribir: 0,50%. Como el diferencial.Ir a: Celda B3
Escribir: 10. Como el plazo del préstamo.Ir a: Celda B4
Escribir: 2%. Como el EURIBOR del primer año.Ir a: Celda B5
Escribir: 0,20. Como el incremento del EURIBOR.
FORMULAS PREVIAS PARA TABLA DE TASAS
Ir a: Celda A8
Escribir: 0. Como el inicio del período en años.Copiar la celda A8.
Aplicar serie de relleno hasta la celda A17. Aplicará relleno hasta el año 10.
Ir a: Celda B8
Escribir: =B4. Como el valor inicial del Euribor.Ir a: Celda B9
Escribir: =B8+$B$5. Como el valor del Euribor más el incremento anual.Copiar la celda B9
Pegar la celda en el rango B10:B17, como fórmula.Ir a: Celda C8
Escribir: =B8+$B$2. Como la suma del Euribor del período más el diferencial.Copiar la celda C8
Pegar en el rango C9:C17, como fórmula.Ir a: Celda D8
Escribir: =(1+C8)^(1/12)-1. Como la fórmula de la tasa mensual.Copiar la celda D8
Pegar en e rango D9:D17, como fórmula.
AÑO. Insertamos los datos para el período años.
Ir a: Celda A20
Escribir: 0. Como el inicio del período en años.Ir a: Celda A21
Escribir: =ENTERO(B20/12)+1.Como la fórmula que redondea B20 entre los 12 meses sumando 1 para cambiar de año.
Copiar la celda A21
Pegar en el rango A22:A140, como fórmula (año 10).
MES. Insertamos los datos para el período meses.
Ir a: Celda B20
Escribir: 0. Como el inicio del período en meses.Seleccionar la celda B20.
Aplicar serie de relleno hasta la celda B140. Aplicará relleno hasta el mes 120 (año 10).
TASA MENSUAL. Insertamos las fórmulas para la tasa mensual.
Ir a: Celda C21
Escribir: =BUSCARV(A21;$A$8:$D$17;4)Como la fórmula que busca el valor en la tabla Euribor.
Copiar la celda C21
Pegar en el rango C22:C140.
CUOTA
Ir a: Celda D21
Escribir:=-PAGO(C21;120-B20;G20)Como la fórmula que calcula la cuota mensual fija (como negativo, para que el valor se muestre en positivo).
Copiar la celda D21
Pegar en el rango D22:D140.
INTERÉS. Insertamos las fórmulas para el interés.
Ir a: Celda E21
Escribir: =G20*C21.Como el saldo final por la tasa mensual.
Copiar la celda E21
Pegar en el rango E22:E140.
ABONO CAPITAL.
Ir a: Celda F21
Escribir: =D21-E21Como la cuota menos los intereses del período.
Copiar la celda F21
Pegar la celda en el rango F22:F140.
SALDO FINAL.
Ir a: Celda: G20
Escribir: =B1. Como el valor del préstamo.Ir a: Celda G21
Escribir: =G20-F21Como el valor del saldo final del periodo anterior menos la amortización del período.
Copiar la celda G21
Pegar en el rango G22:G140.
CAPITAL AMORTIZADO.
Ir a: Celda H21
Escribir: =H20+F21Como el capital amortizado del período anterior más el abono capital del período.
Copiar la celda H21
Pegar en el rango H22:H140.
Verificar que la tabla de amortización está correctamente realizada, el último plazo del saldo final será cero (G140). Considerando el préstamo variable como de cuota fija escalonado con diferente tasa.
Excel Avanzado- Consultar:
Si seguimos el ejemplo paso a paso, en la formula que se escribe en D21 se obtiene como resultado 0, pues hace referencia a la celda G20 que esta vacia. La formula dice: =_PAGO(C21;120-B20;G20).
Segun la funcion PAGO la celda G20 hace referencia a la cantidad total de una serie de pagos futuros.
¿me perdi de algo?
Hola José Antonio,
La fórmula de la celda D21 hace referencia a la celda G20, la celda está vacía hasta que pongamos la fórmula =B1 (Apartado SALDO FINAL, punto 1).
Saludos.
Si la revisión fuese semestral, es decir; el euribor más diferencial cambia cada 6 meses . Que fórmula tendríamos que usar . Pensaba que dividiendo entre 6 en vez de entré 12 saldría , pero no es así .
Hola José Luis,
La fórmula para aplicar la TAE es:
Donde:
- r, es el tipo de interés expresado en términos relativos (4%= 0,04), Interés Nominal.
- f, frecuencia de pagos/cobros del interés, es 12 si el tipo es mensual, 6 si es bimestral, 4 si es trimestral, 3 si es cuatrimestral, 2 si es semestral, y 1 si es anual.
Queda añadir la variación semestral del euribor.
Saludos
El cálculo del año sería
ENTERO((B20-1)/12)+1
Hola Pp,
La fórmula que propones también es válida, según consideremos el acumulado con el valor anterior o siguiente.
Saludos.
Hola! Una pregunta, estoy calculando un cuadro y quiero saber si lo he hecho bien. Me han dicho que si el capital pendiente del último periodo es cero, es un indicativo de que el cuadro está bien. Sin embargo, aunque esa celda me da cero, ha aplicado los tipos correctos y las mensualidades son correctas también, la cuota no me coincide con la que se está pagando.
¿Podrías indicar algún otro truco para saber si mi cuadro está bien calculado?
Hola Amanda,
Dependerá de como se abonan los intereses y el capital, generalmente los intereses se liquidan antes que el capital. Cuando el saldo final es cero, el préstamo está liquidado, o con alguna clausula especial de liquidación. Otra cuestión es si el pago es al final del período o al principio.
Saludos.
buenas tardes, como se calcularía el cuadro de amortizacion si se revisa la hipoteca semestralmente y el Euribor baja un 0.2% cada tres meses?
.