01 / 09El Cuadro Siguió Pagando Sin Préstamo

Funciones FinancierasPAGOFórmulas

Cuadro de amortización con pagos extra en Excel: saldo cero

Amortizar Antes el Préstamo del Escáner Iba a Ahorrar 5.337 £ de Intereses. Ahorró 3.549 £, Porque el Cuadro Siguió Pagando Cuando el Préstamo Ya No Existía

19 nov 20267 min de lectura

UsaMIN, MAXPAGOVFPAGO.INT.ENTRE, PAGO.PRINC.ENTRE

Thornbury Dental decidió pagar 500 £ extra al mes en un préstamo de 60.000 £ porque el cuadro prometía 5.336,98 £ de intereses ahorrados. El ahorro real fue de 3.548,94 £: la diferencia eran intereses que la hoja abonaba meses después de liquidar el préstamo.

CifraEl cuadroLa entidad
Intereses pagados5.101,14 £6.889,18 £
Intereses ahorrados5.336,98 £3.548,94 £
Última cuotaMes 60Mes 40

Alcance. PAGO, MIN, VF, NPER, SUMA y CONTAR.SI funcionan en todas las versiones de Excel. BUSCARX necesita Excel 2021 o Microsoft 365.

El cuadro calcula los intereses cada mes sobre el saldo inicial, como la mayoría de los préstamos a empresas y particulares en el Reino Unido.

Revisa tu contrato: algunas entidades calculan los intereses por días.

01El Cuadro Siguió Pagando Sin Préstamo

Un cuadro de amortización copiado en un número fijo de filas no sabe cuándo termina el préstamo. Paga la misma cuota en cada fila, quede algo pendiente o no.

El préstamo está en H1:H4 y el cuadro en A1:E61, una fila por mes:

H1:  60000                              importe prestado
H2:  6,5%                               tipo anual
H3:  =REDONDEAR(-PAGO(H2/12;60;H1);2)   → 1.173,97   cuota del contrato
H4:  500                                pago extra

En el cuadro, B2 es =$H$1 y B3 es =E2. El interés es =B2*$H$2/12, la cuota =$H$3+$H$4 y el saldo final =B2+C2-D2.

Con 500 £ extra el préstamo se liquida antes. En el mes 40 solo se debían 1.604,35 £, pero la hoja pagó 1.673,97 £, y siguió pagándolas veinte meses más.

Escenario: Monta el cuadro anterior en una hoja vacía y copia la fila 2 hasta la 61. Comprueba que E41, el saldo final del mes 40, muestra -69,62 en vez de cero.

Pruébalo en la cuadrícula

02El Interés Negativo Redujo el Total

En cuanto el saldo pasa a negativo, =B2*$H$2/12 también se vuelve negativo. La hoja empieza a cobrarle intereses a la entidad, y esas cifras negativas se restan del total.

Préstamo del escáner de Thornbury Dental, el cuadro de pagos extra en torno al mes 40

Filas 39 a 43 y 61 del cuadro de pagos extra de Thornbury Dental para un préstamo de 60.000 £ al 6,5% a 60 meses, con una cuota de 1.173,97 £ más 500 £ al mes. B es el saldo inicial, C el interés, =B2*$H$2/12, D la cuota, =$H$3+$H$4, y E el saldo final, =B2+C2-D2. Desde el mes 40 el saldo es negativo, y el interés también.

ABCDEF
1
Month
Opening balance
Interest
Payment
Closing balance
What the sheet did
2
Month 38
4899.49
26.54
1673.97
3252.06
A normal month: interest on what is still owed.
3
Month 39
3252.06
17.62
1673.97
1595.7
The last full payment.
4
Month 40
1595.7
8.64
1673.97
-69.62
Only 1,604.35 was owed. The sheet paid 69.62 too much.
5
Month 41
-69.62
-0.38
1673.97
-1743.97
The loan is gone. The sheet pays again and books interest in Thornbury's favour.
6
Month 42
-1743.97
-9.45
1673.97
-3427.39
Another payment, and more negative interest.
7
Month 60
-33481.73
-181.36
1673.97
-35337.06
The last row: 20 months of negative interest, -1,788.04 in all.

fxLas celdas con fórmulas están resaltadas en verde

Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas

La celda del total de intereses era =SUMA(C2:C61), y el ahorro era el interés del cuadro de 60 meses menos esa cifra:

MesesIntereses en la columna C
Del 1 al 406.889,18 £
Del 41 al 60-1.788,04 £
Del 1 al 605.101,14 £

Frente a los 10.438,12 £ del cuadro del contrato, eso se leía como 5.336,98 £ ahorrados. Las últimas 20 filas se habían inventado 1.788,04 £.

La pista estuvo siempre en la última fila: un saldo final de -35.337,06 £. Nadie bajó hasta la fila 61.

Escenario: Escribe =SUMA(C42:C61) en cualquier celda vacía. Comprueba que devuelve -1.788,04, el interés que la hoja abonó después de liquidar el préstamo.

Pruébalo en la cuadrícula

03Limita Cada Cuota a Lo Que Se Debe

El arreglo es una sola función en la columna de cuotas. Paga la cuota prevista o el saldo más el interés del mes, lo que sea menor:

D2:  =MIN($H$3+$H$4;B2+C2)

En el mes 40 el saldo más el interés suma 1.604,35, así que eso es lo que paga MIN, y el saldo final queda exactamente en cero.

Desde el mes 41 el saldo inicial es cero, el interés es cero y MIN paga cero. El cuadro se detiene solo, copies las filas que copies.

