Cuadro Amortizacion Excel: Prestamos e Hipotecas

Parte A. Cuadro de amortización de un préstamo paso a paso

El sistema francés es el que usan prácticamente todas las hipotecas y préstamos personales en España. Su rasgo distintivo es que la cuota es constante durante toda la vida del préstamo (mientras el tipo no cambie), y lo que varía es el reparto interno: al principio pagas mucho interés y poco capital, y al final ocurre lo contrario. Por eso amortizar pronto ahorra tanto y amortizar en el año veinte apenas mueve la aguja.

Paso 1: monta la zona de datos

Reserva las primeras filas para las variables y no las mezcles con la tabla. Una estructura que funciona bien:

• B1: capital concedido, por ejemplo 150.000
• B2: tipo de interés nominal anual (TIN), por ejemplo 3,00 % introducido como 0,03
• B3: plazo en años, por ejemplo 25
• B4: periodos por año, 12 si la cuota es mensual
• B5: número total de cuotas, con =B3*B4
• B6: tipo periódico, con =B2/B4
• B7: fecha de la primera cuota

Nombra esas celdas desde el cuadro de nombres (capital, tin, nper, tasa_mes). Las fórmulas se leen mucho mejor y evitas el clásico error de arrastrar una referencia que debía ser absoluta.

Paso 2: calcula la cuota con PAGO

La función es =PAGO(tasa; nper; va; [vf]; [tipo]). Con los datos anteriores:

=PAGO(B6; B5; -B1)
=PAGO(0,03/12; 300; -150000)   →   711,31 €

El signo negativo en el capital hace que la cuota salga positiva, que es como la queremos ver en la tabla. Si prefieres dejar el capital en positivo, envuelve el resultado en un ABS(). Esa cuota de 711,31 € multiplicada por 300 meses da 213.393 €, de los cuales 63.393 € son intereses. Ver ese número en pantalla antes de firmar cambia bastantes decisiones.

Paso 3: desglosa cada cuota con PAGOINT y PAGOPRIN

En la fila 10 pon los encabezados (Nº, Fecha, Cuota, Intereses, Capital, Amortizado acumulado, Pendiente) y en la fila 11 arranca el cuadro:

A11: 1
B11: =$B$7
C11: =PAGO($B$6; $B$5; -$B$1)
D11: =PAGOINT($B$6; $A11; $B$5; -$B$1)
E11: =PAGOPRIN($B$6; $A11; $B$5; -$B$1)
F11: =E11
G11: =$B$1-F11

En la fila 12 y siguientes solo cambian tres celdas: A12: =A11+1, B12: =FECHA.MES($B$7; A12-1), F12: =F11+E12 y G12: =G11-E12. Arrastra hasta la fila 310 y tendrás las 300 cuotas. Para que las filas sobrantes no muestren ceros ni errores, envuelve las fórmulas en =SI($A11>$B$5; ""; ...).

La comprobación de que el cuadro está bien montada es simple: la última celda de la columna Pendiente tiene que dar cero (o algo del orden de 0,000001 por redondeo), y la suma de la columna Capital tiene que coincidir con el capital concedido. Si no cuadra, casi siempre es un anclaje de referencia mal puesto o un tipo anual metido donde iba el periódico.

Paso 4: los totales y los atajos que ahorran filas

Si solo quieres el resumen y no el cuadro entero, hay dos funciones que resuelven en una línea lo que la tabla resuelve en trescientas:

Intereses pagados en los 5 primeros años:
=PAGO.INT.ENTRE(B6; B5; B1; 1; 60; 0)   →   -21.700 € aprox.

Capital devuelto en esos mismos 60 meses:
=PAGO.PRINC.ENTRE(B6; B5; B1; 1; 60; 0)   →   -21.741 € aprox.

El dato es demoledor y merece la pena enseñárselo a cualquiera que vaya a firmar: en los cinco primeros años de una hipoteca a 25 años al 3 %, de los 42.678 € desembolsados algo más de la mitad se ha ido en intereses. El saldo pendiente tras esas 60 cuotas sigue siendo de 128.259 €.

Sistema francés, lineal y americano: cuál te conviene

