☰

Excel 中 CUMPRINC 公式的数学等价公式是什么

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 中 CUMPRINC 公式的数学等价公式是什么

我正在开发一个应用程序，想要计算 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

试图找到公式，但到目前为止找到的所有来源都无法分解我能够完成的例子。

谢谢！

答案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 Sheets 提供的那样

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

-448.672

相关内容