excel - 从 Excel 中的多个主体计算 PMT

Question

我正在准备一份现金流量表，我每个月都会在其中贷款——所有贷款的利率和期限都相同。因此，我需要从到目前为止所有贷款中预测每个月的 EMI、利息和本金。

实际上，我发现了 PMT。我以 1 美元计算了 PMT，并将其乘以所有事件本金的总和(使用 OFFSET 公式确定一个时期是否在贷款期限内)。就像是...

=SUM(OFFSET(AMOUNT_HEADER, MAX(1, Curr_Period-(DURATION*12)+1), 0, MIN(Curr_Period, DURATION*12), 1))*-PMT(Interest/12,DURATION*12,1)

现在，虽然我能够破解EMI，主要是因为它每个月“相等”，但我无法弄清楚如何分割本金和利息。

任何人都可以提出一些准确的或每月本金和利息的近似值吗？

Answer 1

查看 Excel 函数 PPMT 和 IPMT，它们分别计算贷款期间任何时期的本金和利息。要获得任何时期的利息支付总和，您可以使用 ARRAY 公式 (CTRL-ENTER):

=SUM(IPMT(Interest/12,ROW(OFFSET(AMOUNT_HEADER,Curr_Period+1,0,1,1))-ROW(OFFSET(AMOUNT_HEADER,1,0,Curr_Period,1)),DURATION*12,-OFFSET(AMOUNT_HEADER,1,0,Curr_Period,1),0,0))

此公式假定它们是一列贷款金额，列标题为 AMOUNT_HEADER。假设第一笔贷款发生在 Period=0。 Curr_Period 假设为 1, 2, 3, ... 公式的 ROW(...)-ROW(...) 部分用于为每笔贷款生成一系列年龄，因此如果 Curr_Period = 5，这部分公式生成 {5;4;3;2;1}。这些年龄用作 IPMT 中的每个参数。

备注 :这个公式不能防止超过 Curr_Period 超过任何贷款的期限，但可以修改为这样做。它也可以修改为总结本金而不是利息