El sistema lineal paga casi 7.000 € menos de intereses, pero exige un esfuerzo inicial un 23 % mayor. La carencia hace lo contrario: alivia los dos primeros años y te cuesta unos 7.200 € extra. Ninguno es mejor en abstracto; depende de si tu problema es el coste total o la tesorería de los próximos veinticuatro meses.

Para montar el lineal en la misma hoja, la parte de capital es fija (=$B$1/$B$5, es decir 500 € mensuales) y los intereses se calculan sobre el saldo del mes anterior (=G10*$B$6). La cuota es la suma de ambas. Poner los dos cuadros en columnas paralelas y un gráfico de líneas encima deja la comparación resuelta de un vistazo.

TIN y TAE: por qué tu cuadro nunca coincidirá con el del banco

El TIN es el tipo nominal que se aplica al capital pendiente para calcular los intereses. Es el número que entra en tu columna de intereses. La TAE incorpora además las comisiones, los gastos obligatorios y el efecto de la capitalización, y sirve para comparar ofertas entre entidades. Si un préstamo no tuviera ningún gasto, la relación sería puramente matemática: con TIN del 3 % mensualizado, la TAE es =(1+0,03/12)^12-1 = 3,042 %.

Con gastos, la cosa cambia. Supón el mismo préstamo de 150.000 € con una comisión de apertura del 0,5 % (750 €) y una tasación de 350 €. Recibes 148.900 € netos y devuelves 300 cuotas de 711,31 €. La rentabilidad real de la operación para el banco, y el coste real para ti, sale con una tasa interna de retorno:

=TASA(300; -711,31; 148900)*12   →   3,067 % nominal
TAE = (1+0,003067/12)^12-1        →   3,11 %

Once puntos básicos de diferencia sobre el TIN por dos gastos modestos. Cuando entran seguros de vida y de hogar vinculados, la brecha se abre bastante más. Para escenarios con fechas irregulares, TIR.NO.PER(valores; fechas) es más preciso que TASA, porque respeta el calendario real en lugar de asumir meses idénticos.

Cuando compares dos hipotecas, no mires la cuota: mira la TAE y el coste total del préstamo que aparecen en la FEIN. Una cuota más baja casi siempre significa un plazo más largo, y un plazo más largo significa más intereses.

Fija, variable y mixta: cómo modelar cada una

La hipoteca a tipo fijo es la fácil: un solo tipo, una sola cuota, el cuadro que ya has montado. Las otras dos exigen un ajuste.

• Variable. El tipo se revisa cada seis o doce meses como Euríbor más un diferencial (habitualmente entre el 0,50 % y el 1,10 % según vinculación). En Excel, añade una columna «Tipo aplicable» y calcula los intereses con =G_anterior*(tipo_columna/12). Cada revisión recalcula la cuota con =PAGO(tipo/12; cuotas_restantes; -saldo_pendiente). Monta tres escenarios: Euríbor al 2 %, al 3 % y al 4 %, y comprueba si aguantas el peor.
• Mixta. Un tramo inicial a tipo fijo, típicamente entre 3 y 10 años, y el resto a variable. Se modela como dos cuadros encadenados: al terminar el tramo fijo, el saldo pendiente pasa a ser el capital del segundo cuadro. La trampa está en que el tramo fijo suele coincidir con los años de más peso de intereses, así que el atractivo del gancho inicial es menor de lo que parece.
• Con suelo o techo. Si el contrato tiene límites, usa =MEDIANA(suelo; euribor+diferencial; techo) para acotar el tipo aplicable sin anidar condicionales.

El Euríbor a doce meses se publica a diario y el Banco de España difunde la media mensual, que es el dato que suelen usar las escrituras. Consulta siempre el valor oficial del mes de revisión que indique tu contrato antes de dar por bueno un escenario: una décima de diferencia en un saldo de 150.000 € son unos 150 € al año.

Amortización anticipada: reducir cuota o reducir plazo

Es la pregunta que más tráfico genera y la que mejor responde una hoja de cálculo. Retomamos el ejemplo: hipoteca de 150.000 € al 3 % a 25 años, cuota de 711,31 €. Tras cinco años quedan 240 cuotas y un saldo pendiente de 128.259 €. En ese momento aportas 10.000 € extra.

