如何用excel计算房贷(借贷公式如何设置)

1.正常算法：列出一个大表来求解。

因为很多人不了解房贷计算原理，为了大家方便，我先说正常人算法(如果这部分比较熟悉，直接跳到后面)。计算前必须知道的几个参数：贷款总额、利率、总还款期。此时有人会问，月供不是必须的参数吗？其实不是，因为知道了贷款总额、利率、还款总年限，就可以自己算月供了。

那么我们先降低难度，假设我们都知道贷款总额、利率、还款总期数、月供这四个数字，那么我们就可以通过做表的方式算出来。为了方便大家理解，我采用一个大概的情况，假设贷款总额为100万，利率为基准利率的4.9%，贷款20年，即还款总额为20年*12期/年=240期，月供6544.44元。我先给你看成品表：

第一栏是期初贷款余额(也等于上一栏第二栏的期末贷款余额)。第二栏是期末贷款余额，即本期支付月供后剩余的贷款余额。第三栏是月供。第四栏是当期应支付的利息。第五栏是当期支付的本金。其中，第二列等于第一列减去第五列，可以直观的理解。第五列等于第三列减去第四列，唯一计算公式稍微复杂的是第四列。

第四列公式上方的数字用红框标出，其中round函数是取两位小数的函数，不用也没关系。

其实按揭的原理很简单。我以第一个月为例。第一个月，我欠银行贷款100万，银行每月收取100万利息乘以4.9%的1/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，-1，000，000)。当然，因为这个计算出来的小数点很长，你可以再应用一个舍入函数，你可以看到计算出来的结果是6544.44。

2.精英算法：得到它的公式。

我先告诉你结果，然后再说原理。直接EXCEL算法是FV([年利率]/12，[还款期数]，[月供]，[贷款总额])。你是对的。这个公式就够了。

你可能不太理解这个公式。我先用数学公式模拟一下。同样的算法是：[贷款余额]*(1 ([年利率]/12)[已还期数]-[月供]*(1-(1[年利率]/12)[已还期数)]/(1-(1)

这里使用的原理在金融中很常见，它被称为复制原理。怎么抄？假设我的房贷没有按照原来的方式计算，银行给我一个“替代”的方法。银行要求我每个月去银行投资一个定投产品。这个产品的利率和复利方式和房贷一模一样，每个月的定投钱和月供一模一样。那么对于我来说，我每个月还是给银行那么多月供，其实和还月供是一样的。

现在，唯一不同的是，我的房贷没有还，每个月都会扭亏为盈。但与此同时，银行让我做的理财也在扭亏为盈。到期末，我定投的利润就和房贷的总结果一样了。我会直接拿定投的钱，一次性把房贷连本带利还上，这样就和银行扯平了。那么房贷利息总额，减去我的虚拟定投产品总额，就是我还欠银行的钱。

这就是这些数学算法的内涵。数学算法前半部分[贷款余额]*(1 ([年利率]/12)[还款期数]，即房贷还清后我欠银行的钱。第二部分【月供】*(1-(1[年利率]/12)【已还期数】/(1-(1[年利率]//12))为本人月供产生的本息之和。后面部分其实用的是几何级数的求和法，或者EXCEL的FV函数变成FV([年利率]/12，[还款期数]，[月供])。

在这里看到朋友是不是感觉很熟悉？和前面的公式基本一样。这个说法非常正确！实际上，前半部分

部分也可以用FV函数来算，算法是FV([年利率]/12,[已还款期数],0,-[贷款总额])，那么两个FV函数一合并，就是FV([年利率]/12,[已还款期数],[月供],-[贷款总额])。

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

相关问答：如何计算房贷余额

一、要准确计算出按揭款的余额，你必须清楚知道自己的贷款金额、贷款开始日期、执行利率，还款方式，中途央行调息记录等关健信息：
1、等额本息还款法计算公式：
[贷款本金×月利率×（1+月利率）^还款月数]÷[（1+月利率）^还款月数－1]
下面举例说明等额本息还款法，
假定借款人从银行获得一笔20万元的个人住房贷款，贷款期限20年，贷款月利率4.2‰，每月还本付息。按照上述公式计算，每月应偿还本息和为1324.33元。
现行的基准利率为5.4%，等额本息还款法的计算方式很复杂，“^”表示“幂”，建议你在网下一个专业的贷款计算器计算，地址如下：http://wenku.baidu.com/view/6ad868c2cf84b9d529ea7a4f
2、等额本金还款法计算公式：
因等额本金还款法每期还款月供不一样，第N期还款月供
=贷款本金/贷款期数+[贷款本金-贷款本金/贷款期数*(N-1)]*年利率/12
3、中途有利率调整时，应分段从调整日按剩余的贷款本金和剩余时间，按现行利率从新计算。
二、有三种情况下会产生计算余额不一致：
1、人民银行的基准利率有所调整，贷款时执行的利率是按原利率执行，中途利率调整后是，银行是根据剩余贷款本金和剩余贷款期限按新利率从新计算的，所以不管你按之前的利率算还是按现在的利率算，月供都会有差异。
2、当时执行的利率有上浮或下浮，在房贷较紧的时候，银行会放缓审批和上调利率，政策宽松的时候银行之间则会抢业务，会在基准利率上打折，如果你不知道自己的利率是在基准上面上浮或下浮，按基准利率算，月供肯定会不一样。
3、选择的还款方式不同，银行贷款有等额本息还款法和等额本金还款法，你在用房贷计算器时选择的方式和你银行贷款时选择的方式不一样，也会造成月供不一样。