|
老墨之前一篇文章《有趣的函数（2）：PMT，帮你算清每月还多少钱？》曾经介绍过PMT()函数的应用，它用来计算每月固定还本付息的函数，算出来的结果是个总额，包含了本金部分和利息部分，但是不能区分多少是本金，多少是利息。所以，我们始终无法搞清楚究竟每个月还的钱，是本金和利息都是不变，还是变化的，到最后几个月是本金在减少呢，还是利息在减少？今天，老墨隆重介绍一下PMT()函数另外两个兄弟：PPMT()和IPMT()，让我们搞清楚PMT()算出来的每月还款额中究竟多少是本金、多少是利息，还钱也要还得清清楚楚、明明白白的不是。某人申请一笔一年期贷款，年利率4.75%，约定还款期共12期，还款方式每月固定还本付息。用PMT()函数我们可以很快算出每个月固定归还本息金额是4274.65元。B8单元格就是PMT()算的结果，公式为：=PMT(B5,B7,B3)，其中B5单元格为月利率（=年利率/12），B7单元格为12个月还款期，B3单元格为贷款总额。PPMT(rate, per, nper, pv, [fv], [type])1）Rate，必需。各期利率（按月还款则是月利率，按年还款则是年利率）。2）per， 必需。 指定期数，该值必须在 1 到 nper 范围内。4）pv，必需。现值即一系列未来付款当前值的总和。5）fv，可选。 未来值，或在最后一次付款后希望得到的现金余额。 如果省略 fv，则假定其值为 0（零），即贷款的未来值是 0。如果不省略fv，则可以选填数字 0 或 1，用以指定各期的付款时间是在期初还是期末。请确保指定 rate 和 nper 所用的单位是一致的。 如果贷款为期四年（年利率 12%），每月还一次款，则 rate 应为 12%/12，nper 应为 4*12。 如果对相同贷款每年还一次款，则 rate 应为 12%，nper 应为 4。图中D、E列按还款12期所列表格形式，以E3单元格公式为例：=PPMT($B$5,D4,$B$7,-$B$3,0)，其中：2）参数per，D4是期数，相对地址，下拉过程依行变动；4）参数pv，为-$B$3，还款总额-50000；将E3单元格公式向下复制后便可得出一列12期每期偿还本金的金额，从中我们看出本金是由少到多每期增加的，这意味着利息将是由多到少逐期减少的。IPMT(rate, per, nper, pv, [fv], [type])1）Rate，必需。各期利率（按月还款则是月利率，按年还款则是年利率）。2）per， 必需。 指定期数，该值必须在 1 到 nper 范围内。4）pv，必需。现值即一系列未来付款当前值的总和。5）fv，未来值，或在最后一次付款后希望得到的现金余额。 如果省略 fv，则假定其值为 0（例如，贷款的未来值是 0）。6）Type，可选。 数字 0 或 1，用以指定各期的付款时间是在期初还是期末。 如果省略 type，则假定其值为 0。1）与PPMT()一样，确保指定 rate 和 nper 所用的单位是一致的。要么按年计算，要么按月计算，不可年与月混用。2）对于所有参数，支出的款项，如银行存款，以负数表示；收入的款项，如股息支票，以正数表示。图中F列以IPMT()计算的每期偿还利息金额，以F3单元格公式为例：=IPMT($B$5,D4,$B$7,-$B$3,0)，仔细对比与PPMT()参数写法，其他两者是一致的，但结果却是完全不同。同样我们将F3单元格公式向下复制后便可得出一列12期每期偿还利息的金额，利息确实是由多到少逐期减少的。到了这里至于PMT与PPMT、IPMT三者之间关系其实很明显了嘛！就是：下图中G列就是本金 利息之和，与B8单元格PMT()得出结果是一致的。H列是每期还款之后本金的变化，公式很简单：=$B$3-SUM($E$4:E4)，就是贷款本金总额减去每期归还本金的依次累计。至此，我们终于搞清楚了每月等额还本付息还的那些钱里究竟有多少是本金，有多少是利息了，欠债还钱还是要弄个明白的。你在工作遇到有趣的Excel案例及问题，欢迎@我，相互交流并分享给e友~~~
|
