Excel一招搞定指定期限内每期还款额、本金和利息的计算

本金和利息是公司支付方法的重要手段，也是公司日常财务工作的重要部分。在财务管理中，本金和利息常常是十分重要的变量。为了能够方便高效地管理这些变量，处理好财务问题，Ex中提供了计算各种本金和利息的函数。下面分别进行介绍。

1.使用PMT函数计算贷款的每期付款额PMT函数用于计算基于固定利率及等额分期付款方式，返回贷款的每期付款额。

语法结构：PMT(,,v,[fv],[])

参数：

●：必需的参数，代表投资或贷款的各期利率。通常用年利表示利率，如果是按月利率，则利率应为11%/12。如果指定为负数，则返回错误值【NUM!】。

●：必需的参数，代表总投资期或贷款期，即该项投资或贷款的付款期总数。

●v：必需的参数，代表从该项投资（或贷款）开始计算时已经入账的款项，或者一系列未来付款当前值的累积和。

●fv：可选参数，表示未来值，或者在最后一次支付后希望得到的现金余额。如果省略fv参数，则假设其值为零。如果忽略fv参数，则必须包含参数。

●：可选参数，是一个逻辑值0或1，用以指定付款时间在期初还是期末。0为期末，1为期初。

在财务计算中，了解贷款项目的分期付款额，是计算公司项目是否盈利的重要手段。

例如，D公司投资某个项目，向银行贷款60万元，贷款年利率为4.9%，考虑20年或30年还清。请分析一下两种还款期限中按月偿还和按年偿还的项目还款金额，具体操作步骤如下。

S01输入公式。新建一个工作簿，输入如图所示的内容，在B8单元格中输入公式"=PMT(E3/12,C3*12,A3)"，即可计算出该项目贷款20年时每月应偿还的金额。

S02查看计算结果。在E8单元格中输入公式"=PMT(E3,C3,A3)"，即可计算出该项目贷款20年时每年应偿还的金额，如图所示。

技术看板

使用PMT函数返回的支付款项包括本金和利息，但不包括税款、保留支付或某些与贷款有关的费用。

S03输入公式。在B9单元格中输入公式"=PMT(E3/12,C4*12,A3)"，即可计算出该项目贷款30年时每月应偿还的金额，如图所示。

S04查看计算结果。在E9单元格中输入公式"=PMT(E3,C4,A3)"，即可计算出该项目贷款30年时每年应偿还的金额，如图所示。

2.使用IPMT函数计算贷款在给定期间内支付的利息基于固定利率及等额分期付款方式情况下，使用PMT函数可以计算贷款的每期付款额。但有时候需要知道在还贷过程中，利息部分占多少，本金部分占多少。如果需要计算该种贷款情况下支付的利息，就需要使用IPMT函数。

IPMT（偿还利息部分）函数用于计算在给定时间内对投资的利息偿还额，该投资采用等额分期付款方式，同时利率为固定值。

例如，小陈以等额分期付款方式贷款买了一套房子，需要对各年的偿还贷款利息进行计算，具体操作步骤如下。

S01输入公式。新建一个工作簿，输入如图所示的内容，在C8单元格中输入公式"=IPMT($E$3,A8,$C$3,$A$3)"，即可计算出该项贷款在第一年需要偿还的利息金额。

S02查看计算结果。使用Ex中的自动填充功能，计算出各年应偿还的利息金额，如图所示。

3.使用PPMT函数计算贷款在给定期间内偿还的本金基于固定利率及等额分期付款方式情况下，还能使用PPMT函数计算贷款在给定期间内投资本金的偿还额，从而更清楚地确定某还贷的利息/本金是如何划分的。

例如，要分析上个案例中房贷每年偿还的本金数额，具体操作步骤如下。

S01输入公式。在E8单元格中输入公式"=PPMT($E$3,A8,$C$3,$A$3)"，即可计算出该项贷款在第一年需要偿还的本金金额，如图所示。

S02查看计算结果。使用Ex中的自动填充功能，计算出各年应偿还的本金金额，如图所示。

4.使用ISPMT函数计算特定投资期内支付的利息如果不采用等额分期付款方式，在无担保的普通贷款情况下，贷款的特定投资期内支付的利息可以使用ISPMT函数来进行计算。

