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) の初めの未払い金額が算出されることを意味します。
他の 2 つの方程式は、ローンの開始条件と終了条件を単純に示しています。
支払いpが各期間の終わりではなく初めに行われる場合、最初の式は次のように変わることに注意してください:
v(i+1) = (v(i)-p)*(1+r) そしてv(i)は期間iの初めの未払い額になります。支払いが行われる直前。
以下の分析では、L、r、n に基づいて p を決定し、各期間の終了時に支払いが行われることを前提としています。
数学的分析
これは、連続する期間の未払いローン額の関係から始まります
v(i+1) = v(i)*(1+r) - p [式1]
式1はすべての期間に適用できるので、
v(i+2) = v(i+1)*(1+r) - p
ここで、式1をこの2番目の式の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
したがって、式2を使用してv(i+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 が乗じる因数は、逆順に書かれた有限等比級数です。等比級数 (Google で検索してください) とは、連続する各項が前の項に一定量を乗じた合計です。
一般的な有限幾何級数では、
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)
潜在的には、4 つの異なるシナリオが考えられます。支払いと借入の取引が行われるタイミングはそれぞれ 2 つあり、期間の開始時または終了時のいずれかです。したがって、2 種類の取引のそれぞれに 2 つの可能性があり、合計で 4 つの可能性が生まれます。各シナリオは、上記のような分析に適しています。分析されるシナリオは、各期間の終了時に支払い、開始時に追加の借入を行うというものです。残りの 3 つのシナリオは、読者の課題として残されています。
警告
実際には、期間が数か月の場合、金融機関は各月の長さが異なることを認識するために日次利息計算を使用することが多く、一部の金融機関（Barclaycard UK など）では、口座に利息が適用される日付を月ごとに変更しています。したがって、一般的に、PMT計算と上記の分析に基づく計算は、現実に何が起こるかについての妥当ではあるものの、正確な推定値ではありません。
(*) 真の数学者は、もちろん、出現したパターンの観察を「真実」として頼りにするだけでなく、そのパターンの一般的な真実を証明 (または反証) しようとします。簡単にするために、方程式 m が一般的に真実であることを示す証明は省略しましたが、私を信じてください (私は数学の分野でいくつかの学位を持っています)、証明は存在します。