Reducir plazo ahorra más del doble que reducir cuota, porque elimina de golpe los meses finales del cuadro y con ellos sus intereses. La contrapartida es que tu obligación mensual no baja, así que si prevés un ingreso irregular o un cambio de trabajo, la reducción de cuota te da un colchón que la de plazo no da. En Excel el cálculo de la nueva duración sale con =NPER(B6; -711,31; 118259,25), que devuelve 215,14 periodos.

Comisiones y límites legales que debes meter en la hoja

La Ley 5/2019 reguladora de los contratos de crédito inmobiliario puso techo a lo que puede cobrarte el banco por adelantar capital en préstamos hipotecarios sobre vivienda firmados desde junio de 2019. Los topes, siempre limitados además a la pérdida financiera real de la entidad, son estos:

• Tipo variable: 0,25 % del capital reembolsado si la amortización se produce en los cinco primeros años, o bien 0,15 % si ocurre en los tres primeros, según la opción pactada en el contrato. Pasado ese plazo, cero.
• Tipo fijo: hasta el 2 % durante los diez primeros años y hasta el 1,5 % a partir del undécimo.
• Paso de variable a fijo mediante novación o subrogación: máximo 0,15 % durante los tres primeros años y sin comisión después.

En la hoja, añade una celda con el porcentaje aplicable y descuéntalo del ahorro calculado. Amortizar 10.000 € con una comisión del 0,25 % cuesta 25 €, irrelevante frente a los 7.681 € de ahorro. Amortizar 60.000 € de una hipoteca fija en el año cuatro, con un 2 %, son 1.200 € que sí cambian el resultado.

Queda una comparación que la hoja también resuelve: amortizar frente a invertir. Si tu hipoteca está al 3 % y encuentras una alternativa con rentabilidad esperada superior después de impuestos y con un riesgo que puedas asumir, amortizar deja de ser automáticamente la mejor opción. Añade una columna con la rentabilidad neta de tu alternativa y compárala con el tipo del préstamo. Y si adquiriste tu vivienda habitual antes del 1 de enero de 2013, revisa si conservas el régimen transitorio de la deducción por inversión en vivienda habitual, porque amortizar hasta el límite anual deducible puede tener un retorno fiscal que altera la decisión. Ese punto concreto conviene confirmarlo con un asesor fiscal, ya que depende de tu situación personal y de la normativa autonómica aplicable.

Parte B. Amortización contable del inmovilizado

Aquí no hay banco ni intereses. Lo que se reparte es el coste de un bien que la empresa usa durante varios ejercicios. El Plan General de Contabilidad obliga a reconocer esa depreciación sistemática desde que el elemento está en condiciones de funcionamiento, y la Ley 27/2014 del Impuesto sobre Sociedades marca los límites de lo que Hacienda acepta como gasto deducible. La tabla de Excel que necesitas tiene una fila por bien y una columna por ejercicio.

La tabla oficial de coeficientes (artículo 12.1 LIS)

Se considera que la depreciación es efectiva cuando la dotación resulta de aplicar un coeficiente lineal comprendido entre el máximo de la tabla y el mínimo que se deduce del periodo máximo. Estos son los elementos más habituales:

Dos matices que se olvidan a menudo. El primero: los terrenos no se amortizan, así que al comprar un edificio hay que separar el valor del suelo del valor de la construcción, y el criterio habitual para hacerlo es la proporción que marca el recibo del IBI entre valor catastral del suelo y valor catastral total. El segundo: los elementos usados admiten hasta el doble del coeficiente lineal máximo, calculado sobre el precio de adquisición, algo que abarata mucho la compra de maquinaria de segunda mano.

Lineal, degresivo y suma de dígitos en Excel

Tomamos una máquina de 30.000 €, valor residual cero, coeficiente lineal máximo del 12 %, lo que implica un periodo máximo de 18 años y una vida útil mínima de 8,33 años si aplicas el máximo.