例如，在上个房贷案例中如果要换成普通贷款形式，计算出每年偿还的利息数额，具体操作步骤如下。

S01输入公式。在G8单元格中输入公式"=ISPMT($E$3,A8,$C$3,$A$3)"，即可计算出该项贷款在第一年需要偿还的利息金额，如图所示。

S02查看计算结果。使用Ex中的自动填充功能，计算出各年应偿还的利息金额，如图所示。

格格技能——巧用EXCEL函数，算清各种收益利率

我正在参加小她4月财女新技能有奖征集活动，每人最高可得450元，你也来试试吧：

其实所谓的精算师也并没有什么太神秘的，如果能够掌握常用的工具，你也可以天下无敌。今天格格就手把手教大家几个EXCEL函数公式，能够让你面对一堆真真假假虚虚实实的数据不再头疼！

一、FV函数——基于固定利率和等额分期付款方式，计算某项投资的未来值！

打开一个EXCEL表格——插入函数，在弹出窗口的选择类别中选择“财务”，下拉菜单中找到FV。

点击确认，会弹出函数参数的窗口。

解释一下，其实里面已经有解释了，但是我把它翻译成更加通俗易懂的大白话，可能会让财蜜们看得更明白，我们以小她的12存单法年利率为例哈，没有任何活动的情况上，年利率是8.3%：

R——各期利率。这里，年利率为8.3%，如果我们选择正常的12存单法，即每月月初存入一笔2000块，那么这里的R值就输入34，但是如果我们选择52周那种做法，为了保持本金和周期一致，我会选择48周，每周存入500元，那么这里的R值就输入34。

N——总投资期。12存单就输入12，代表12个月，每月1期。48周就输入48，代表48周，每周为1期。

P——各期支出金额。注意，因为是投入，所以值为负值，12个月是各期支出金额是-2000，48周各期支出金额是-500。

Pv——从该项投资开始计算时已经入账的款项。因为我们是从现在开始投入，那么之前就是没有，所以填0就好啦。

T——指定付款时间是期初还是期末，如果是期初选择1，期末的话选择0，我们假定为每月或者是每周投入，那么就是期初，输入1。

如下图，第一种，12个月存入，到期本息为25106.

第二种，48周存入，到期本息是25044.

所以亲爱的财蜜们，如果不考虑资金灵活性的话，关于52周攒门槛提高的问题，其实也是有另外的解决途径的是不是？上面这个分析可以看出，全部选择月初存12单，最后到期的本息比48周还要多那么一点点呢。

同理，用这个公式，也可以用来算等额本息的房贷问题，感兴趣的同学可以自己算一下哈~

二、NPER——基于固定利率和等额分期付款方式，计算某项投资的期数！

和上面的一样一样的哈，在财务的函数中找到NPER函数，依然是以12存单法年利率为例哈，没有任何活动的情况上，年利率是8.3%。

假定我每月投入2000元，然后期待到期后有一个50000元的回款，各参数如下：

R和P和FV函数中的定义完全一样。

Fv——未来值。即期望能够得到的本息合计金额，所以输入5000。

最后的计算结果是23.13，这里的结果指期数，因为是每月为一期，所以可以理解为投入23.13个月之后，到期就可以拿到50000现金！

这个公式其实可以帮助你计算分红型保险产品，或者是任何一种你预期要得到的回报所需要的期限。

三、PMT——基于固定利率和等额分期付款方式，计算指定期数内，每期投入的金额！

依然是以12存单法年利率为例哈，没有任何活动的情况上，年利率是8.3%。我期待投入12期后，到期能够拿回50000的本息，那么就找到PMT函数，如下：

R和之前的完全一样

N——你期望的期数，因为我们假设是一年，所以期数输入12。

Fv——期待的未来值，我们目标为50000。

看计算值为3982。也就是说如果想要得到这个期望值，你需要每个月存入3982，才能保证最后的50000元。

四、PV——计算某项投资的一系列将来偿还额的当前总值。

大白话来说吧，这个是类似于计算复利的一个函数。计算的结果是一次性投入的钱。

依然是以12存单法年利率为例哈，没有任何活动的情况上，年利率是8.3%。我期待在5年后，可以本息一共拿到50000，而在这中间，一年到期后我会选择本息继续投入，那么就找到PV函数。

如下图：

