Cálculo de intereses diarios sobre una base de 30/360, considerando que el pago se produce antes de lo previsto - ¿cómo en Excel?

Question

Estoy configurando una hoja de cálculo de pagos para un préstamo recién concedido, y quiero poder hacer cálculos diarios de los intereses acumulados.

El deudor ha recibido un calendario de pagos con una estructura de sólo intereses, por lo que sabe de antemano que, siempre que no haga ninguna amortización no programada, tendrá que pagar la misma cantidad de intereses cada mes.

El prestamista ha previsto cobrar una cantidad fija de intereses cada mes, por lo que supongo que el devengo diario de intereses se calcula prorrateando la cantidad fija en función del número de días del periodo concreto.

Estoy tratando de averiguar en caso de que el deudor haga un pago anticipado y lo suficientemente grande como para amortizar parcialmente el principal, entonces debe haber un recálculo de los intereses diarios devengados para los días restantes en el período.

¿Puede alguien ayudarme en la lógica de cómo se debe hacer esto?

Answer 1

Estoy seguro de que debe querer decir "El prestamista ha previsto cobrar una cantidad fija de interés cada mes", porque los intereses serían proporcionales al saldo adeudado, que disminuye a medida que se va pagando el préstamo.

Así, si tomamos el ejemplo de un préstamo de 1.000 dólares, con un interés anual nominal del 7,2% compuesto mensualmente utilizando 30/360 amortizado en 12 meses.

Con

principal     s = 1000
annual rate .  .  0.072
daily rate  .  .  0.072/360 = 0.0002
monthly rate  r = 0.0002*30 = 0.006

number of months  n = 12
monthly payment   d = s r (1 + 1/((1 + r)^n - 1)) = 86.62

el importe del pago fijo sería de 86,62 dólares

Caso de un pago doble

Por ejemplo, si el prestatario realiza un doble pago en la tercera cuota.

La balanza b después de un normal Tercer pago ( x = 3 ) sería

x = 3
b = (d + (1 + r)^x (r s - d))/r = 756.69

y podrías recalcular los pagos a partir del tercer mes y serían los mismos

s = b
r = 0.006
n = 9
d = s r (1 + 1/((1 + r)^n - 1)) = 86.62

o calcular n de s , r & d

n = -(log(1 - (r s)/d)/log(1 + r)) = 9

Sin embargo, con el doble pago el principal s restante es menor

s = b - d = 670.07
r = 0.006
n = 9
d = s r (1 + 1/((1 + r)^n - 1)) = 76.70

y los importes de los pagos de los 9 meses restantes sólo serían de 76,70 dólares

O el pago podría mantenerse en 86,62 dólares y el préstamo se pagaría antes: Calculando n

s = b - d = 670.07
r = 0.006
d = 86.62
n = -(log(1 - (r s)/d)/log(1 + r)) = 7.945

El préstamo se pagaría antes de 8 pagos adicionales. Así que el cálculo del saldo después del séptimo pago adicional ...

s = b - d = 670.07
r = 0.006
d = 86.62

x = 7
b = (d + (1 + r)^x (r s - d))/r = 81.37

El saldo después del séptimo pago adicional es de 81,37 dólares y el pago final sería

b (1 + r) = 81.86

El pago final en el undécimo mes en total sería de 81,86 dólares

Puedes hacer estos cálculos en Excel, o con una calculadora de bolsillo. En realidad, no es necesario establecer una tabla de amortización en Excel, aunque es una buena comprobación.

Caso de un pago extra en cualquier día

Por ejemplo, si se realiza un pago extra de 100 dólares a los 10 días después de el tercer pago regular.

Como en el caso anterior, el saldo b después del tercer pago normal ( x = 3 ) sería

x = 3
b = (d + (1 + r)^x (r s - d))/r = 756.69

Después de diez días de intereses al tipo diario del 0,02%

s = b * (1 + daily rate * 10) = 756.69 * 1.002 = 758.20

Entonces se hace el pago extra de 100 dólares

s = s - 100 = 658.20

Para no reiniciar las fechas de pago, retroceda 10 días de intereses desde el nuevo saldo y vuelva a calcular los pagos para las fechas originales.

s = s/1.002 = 656.89

r = 0.006
n = 9

d = s r (1 + 1/((1 + r)^n - 1)) = 75.19

Después de la $100 payment the regular payments reduce to $ 75,19 por los 9 pagos restantes.