使用Excel中PPMT函数和IPMT函数进行等额还款的计算方法 - Office办公助手
专业的Office办公软件学习和Office办公软件教程网站！
使用Excel中PPMT函数和IPMT函数进行等额还款的计算方法
发布时间：03-29　来源：Office办公助手()
在处理等额贷款业务时，用户经常需要计算贷款金额以及本金、利息等。在Excel中，使用PMT函数可以计算每期应偿还的贷款金额，使用PPMT函数和IPMT函数可以计算每期还款金额中的本金和利息。下面介绍使用Excel中PPMT函数和IPMT函数进行等额还款的计算方法。
1、启动并打开工作表，选择单元格，在编辑栏中输入公式&=PMT(B3，B4，B2)&，然后按&Enter&键显示结果，如图1所示。
图1　在编辑栏中输入公式
PMT函数的功能是基于固定利率和分期付款返回贷款每期的付款额度，其语法格式如下。
PMT(rate,nper,pv,fv,type)
其中，参数rate为利率；参数nper为投资或贷款期限；参数pv为现值或一系列未来付款的当前值的累计和，也就是本金；参数fv为未来值或最后一次付款后希望得到的现金金额，该参数如果省略，表示最后一笔贷款的未来值为0；参数type为贷款偿还方式，为0表示期末，为1表示期初，默认值为0。
2、在工作表的E3单元格中输入公式&=PPMT($B$3，D3，$B$4，-$B$2，0)&计算每期偿还的本金，然后将公式填充到其下的单元格中，获得的计算结果如图2所示。
图2　显示各期偿还的本金
PPMT函数可以基于固定利率及等额分期付款方式返回投资在一定期间内的本金偿还额，其语法格式如下。
PPMT(rate,nper,pv,fv,type)
其中，参数rate是每期利率；nper用于计算其本金数额的期数，其值必须介于1与nper参数之间；参数nper为总投资期数；即付款总期数；参数pv为现值；参数fv为未来值；参数type用以指定各期的付款时间是在期首还是期末，0为期首，1为期末。
3、在工作表的F3单元格中输入公式&=IPMT($B$3，D3，$B$4，-$B$2，0)&计算每期偿还的利息，然后将公式填充到其下的单元格，获得的计算结果如图3所示。
图3　显示各期利息值
IPMT函数基于固定利率及等额分期付款方式返回给定期数内对投资的利息偿还额，其语法格式如下。
IPMT(rate,nper,pv,fv,type)
这里各个参数的含义与PMT函数相同。
4、在G3单元格中输入公式&=E3+F3&计算每期的本息和，然后向下复制公式得到需要的结果，如图4所示。
图4　计算本息和
5、在H3单元格中输入公式&=$B$2-SUM(E$3：E3)&计算每期偿还后剩余的贷款余额，然后向下复制公式后的结果，如图5所示。
图5　计算剩余的贷款余额
本文已经收录至：《》 -
上一篇： 下一篇：Excel表格如何进行跨工作表计算_Excel表格_excel教程网
欢迎大家来到Excel教程网！
当前位置： >
Excel表格如何进行跨工作表计算
我这是无意当中得来的经验，有一次老板让统计三个月的汇总数据，马上要，用QQ传过去。一下难倒我了，那么多数据，在三个独立的EXCEL表格内，用手工计算再重新填的话，别说马上，火箭上也完不成，就算完成了也难免出。于是急中生智，我来了一个跨表格计算。
如何进行跨工作表计算我这是无意当中得来的经验，有一次老板让统计三个月的汇总数据，马上要，用QQ传过去。一下难倒我了，那么多数据，在三个独立的EXCEL表格内，用手工计算再重新填的话，别说马上，火箭上也完不成，就算完成了也难免出。于是急中生智，我来了一个跨表格计算。操作方法1、首先把要汇总的EXCEL表移动复制到一个Excel表格中，使之成为其中一个表格扉页，打开所有有送联的EXCEL表，把其中的小数据表移动复制到大表格中汇总，右击需复制的表格扉页下面的索引，在弹出的菜单中点击“移动或复制工作…”。2、在对弹出的对话框中选中目标工作表，点击“建立副本”，不然就是移动了，再点击确定。3、这时目标Excel表中出现了复制过来的工作表扉页，同理，把相关的表格扉页都复制过来，再把各扉页索引名称和表格名称修改为所需要的名称（注：不复制过来也能实现跨表格计算，但不利于网络传送）4、下面开始计算：如“周口地区”数量：选择中数量表格单元，输入：“=”后，逐个点击要相加的表格扉页中的相应的单元格，逐个点击“+”号，如其他月份中的“周口地区”数量，如图中汇总表格单元格中出现的“='12月份地区'!D4+'10月地区'!D4+'11月地区'!D4”这段公式，意思是这个单元格=“12月份地区这个表格中的D4单元格”加“10月地区”表格中D4单元格加“11月地区”表格中D4单元格中的数据，然后回车确认，这时汇总表相应单元格中就出现了三个月相加的数据。5、再利用Excel表格特殊的拖拉复制法，把公式复制相关这一表格行中，一行数据都自动相加的，用此方法在其他行中输入公式，完了一后一张完美的跨表格运算的EXCEL工作表出现了。此法省时省力，可举一反三，推广应用。注意事项要细心操作，不能把最初公式中的单元格点击错，否则全盘错误。汇总表格中相关计算单元格一定先输入“=”号，不然不能跨表。
最新发布教程
推荐教程排行
推荐热门教程
推荐视频教程冯秉先：利用excel制作的全能利息计算表
我的家乡鄂尔多斯市，在GDP大跃进大潮中一度曾领引了时代的潮流，被具有“超常规发展”思维的领导们，执意要打造成一座国际化的大城市，依托于丰富的地下煤炭资源，和近似疯狂的房地产开发，产生了影子银行（民间借贷）的火爆，民间借贷成了企事业单位和人人参与的全民性行动。
随着房地产市场的低迷，民间借贷开始崩盘，房地产业资金链的断裂，导致了大量空置房屋及阑尾楼，从事借贷业务的公司和人，把前几天积累起来的现金换成了空置的房产、地皮和无法收回的烂账。本人接触过一些民企和小贷公司，会计人员不能准确地报出借贷账目报表，由于借贷利息的计算随着日期每天都在增长变化，每一笔借贷约定利率又不同，而且计息方式也不一，如果用手工计算，尤其是需要计复利时，大量的计算费工费时，所以，老板需要当天的数据时，会计人员不能够及时准确地提供出报表。
本人利用excel设计制作的全能借贷利息计算表，使用者只需要输入本金、利率和起始借款日期，其余利息、本金加利息，由于excel内置时钟，随着每天日期的变化自动计息，任何时候都能显示你所需要的各种统计数据。
以下图所示，此为本人为一家小贷公司制作的利息计算表为范例，几点说明如下：
1、民间借贷利率俗称为“3分5的利”即指月利息为3.5%，即0.035，则日利率为：月利率*12/365=0.035*12/365=0.00115。
2、民间借贷约定俗称按月计复利，复利计算公式为：本金*（1+月利率）^整月数（假设本金为B，月利率为R，整月数为M，则复利息=B*(1+R)^M，其中“^”表示乘方。
3、需要用到的几个常用的时间函数：
当日日期=NOW（），或=TODAY（）；
DATEDIF(起始日期,今天,“y”) ，从起始日期到今天经过的整年数；
DATEDIF(起始日期,今天,“ym”)，从起始日期到今天经过的整月数
DATEDIF(起始日期,今天,“md”)，从起始日期到今天期间不足整月的天数。
本范例按照用户要求反应出三种利息计算数据：（1）按借贷合同约定的利率计算；（2）按民间借贷约定俗称满整月计复利计（因为法律不保护复利，所以复利不反应在合同中，以个人信用来保障）；（3）按法定允许的利率计（现行民间借贷规定不高于人民银行基准利率的4倍，通常为月利率0.024。
本范例累计借出本金48，500，000元，到日，按合约本利129,404,714元，按复利计本利合计248,478,524元，按法定允许本利合计102,055,754元。
无论借方还是贷方老板，如果每天看着这张表，都会心惊肉跳的！做什么生意能够承担如此高的利息呢？怎么能不产生坏账风险呢？一方可以说在饮鸩止渴，一方可以说在利令智昏。不过笔者以为，如果每天让老板看着这张能够自动计息的表，或许能够有所启发而觉悟，不至于越陷越深！
范例图片（借贷利息计算表）
已投稿到：
以上网友发言只代表其个人观点，不代表新浪网的观点或立场。查看: 9005|回复: 12
这个EXCEL表格金额公式怎么设置？
阅读权限20
在线时间 小时
(53.41 KB, 下载次数: 36)
09:39 上传
点击文件名下载附件
我想请教一下在入账金额一栏我该设置怎么样的公式？可以一次性输入金额,而不用一列一列,一个数一个数输进去呢？
clip_image002.jpg (29.91 KB, 下载次数: 24)
09:46 上传
阅读权限70
在线时间 小时
本帖最后由 skyzxh 于
10:10 编辑
这个做个小的自动宏就可以自动写入了.
阅读权限95
在线时间 小时
G8公式：=LEFT(RIGHT(& &&$E8*$F8*100,10-COLUMN(A1)))复制代码右拉，下拉到第10行。
合计行G11公式：=LEFT(RIGHT(& &&SUMPRODUCT((0&$G8:$O10)*10^(9-COLUMN($A:$I))),10-COLUMN(A1)))复制代码右拉。
10:14 上传
点击文件名下载附件
94.37 KB, 下载次数: 59
阅读权限20
在线时间 小时
skyzxh 发表于
这个做个小的自动宏就可以自动写入了.
不太明白呀 可以详细教一下吗？谢谢
阅读权限20
在线时间 小时
& & & & & & & &
swl4561255 发表于
G8公式：右拉，下拉到第10行。
合计行G11公式：右拉。
我想再请教一下&&那比如说再没有数量金额的情况&&
千& & & & 百& & & & 十& & & & 元
9& & & & 4& & & & 2& & & & 5
我想输入这个数字金额,有什么办法一次性输入而不用一列列输吗？谢谢&&高手
阅读权限50
在线时间 小时
=IF(COLUMN()&15-LEN($E8*$F8*100)+1,&&,MID(TEXT($E8*$F8*100,&&),COLUMN(A1),1))
阅读权限20
在线时间 小时
Moxiu 发表于
=IF(COLUMN()
谢谢你们的帮助。
阅读权限95
在线时间 小时
<font color="#5199198 发表于
我想再请教一下&&那比如说再没有数量金额的情况&&
千& & & & 百& & & & 十& & & & 元
把公式中的$E8*$F8用存储单元格名称代替，其余部分不变。
阅读权限20
在线时间 小时
swl4561255 发表于
把公式中的$E8*$F8用存储单元格名称代替，其余部分不变。
555~本人小白&&不是很懂这句话把公式中的$E8*$F8用存储单元格名称代替,可以再发一次EXCEL表给我看看吗？
阅读权限95
在线时间 小时
<font color="#5199198 发表于
555~本人小白&&不是很懂这句话把公式中的$E8*$F8用存储单元格名称代替,可以再发一次EXCEL表给我看看吗？
请参考第11行的公式：=LEFT(RIGHT(& &&$E11*100,10-COLUMN(A1)))复制代码
(94.37 KB, 下载次数: 34)
11:13 上传
点击文件名下载附件
玩命加载中，请稍候
玩命加载中，请稍候
Powered by
本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！ & & 本站特聘法律顾问：徐怀玉律师 李志群律师* 您要为您所发的言论的后果负责，故请各位遵纪守法}