我想修改 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) 為第一期開始時的未清金額付款 p 即將付款前。
以下的分析根據 L、r 和 n 確定 p,並假設在每個期間結束時付款。
數學分析
這要從連續期間未償還貸款金額之間的關係開始
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
因此,使用公式 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)、r和p項表示v(i+1)、v(i+2)和v(i+3)。方程式 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)
可能有四種不同的場景:支付和借貸交易進行時各有兩種可能性- 可以在周期開始或結束時- 因此兩種類型的交易中的每一種都有兩種可能性,總共有四種可能性。每個場景都適合上述分析。每個期間結束時的付款和開始時的額外借款是分析的場景 - 其餘三種可能的場景留給讀者作為練習。
警告
在實踐中,當週期為幾個月時,金融機構經常使用每日利息計算來認識到每個月的長度不同,有些(看看你,巴克萊英國卡)甚至會改變每月應用利息的日期到帳戶。因此,一般來說,PMT
計算和基於上述分析的計算對現實情況提供了合理但不精確的估計。
(*)當然,真正的數學家不僅會依賴對湧現模式的觀察作為“真理”,還會著手證明(或反駁)該模式的普遍真理。為了簡單起見,我省略了證明方程式 m 一般為真所涉及的證明,但是,相信我(我有幾個數學學科的學位),證明是存在的。