Lineal:            =SLN(30000; 0; 8,33)          →  3.600 €/año
Suma de dígitos:   =SYD(30000; 0; 8; periodo)     →  6.667 € el año 1
Degresivo doble:   =DDB(30000; 0; 8; periodo; 2)  →  7.500 € el año 1
Saldo decreciente: =DB(30000; 0; 8; periodo)      →  aplica tasa fija sobre VNC
Parcial por meses: =VDB(30000; 0; 96; 0; 7; 2)    →  primer ejercicio prorrateado

El Reglamento del Impuesto sobre Sociedades admite el método de porcentaje constante sobre valor pendiente multiplicando el coeficiente lineal por 1,5 si la vida útil es inferior a cinco años, por 2 si está entre cinco y ocho, y por 2,5 si llega a ocho o más, con un porcentaje mínimo del 11 %. Con nuestro 12 % y 8,33 años de vida, el multiplicador es 2,5 y el porcentaje constante queda en el 30 %. La progresión sería: 9.000 € el primer año, 6.300 € el segundo, 4.410 € el tercero, 3.087 € el cuarto. También se admite el método de suma de dígitos. Los edificios, el mobiliario y los enseres quedan fuera de los dos métodos degresivos y solo pueden amortizarse de forma lineal.

Los métodos degresivos no te hacen deducir más dinero: te lo adelantan. El gasto total a lo largo de la vida del bien es idéntico. Lo que cambia es en qué ejercicio pagas menos impuesto, y eso solo interesa si tienes base imponible positiva contra la que compensar.

Incentivos para empresas de reducida dimensión

Si la cifra de negocios del ejercicio anterior fue inferior a 10 millones de euros, la empresa entra en el régimen de entidades de reducida dimensión y accede a tres ventajas que conviene tener modeladas en la hoja:

• Amortización acelerada: los elementos nuevos del inmovilizado material, las inversiones inmobiliarias y el inmovilizado intangible pueden amortizarse al doble del coeficiente lineal máximo de tabla. La máquina del ejemplo pasaría de 3.600 € a 7.200 € anuales.
• Libertad de amortización por creación de empleo: los elementos nuevos del inmovilizado material y las inversiones inmobiliarias pueden amortizarse libremente hasta un importe de 120.000 € por cada unidad de incremento de la plantilla media, siempre que ese incremento se mantenga durante los veinticuatro meses siguientes.
• Bienes de escaso valor: los elementos con un precio de adquisición unitario que no supere los 300 € se pueden amortizar libremente, con un límite conjunto de 25.000 € por periodo impositivo. Es la vía para no llevar ficha individual de cada silla o cada teclado.

Existen además regímenes de libertad de amortización vinculados a inversiones en instalaciones de autoconsumo y energía procedente de fuentes renovables, cuyo alcance y vigencia han ido cambiando de un ejercicio a otro. Antes de aplicarlos, comprueba la redacción vigente para el periodo impositivo que estés cerrando y contrástalo con tu asesor fiscal: son incentivos con requisitos de mantenimiento y con consecuencias si se incumplen.

Del Excel al asiento contable

La tabla de Excel alimenta un asiento anual muy sencillo. Para la dotación de la máquina por 3.600 €:

681  Amortización del inmovilizado material .......  3.600,00  (Debe)
   a  2813  A.A. maquinaria ..........................  3.600,00  (Haber)

Las cuentas paralelas son la 680 con la 280 para el intangible y la 682 con la 282 para las inversiones inmobiliarias. La amortización acumulada figura en el balance minorando el activo, y la diferencia entre valor de adquisición y amortización acumulada es el valor neto contable, que es lo que necesitas cuando vendes o das de baja el elemento. Si contabilizas por encima del límite fiscal, ese exceso no es deducible en el ejercicio y genera un ajuste extracontable positivo en el modelo 200 que revertirá en años posteriores, junto con su correspondiente activo por impuesto diferido. Un autónomo en estimación directa simplificada no usa esta tabla sino la tabla simplificada aprobada por Orden de 27 de marzo de 1998, donde los edificios van al 3 %, la maquinaria al 12 %, los elementos de transporte al 16 %, los equipos informáticos al 26 % y las herramientas al 30 %.

Conviene no confundir amortización con deterioro. La amortización es sistemática y previsible; el deterioro es una pérdida de valor sobrevenida que se registra cuando el importe recuperable del activo cae por debajo de su valor en libros, con sus propias cuentas y su propio tratamiento fiscal. Tampoco se amortizan los activos mantenidos para la venta ni los terrenos, salvo el caso particular de las escombreras.

Los siete errores que más veces rompen una plantilla de amortización

1. Meter el tipo anual donde va el periódico. Si escribes 0,03 en lugar de 0,03/12 en la función PAGO, la cuota se dispara y todo el cuadro queda inservible. Deja el tipo periódico en su propia celda y referencia siempre esa.
2. Olvidar los anclajes. Al arrastrar 300 filas, cualquier referencia a la zona de datos tiene que ir con dólares ($B$6). Es el fallo que provoca el 90 % de los cuadros que no cierran a cero.
3. Confundir el periodo con la fila. El argumento periodo de PAGOINT es el número de cuota, no el número de fila de la hoja. Si tu tabla arranca en la fila 11, el periodo va en su propia columna empezando en 1.
4. Mezclar TIN y TAE. Los intereses del cuadro se calculan siempre con el nominal. Meter la TAE en la columna de intereses infla el resultado y hace que el cuadro no coincida con los recibos del banco.
5. No prorratear el primer año de amortización contable. Un bien dado de alta el 1 de octubre solo genera tres meses de dotación en ese ejercicio, no doce. Calcula los días o meses reales con =SLN(...)/12*meses_de_uso.
6. Amortizar el terreno. Si compras un local por 200.000 € y el suelo representa el 30 % según el catastro, la base amortizable son 140.000 €, no 200.000 €.
7. Fiarse del redondeo. Excel arrastra decimales que el banco redondea a dos. Una diferencia de céntimos por cuota se acumula en 300 meses. Si necesitas cuadrar al céntimo con el recibo, aplica REDONDEAR(...;2) en la cuota y ajusta la última.

Trucos de Excel que multiplican la utilidad de la hoja

• Buscar objetivo (Datos, Análisis de hipótesis): fija la cuota máxima que puedes pagar y deja que Excel te diga qué capital puedes pedir o qué plazo necesitas.
• Tabla de datos de dos variables: cruza tipo de interés en filas y plazo en columnas para obtener una matriz de cuotas de un solo golpe. Es la forma más rápida de ver la sensibilidad de tu hipoteca variable.
• Administrador de escenarios: guarda tres juegos de datos (optimista, central, pesimista) y compáralos en un informe resumen.
• Formato condicional con barras de datos sobre las columnas de interés y capital: la tijera visual del sistema francés se entiende sin explicar nada.
• Validación de datos en la hoja de inmovilizado con la lista de elementos de la tabla oficial, más un BUSCARV que traiga el coeficiente máximo. Evita teclear porcentajes de memoria.
• Nombres definidos y tablas estructuradas: convierte el cuadro en tabla con Ctrl+T para que las fórmulas se propaguen solas al añadir filas.

Preguntas frecuentes

Antes de tomar una decisión con estos números

Esta plantilla y esta guía sirven para entender la mecánica y hacer simulaciones fiables, y los ejemplos numéricos están calculados con las mismas fórmulas que usa el sector. Aun así, tratamos materia financiera y fiscal, donde el detalle de tu contrato o de tu ejercicio contable puede cambiar el resultado por completo.

Los importes de esta página son ejemplos de cálculo, no una oferta ni una recomendación personalizada. Antes de amortizar anticipadamente, de subrogar una hipoteca o de aplicar un coeficiente de amortización en tu declaración, contrasta el caso con tu entidad financiera y con un asesor fiscal o contable colegiado. La normativa del Impuesto sobre Sociedades y los incentivos a la amortización cambian de un ejercicio a otro.

Si esta plantilla te ha servido, en la sección de Excel tienes las hojas que suelen acompañarla: el balance de situación, la cuenta de pérdidas y ganancias donde acaba la dotación anual y el dashboard de KPI para seguir el endeudamiento mes a mes. Todas son gratuitas, editables y sin marca de agua.