R——不用分期了，所以就选择年利率。

N——期望的年限，假设是5年。

P——各期获得的金额，因为我们是本息再投资的，所以输入值为0。

Fv——期待的未来值，50000.

最后计算的结果是33560。

这个结果的意义在于，如果保持8.3%的收益，5年后想要拿到50000，那么可以现在一次性投入33560，就可以达到目标了。

其实这个和复利的计算方式一样，不信你可以算算看：33560.4*1.083^5=49999.999

关于EXCEL的更多函数计算公式，格格也在慢慢学习。

今天的技能就分享到这里，有没有帮到你呢？如果有，请点赞，请收藏。。。

贷款计算不复杂 学会这4个公式就好

本文是接着文章一文读懂贷款利率、利息、分期还款额的计算（附案例演示），继续探讨如何运用Ex计算贷款利率、利息、分期还款额。

前一篇文章讲解了RATE函数、PMT函数，本文将介绍贷款函数。

一、PPMT函数与IPMT函数

PMT函数用来计算基于固定利率和等额分期还款方式下的每期还款额。PPMT函数和IPMT函数则基于固定利率和等额分期还款方式，分别计算每期还款额中的本金和利息。三者关系为PMT=PPMT+IPMT。

①PPMT函数：=PPMT（，，，v，[fv]，[]）

用途：PPMT函数是PiifP的缩写，其中Pii意思是本金，P意思是每期还款额。因此，PiifP的意思是每期还款额中的本金数额。PPMT函数是基于固定利率及等额分期付款方式，返回贷款的每期付款额中的本金数额。

参数：R为每期利率，P（Pi的缩写，意思是某一特定时期）用于计算本金数额的期数（1≤P≤N），N为付款期数，Pv为本金，Fv为期末本金和利息和，T指定各期的还款时间是在期初还是在期末（期初值为1，期末值为0）。

②IPMT函数：=IPMT（，，，v，[fv]，[]）

用途：IPMT函数是IfP的缩写，其中I是利息的意思。因此，IfP的意思是每期还款额中的利息。IPMT函数是基于固定利率及等额分期还款方式，计算贷款的每期还款额中的利息。

参数：IPMT函数的参数与PPMT参数完全一致，所以不再介绍。

案例：在借呗借款10000元，选择分6期，即6个月偿还，日利率万分之五，请问第3期偿还的本金和利息分别是多少？

在Ex输入：=PPMT（，，，v，[fv]，[]）=（0.05%，3，6，10000）和=IPMT（，，，v，[fv]，[]）=（0.05%，3，6，10000），分别得到结果为-1653.77元、-101.48元，即第3期时需要偿还本金1653.77元，需要支付利息101.48元。每期还款额PMT为1755.25元。

二、CUMPRINC函数、CUMIPMT函数

CUMPRINC函数计算一定时期内（从-i到-i），累计还款额中的本金之和；CUMIPMT函数计算一定时期内（从-i到-i），累计还款额中的利息之和。这两个函数与PMT函数的关系为：PMT*求和期数=CUMPRINC+CUMIPMT。

①CUMPRINC函数：=CUMPRINC（，，v，_i，_i，）

用途：CUMPRINC函数由表示累计的Civ的前三个字母和表示本金的Pii的前五个字母组合而成，给定了计算的范围为表示时间开始的_i和表示时间结束的_i，因此CUMPRINC函数意为特定时期的本金之和。

参数：为每期利率，为还款期数，v为贷款总额，_i开始时间，_i结束时间，指定各期的还款时间是在期初还是在期末（期初值为1，期末值为0）。由于每一期还款是在期末，比如下面会列举的借呗案例中2017年10月5日借款的首次还款时间为第一个月满后的2017年11月5日，所以不能省略，需要填写表示期末的值0。

②CUMIPMT函数：=CUMIPMT（，，v，_i，_i，）

用途：CUMIPMT函数由Civ的前三个字母和IfP的缩写IPMT构成，给定了计算的范围为表示时间开始的_i和表示时间结束的_i，因此CUMIPMT函数意为特定时期的利息之和。

参数：CUMIPMT函数的参数与CUMPRINC函数的参数完全一致，所以不再介绍。

案例：在借呗借款10000元，选择分6期，即6个月偿还，日利率万分之五，请问第3期到第5期一共还了多少本金，又一共还了多少利息？

