315 | 原创 | 汽车金融Excel财务函数应用进阶篇

温馨提示：点击上方蓝字关注“kk汽车金融行业研究”，或者添加微信号：kk50281984。这是一个专业而温暖的账号，因为账号后面有一群专业而温暖的人,立志做你的“汽车金融专业知识云端支持系统”，也就是做你的哆啦A梦☺。

财务函数进阶篇—贷款相关

在之前的一篇文章我介绍了PV,FV,PMT,RATE,NPE函数，利用这些基础函数我们能完成一些最基本的运算。这次我将介绍一些进阶级的财务函数（再高深的了我也不会了– –），利用这些函数，我们能解决实物操作中大部分基本贷款数据的测算。

IPMT PPMT函数

在介绍IPMT和PPMT函数之前，我们先利用之前介绍的PMT函数制作一个摊销表，以贷款30万元，贷款期限24个月，年利率为6.5%为例（学过会计的同学应该有印象，这和债券摊销几乎一模一样）。

第一期：产生利息=300000*6.5/12=1625

       偿还本金=13364-1625=11739

       剩余贷款金额=30000-11739=288261

第二期：产生利息=288261*6.5/12=1561

       偿还本金=13364-1561=11802

        剩余贷款金额=288261-11802=276459

……………………

按照这样的规则直接本金和利息摊销完毕。顺带介绍一下，B行所有偿还利息之和除以本金就是我们常说的费用化利率，它没有考虑货币时间价值，但由于费用化利率低于实际利率，销售普遍采用费用化利率进行宣传。

利用这个摊销表我们可以求得任何一期偿还的利息或者本金。但是我们同样可以利用EXCEL函数实现这一功能，这就是我们下面要介绍的函数IPMT及PPMT函数，这两个函数在EXCEL中的格式如下：

IPMT(rate, per, nper, pv,fv, type)：求某一期偿还的利息。
PPMT(rate, per, nper, pv,fv, type)：求某一期偿还的本金。

相比于PMT函数，IPMT和PPMT仅多了一个”PER”值，PER值就是所求偿还本金或者利息所处的期数。例如，我们要求摊销表中地4期偿还的利息，我们可以利用IPMT函数做以下计算：

IPMT(6.5%/12, 4, 24, 300000) =-1433（负数代表现金流出）

这与摊销表中的数值一模一样。有兴趣的同学可以利用IPMT或者PPMT求一下某一期的利息或者本金，我在此就不再详细说明了。

CUMIPMTCUMPRINC函数

看到这里，有好奇的人肯定要问：能不能使用EXCEL求得某两期之间所交的利息或者本金之和呢？答案是肯定的，利用CUMIPMT和CUMPRINC函数可以解决这个问题。这两个函数在EXCEL中的格式如下：

CUMIPMT(rate,nper,pv, start_period,end_period,type)：求利息
CUMPRINC(rate,nper,pv,start_period,end_period,type)：求本金

这两函数与我们之前介绍的函数仅仅多了2个陌生的参数’’start_period,end_period’’,（计算起始期，计算结束期）。

下面举例说明，

求摊销表中第2期至第6期总共交了多少利息，我们可以做以下计算：

=CUMPRINC(6.5%/12,24, 300000, 2, 6, 0) 
= -59,655 

与摊销表中金额一致，有兴趣的同学可以自己利用CUMPRINC函数进行计算，由于其原理基本与CUMIPMT函数基本一致，在此不做过多介绍。

   写到这里，与贷款相关的基本财务函数已经介绍完毕了，上述函数的计算都是建立在每期现金流量一致的情况下。如果贷款每一期的还款额都不一致，那该如何计算一笔贷款的实际利率呢？这个时候就该引入内含报酬率的概念了。所谓内含报酬率，是指能够使未来现金流入现值等于未来现金流出现值的贴现率，或者说是使投资方案净现值为零的贴现率。在EXCEL中也提供了一个IRR函数来计算内含报酬率，下面我将介绍IRR函数以及和其相关的XIRR和MIRR函数。

IRR函数

每一笔投资或者贷款都会产生一系列的现金流。就拿贷款来说，站在消费者的角度，期初会有现金的流入，每个月还贷款就会有现金的流出。IRR就是利用这一系列现金流，反推一笔投资或贷款的内含报酬率。

将每一笔现金流量按照内一定比例率折现，使所有现金流的现值为0,这个比例就是内含报酬率。若C0、C1、C2、C3…Cn分別代表为期初到n期的現金流量，正值代表现金流入，负值代表现金流出，内含报酬率的可用以下方程解释：

0 = C0 + C1/(1+rate)¹ +C2/(1+rate)² + C3/(1+rate)³….+Cn/(1+rate)ⁿ

找出符合方程的RATE，就是所求的内含报酬率。参加过注册会计师考试的同学应该都会用插值法计算内含报酬率。

