Я хотел бы изменить функцию Excel PMT, чтобы учесть фиксированную ежемесячную выдачу по кредиту. Например, если кредит — это кредитная карта, держатель карты может тратить деньги каждый месяц, и это изменит ежемесячный платеж, необходимый для погашения кредита.
Этот ответ определяет уравнение для функции PMT следующим образом P = (Pv*R) / [1 - (1 + R)^(-n)]:https://superuser.com/a/871411
Как можно изменить эту формулу, включив в нее фиксированный ежемесячный отток?
Например, если кредит — это кредитная карта, и держатель карты тратит по карте 50 долларов в месяц, как нам изменить приведенную выше формулу, чтобы учесть это? Срок кредита должен остаться прежним, но я хотел бы рассчитать новый ежемесячный платеж, необходимый для погашения кредита, чтобы учесть также и ежемесячный списание.
решение1
Такие функции, как PMTзащита пользователей от необходимости понимать математику, лежащую в основе финансовых расчетов Excel. Чтобы получить тип выражения, указанный вhttps://superuser.com/a/871411необходимо понять эти математические выкладки, а затем адаптировать их для работы в описанном сценарии.
Основные математические соотношения следующие:
v(i+1) = v(i)*(1+r) - p
v(1) = L
v(n+1) = 0
где сумма L берется в долг на n периодов под процентную ставку r за период и производятся выплаты сумм pв конце каждого периода. v(i) — сумма непогашенного кредита на начало i-го периода.
Первое соотношение (уравнение)
v(i+1) = v(i)*(1+r) - p
простыми словами это означает, что сумма задолженности на начало периода i увеличивается путем добавления процентов, начисленных в течение периода i, а затем вычитается на сумму платежа, произведенного в конце периода i, чтобы получить сумму задолженности на начало следующего периода (период i+1).
Два других уравнения просто определяют начальные и конечные условия кредита.
Обратите внимание, что если платежи p производятся в начале каждого периода (а не в конце), первое уравнение изменится на:
v(i+1) = (v(i)-p)*(1+r), а v(i) будет суммой задолженности на начало периода i.непосредственно перед совершением платежа p.
Приведенный ниже анализ, определяющий p через L, r и n, предполагает, что платежи производятся в конце каждого периода.
Математический анализ
Это начинается с соотношения между суммой непогашенного кредита в последовательных периодах
v(i+1) = v(i)*(1+r) - p [Уравнение 1]
поскольку уравнение 1 применимо ко всем периодам, то следует, что
v(i+2) = v(i+1)*(1+r) - p
Теперь, используя уравнение 1 для замены v(i+1) во втором уравнении, получаем
v(i+2) = (v(i)*(1+r) - p) * (1+r) - p
Которое, с небольшой перестановкой, можно записать как
v(i+2) = v(i)*(1+r)^2 - p * ((1+r) + 1) [Уравнение 2]
Опять же, из уравнения 1 следует, что
v(i+3) = v(i+2)*(1+r) - p
Итак, замена v(i+2) с использованием уравнения 2 дает
v(i+3) = (v(i)*(1+r)^2 - p * ((1+r) + 1)) * (1+r) - p
которое можно переписать как
v(i+3) = v(i)*(1+r)^3 - p * ((1+r)^2 + (1+r) + 1) [Уравнение 3]
Уравнения 1, 2 и 3, соответственно, выражают v(i+1), v(i+2) и v(i+3) через v(i), r и p. В уравнениях 1, 2 и 3 есть возникающий шаблон (*), который можно использовать для записи общего уравнения m как
v(i+m) = v(i)*(1+r)^m - p * ((1+r)^(m-1) + (1+r)^(m-2) + ... + (1+r) + 1)[Уравнение m]
Фактор, который умножает p, — это конечная геометрическая прогрессия, записанная в обратном порядке. Геометрическая прогрессия (погуглите) — это сумма, в которой каждый последующий член — это предыдущий член, умноженный на постоянную величину.
Для общей конечной геометрической прогрессии, записанной
S(m) = 1 + x + x^2 + x^3 + ... + x^(m-1)
Есть известное выражение, что
S(m) = (x^m - 1)/(x - 1)
В уравнении m геометрическая прогрессия записана в обратном порядке и x = 1+r, поэтому уравнение можно упростить до
v(i+m) = v(i)*(1+r)^m - p((1+r)^m - 1)/(1+r - 1))
или, упрощая последний член знаменателя
v(i+m) = v(i)*(1+r)^m - p((1+r)^m - 1))/r [Уравнение m']
Теперь установим общее значение m равным числу периодов n, установим i равным 1 и учтем граничные условия, что
v(1) = L и
v(n+1) = 0
Это дает версию уравнения m' в виде
0 = L*(1+r)^n - p((1+r)^n - 1)/r
что, с небольшой перестановкой, можно записать как
p = (L * r * (1+r)^n)/((1+r)^n - 1)
или, разделив числитель и знаменатель в правой части на (1+r)^n
p = (L*r)/(1 - (1+r)^(-n)) [Уравнение для p]
что, по сути, является ранее найденной формулой.
Сценарий с дополнительным заимствованием
Здесь предположим, что в начале каждого периода (включая первый) берется в долг дополнительная сумма b. v(i) теперь представляет собой сумму непогашенного кредита в начальном периоде i,непосредственно перед добавлением суммы b к кредиту.
Отношения сейчас
v(i+1) = (v(i)+b)*(1+r) - p
v(1) = L
v(n+1) = 0
применение того же вида анализа, который изложен выше, приводит к аналогии уравнения m' как
v(i+m) = v(i) * (1+r)^m + b * (1+r)*((1+r)^m - 1)/r - p * ((1+r)^m -1)/r
который после применения начальных и конечных условий может быть решен, после небольшой манипуляции, следующим образом:
p = (L * r)/(1 - (1+r)^(-n)) + b * (1+r)
Потенциально существует четыре различных сценария: по два варианта для каждого из моментов совершения транзакций платежа и заимствования — либо в начале, либо в конце периодов — то есть по два варианта для каждого из двух типов транзакций, что в сумме дает четыре варианта. Каждый сценарий поддается анализу, изложенному выше. Платежи в конце каждого периода и дополнительное заимствование в начале — это анализируемый сценарий — остальные три возможных сценария оставлены в качестве упражнения для читателя.
Предупреждение
На практике, когда периодами являются месяцы, финансовые учреждения часто используют ежедневные расчеты процентов, чтобы распознать, что каждый месяц имеет разную продолжительность, а некоторые (например, Barclaycard UK) даже меняют даты от месяца к месяцу, когда проценты начисляются на счета. Поэтому, как правило, PMTрасчеты и расчеты, основанные на приведенном выше анализе, дают разумные, но не точные оценки того, что происходит в реальности.
(*)Настоящие математики, конечно, не просто полагались бы на наблюдение эмерджентного паттерна как на «истину», а стремились бы доказать (или опровергнуть) общую истинность этого паттерна. Для простоты я опустил доказательство, необходимое для демонстрации того, что уравнение m верно в общем случае, но, поверьте мне (у меня есть пара степеней по математическим предметам), доказательство существует.