¿Cuál sería el equivalente matemático de la fórmula CUMPRINC en Excel?

Question

Trabajando en una aplicación en la que quiero calcular el capital pagado de una hipoteca después de n años.

Hipoteca (M) de $100.000 Interés (I) del 5% Plazo de amortización (T) de 25 años El pago mensual (P) es de $581,60 (hipoteca canadiense) Escenario: 5 años o 60 meses

La fórmula CUMPRINC es:

CUMPRINC((I/2+1)^(2/12)-1, (T * 12), M, 1, 60, 0) = $11,492.49

Intenté encontrar la fórmula, pero todas las fuentes encontradas hasta ahora no desglosan un ejemplo que he podido hacer funcionar.

¡Gracias!

Answer 1

Encontré una excelente respuesta en la publicación.
¿Cómo calculo el capital pagado de una hipoteca?

Cito esta respuesta a continuación:

La pregunta es: "Me gustaría saber cuánto habría pagado en capital de la hipoteca después de n períodos".

No está claro si desea que se reembolse el capital o el capital restante, por lo que aquí hay fórmulas para el capital restante en el mes n, el capital reembolsado en el mes n y el capital acumulado reembolsado en el mes n.

p[n] = (d + (1 + r)^n (r s - d))/r

pr[n] = (d - r s) (r + 1)^(n - 1)

accpr[n] = (d - r s) ((1 + r)^n - 1)/r  

dónde

p[n] is the principal remaining in month n, i.e the balance
pr[n] is the principal repayment in month n
accpr[n] is the accumulated principal repaid in month n

s is the initial loan principal
r is the monthly interest rate i.e. nominal annual rate ÷ 12
d is the regular monthly payment

Ejemplo

Tomando un préstamo de £1000 durante 3 años con un interés del 10% mensual (bastante alto, pero es sólo un ejemplo), el pago mensual dpor fórmula estándares

s = 1000
r = 0.1
n = 36

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

Usando estas cifras en un cálculo del principal restante, es decir, el saldo:

s = 1000
r = 0.1
d = 103.34306381837332

n = 36
p[n] = (d + (1 + r)^n (r s - d))/r = 0 as expected

Parcela del capital restante durante el plazo de 3 años

p[n] = (d + (1 + r)^n (r s - d))/rpara n = 0an = 36

Asimismo para el cálculo de las amortizaciones de principal:

Gráfico de pagos de principal durante el plazo de 3 años

pr[n] = (d - r s) (r + 1)^(n - 1)para n = 1an = 36

Los pagos de principal acumulados después de 36 meses:

n = 36
accpr[36] = (d - r s) ((1 + r)^n - 1)/r = 1000

en comparación con los reembolsos totales de 36 d = 3720.35.

Ejemplo de tabla de amortización

month  interest   principal repayment =          accumulated     balance
n      at 10%     payment - interest repayment   princ. repmt.   p[n]
0                                                                1000
1      100        103.34306 - 100 = 3.34306        3.34306       996.657
2      99.6657    103.34306 - 99.6657 = 3.67737    7.02043       992.98
3      99.2979    103.34306 - 99.2979 = 4.04511    11.0655       988.934
...
35     17.9356    103.34306 - 17.9356 = 85.4075    906.052       93.9482
36     9.39482    103.34306 - 9.39482 = 93.9482    1000          0

Derivación

El saldo de un préstamo sigue esta ecuación de recurrencia.

p[n + 1] = p[n] (1 + r) - d

dónde

p[n] is the balance of the loan in month n
r is the monthly interest rate
d is the regular monthly payment

Esto se puede resolver así (usandoMatemáticasen este caso).

RSolve[{p[n + 1] == p[n] (1 + r) - d, p[0] == s}, p[n], n]

dóndes is the initial loan principal

flexiblep[n_] := (d + (1 + r)^n (r s - d))/r

Esta notación expresa una fórmula para el saldo del mes n, que se puede utilizar en una función para el pago del principal pr(es decir, el pago regular menos el pago de intereses sobre el saldo del mes anterior).

pr[n_] := d - (p[n - 1] r)

La combinación de estas expresiones produce una expresión en términos de d, r, s y n.

pr[n_] := (d - r s) (r + 1)^(n - 1)

Después de nperíodos el principal acumulado reembolsado es:

accpr[n] = Σ(d - r s) (r + 1)^(k - 1)para k = 1ak = n

∴ por inducción,accpr[n] = (d - r s) ((1 + r)^n - 1)/r

Apéndice

Los resultados anteriores se pueden obtener de forma más sencilla utilizando elFórmula estándar para el valor presente de una anualidad ordinaria., tratando la parte restante de la hipoteca como un pequeño préstamo en sí.

Por ejemplo, obteniendo valores para el mes 28.

s = 1000
r = 0.1
n = 36

P = r s/(1 - (1 + r)^-n) = 103.34306381837332

El saldo restante en el mes 28.

x = 36 - 28 = 8

balance = P(1 - (1 + r)^-x)/r = 551.328

principal paid = principal - balance = 448.672

Lo cual concuerda con la formulación anterior.

accpr[28] = 448.672

y como Wick proporciona para Excel y Google Sheets

=CUMPRINC(0.1,36,1000,1,28,0)

-448.672