excel单变量求解贷款年限

『壹』 如何用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..com/view/6ad868c2cf84b9d529ea7a4f2、等额本金还款法计算公式：因等额本金还款法每期还款月供不一样，第N期还款月供=贷款本金/贷款期数+[贷款本金-贷款本金/贷款期数*(N-1)]*年利率/123、中途有利率调整时，应分段从调整日按剩余的贷款本金和剩余时间，按现行利率从新计算。二、有三种情况下会产生计算余额不一致：1、人民银行的基准利率有所调整，贷款时执行的利率是按原利率执行，中途利率调整后是，银行是根据剩余贷款本金和剩余贷款期限按新利率从新计算的，所以不管你按之前的利率算还是按现在的利率算，月供都会有差异。2、当时执行的利率有上浮或下浮，在房贷较紧的时候，银行会放缓审批和上调利率，政策宽松的时候银行之间则会抢业务，会在基准利率上打折，如果你不知道自己的利率是在基准上面上浮或下浮，按基准利率算，月供肯定会不一样。3、选择的还款方式不同，银行贷款有等额本息还款法和等额本金还款法，你在用房贷计算器时选择的方式和你银行贷款时选择的方式不一样，也会造成月供不一样。

『贰』 EXCEL用模拟运算表求 出当年限和贷款金额改变时 每月的偿还金额

1、找到“数据”；

『叁』 用excel怎么使用公式求：某人需贷款20万，他准备每月还款1500元，18年还清，使用单变量求解来计算贷款利率

月利率:=RATE(贷款期限(月),每月还款数(还款是负值,借为正),贷款额)*12
所以本次贷款年利率:
=rate(18*12,-1500,200000)*12=5.8545%

『肆』 已知贷款金额100万元 每月还贷20000元 贷款利率8%,用EXCEL单变量求解求多少年还清

在表格中:第一行输入:贷款金额:100万
第二行:月还:2万
第三行:利率8%
第四行: 选择插入函数:-------财务-------NPER-------在函数参数中:RATE输入：8%/12；PMT：-2；PV：100；FV：0； TYPE：不输，直接点确定，将计算结果除以12就得出了贷款年限。

『伍』 excel 利用单变量求解，计算20年期贷款，在利率为5.94%，月还款能力为4300的情况下，能贷款多少

原来用单变量求解解决比较复杂，这问题是有财务专用函数PV()处理的，按你贴出图片的数据位置，贷款金额在F4、年利率在F5、贷款年限在F6、月还款额在F7，在F4输入公式：
=PV(F5/12,F6*12,-F7)

或直接输入数值计算：
=PV(5.94%/12,20*12,-4300)

PV()函数用法：
PV(rate,nper,pmt,fv,type)
Rate 为各期利率。例如，如果按 10% 的年利率借入一笔贷款来购买汽车，并按月偿还贷款，则月利率为 10%/12（即 0.83%）。可以在公式中输入 10%/12、0.83% 或 0.0083 作为 rate 的值。
Nper 为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。例如，对于一笔 4 年期按月偿还的汽车贷款，共有 4*12（即 48）个偿款期数。可以在公式中输入 48 作为 nper 的值。
Pmt 为各期所应支付的金额，其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其他费用及税款。例如，$10,000 的年利率为 12% 的四年期汽车贷款的月偿还额为 $263.33。可以在公式中输入 -263.33 作为 pmt 的值。如果忽略 pmt，则必须包含 fv 参数。
Fv 为未来值，或在最后一次支付后希望得到的现金余额，如果省略 fv，则假设其值为零（一笔贷款的未来值即为零）。例如，如果需要在 18 年后支付 $50,000，则 $50,000 就是未来值。可以根据保守估计的利率来决定每月的存款额。如果忽略 fv，则必须包含 pmt 参数。
Type 数字 0 或 1，用以指定各期的付款时间是在期初还是期末。

『陆』 EXCEL请用单变量求解工具计算月还款额为6000,年限为30年时的贷款年利率

按以下步骤操作：
1、数据----假设分析----单变量求解
2、目标单元格，选中你的月还款额数据所在单元格（5368.22）,目标值输入6000，可变单元格选中利率所在的单元格（5.00%），确定
以后再截图，把行列标附带上，不然不知道目标单元格的名称。
注意，可变单元格必须为数字，目标单元格需要是公式。