Préstamo con tabla de amortización TAE en Excel

Compatibilidad: Truco compatible con Excel 365Truco compatible con Excel 2016Truco compatible con Excel 2013Truco compatible con Excel 2010Truco compatible con Excel 2007

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.

Préstamo con tabla de amortización TAE en Excel
En la columna A (rango A1:A5) disponemos de los valores que deseamos evaluar.
 AB
1Valor préstamo: 
2Diferencial: 
3Duración: 
4Euribor año 1: 
5Incremento Euribor: 
  1. Ir a: Celda B1 / Escribir: 100.000. Como el valor del préstamo.

  2. Ir a: Celda B2 / Escribir: 0,50%. Como el diferencial.

  3. Ir a: Celda B3 / Escribir: 10. Como el plazo del préstamo.

  4. Ir a: Celda B4 / Escribir: 2%. Como el EURIBOR del primer año.

  5. Ir a: Celda B5 / Escribir: 0,20. Como el incremento del EURIBOR.

Préstamo con tabla de amortización TAE en Excel con valores
  • FORMULAS PREVIAS PARA TABLA DE TASAS

  1. Ir a: Celda A8 / Escribir: 0. Como el inicio del período en años.

  2. Copiar la celda A8.

  3. Aplicar serie de relleno hasta la celda A17. Aplicará relleno hasta el año 10.

  4. Ir a: Celda B8 / Escribir: =B4. Como el valor inicial del Euribor.

  5. Ir a: Celda B9 / Escribir: =B8+$B$5. Como el valor del Euribor más el incremento anual.

  6. Copiar la celda B9 / Pegar la celda en el rango B10:B17, como fórmula.

  7. Ir a: Celda C8 / Escribir: =B8+$B$2. Como la suma del Euribor del período más el diferencial.

  8. Copiar la celda C8 / Pegar en el rango C9:C17, como fórmula.

  9. Ir a: Celda D8 / Escribir: =(1+C8)^(1/12)-1. Como la fórmula de la tasa mensual.

  10. Copiar la celda D8 / Pegar en e rango D9:D17, como fórmula.

Hoja Excel con fórmulas previas para taba de tasas
  • AÑO. Insertamos los datos para el período años.

  1. Ir a: Celda A20 / Escribir: 0. Como el inicio del período en años.

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

  3. Copiar la celda A21 / Pegar en el rango A22:A140, como fórmula (año 10).

Hoja Excel con fórmula para el Año del préstamo
  • MES. Insertamos los datos para el período meses.

  1. Ir a: Celda B20 / Escribir: 0. Como el inicio del período en meses.

  2. Seleccionar la celda B20.

  3. Aplicar serie de relleno hasta la celda B140. Aplicará relleno hasta el mes 120 (año 10).

Hoja Excel con fórmula para el Mes del préstamo
  • TASA MENSUAL. Insertamos las fórmulas para la tasa mensual.

  1. Ir a: Celda C21 / Escribir:

    =BUSCARV(A21;$A$8:$D$17;4)

    Como la fórmula que busca el valor en la tabla Euribor.

  2. Copiar la celda C21 / Pegar en el rango C22:C140.

Hoja Excel con fórmula para la Tasa Mensual del préstamo
  • CUOTA

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

  2. Copiar la celda D21 / Pegar en el rango D22:D140.

Hoja Excel con fórmula para la cuota del préstamo
  • INTERÉS. Insertamos las fórmulas para el interés.

  1. Ir a: Celda E21 / Escribir:

    =G20*C21.

    Como el saldo final por la tasa mensual.

  2. Copiar la celda E21 / Pegar en el rango E22:E140.

Hoja Excel con fórmula para el interés del préstamo
  • ABONO CAPITAL.

  1. Ir a: Celda F21 / Escribir:

    =D21-E21

    Como la cuota menos los intereses del período.

  2. Copiar la celda F21 / Pegar la celda en el rango F22:F140.

Hoja Excel con fórmula para el Abono capital del préstamo
  • SALDO FINAL.

  1. Ir a: Celda: G20 / Escribir: =B1. Como el valor del préstamo.

  2. Ir a: Celda G21 / Escribir:

    =G20-F21

    Como el valor del saldo final del periodo anterior menos la amortización del período.

  3. Copiar la celda G21 / Pegar en el rango G22:G140.

Hoja Excel con fórmula para el Saldo final del préstamo
  • CAPITAL AMORTIZADO.

  1. Ir a: Celda H21 / Escribir:

    =H20+F21

    Como el capital amortizado del período anterior más el abono capital del período.

  2. Copiar la celda H21 / Pegar en el rango H22:H140.

Hoja Excel con fórmula para el capital amortizado del préstamo

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.

Hoja Excel con el resultado del préstamo, celda G140 con valor cero
Nivel de dificultad: Avanzado Excel Avanzado

2 comentarios en “Préstamo con tabla de amortización TAE en Excel

  • 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?

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *