excel小技巧:用excel计算银行还贷

3/30/2009来源:Excel教程人气:8139

    现如今,从银行贷款购车买房,已经是很多人实现自己梦想的一种流行做法,特别是有较相对稳定收入的工薪一族,只要交了首期,每月定期等额付银行一定的款项,就可以拥有自己的私家车和房子,提前拥有高质量的生活水平。 购买者每月等额付款,都知道这个款项里包含了部分本金和利息,从付款的第一个月起到最后一个月,这个数字都相同,但对大多数人来说,并不太清楚每个月的还款额是怎么得来的。如果想提前还贷,就更加搞不清楚了。其实我们很多人正在使用的office套件中的Excel中,就提供有pmt、ipmt和ppmt函数来计算银行还贷,利用它们你可以很容易计算出自己的还贷款额,每月还给银行的利息以及本金等内容。 

  1 计算每月等额还款额 excel中提供的pmt函数是基于固定利率及等额分期付款方式,返回投资或贷款的每期付款额。利用pmt函数可以计算出每月偿还的款额。 1.1 pmt函数的使用格式 pmt(rate,nper,pv,fv,type) rate 为各期利率,是一固定值。 nper 为总投资(或贷款)期,即该项投资(或贷款)的付款期总数。 pv 为现值,即从该项投资(或贷款)开始计算时已经入账的款项,或一系列未来付款当前值的累积和,也称为本金。 fv 为未来值,或在最后一次付款后希望得到的现金余额,如果省略 fv,则假设其值为零(例如,一笔贷款的未来值即为零)。 

  type 只能用数字0 或1,用以指定各期的付款时间是在期初还是期末。如果省略 type,则假设其值为零。 1.2 说明 pmt 函数返回的支付款项包括本金和利息,但不包括税款、保留支付或某些与贷款有关的费用。 应确认所指定的 rate 和 nper 单位的一致性。例如,同样是四年期年利率为 12% 的贷款,如果按月支付,rate 应为 12%/12,nper 应为 4*12;如果按年支付,rate 应为 12%,nper 为 4. 1.3 银行还贷计算示例 假如你购一套200,000元的房子,首付30%后,你的贷款本金是140,000元,10年按揭付清(至0),按目前银行商业住房贷款利率,10年是月利率是4.2‰。 在excel电子表格的某单元中,输入: =pmt(0.0042,10*12,140000,0,0) 回车确认后,所得的值1487.66元,就是你每月等额还款的数字,它包括了本金与利息。 输入公式中的0.0042是月利率,如果是年利率,要将年利率除以12转换为月利率;10*12是还款的总月数;140000是贷款的本金;第一个0,指还完所有本息后其最后值就是0;第二个0表示期末付,银行多设置为期末付。 除了用于贷款之外,函数 pmt 还可以计算出别的以年金方式付款的支付额。例如,如果需要以按月定额存款方式在 10 年中存款 ¥50,000,假设存款年利率为 6%,则函数 pmt 可以用来计算月存款额: pmt(6%/12, 10*12, 0, 50000) 等于 -¥305.10 即向 6% 的存款帐户中每月存入 ¥305.10,10 年后可获得 ¥50,000. 2 计算各月偿还的利息excel中的ipmt函数是一个基于固定利率及等额分期付款方式,返回投资或贷款在某一给定期次内的利息偿还额的函数,利用ipmt函数可以计算各月偿还的利息额。

  2.1 ipmt函数的使用格式 ipmt(rate,per,nper,pv,fv,type) rate 为各期利率,是一固定值。 per 用于计算其利息数额的期次,必须在 1 至 nper之间。 nper 为总投资(或贷款)期,即该项投资(或贷款)的付款期总数。 pv 为现值,即从该项投资(或贷款)开始计算时已经入账的款项,或一系列未来付款当前值的累积和,也称为本金。 fv 为未来值,或在最后一次付款后希望得到的现金余额。如果省略 fv,则假设其值为零(例如,一笔贷款的未来值即为零)。 

  type 只能是数字 0 或 1,用以指定各期的付款时间是在期初还是期末。如果省略 type,则假设其值为零。 2.2 说明 应确认所指定的 rate 和 nper 单位的一致性。例如,同样是四年期年利率为 12% 的贷款,如果按月支付,rate 应为 12%/12,nper 应为 4*12;如果按年支付,rate 应为 12%,nper 为 4。

  在所有参数中,支出的款项,如银行存款,表示为负数;收入的款项,如股息收入,表示为正数。 2.3 各月利息计算 按以上例子(即贷款140000元,10年还清,月利率4.2‰)来计算各月的利息。在excel的a1填上“第n月”,b1填上“第n月偿还利息”,然后在a2到a121分别填上1~120,表示第1个月到第120个月。在b2单元中输入: =ipmt(0.0042,a2,10*12,140000,0,0) 回车确认即可得出-588.00,即第1个月的利息为588.00元。