Cómo calcular el pago constante de un préstamo (como PMT) pero el pago doble adicional (sin intereses) dos veces al año

Esta es mi primera publicación en esta comunidad, ya intenté encontrar mi respuesta y no tuve éxito :(.

Estoy trabajando en una manera de entender la fórmula detrás de un préstamo que le permite al cliente pagar extra la misma cantidad mensual dos veces al año sin aumentar los intereses, una opción que funciona con pagos flexibles en algunos bancos y común en lugares como Perú.

Un ejemplo (tomado de un programa de amortización real):

+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| #  | Date       | Month | Payment  | Amortization | Interests | Balance    | Comment                          |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| 1  | 2015-04-30 | Apr   | 2,699.00 | -332.10      | 3,031.10  | 439,425.00 | First payment, usually different |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| 2  | 2015-05-31 | May   | 2,715.34 | 39.90        | 2,675.44  | 439,757.10 |                                  |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| 3  | 2015-06-30 | Jun   | 2,711.25 | -53.40       | 2,764.65  | 439,717.20 |                                  |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| 4  | 2015-07-31 | Jul   | 5,614.66 | 2,939.14     | 2,675.52  | 439,770.60 | Double payment                   |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| 5  | 2015-08-31 | Aug   | 2,708.01 | -127.38      | 2,835.39  | 436,831.46 |                                  |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| 6  | 2015-09-30 | Sep   | 2,716.12 | 57.70        | 2,658.42  | 436,958.84 |                                  |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| 7  | 2015-10-31 | Oct   | 2,716.14 | 58.08        | 2,658.06  | 436,901.14 |                                  |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| 8  | 2015-11-30 | Nov   | 2,712.08 | -34.50       | 2,746.58  | 436,843.06 |                                  |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| 9  | 2015-12-31 | Dec   | 5,615.47 | 2,957.55     | 2,657.92  | 436,877.56 | Double payment                   |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| 10 | 2016-01-31 | Jan   | 2,712.92 | -15.28       | 2,728.20  | 433,920.01 |                                  |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| 11 | 2016-02-29 | Feb   | 2,716.97 | 76.95        | 2,640.02  | 433,935.29 |                                  |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| 12 | 2016-03-31 | Mar   | 2,716.99 | 77.44        | 2,639.55  | 433,858.34 |                                  |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| 13 | 2016-04-30 | Apr   | 2,712.96 | -14.37       | 2,727.33  | 433,780.90 |                                  |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| 14 | 2016-05-31 | May   | 2,717.01 | 77.84        | 2,639.17  | 433,793.27 |                                  |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| 15 | 2016-06-30 | Jun   | 2,712.98 | -13.95       | 2,726.93  | 433,717.43 |                                  |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| 16 | 2016-07-31 | Jul   | 5,616.36 | 2,977.58     | 2,638.78  | 433,732.48 | Double payment                   |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+
| 17 | 2016-08-31 | Aug   | 2,713.84 | 5.55         | 2,708.29  | 430,753.80 |                                  |
+----+------------+-------+----------+--------------+-----------+------------+----------------------------------+

Nota: en Perú calculan la mensualidad incluyendo el cargo del seguro de vida, resté los conceptos para simplificar (pero el resultado es una mensualidad no fija).

Entonces, mi problema es: necesito calcular un pago fijo mensual que permita dos pagos adicionales al año y esos sin aplicar intereses.

Por ejemplo, un préstamo a 30 años, tasa anual del 12 % y USD 1000, utilizando una hipoteca de amortización constante:

P = A / ((( 1 + i ) ^ n - 1 )/( i ( 1 + i ) ^ n ))
A = loan amount = 1,000
i = monthly rate = (( 1 + 12% ) ^ ( 1 / 12 ) - 1 ) = 0.00948879293
n = periods = 30 * 12 = 360
---
So, my payment (P) will be: USD 9.83 ~

Estoy buscando una estrategia matemática financiera que me permita pagar dos veces esos 10 dólares dos veces más al año (pagando el mismo interés en esos meses).

Si pago solo, funciona a las mil maravillas:

+--------------------------------------------------------------+
| Single Payment                                               |
+-----+---------+---------+----------+--------------+----------+
| #   | # Month | Payment | Interest | Amortization | Balance  |
+-----+---------+---------+----------+--------------+----------+
| 0   |         |         |          |              | 1,000.00 |
+-----+---------+---------+----------+--------------+----------+
| 1   | 1       | 9.83    | 9.50     | 0.33         | 999.67   |
+-----+---------+---------+----------+--------------+----------+
| 2   | 2       | 9.83    | 9.50     | 0.33         | 999.34   |
+-----+---------+---------+----------+--------------+----------+
| 3   | 3       | 9.83    | 9.49     | 0.33         | 999.01   |
+-----+---------+---------+----------+--------------+----------+
| 4   | 4       | 9.83    | 9.49     | 0.34         | 998.67   |
+-----+---------+---------+----------+--------------+----------+
| 5   | 5       | 9.83    | 9.49     | 0.34         | 998.34   |
+-----+---------+---------+----------+--------------+----------+
| 6   | 6       | 9.83    | 9.48     | 0.34         | 997.99   |
+-----+---------+---------+----------+--------------+----------+
| 7   | 7       | 9.83    | 9.48     | 0.35         | 997.65   |
+-----+---------+---------+----------+--------------+----------+
| 8   | 8       | 9.83    | 9.48     | 0.35         | 997.30   |
+-----+---------+---------+----------+--------------+----------+
| 9   | 9       | 9.83    | 9.47     | 0.35         | 996.95   |
+-----+---------+---------+----------+--------------+----------+
| 10  | 10      | 9.83    | 9.47     | 0.36         | 996.59   |
+-----+---------+---------+----------+--------------+----------+
| 11  | 11      | 9.83    | 9.47     | 0.36         | 996.23   |
+-----+---------+---------+----------+--------------+----------+
| 12  | 12      | 9.83    | 9.46     | 0.36         | 995.87   |
+-----+---------+---------+----------+--------------+----------+
| ... |         |         |          |              |          |
+-----+---------+---------+----------+--------------+----------+
| 355 | 7       | 9.83    | 0.54     | 9.29         | 47.72    |
+-----+---------+---------+----------+--------------+----------+
| 356 | 8       | 9.83    | 0.45     | 9.37         | 38.35    |
+-----+---------+---------+----------+--------------+----------+
| 357 | 9       | 9.83    | 0.36     | 9.46         | 28.88    |
+-----+---------+---------+----------+--------------+----------+
| 358 | 10      | 9.83    | 0.27     | 9.55         | 19.33    |
+-----+---------+---------+----------+--------------+----------+
| 359 | 11      | 9.83    | 0.18     | 9.64         | 9.69     |
+-----+---------+---------+----------+--------------+----------+
| 360 | 12      | 9.78    | 0.09     | 9.69         | 0.00     |
+-----+---------+---------+----------+--------------+----------+

Pero el doble pago obviamente produce un saldo negativo... Necesito que sea exactamente igual: llegar a cero en el último.

+-----------------------------------------------------------------+
| Double paying (july and december)                               |
+-----+---------+-----------+----------+--------------+-----------+
| #   | # Month | Payment   | Interest | Amortization | Balance   |
+-----+---------+-----------+----------+--------------+-----------+
| 0   |         |           |          |              | 1,000.00  |
+-----+---------+-----------+----------+--------------+-----------+
| 1   | 1       | 9.83      | 9.50     | 0.33         | 999.67    |
+-----+---------+-----------+----------+--------------+-----------+
| 2   | 2       | 9.83      | 9.50     | 0.33         | 999.34    |
+-----+---------+-----------+----------+--------------+-----------+
| 3   | 3       | 9.83      | 9.49     | 0.33         | 999.01    |
+-----+---------+-----------+----------+--------------+-----------+
| 4   | 4       | 9.83      | 9.49     | 0.34         | 998.67    |
+-----+---------+-----------+----------+--------------+-----------+
| 5   | 5       | 9.83      | 9.49     | 0.34         | 998.34    |
+-----+---------+-----------+----------+--------------+-----------+
| 6   | 6       | 9.83      | 9.48     | 0.34         | 997.99    |
+-----+---------+-----------+----------+--------------+-----------+
| 7   | 7       | 19.65     | 9.48     | 10.17        | 987.82    |
+-----+---------+-----------+----------+--------------+-----------+
| 8   | 8       | 9.83      | 9.38     | 0.44         | 987.38    |
+-----+---------+-----------+----------+--------------+-----------+
| 9   | 9       | 9.83      | 9.38     | 0.45         | 986.93    |
+-----+---------+-----------+----------+--------------+-----------+
| 10  | 10      | 9.83      | 9.38     | 0.45         | 986.48    |
+-----+---------+-----------+----------+--------------+-----------+
| 11  | 11      | 9.83      | 9.37     | 0.46         | 986.03    |
+-----+---------+-----------+----------+--------------+-----------+
| 12  | 12      | 19.65     | 9.37     | 10.29        | 975.74    |
+-----+---------+-----------+----------+--------------+-----------+
| ... |         |           |          |              |           |
+-----+---------+-----------+----------+--------------+-----------+
| 355 | 7       | 19.65     | -43.01   | 62.66        | -4,589.83 |
+-----+---------+-----------+----------+--------------+-----------+
| 356 | 8       | 9.83      | -43.60   | 53.43        | -4,643.26 |
+-----+---------+-----------+----------+--------------+-----------+
| 357 | 9       | 9.83      | -44.11   | 53.94        | -4,697.20 |
+-----+---------+-----------+----------+--------------+-----------+
| 358 | 10      | 9.83      | -44.62   | 54.45        | -4,751.65 |
+-----+---------+-----------+----------+--------------+-----------+
| 359 | 11      | 9.83      | -45.14   | 54.97        | -4,806.61 |
+-----+---------+-----------+----------+--------------+-----------+
| 360 | 12      | -4,852.27 | -45.66   | -4,806.61    | 0.00      |
+-----+---------+-----------+----------+--------------+-----------+

Eso en hojas de google, aquí .

¿Alguna idea? Realmente apreciaré la ayuda!

gracias de antemano

La forma más fácil de resolver esto es con un programa de computadora. Las fórmulas de amortización están diseñadas para aceptar el mismo pago cada período... Realmente no funcionan con pagos irregulares porque tienes que recalcular la amortización restante en el mes en que ocurre el pago irregular.
Para su hoja de cálculo, todo lo que debe hacer es cambiar el pago en las celdas apropiadas (como lo está haciendo ahora) y luego poner una fórmula especial en la última celda que calcula el pago de liquidación.
@RobertHarvey, muchas gracias, pero precisamente estoy tratando de crear mi propio código para resolver esto... así que la primera sugerencia está fuera de lugar para mí. Sobre el segundo, mi hoja ya está haciendo eso: el último pago asume el papel de corregir cualquier ronda () falta de precisión.
Bueno, entonces no entiendo muy bien. El último pago debe diseñarse de modo que siempre resulte en un saldo de cero.
Y es: docs.google.com/spreadsheets/d/… . El saldo final (pago #360) es cero en ambas tablas. El que tiene pagos iguales funciona bien, pero el otro termina pagando mucho más por el préstamo (eso es que el pago también es un número negativo)
OK, creo que veo lo que estás tratando de hacer. Desea diseñar un pago mensual de modo que, si se realizan pagos adicionales irregulares a intervalos regulares, la hipoteca aún se amortizará en un período de 360 ​​meses. Hay un enfoque ingenuo que utiliza una búsqueda binaria; básicamente, escribiría un código que "adivina" el valor mensual correcto, calcule la amortización, incluidos los pagos irregulares, vea si está por encima o por debajo de los 360 meses, cree una nueva suposición y repita hasta que finalmente converja en la solución correcta.
No sé cómo hacerlo con una sola fórmula matemática, pero estoy bastante seguro de que tal ejercicio probablemente involucre ecuaciones diferenciales, lo cual está más allá de mi capacidad matemática.
¡Sí, eso es todo! Pero mis pagos irregulares son regulares: 2 extra cada año. Así que creo que debería haber una manera de diseñar un pago un poco más bajo pero regular.
El interés se calcula puntualmente en cada ciclo de pago. Entonces, la única forma de hacerlo (aparte de alguna ecuación diferencial de la que no estoy al tanto) es aumentar el pago en los intervalos deseados, ejecutar la amortización hasta que llegue a cero, ver si resultó en 360 pagos, ajustar el pago inicial y empezar de nuevo. Repita hasta que encuentre el pago que logre el resultado deseado de 360 ​​pagos a saldo cero.
Algo así, pero estoy buscando un enfoque no iterativo. Como la fórmula de pago, pero incluyendo estos adicionales que no pagan intereses: P = A / ((( 1 + i ) ^ n - 1 )/( i ( 1 + i ) ^ n ))
Si lo entendi. Claramente, la fórmula estándar no va a ser suficiente.
¿Por qué los pagos en su ejemplo son tan irregulares? Esperaría que fueran todos iguales a excepción de los pagos dobles, que deberían ser el doble de los demás.
Porque en el Perú suelen calcular el pago igual/fijo usando la tasa anual más la del seguro de vida. Resté el monto mensual del seguro de vida y luego resulta en un pago irregular... puede que no sea un buen ejemplo, pero es todo lo que tengo por ahora. Actualizaré el ejemplo y agregaré lo original, tal vez sea mejor.
No entiendo cómo es esto una solución :(

Respuestas (1)

Logré calcular el pago único que dejó el saldo en cero :)

El truco era: calcular una proporción mensual, duplicar esa proporción en cada mes de pago doble, sumar todas esas proporciones y dividir el monto principal en eso.

Se necesitan dos iteraciones, ¡pero funciona bien! Todavía buscando una forma no iterativa ...

La proporción mensual: 1/(1+monthly rate)^period, ej. el ultimo deberia ser 1/(1+0.00948879293)^360= 0.03337792393. Dado que el último es de doble pago: 0.03337792393*2= 0.06675584786.

Ya actualicé las fórmulas en la misma hoja de Google ( aquí ) y trabajaré en una solución lineal.

Como esto:

+──────+──────────+──────────────+────────+──────────+───────────+───────────────+──────────+
| #    | # Month  | Double pay?  | Ratio  | Payment  | Interest  | Amortization  | Balance  |
+──────+──────────+──────────────+────────+──────────+───────────+───────────────+──────────+
| 0    |          |              |        |          |           |               | 1,000.00 |
| 1    | 1        | FALSE        | 0.99   | 8.46     | 9.50      | -1.04         | 1,001.04 |
| 2    | 2        | FALSE        | 0.98   | 8.46     | 9.51      | -1.05         | 1,002.10 |
| 3    | 3        | FALSE        | 0.97   | 8.46     | 9.52      | -1.06         | 1,003.16 |
| 4    | 4        | FALSE        | 0.96   | 8.46     | 9.53      | -1.07         | 1,004.23 |
| 5    | 5        | FALSE        | 0.95   | 8.46     | 9.54      | -1.08         | 1,005.32 |
| 6    | 6        | FALSE        | 0.94   | 8.46     | 9.55      | -1.09         | 1,006.41 |
| 7    | 7        | TRUE         | 1.87   | 16.91    | 9.56      | 7.35          | 999.06   |
| 8    | 8        | FALSE        | 0.93   | 8.46     | 9.49      | -1.03         | 1,000.09 |
| 9    | 9        | FALSE        | 0.92   | 8.46     | 9.50      | -1.04         | 1,001.13 |
| 10   | 10       | FALSE        | 0.91   | 8.46     | 9.51      | -1.05         | 1,002.19 |
| 11   | 11       | FALSE        | 0.90   | 8.46     | 9.52      | -1.06         | 1,003.25 |
| 12   | 12       | TRUE         | 1.79   | 16.91    | 9.53      | 7.38          | 995.87   |
| ...  |          |              |        |          |           |               |          |
| 355  | 7        | TRUE         | 0.07   | 16.91    | 0.62      | 16.29         | 49.28    |
| 356  | 8        | FALSE        | 0.03   | 8.46     | 0.47      | 7.99          | 41.29    |
| 357  | 9        | FALSE        | 0.03   | 8.46     | 0.39      | 8.06          | 33.23    |
| 358  | 10       | FALSE        | 0.03   | 8.46     | 0.32      | 8.14          | 25.09    |
| 359  | 11       | FALSE        | 0.03   | 8.46     | 0.24      | 8.22          | 16.87    |
| 360  | 12       | TRUE         | 0.07   | 17.03    | 0.16      | 16.87         | 0.00     |
+──────+──────────+──────────────+────────+──────────+───────────+───────────────+──────────+
No se puede conseguir mucho mejor que eso.
Ahora estoy investigando una forma de calcular un factor de valor presente para series uniformes y no uniformes... quién sabe, puede funcionar: D
Cómo calcular el pago constante de un préstamo (como PMT) pero el pago doble adicional (sin intereses) dos veces al año
RespuestasAquíPolítica de privacidad1y, 0v