这几天因为存量房贷转轨对接LPR的事情，大家对于房贷突然又关注了起来。至于怎么选，前两天也说了我的建议。不过我建议归建议，怎么选最关键的是对未来利率走势的预判，我说的再多，也不如大家自己决定的好。
正是因为大家对房贷突然关注了，所以这几天突然有很多朋友来问我，还了这么长时间的房贷，现在还欠银行多少钱？也就是房贷余额还有多少。其实稍微懂一点金融的朋友就知道用EXCEL，列个表来算是比较容易的。但是很多人不知道，其实不用列个表，如果房贷利率从来没变过的话，在EXCEL只用一个公式就能算出来了。下面告诉大家怎么操作以及原理。
1.正常人算法：列一个大表求解
因为很多人不懂房贷计算的原理，为了方便大家，我先讲正常人算法（如果这部分很熟的话直接跳过到后面就行）。在计算之前必须要知道的几个参数：贷款总额、利率和还款总期数。讲到这里有人就要问了，月供多少钱不是必须参数吗？其实并不是的，因为知道贷款总额、利率和还款总期数就能自己算月供了。
那么我们先降低难度，假设贷款总额、利率、还款总期数和月供这四个数字我们都知道，接下来我们列一个表格就能算出来。为了方便大家理解，我采用一种通用的情况，假设贷款总额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。
2.精英算法：一个公式搞定
我先告诉大家结果，然后再说原理吧。直接用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或者打电话银行，直接查询房贷余额即可。 |
QQ好友和群
