delphij's Chaos

选择chaos这个词是因为~~实在很难找到一个更合适的词来形容这儿了……

22 Jan 2012

包含贷款保险的还款方案计算

问题:如果银行要求购买贷款保险,并且已知贷款保险在本金+首付达到22%时会自动撤销,但已经支付或预付的部分不会退还,已知贷款总金额、固定利率、年限,计算采用不同还款方案(按PMT计划,比PMT多还定额等)中一次性支付,以及每月支付的金额变化,以及若 cash out 时的收益差异。

(本质上这个问题是个动态规划问题,不过具体的计算过程比较有实用价值,本文以 Google Docs 的 Spreadsheet 来举例,其他电子表格软件如 OpenOffice 的 Calc,以及 Excel 大同小异)。

假定总共的还款期数为 360 期(30年),可以创建一个有362行的表格(第一行是表头)。A列填入月份的序号,0 .. 360,B列填入还款日期(这个可以用公式做,也可以直接填充,这些日期并不参与计算,因此不太重要)。C列全部填0(计划多还)。

首先要算出贷款的 PMT 值,这个很容易,假设年名义利率为 RATE,贷款金额为 PV,贷款期数为 NPER(按月算),则公式为 =PMT(RATE/12,NPER,PV) 。把这个数字(由于 Google Docs 的限制,最好直接复制这个数字然后填充,如果是公式,则会因为填充时会自动根据单元格位置来调整导致数字不对)填入所有360行的 E 列,标记为"原计划还款"。

由于需要考虑提前还款的情况,我们不能用 PPMT 和 IPMT 来直接计算本利的偿还情况。但是,如果知道上一月份的本金余额,当月偿还的利息是容易计算的(相当于以同样利率、上月本金余额,按360-当月序号的长度来重新贷款)。在 L 列填入当月本金余额:在第0个月,很明显这个金额就是总价 - 首付。因此,在L2这一行填写这个数值。接下来就可以知道每个月所偿还的利息了,我们用 J 列来表示所付利息,在 J3 填写 =IPMT(RATE/12,1,360-A3+1,L2),然后将其填充至 J4..J362。此时,J4..J362还无法算出(因为我们并不知道 L3..L361 的数值)。

为了方便观察提前 payoff 的效果,我们还应算出每个月累计偿还的总利息。在 K 列填入利息偿还累计,K2填0, K3 公式为 K2+J3,填充至 K4..K362。

想要知道当月的本金余额,就必须知道当月偿还的本金金额。这个金额是当月支付的还款额减去当月应该支付的利息,即 E列 + C列 - J列。对于固定利率的贷款,如果提前还款的话,由于本金减少但每月还款额并不减少,因此会导致之后每个月偿还的本金增加(这也是为什么提前还款可以减少很多利息)。所以为了直观起见,在 I3 填入公式 =PPMT(RATE/12,1,360-A3+1,L2),并填充到 I4..I362。这样,当月额外偿还的本金就可以用 C 列 + (E列 - I列 - J列) 算出。我们将这个公式填充到 D 列(额外偿还本金),并在 L3 填入公式 =IF(L3+I4+D4>0,L3+I4+D4,0),填充至 L4..L362。

F列是实际支付的贷款金额。F3公式为 =IF(L2>-E3-C3,E3+C3,-L2+J3),即如果当月可还清,则为还清金额,否则为原计划还款+额外还款金额。

G列填入当月需要缴纳的贷款保险金额。设贷款标的总价为 TOTAL,每月需要支付的贷款保险金额为 INS,则公式 G3 为 =-IF(L2>TOTAL*0.78,INS,0),其中 0.78,填充至 G4..G362。

H列填入实际支付给银行的金额,F+G。

这样就可以很容易的得出汇总值:

正常还款时,支付的总利息为 =CUMIPMT(RATE/12,360,PV,1,360,0)。

实际支付的利息总额为 =SUM(J:J)。

实际还款期数为 =COUNTIF(F:F,"<0")

实际支付的贷款保险费用为 预先支付的金额 + SUM(G:G)

在对应月份的 C 列填入计划多还的金额即可看到计算结果(注意,上述公式假定此列数值应为负值)。

上述方法仅供参考。


Archived: 1 Comment

Frank | August 2, 2014 11:36 AM

“在 L3 填入公式 =IF(L3+I4+D4>0,L3+I4+D4,0)”

公式应为 =IF(L2+I3+D3>0,L2+I3+D3,0) 。