但是EXCEL是如何来计算内行报酬率的呢？先从IRR在EXCEL说的计算格式说起： IRR(Values, guess)，Values为一系列的现金流，由于EXCEl中IRR是使用迭代算法计算的，系统默认从10%开始计算，如果迭代20次仍未找到符合精度的结果系统报错，显示为#NUM!。所以，如果函数报错则表明计算结果和10%相差太远，迭代20次仍未找到结果，这时候需要使用guess值给excel一个参考，让它从给的guess值开始计算。这就是guess值的含义，在计算月利率和年利率时你可根据实际情况调整guess值大小。现在举具体来说明IRR函数在EXCEL的运用。

小明从汽车金融公司贷款10万元买车，期限1年，汽车金融公司根据其收入情况制定个性话的还款方式，前10个月每个月还5千块，最后两个月还款３万元，请问贷款的实际利率为多少？

＝ＩＲＲ（Ｂ２：Ｂ１４）＊１２＝１３．２２％

IRR的期数并没有时间的限制，可以为一年为一期，可以一天为一期，也可以一个月为一期，这由使用者自行决定。但需要注意的是如果每一格代表一个月的现金流，那么对应所求出的内含报酬率为月报酬率，每一格代表一年的现金流，那么对应所求出的内含报酬率为年报酬率。

XIRR函数

若要利用IRR函数來计算报酬率，现金流量必须是以『一期』为单位，也就是输入的现金流量必须有期数的观念。但是在现实生活中，现金流量并非定期式的，这在一些投资案例中尤为常见，如在某些融资租赁业务中出租方根据承租方企业的现金流情况制定个性话的还款方式。XIRR函数在EXCEL中的计算格式为：

XIRR(values, dates, guess)

相比于IRR函数，XIRR函数仅仅多了一个DATE参数，DATE指现金流发生的日期。我们可以根据现金流的实际情况，在EXCEL中制做如下表格：

然后输入XIRR函数公式XIRR(values1: valuesN，dates1: datesN)求得内含报酬率，需要指出是XIRR函数求出来的内含报酬率就是年报酬率。

MIRR函数

  MIRR函数是IRR的函数的改良公式，它考虑了现金流用于再投资的情况。下面举实例来说明：一个投资案的现金流量如下：{-10000, 500, 500, 10500}这些金流量一期为一年，期初拿出10,000元，第1年底拿回500元，第2年底也拿回500元，第3年底拿回10500元,假设每年收回500元，投资者用于定期存款，年利率为2%，求考虑了再投资情况下的实际利率。

我们可以在EXCEL中输入MIRR函数：=MIRR(values, finance_rate, reinvest_rate) = MIRR(({-10000, 500, 500, 10500}), 0, 2%)=4.86%

这与不考虑再投资情况下，利用IRR函数计算出来的报酬率是不一致的，有兴趣的同学可以自己研究一下。

最后，终于，写完了。然后我想说点题外话，作为一名汽车金融库存融资的业务人员，写的第一篇文章竟然是财务函数，我也感到挺意外的。第一篇文章发出之后不少同行加我微信问各种各样的问题，作为一个刚入行一年的新人深感压力，但同时又敦促我不断学习。最后还想啰嗦一句，做库存的前辈求加个微信啊！想写一下库存业务，无奈心中还有很多困惑，希望能有机会向大家请教！

匡匡点评：

上次Z.j.Q的文章发布出来后，本以为阅读量不大，结果没想到阅读量也不低（累积到现在2500多了），说明还是有需求。作者本来还不是做财务的，做库存融资的，做库存的朋友可以加他交流下zhangjiaqi-xixi

百科全书：回复下面关键字就能获得文章，| 隔开的那些

发展概况

汽车金融是什么|渗透率|汽车金融百科全书|汽车金融简史|发展概况|汽车金融与汽车电商|资金方名单|总目录|kk汽车金融说明书| SP调查|2015年汽车金融报告

报告

德勤2015年报告|德勤2014年报告| 2014年中国汽车金融报告|2014年版银行业运行报告|经销商集团金融白皮书|满意度调研|2014年消费信贷调研|2013年汽车消费金融研究|论文零售风险|征信系统报告|普华永道金融报告|经销商风险状况|2015车抵贷报告

组织及公司 

通用金融|GMAC|上汽财务|一汽金融|大众金融|北现金融|丰田金融|华晨东亚|比亚迪金融|宝马金融|福特金融|力帆|裕隆金融|东风金融|兵装财务|奔驰金融

平安银行|宁波通商银行|民生银行|中信银行

先锋太盟|融悦资本|稻普集团|佰仟金融|悦达金融|广汽租赁|中安金控|建元资本|美利车金融

融资租赁公司|担保公司

太平洋汽车网|易车网|汽车之家|ZestFinance|阿里巴巴|微众银行|P2P|银行业协会汽车金融委员会|滴滴|第一车网|第一车贷

青岛中瑞|金刚科技

人物 

张晓俊|傅忠强|李文国|科瑞爵|殷耀亮|韩勇|陈金亮|王炜| Stadtler |陈晨|范凯思|余亚瑞|潘未名|小分队冒泡|夏文佳|匡匡

SP专区

SP联盟 | SP| SP调查|SP定位| SP规避欺诈风险|SP自主贴息

关键字目录：520，kk汽车金融送你一本《汽车金融百科全书》