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.
| Cifra | El cuadro | La entidad |
|---|---|---|
| Intereses pagados | 5.101,14 £ | 6.889,18 £ |
| Intereses ahorrados | 5.336,98 £ | 3.548,94 £ |
| Última cuota | Mes 60 | Mes 40 |
Alcance.
PAGO,MIN,VF,NPER,SUMAyCONTAR.SIfuncionan en todas las versiones de Excel.BUSCARXnecesita 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.
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.
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:
| Meses | Intereses en la columna C |
|---|---|
| Del 1 al 40 | 6.889,18 £ |
| Del 41 al 60 | -1.788,04 £ |
| Del 1 al 60 | 5.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.
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.
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ón | Sin MIN | Con MIN |
|---|---|---|
| Pagado menos capital | 40.438,20 £ | 6.889,18 £ |
| Total de intereses | 5.101,14 £ | 6.889,18 £ |
| Saldo más bajo | -35.337,06 £ | 0,00 £ |
| Saldos negativos | 21 | 0 |
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.
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.
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 extra | Meses | Intereses | Ahorro |
|---|---|---|---|
| 0 £ | 60 | 10.438,12 £ | 0,00 £ |
| 250 £ | 48 | 8.291,93 £ | 2.146,19 £ |
| 500 £ | 40 | 6.889,18 £ | 3.548,94 £ |
| 750 £ | 35 | 5.900,83 £ | 4.537,29 £ |
| 1.000 £ | 30 | 5.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.
07Cinco Trampas
- Una cuota fija sin límite. Sin
MIN, el cuadro paga por debajo de cero y anota intereses negativos. - Fiarse de un total sobre un rango largo.
=SUMA(C2:C61)suma sin quejarse los meses negativos. Mira antes=MIN(E2:E61). - Usar
PAGO.INT.ENTREcon pagos extra. Calcula la cuota a partir del plazo, así que siempre describe el cuadro del contrato. - Redondear cada fila. Redondea la cuota una sola vez en
H3, o cada mes añade su propio error. - 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
MINlo detenga.
08Cuatro Ejercicios
- Reprodúcelo. Monta el cuadro roto y confirma que
E61muestra -35.337,06. - Arréglalo. Añade
MINa la columna D y confirma un total de intereses de 6.889,18. - Compruébalo. Confirma que
=SUMA(D2:D61)-H1y=SUMA(C2:C61)coinciden. - Cámbialo. Pon
H4a 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.