不同回款方式的性价比,让excel来告诉你!

摘要: 万能的excel。

10-11 18:06 首页 布谷新金融

我们的后台常常会收到类似这样的问题


“为什么我投资了年化收益率10%的1年标,最后收益只有5.49%?”


或者是下面这样的留言


我们发现,很多人对不同还款方式下的实际收益率存在误解


之前我们聊过excel才是救星,一招搞定收益率计算难题,今天,我们再挖掘一下excel的其他功能,帮大家计算下在同样收益率情况下,不同还款方式的回款总金额差距有多大。


目前常见的回款(贷款)方式有:等额本息气球贷等额本金一次性还本付息按期还息,到期付本


投资一款5万元,1年期,年化收益率15%的产品为例。(注:案例中年化收益率为虚拟。)


1、等额本息


每月偿还同等数额的贷款(包括本金和利息)。


计算方式如下:


在excel公式中选中PMT公式,依次在rate里填入月利率(年利率/12)、在nper里填入还款期数(月)、在pv里输入投资金额(正负都可以),就能得到每期的还款额。


如图所示:



可以看到,一年后本息总额是54,154.99元不考虑资金二次利用的话,实际年化利率是8.3%


2、气球贷


这种借款方式有不同的叫法,其实是一种变相的等额本息还款。借款人选择一个较短的贷款期限,但以较长的期限来计算月供,这样借了气球贷的人在还款前期,每期还款金额较低,但最后一期偿付金额很高,像气球一样前小后大。


计算方式如下:


假设这笔钱按照2年来计算月供。


还是用刚才的PMT公式,只是将期限改为24期。



第12期需要全部还清,所以前11期还款金额相同,为2424.33元,而最后一期使用另外的公式。


首先,我们要知道我们还的钱里面,有多少是本金,所以我们打开CUMPRINC公式:


以月为单位,在rate处输入月利率;


nper处输入24(因为气球贷按照24期的方式还款);


start_period输入1,end_period输入11(因为目前还款了11期);


可算出,目前收到的本金为21077.27


那么,未还的本金就=50000-21434.338=28922.73元


本月要收的利息为28922.73×15%/12=361.53元


最后一期还款总额是:利息+本金=29284.26元


加上前面11个月收到的本息,最终本息之和是55951.89元。同样,不考虑资金二次利用的话,实际年化利率是11.9%


3、等额本金


还款期内把贷款数按总额等分,每月偿还同等数额的本金和剩余贷款在当月所产生的利息,每月的还款本金额固定,而利息越来越少,所以每个月还款总额越来越少。


计算方式如下:


等额本金没有直接的公式,首先将本金按期数均分,50000/12=4166.67元



然后,我们计算还款利息:


在excel里输入=(50000-(G3-1)*$H$3)*15%/12。


(G3-1)*$H$3是(期数-1)乘以每个月还款本金,等于已还款金额。(H3需加上$符号)。


50000-(G3-1)*$H$3就是待还本金。


待还本金×利率(15%/12)就是是每个月还的利息。


这样,我们算出第一期还款利息为625元,用鼠标点击I3右下角,向下拉(也就是填充句柄),就可以算出12期每期还款利息。


本金+利息=每期还款金额。



可以看到,等额本金的本息之和是54062.54元,不考虑资金二次利用的话,实际年化利率是8.1%


攻克了3个难关后,剩下的两种还款方式计算就轻松多了。


4、按月付息,到期还本


也就是每月偿还利息,到期后再还本。


计算方式如下:


每月利息=50000*15%*借款天数/365,一般平台会按照每月均等(30天)来算。


计算可得,每月付息625元如图所示:



到期还本金+当期利息=50000+625=50625元本息之和是57500元不考虑资金二次利用的话,实际年化利率是15%


5、一次性还本付息


也就是贷款到期后一次性归还本金和利息。


计算方式:利息=本金×利率×借款期数(年利率对应年,月利率对应月)


还款总额=利息+本金,如下图所示,结果跟第4种一样。



我们也做了个小对比:


(点击可查看大图)


有人可能会问,为什么同样的收益,收到的回款不同?


这是因为,由于每个月都有回款,所以等额本息、等额本金两种方式实际没有占用你的资金那么长的时间


以我们这次计算为例,选择等额本金还款,相当于你投资的50000元中,分成了均等12份,只有1份4166.67元出借了1年,1份4166.67元出借了11个月,1份4166.67元出借了10个月,依次类推。


如果你把第1期回款投到了同样收益的项目11个月,第2期回款投10个月……这样的话,年化收益就是标注的15%。


虽然很多投资平台都有回款计算器,但是如果能自己掌握计算方法,就能验证平台回款是否有“偷工减料”了。


反向思考,当你贷款的时候,对方宣传借50000元1年,等额本息只需还款54154元的时候,实际利率并非8.3%,而是15%,约2倍的利率。


excel的用法还有很多,比如:


贷款也可以使用excel计算自己的实际还款利率,选择等额本息还款的话:假设每个月还款4512.92元,还款12个月,贷款金额为5万元,


在excel中输入=Rate(12;-4512.92;50000)


可得到月利率,值×12就等于年利率,可计算出结果为年化利率15%


如果群众呼声大的话,咱们以后可以继续聊聊excel的花式用法


你有什么使用excel的小诀窍吗?


欢迎来留言分享!


关联阅读:等额本息还款太麻烦?你只是没有get它的好而已


首页 - 布谷新金融 的更多文章: