☰

Excel 中 CUMPRINC 公式的數學等效項是什麼

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 中 CUMPRINC 公式的數學等效項是什麼

正在開發一個應用程序，我想計算 n 年後償還抵押貸款的本金。

抵押貸款 (M) 為 $100,000 利息 (I) 為 5% 攤銷期限 (T) 為 25 年 每月 Pmt (P) 為 $581.60（加拿大抵押貸款） 場景：5 年或 60 個月

CUMPRINC 公式為：

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

試圖找到公式，但到目前為止找到的所有來源都沒有分解我能夠工作的範例。

謝謝！

答案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))/rn = 0為了n = 36

在此輸入影像描述

本金還款的計算也是如此：

三年期本金償還情形圖

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 Sheets 提供的那樣

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

-448.672

相關內容