妙用PMT函数计算贷款的月偿还金额，一般人都不会的Excel技巧

妙用PMT函数计算贷款的月偿还金额，一般人都不会的Ex技巧

深度讲解：利用PMT函数计算贷款的月偿还金额！

计算贷款的月偿还金额——PMT函数对于年轻一族来说，贷款买房买车很常见，怎样计算贷款的月偿还金额呢？

Ex提供的PMT函数是完成这个任务的好工具。

PMT函数的定义以及说明：

PMT是基于固定利率以及等额分期付款方式，返回贷款的每期付款额，语法为：

PMT(，，v，fv，)

参数：贷款利率，该项贷款的付款总数，v为现值（也称为本金），fv为未来值（或最后一次付款后希望得到的现金余额），指定各期的付款时间是在期初还是期末（1为期初，0或省略为期末）。

说明：PMT返回的支付款项包含本金和利息，但不包括税款，保留支付或某些与贷款有关的费用。和单位要一致，例如，同样是4年期年利率10%的贷款，如果按月支付，应为10%/12，应为4*12；如果按年支付，应为10%，应为4。

例1：利用PMT函数计算每月还款额，如图3-13-10所示。

图3-13-10

B4公式参数设置如图3-13-11所示。

图3-13-11

注意：给定的利率是年利率，一定要改为月利率。单击确定后，得到图3-13-12。

图3-13-12

例2：PMT函数结合模拟运算表，分析计算出还款期限为60个月，每月“应付款”随着“贷款额”和“年利率”的变化而相应变化的结果，如图3-13-13所示。

图3-13-13

单击A2单元格，选中PMT函数，参数设置如图3-13-14所示。

单击确定，计算结果如图3-13-15所示。

图3-13-14

图3-13-15

鼠标选中A2:E8，选中数据菜单下的“模拟分析”中的“模拟运算表”，如图3-13-16所示。

图3-13-16

模拟运算表设置如图3-13-17所示。

单击“确定”，得到结果，如图3-13-18所示。

图3-13-17

图3-13-18

从上面的例子看来，只要掌握好PMT参数的使用，就很好使用PMT函数，千万别忘了利率是年还是月，这个很重要。

你距离算清贷款利率，还差一个IRR函数

本文开始将介绍NPV函数、IRR函数、XNPV函数和XIRR函数。这四个函数均为投资评价函数，比如IRR广受地产商、风险投资机构的欢迎。

相对于前面介绍的函数均基于等额分期付款这种规则的现金流，这四个函数可以帮助人们完成不规则现金流情况下的贷款计算问题。

在讲NPV函数前，需要先界定一个概念：贴现率。

由于资金具有时间价值，所以同样数量的资金在不同时间点价值不一样。老百姓常常感叹的钱不值钱就是货币的时间价值作用的结果。一笔现在的资金，也可以理解为是未来某一时点一定量的资金打个折扣到现在的价值，如“10000元按照年利率10%投资，一年后本利和为11000元”，按照折现理论也可以理解为“一年后的11000元，相当于现在的10000元，其贴现率为：=RATE(1,0,10000,-11000)=10%”。打折扣的过程称之为折现；打折扣时的利率，称之为折现率，即贴现率。

在贷款中，贴现率一般是时间的补偿和风险的补偿之和——前者对应货币资金时间上的折扣，为无风险利率，可以用国债来确定具体是多少；在借款活动中，后者对应借款方风险的折扣，比较难以量化。

在前面讲解Ex的文章中，第一消费金融（ID：TCFC）已经明确，投资和贷款，完全是一回事，不过因为立场不同产生了不同称谓——投资方的收益，也就是借款方付出的代价；投资方的收益率即借款方承担的利率。

贴现率是用来描述投资活动的，在贷款活动中即借款方承担的利率。讲完了贴现率这个术语，接下来开始讲NPV函数。

NPV由表示净值的N和表示现值的PV的首字母构成，中译为净现值。在投资中，NPV是未来一系列支出或收入的当前价值。

