怎么在EXCEL算出房贷余额:普通人列表,金融精英

 公司新闻     |      2020-03-22 13:33

这几天因为存量房贷转轨对接LPR的事情,大家对于房贷突然又关注了起来。至于怎么选,前两天也说了我的建议。不过我建议归建议,怎么选最关键的是对未来利率走势的预判,我说的再多,也不如大家自己决定的好。

正是因为大家对房贷突然关注了,所以这几天突然有很多朋友来问我,还了这么长时间的房贷,现在还欠银行多少钱?也就是房贷余额还有多少。其实稍微懂一点金融的朋友就知道用EXCEL,列个表来算是比较容易的。但是很多人不知道,其实不用列个表,如果房贷利率从来没变过的话,在EXCEL只用一个公式就能算出来了。下面告诉大家怎么操作以及原理。

因为很多人不懂房贷计算的原理,为了方便大家,我先讲正常人算法(如果这部分很熟的话直接跳过到后面就行)。在计算之前必须要知道的几个参数:贷款总额、利率和还款总期数。讲到这里有人就要问了,月供多少钱不是必须参数吗?其实并不是的,因为知道贷款总额、利率和还款总期数就能自己算月供了。

那么我们先降低难度,假设贷款总额、利率、还款总期数和月供这四个数字我们都知道,接下来我们列一个表格就能算出来。为了方便大家理解,我采用一种通用的情况,假设贷款总额100万,利率是基准利率4.9%,贷款20年也就是总还款20年*12期/年=240期,每个月月供是6544.44元。我先把做好的表拿给大家:

第一列是期初的贷款余额(也等于上一列第二列的期末贷款余额)。第二列是期末贷款余额,也就是本期还了月供以后剩余的贷款余额。第三列是月供。第四列是本期应该还的利息。第五列是本期还的本金。其中,第二列等于第一列减去第五列这个用直觉就能理解,第五列的等于第三列减去第四列,唯一有稍微复杂计算公式的是第四列。

第四列的公式上面的图用红框标出来了,其中round函数是取小数点后2位的函数,不用的话也没关系。

其实房贷的原理很简单,我就用第一个月来举例,第一个月欠银行贷款100万,一个月银行要收利息100万乘以4.9%的12分之一(因为4.9%是年利率,这样算出月利率),也就是产生了4083.33的利息。那么月供6544.44比这个利息多,多出来的部分就用来还本金,并且导致贷款余额减少。那么每一列都用同样的方法计算,一直算到最后一期,这个表就做完了。这里使用EXCEL下拉框拉一下就搞定。

那么随着时间推移,月供里面超过利息的部分越来越多,也就是说,贷款余额越来越少,月供里还的利息也越来越少,还的本金越来越多,最后把整个贷款都还完。

做完表格以后,查房贷余额就容易了,只要知道自己还了多少期,找到对应期数那一行的第三列期末贷款余额就知道了。或者我们也可以用高级一点的办法,用vlookup函数,公式是:vlookup([第一列]:[第三列],[还款期数],3,0)。在我这个例子里面第一列是A列,第三列是C列,还款期数写在D2单元格里面。

讲到这里最简单的模式已经讲完了。其实这个表里面如果不知道月供,也可以算出来的。这里有两种方法,第一种方法就是用EXCEL的方法死算。还是用同样的这个表格,先把第三列月供这一列清空,然后让他们都等于一个未知数单元格,之后使用EXCEL单变量求解的功能,目标单元格是最后一期期末贷款余额等于0,然后EXCEL会自动算出来。因为这个方法比较复杂,而且很傻,现在已经基本上没人用了(甚至知道的人都不多了)。

第二种方法是用年金函数,也就是PMT函数。公式是PMT([年利率]/12,[年数]*12,-[贷款总额]),注意贷款总额前面有一个负号,在我这个例子里就是PMT(B3/12,20*12,-1000000)。当然,因为这个算出来的小数点后面很长,可以再套用一个round函数,可以看到算出来的结果就是6544.44。

我先告诉大家结果,然后再说原理吧。直接用EXCEL算法是FV([年利率]/12,[已还款期数],[月供],-[贷款总额])。对你没看错,就这一个公式就够了,看到这里学过CFA的高端人士肯定已经知道是怎么回事了。这个计算结果跟用表格计算是基本一样的,出现0.02的尾差是因为6544.44这个月供本身就会造成0.07的尾差。

这个公式大家可能不是很明白,我先用数学计算式模拟一下,同样的算法也就是:[贷款余额]*(1+([年利率]/12)^[已还款期数]-[月供]*(1-(1+[年利率]/12)^[已还款期数])/(1-(1+[年利率]//12))。

这里用的原理在金融里面还是挺常见的,叫做复制原理。怎么个复制法呢?就是假设我的房贷不按照原先的方式计算了,银行给我一种“替代”方法。银行要求我每个月去银行投资一个定投产品,这个产品的利率和复利方式跟房贷的利率一模一样,每个月定投的钱也跟月供一模一样。那么对我来说,我每个月还是给银行那么多月供的钱,跟每个月还月供其实是一样的。

现在唯一的区别就是,我的房贷没有还,每个月都会利滚利,但同时银行让我做的理财也在利滚利,到最后一期,我定投理财的利滚利会和房贷利滚利的总结果一样,我就直接拿定投的钱把房贷连本带利一次性还掉就跟银行两清了。那么房贷利滚利的总金额,减掉我这个虚拟的定投产品总金额,就是我还欠银行的钱。

这些就是数学算法的内涵,数学算法的前半部分[贷款余额]*(1+([年利率]/12)^[已还款期数],也就是到已还款期数那一天,房贷利滚利之后我欠银行的钱。后半部分[月供]*(1-(1+[年利率]/12)^[已还款期数])/(1-(1+[年利率]//12)),就是我买月供每个月定投产生的本息和。后半部分其实是用的等比数列的求和方法,或者用EXCEL的FV函数就变成了FV([年利率]/12,[已还款期数],[月供])。

看到这里有朋友是不是觉得很熟悉,这不就跟前面一个公式计算基本差不多的嘛。这个说法非常正确!其实前半部分也可以用FV函数来算,算法是FV([年利率]/12,[已还款期数],0,-[贷款总额]),那么两个FV函数一合并,就是FV([年利率]/12,[已还款期数],[月供],-[贷款总额])。

如果学过CFA的话,这个就更简单了,就是已知现金流和利率情况下,求某一个时点的终值。所以用FV函数一个公式就能解决。这里应用的条件是,整个期限利率是没有变过的,如果利率有变过,比如2014年以前的朋友,那用这一个公式是算不出来的。

当然,有朋友可能觉得,这么算还是太麻烦了。那我的建议是,下载房贷银行的APP或者打电话银行,直接查询房贷余额即可。