Какой математический эквивалент формулы CUMPRINC в Excel?

Question

Работаю над приложением, в котором хочу рассчитать сумму основного долга, выплачиваемого по ипотеке через n лет.

Ипотека (M) на сумму 100 000 долларов США Процентная ставка (I) 5% Срок амортизации (T) 25 лет Ежемесячный платеж (P) составляет 581,60 долларов США (канадская ипотека) Сценарий: 5 лет или 60 месяцев

Формула CUMPRINC выглядит следующим образом:

CUMPRINC((I/2+1)^(2/12)-1, (T * 12), M, 1, 60, 0) = 11 492,49 долл. США

Пытался найти формулу, но все найденные на данный момент источники не содержат ни одного примера, который я мог бы реализовать.

Спасибо!

Answer 1

Я нашел отличный ответ на пост
Как рассчитать сумму основного долга по ипотеке?

Я цитирую этот ответ ниже:

Вопрос: «Я хотел бы узнать, какую сумму основного долга я должен был выплатить по ипотеке через n периодов».

Немного неясно, хотите ли вы получить погашенный основной долг или оставшийся основной долг, поэтому вот формулы для оставшегося основного долга в месяце n, основного долга, погашенного в месяце n, и накопленного основного долга, погашенного в месяце 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  

где

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

Пример

Взяв кредит в размере 1000 фунтов стерлингов на 3 года с процентной ставкой 10% в месяц (довольно высокая, но это всего лишь пример), ежемесячный платеж dпогашается стандартная формулаявляется

s = 1000
r = 0.1
n = 36

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

Используя эти цифры при расчете остатка основного долга, т.е. баланса:

s = 1000
r = 0.1
d = 103.34306381837332

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

Участок основного долга, оставшийся на срок 3 года

p[n] = (d + (1 + r)^n (r s - d))/rдля n = 0того, чтобыn = 36

Аналогично для расчета основного долга:

График выплат основного долга в течение 3-летнего срока

pr[n] = (d - r s) (r + 1)^(n - 1)для n = 1того, чтобыn = 36

Накопленные выплаты основного долга через 36 месяцев:

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

по сравнению с общим объемом погашения 36 d = 3720.35.

Пример таблицы амортизации

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

Вывод

Остаток кредита следует этому уравнению рекуррентности.

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

где

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

Это можно решить следующим образом (используяМатематикав этом случае).

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

гдеs is the initial loan principal

уступающийp[n_] := (d + (1 + r)^n (r s - d))/r

Эта запись выражает формулу для остатка в месяце n, которую можно использовать в функции для погашения основного долга pr(то есть регулярного погашения за вычетом выплаты процентов по остатку за предыдущий месяц).

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

Объединение этих выражений дает выражение в терминах d, r, s и n.

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

По истечении nпериодов накопленная сумма основного долга, выплаченная по кредиту, составляет:

accpr[n] = Σ(d - r s) (r + 1)^(k - 1)для k = 1того, чтобыk = n

∴ по индукции,accpr[n] = (d - r s) ((1 + r)^n - 1)/r

Приложение

Вышеуказанные результаты можно получить проще, используястандартная формула для текущей стоимости обычного аннуитета, рассматривая оставшуюся часть ипотеки как небольшой кредит.

Например, получение значений за 28 месяц.

s = 1000
r = 0.1
n = 36

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

Остаток в 28 месяце

x = 36 - 28 = 8

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

principal paid = principal - balance = 448.672

Что согласуется с предыдущей формулировкой

accpr[28] = 448.672

и как Wick обеспечивает Excel и Google Таблицы

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

-448.672