在Ex中输入=CUMPRINC（，，v，_i，_i，）=（0.05%,6,10000,3,5）和=CUMIPMT（，，v，_i，_i，）=（0.05%,6,10000,3,5），分别得到结果为-5036和-229.7，即第3期到第5期偿还借呗的本金之和为5036元，偿还利息之后为229.7元。

三、制作还款明细表

用Ex制作还款明细表，可以让人清楚地看到整个还款期中每一期偿还的本金和利息，以及每一期偿还后剩余应还本金和应还利息。

讲这部分前，需要先讲一个知识点：相对引用、绝对引用和混合引用。

如果在单元格A1中输入=B1，那么，A1就是B1的引用单元格，而B1则成为A1的从属单元格。从属单元格和引用单元格之间有3种不同的引用方式，分别是相对引用、绝对引用和混合引用。Ex中通过美元符号$来区分这三种引用，输入美元符号的办法是英文状态输入状态下hif+4。

①相对引用

相对引用是指，当复制Ex函数到其他单元格时，Ex保持从属单元格与引用单元格的相对位置不变。

如上图所示，在B1单元格输入=A1后，如果向下复制公式，B列每一个单元格执行的都是等于跟其在同一行的A列的单元格，B2单元格中的公式为=A2，B3中变为=A3，B4中的单元格公式为C4，等等；而如果向右复制公式，右边的单元格执行的是等于其左边相邻的单元格的引用规则，如C1单元格的公式变为=B1，D1单元格的公式变为=C1，等等。

②绝对引用

在绝对引用的情况下，复制公示到其他任何单元格，Ex保持所引用的单元格位置不变。

如上图所示，在单元格B1输入=$A$1，即意味着B1绝对引用A1，之后B1的公示无论是复制到C1，还是B5，或者D3，所引用的单元格位置都指向A1。

③混合引用

使用混合引用时，复制单元格到其他单元格，Ex出现两种情况：列绝对引用，行相对引用；列相对引用，行绝对引用。

如上图所示，在B1单元格先后输入=A1和=$A1，会发现相对引用状况下，将B1单元格的公式=A1复制到C3时，C3返回的是0，因为遵从的是=B3，而B3是空格，所以返回值0；而在绝对引用的情况下，将B1单元格的公式复制到C3时，C3的值从之前的0变成了2，是因为C3单元格遵从是=$A3。

讲完了相对引用、绝对引用和混合引用，接下来可以讲制作贷款明细表了。

案例：在借呗借款10000元，选择分6期，即6个月偿还，日利率万分之五，请问第1期至第6期每一期偿还的本金是多少，每一期支付的利息是多少，每一期偿还后剩余应还本金是多少，每一期偿还后应还的利息还有多少？

先在Ex中C1单元格录入日利率值0.05%，C2单元格录入还款期数值6，C3单元格录入贷款总额v值10000。

在C5单元格，通过PMT函数=PMT(C1*30,C2,C3)计算得到每期还款额值为-1755.25；在C6单元格，通过=C5*C2，即每期还款额乘以还款期数，计算得到还款总额为-10531.51；在单元格C7，可以使用=C6+C3（也可以用更复杂的=CUMIPMT(C1*30,C2,C3,1,6,0)计算出来），即还款总额加贷款总额，计算得到还款利息总额为-531.51。

在E2单元格输入1，向下拉到E7，得到还款期数1到6共6个数值。这个后面计算剩余应还本金、剩余应还利息时均需要相对引用E2单元格。

在F2、G2、H2、I2，依次输入以下四个公式，回车后分别下拉到F7、G7、H7、I7，即可得到此次借呗借款的还款明细表。

每期偿还本金为：=PPMT($C$1*30,$E2,$C$2,$C$3)

每期偿还利息为：=IPMT($C$1*30,$E2,$C$2,$C$3)

剩余应还本金为：=$C$3+CUMPRINC($C$1*30,$C$2,$C$3,1,E2,0)

剩余应还利息为：=CUMIPMT($C$1*30,$C$2,$C$3,1,E2,0)-$C$7

在明细表上，可以清晰地看到等额本息还款模式下，每期偿还的本金越来越多，而每期偿还的利息越来越少。

关于贷款函数的介绍，本文依然没有写完，敬请期待后续文章。