Escenario: Sustituye D2 por =MIN($H$3+$H$4;B2+C2) y cópiala hasta D61. Comprueba que D41 muestra 1.604,35 y que todas las cuotas de debajo muestran 0.

Pruébalo en la cuadrícula

04Comprueba Que los Totales Cuadran

Cada libra pagada es capital o interés. Así que el total pagado menos el importe prestado debe ser igual al total de intereses, y en un cuadro roto no coinciden:

=SUMA(D2:D61)-H1      total pagado menos capital
=SUMA(C2:C61)         total de intereses
=MIN(E2:E61)          saldo final más bajo
=CONTAR.SI(E2:E61;"<0")
ComprobaciónSin MINCon MIN
Pagado menos capital40.438,20 £6.889,18 £
Total de intereses5.101,14 £6.889,18 £
Saldo más bajo-35.337,06 £0,00 £
Saldos negativos210

La diferencia de 35.337,06 £ entre las dos primeras filas es el saldo final negativo. Cuando las dos coinciden, el préstamo termina en cero.

Escenario: Escribe =CONTAR.SI(E2:E61;"<0") junto al cuadro. Comprueba que muestra 21 antes de añadir MIN y 0 después.

Pruébalo en la cuadrícula

05VF Confirma la Última Cuota por Su Cuenta

VF devuelve el saldo tras cualquier número de cuotas, sin cuadro. Úsala para comprobar la fila que más importa, la última:

=-VF(H2/12;39;-(H3+H4);H1)                → 1.595,70
=-VF(H2/12;39;-(H3+H4);H1)*(1+H2/12)      → 1.604,35
=NPER(H2/12;-(H3+H4);H1)                  → 39,96

Tras 39 cuotas se deben 1.595,70 £, y un mes de intereses deja la última cuota en 1.604,35 £. NPER da 39,96 cuotas, así que la 40.ª es un pago parcial.

En el cuadro corregido, =BUSCARX(0;E2:E61;A2:A61) devuelve 40, el mes en que el saldo llega a cero por primera vez.

Escenario: Escribe =-VF(H2/12;39;-(H3+H4);H1)*(1+H2/12) en una celda vacía. Comprueba que coincide con D41 del cuadro corregido, 1.604,35.

Pruébalo en la cuadrícula

06Compara Pagos Extra en el Cuadro Corregido

Con MIN en su sitio, cambia H4 y lee los totales. Cada 250 £ extra ahorran menos que las anteriores, porque queda menos saldo sobre el que cobrar intereses:

Pago extraMesesInteresesAhorro
0 £6010.438,12 £0,00 £
250 £488.291,93 £2.146,19 £
500 £406.889,18 £3.548,94 £
750 £355.900,83 £4.537,29 £
1.000 £305.164,83 £5.273,29 £

Los 5.336,98 £ que daba la hoja rota con 500 £ al mes superaban el ahorro real con 1.000 £ al mes.

Una comparación basada en esa cifra habría ordenado mal todas las opciones.

Escenario: En el cuadro corregido, pon H4 a 250 y lee =SUMA(C2:C61). Comprueba que devuelve 8.291,93, y que D50 es la primera cuota a cero.

Pruébalo en la cuadrícula

07Cinco Trampas

  1. Una cuota fija sin límite. Sin MIN, el cuadro paga por debajo de cero y anota intereses negativos.
  2. Fiarse de un total sobre un rango largo. =SUMA(C2:C61) suma sin quejarse los meses negativos. Mira antes =MIN(E2:E61).
  3. Usar PAGO.INT.ENTRE con pagos extra. Calcula la cuota a partir del plazo, así que siempre describe el cuadro del contrato.
  4. Redondear cada fila. Redondea la cuota una sola vez en H3, o cada mes añade su propio error.
  5. Cortar el cuadro en la fila de liquidación. Si cambias el pago extra, el mes de liquidación se mueve. Copia las 60 filas y deja que MIN lo detenga.

08Cuatro Ejercicios

  1. Reprodúcelo. Monta el cuadro roto y confirma que E61 muestra -35.337,06.
  2. Arréglalo. Añade MIN a la columna D y confirma un total de intereses de 6.889,18.
  3. Compruébalo. Confirma que =SUMA(D2:D61)-H1 y =SUMA(C2:C61) coinciden.
  4. Cámbialo. Pon H4 a 750 y confirma que el préstamo se liquida en el mes 35.

Qué Llevarse

Todas las fórmulas del cuadro eran correctas para un préstamo que todavía existiera. La hoja nunca comprobó si existía, y veinte meses de intereses negativos convirtieron un ahorro de 3.549 £ en 5.337 £.

Limita cada cuota con =MIN($H$3+$H$4;B2+C2), y comprueba que el último saldo final es cero antes de leer ningún total.

Comparte este artículo:
Volver al Blog

Practícalo

MIN, MAX

Un ejercicio en una cuadrícula real

Abrir el ejercicio

Las funciones que usa este artículo

Sintaxis, ejemplos resueltos y los errores que esperar: una página de referencia para cada una.

Sigue leyendo

  1. Cómo detectar valores fijos en fórmulas de ExcelLeer el artículo
  2. Sumar y contar celdas por color en ExcelLeer el artículo
  3. BUSCARX con varios criterios en Excel: dos columnasLeer el artículo
Reto diario · Día 110

Calcula el coste de una financiación con pago final con el cuarto argumento de PAGO

Un ejercicio nuevo cada día, resuelto en una cuadrícula real.

Resolver el reto de hoy