语法：=NPV(，v1，v2，…）

用途：通过使用贴现率，以及一系列未来支出和收入，求一项投资的净现值。

参数：R为某一期间的贴现率；V1、V2…为一定时期内若干笔支出及收入，必须至少包含一个负数（支出）和一个正数（收入）。

净现值是基于一定的贴现率，分别将未来的支出和收入折算成现值并求和，得到未来现金净流量现值，然后将这个未来现金净流量现值与原始投资额比较（净现值=未来现金净流量现值-原始投资额现值），会得到三种结果：

NPV=0，则此次投资不赚不亏；

NPV＞0，表示投资会赚钱，可以投资。

NPV＜0，表示投资会亏钱，不能投资。

案例：在贴现率为20%的情况下，某贷款中介向黄药师贷款100万元。黄药师以未来3年的净收入。此后的3年时间，黄药师的净收入分别为：36万元、25万和88万元。请问这个贷款中介此次给黄药师贷款转了没？

如上图所示，通过计算净现值=NPV(C2,B3:B5)+B2=-17129.63可知，贷款中介此次给黄药师借钱亏了。

接下来介绍XNPV函数、IRR函数和XIRR函数。

一、XNPV函数

在文章现金流不规则的情况下，如何计算贷款利率？末尾的案例中，贷款中介没从黄药师身上亏钱了。该问题如下：

在贴现率为20%的情况下，某贷款中介向黄药师贷款100万元。黄药师将未来3年的净收入全部交付贷款中介。此后的3年时间，黄药师的净收入分别为：36万元、25万和88万元。请问这个贷款中介此次给黄药师贷款赚了没？

很显然，黄药师每一年的净收入的确定，需要在该年度结束后，也即下一年才能确定。案例中贷款中介从黄药师处获得的现金流发生在期末，且间隔时间相等，均为1年。从这个案例可以看出来，NPV函数适用条件是现金流的发生（支出或收入）是基于相同的时间间隔（一般是一年）。

假如贷款中介对黄药师不放心，在黄药师获得一笔收入后马上就拿过来。前面的这个案例，修改为以下情况：

2017年10月7日，在贴现率为20%的情况下，某贷款中介向黄药师贷款100万元。黄药师将未来3年的净收入全部交付贷款中介。此后的3年时间，贷款中介一共从黄药师处获得3笔还款，分别是2018年2月20日的36万元、2019年5月5日的25万元和2020年9月18日的88万元。请问这个贷款中介此次贷款给黄药师赚了没？

在修改后的案例中，贷款中介从黄药师处获得的现金流的时间间隔不相同，因此不能再用NPV来计算。这时XNPV函数就出场了。

XNPV函数求的是一组现金流的净现值，这些现金流不一定定期发生（-iiifhfw,whihh）。

XNPV函数语法：=（，v，）

参数：为现金流的贴现率。v值为与中的支付时间相对应的一系列现金流，必须至少包含一个正数（收入）和一个负数（支出）。XNPV函数是基于1年365天制来计算，将年利率折算成等价的日实际利率。日期为与现金流支付相对应的支付日期表。

如上图所示，修改后的案例中，贷款中介的现金流的净现值结果为=XNPV(B1,B2:B5,A2:A5)=37801.76。贷款中介此次从黄药师身上赚到钱了，虽然不多。修改后案例与上一篇文章那个案例，虽然贷款中介都是3次从黄药师那里拿钱，且拿的数额都一样，但是因为拿钱的时间不一样，最后一个略微亏损，一个略微盈利，就是货币时间价值的体现。

二、IRR函数、XIRR函数

IRR全称IRfR，中译为内部收益率。

IRR是一系列现金流的净现值等于0时，即NPV=0时，相对应的贴现率。比如，假如一个公司用于放贷的资金成本是20%，通货膨胀率确定为10%，不考虑风险补偿率，也即是假设所有放出去的钱不需要付出任何贷后管理成本均能收回来，然后不考虑其它一切费用，那么，这个公司获得的IRR为30%时刚好不赔不赚。如果IRR在30%以上，就可以赚钱；反之亏钱。

IRR函数语法：=IRR（v，[]）

参数：v为一系列按照时间先后顺序发生的现金流；为对IRR计算结果的估计值，不用填写。

与NPV一样，IRR也是用来评价一项投资的收益。

NPV与0比较，为正数可以投资，为负数不能投资，所得的值是一个具体的收益数字，相比于IRR更直观指导投资具体赚多少钱或者亏多少钱。在贷款中，NPV为正数，该数值意味着具体的收益，说明该次贷款在付出了融入资金的成本、资金的时间成本后，获得了不错的风险补偿。但是，NPV的局限性在于，不能用于投资总额不同的项目的比较，比如以下情况：现在有A公司和B公司都有100亿现金。在接下来的5年时间，A公司将这100亿投资于一个项目，这5年的现金流对应的NPV为10亿元；B公司在接下来的5年时间，从100亿元中调取了10亿投了一个项目，这个项目的现金流对应的NPV为5亿元。

IRR与NPV不一样的是，对于一次投资的评价不是用一个具体的金额，而是一个百分数。如果一个项目只用投资一次，后续不用再投资，可以将IRR理解为初始投资额按照IRR以复利计算，到最后退出。比如天图资本（833979.OC）在2009年投资梦网集团（002123.SZ）共2000万元，后续未投资，在2015年通过上市收购的办法退出，整体回报为25113万元，IRR为48.36%。这就相当于天图资本的2000万元从2009年起以48.36%的复利增长了6年多时间。

IRR函数要求现金流必须定期（如每月或每年）发生。如果现金流发生的时间间隔不同，那么就需要用到XIRR函数。IRR函数之于NPV函数，相当于XIRR函数之于XNPV函数。

XIRR函数语法：=（v，，[]）

参数：这三个参与前面均已介绍。

XIRR函数返回一组不一定定期发生的现金流的内部收益率。与XNPV函数一样，XIRR函数也需要现金流发生的具体日期。

在前述案例中，贷款中介从黄药师处收回资金的时间不同，对应的内部收益率也不同，前者为=IRR(B1:B4)=19.07%，后者为=XIRR(E1:E4,D1:D4)=22.43%。这就解释了为什么前一种情况贷款中介略有亏损，而后面一种情况下有微薄盈利，因为前者的内部收益率小于题目给定的贴现率20%，而后者则略高于20%。

知道月供、利率和贷款周期，让你使用Excel轻松计算你的贷款总额

投资现值函数PV的功能是返回投资的现值，即为一系列未来付款的当前值的累积和。它的语法结构如下：

PV(,,,fv,)

PV函数通FV函数类似，也有5个参数

：为各期利率

：总投资期，即该项投资的付款期总数

：为各期所应支付的金额，其数值在整个年金期间保持不变，通常包括本金和利息，但不包括其他费用及税款

fv：指未来值，或在最后一次支付后希望得到的现金余额，如果省略fv，则假设其值为0（一笔贷款的未来值即为0）

例如：加入你决定向公司贷款买车，每月最多可偿还贷款额为5000元，当时的贷款年利率为6.26，预计贷款年限为5年，那么计算此条件下，一共需要偿还本息多少钱

第一步：单击目标单元格B5,在单元格中输入“=PV”，此时屏幕上回显示该函数的语法提示

第二步：根据提示一次输入各个参数的值

第三步：输入完成后，按E键得出计算结果

在年利率6.26%的条件下，如果每月还款5000，贷款5年，一共需要偿还本息257017.49元

在这里需要注意一点：时间单位的一致性。即在使用FV、PV等财务函数计算投资及收益问题时，需要注意时间周期的单位一定要一致，如果利率为年利率，而为每月支出额，贷款年限为年份，则在使用公式时，应统一转换为以“月”为周期或以“年度”为周期再带入函数，才能得出正确的计算结果。

以上就是今天的分享内容，如果觉得有帮助，希望各位朋友能够加关注点赞给予鼓励，期待我们在这里共同学习和探讨。