Excel公式和函数__有关利息和利率计算
Excel 公式和函数有关利息和利率计算
在⽇常⽣活中，经常遇到贷款买房或⼤型设备的问题，此时需要根据具体情况来分析贷款的利息，以确定哪种贷款⽅式最适合⽤户。本节从实际⽣活出发，向⽤户介绍有关利息和利率的计算⽅法。
1．ACCRINT 和ACCRINTM 函数
ACCRINT 函数返回定期付息证券的应计利息。ACCRINTM 函数返回到期时，⼀次性付息有价证券的应计利息。
语法：ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis,calc_method)
ACCRINTM(issue,settlement,rate,par,basis) 在这两个函数中，其参数功能介绍如下：
● Issue 为有价证券的发⾏⽇。● First_interest 为证券的⾸次计息⽇。
● Settlement 为证券的结算⽇。结算⽇是在发⾏⽇之后，证券卖给购买者的⽇期。● Rate 为有价证券的年息票利率。
● Par 为证券的票⾯值，如果省略此参数，则ACCRINT 函数使⽤￥1000的票⾯值。
● Frequency 为年付息次数，如果按年⽀付，frequency=1；按半年期⽀付，frequency=2；按季⽀付，frequency=4。
● Basis 为⽇计数基准类型。下⾯介绍计数的基准及计算标准，如表9-1所⽰。
● calc_method 为逻辑值，指定当结算⽇期晚于⾸次计息⽇期时⽤于计算总应计利息的⽅法。
提⽰参数calc_method 如果值为TRUE(1)，则返回从发⾏⽇到结算⽇的总应计利息。如果值为FALSE(0)，则返回从⾸次计息⽇到结算⽇的应计利息。如果省略此参数，
则默认为TRUE 。
例如，某国库券的交易情况为：发⾏⽇为2008年3⽉1⽇；⾸次计息⽇为2008年8⽉31⽇；结算⽇为2008年5⽉1⽇，票息利率为10.0%；票⾯价值为￥1,000；按半年期付息；⽇计数基准为30/360。
下⾯为了计算国债的应计利息及⼀次性应付利息，可以在⼯作表中，创建如图9-1所⽰。
图9-1 创建数据信息图9-2 选择ACCRINT 函数
选择【公式】选项卡，单击【函数库】组中的【插⼊函数】按钮。在弹出的【插⼊函数】对话框中，选择ACCRINT 函数，如图9-2所⽰。
然后，在弹出的【函数参数】对话框中，分别设置函数的参数，即可得出“国债应计利息”的计算结果，如图9-3所⽰。
图9-3 计算“国债应计利息” 图9-4 计算“⼀次性应付利息”
选择B12单元格，输⼊“=ACCRINTM(A2,A4,A5,A6,A7)”公式，即可计算出“⼀次性应付利息”的值，如图9-4所⽰。
在该函数的运算过程中，为避免计算结果出错，应注意以下⼏点内容：
●参数issue 、first_interest 、settlement 、frequency 和basis 将被截尾取整。
●如果参数issue 、first_interest 或settlement 不是有效⽇期，则ACCRINT 函数将返回错误值#VALUE!。●如果参数rate≤0或par≤0，则ACCRINT 函数将返回错误值#NUM!。
●如果参数frequency 不是数字1、2或4，则ACCRINT 函数将返回错误值#NUM!。●如果参数basis<0或basis>4，则ACCRINT 函数将返回错误值#NUM!。●
如果参数issue≥settlement ，则ACCRINT 函数将返回错误值#NUM!。
2．IPMT 函数
该函数是⼀个基于固定利率及等额分期付款⽅式，返回投资或贷款在某⼀给定期次内的利息偿还额的函数，利⽤IPMT 函数可以计算各⽉偿还的利息额。
语法：IPMT(rate,per,nper,pv,fv,type)
其中，IPMT 函数中，包含的6个参数功能如下：
● Rate 为各期利率，是⼀固定值。
● Per ⽤于计算其利息数额的期次，必须在1⾄nper 之间。
● Nper 为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。
●
Pv 为现值，即从该项投资（或贷款）开始计算时已经⼊账的款项，或⼀系列未来付款当前值
显⽰
创建表格
单击
选择
单击
设置
计算
输⼊
的累积和，也称为本⾦。● Fv 为未来值，或在最后⼀次付款后希望得到的现⾦余额。如果省略 fv ，则假设其值为0（例如，⼀笔贷款的未来值即为0）。
●
Type 只能是数字0或1，⽤以指定各期的付款时间是在期初还是期末。如果省略type ，则假设其值为0。
注意应确认所指定的参数rate 和nper 单位的⼀致性。例如，同样是四年期年利率为12%
的贷款，如果按⽉⽀付，rate 应为12%/12，nper 应为4*12；如果按年⽀付，rate
应为12%，nper 为4
提⽰在IPMT 函数中，对于所有参数，⽀出的款项，如银⾏存款，表⽰为负数；收⼊的
款项，如股息收⼊，表⽰为正数。
例如，某⼈购⼀套250,000元的房⼦，⾸付30%后，8年按揭付清（⾄0），按⽬前银⾏商业住房贷款利率，8年的⽉利率是0.42%，试求各⽉偿还的利息额。
在⼯作表中，创建“贷款偿还额计算”表格，并在B7单元格中，输⼊“=B2-B2*B3”公式，计算“贷款⾦额”，如图9-5所⽰。
图9-5 计算“贷款⾦额” 图9-6 设置参数
然后，选择B8单元格，在【函数参数】对话框中，设置各参数，如图9-6所⽰，即可计算出“每⽉还款利息⾦额”。3．CUMIPMT 函数
该函数是基于固定利率和等额分期付款⽅式，返回开始期次到结束期次间累计偿还的利息⾦额。也就是返回⼀笔贷款在给定的start-period 到end-period 期间累计偿还的利息数额。
语法：C UMIPMT(rate,nper,pv,start_period,end_period,type) 其中，在该函数中主要包含6个参数，其功能如下：
● Rate 为利率。● Nper 为总付款期数。● Pv 为现值。
● Start_period 为计算中的⾸期，付款期数从 1 开始计数。● End_period 为计算中的末期。
●
Type 为付款时间类型，若Type 值为0（零），则表⽰期末付款；若Type 值为1，则表⽰期初付款。
例如，⼀笔住房抵押贷款的交易情况如下：年利率为9.00%；期限为10年；现值为￥180，
000，试求第3年到第7年之间累计偿还的利息⾦额为多少？
在⼯作表中，创建“住房抵押贷款”表格，输⼊“=CUMIPMT(B2,B3,B4,B5,B6,0)”公式，
显⽰结果
输⼊
结果
设置
即可求出第3年到第7年累计利息⾦额为-55996.37998，如图9-7所⽰。
图9-7 计算累计利息⾦额
该函数在计算过程中，为避免函数的计算结果出错，以注意以下⼏点：
●
应确认所指定的参数rate 和nper 单位的⼀致性。例如，同样是四年期年利率为10%的贷款，如果按⽉⽀付，参数rate 应为10%/10，⽽参数nper 应为4*12；如果按年⽀付，rate 应为10%，nper 为4。●参数Nper 、start_period 、end_period 和type 将被截尾取整。
●如果参数rate ≤0、nper≤0或pv≤0，CUMIPMT 函数返回错误值#NUM!。
●如果start_period<1、end_period<1或start_period>end_period ，CUMIPMT 函数返回错误值#NUM!。●
如果参数type 不是数字0或1，CUMIPMT 函数将返回错误值#NUM!。
4．DISC 函数
该函数返回有价证券的贴现率。其中，贴现率是指将未来⽀付改变为现值所使⽤的利率，或指持票⼈以未到期的票据向银⾏要求兑现，银⾏将利息先⾏扣除所使⽤的利率。
语法：DISC(settlement,maturity,pr,redemption,basis)
其中，在该函数中，参数settlement 和basis 与ACCRINT 函数中的参数含义相同，这⾥只介绍其他3个参数的功能：
● Maturity 为有价证券的到期⽇。到期⽇是有价证券有效期截⽌时的⽇期。● Pr 为⾯值￥100的有价证券的价格。
● Redemption 为⾯值￥100的有价证券的清偿价值。
提⽰
清偿价值是⼀种带有强制条件的变现价值，即指资产按照公允价值扣除预计将要发⽣的相关费⽤和税⾦后的价值。
例如，某债券的交易情况如下：成交⽇为2008年3⽉18⽇，到期⽇为2008年8⽉7⽇，价格为￥45.834，清偿价格为￥48，⽇计数基准为实际天数/360，试计算该债券的有价证券值。
在⼯作表中，创建“计算债券贴现率”表格，并在B7单元格中，输⼊“=DISC(B2,B3,B4,B5,B6)”公式，即可计算“有价证券”的值，
如图9-8所⽰。
图9-8 计算债券贴现率
结果
输⼊
输⼊
该函数在介绍的过程中，需注意以下⼏点内容：
●参数Settlement 、maturity 和basis 将被截尾取整。
●如果参数settlement 或maturity 不是合法⽇期，函数DISC 返回错误值#V ALUE ！。●如果参数pr≤0或redemption≤0，函数DISC 返回错误值#NUM!。●如果参数basis<0或basis>4，函数DISC 返回错误值#NUM!。●
如果参数settlement≥maturity ，函数DISC 返回错误值#NUM!。
5．RATE 函数
该函数可以⽤来计算盈利率，即求出基于固定利率及在等额分期付款⽅式下，返回的贷款每期付款额。
语法：RATE(nper,pmt,pv,fv,type,guess) 其中，RATE 函数的各参数功能如下：
● Nper 为总投资期，即该项投资的付款期总数。
● Pmt 为各期付款额，其数值在整个投资期内保持不变。通常pmt 包括本⾦和利息，但不包括其他费⽤或税⾦。如果忽略了pmt ，则必须包含fv 参数。
● Pv 为现值，即从该项投资开始计算时已经⼊帐的款项，或⼀系列未来付款当前值的累积和，也称为本⾦。
● Fv 为未来值，或在最后⼀次付款后希望得到的现⾦余额。如果省略fv ，则假设其值为0（例如，⼀笔贷款的未来值即为0）。
● Type 数字0或1，⽤以指定各期的付款时间是在期初还是期末。●
Guess 为预期利率。如果省略预期利率，则假设该值为10%。
例如，如果存⼊银⾏3600元存款，且今后每⽉末存⼊500元，预在10年后使得存款
数额达到100000元，计算其⽉利率和年利率。
在⼯作表中，创建“银⾏存款利息”表格。然后，选择B6单元格，输⼊“=RATE(B2*12,B3,B4,B5)”公式，即可计算出“⽉利率”的值，如图9-9所⽰。
图9-9 计算⽉利率图9-10 计算年利率
选择B7单元格，输⼊“=RATE(B2*12,B3,B4,B5)*12”公式，即可求出“年利率”的值，如图9-10所⽰。 6．IRR 函数
IRR 函数返回由数值代表的⼀组现⾦流的内部收益率。这些现⾦流不⼀定必须为均衡的，但作为年⾦，它们必须按固定的间隔发⽣，如按⽉或按年。内部收益率为投资的回收利率，其中包含定期⽀付（负值）和收⼊（正值）。
语法：IRR(values,guess)
在该函数中，主要包含两上参数，其功能如下：
●
Values
该参数为数组或单元格的引⽤，包含⽤来计算返回的内部收益率的数字。在使⽤参数Values 时应注意参数Values 必须包含⾄少⼀个正值和⼀个负值，以计算返回的内部收益率。还应注意IRR 函数根据数值的顺序来解释现⾦流的顺序，应确定按需要的顺序输⼊了⽀付和收⼊的数值。另外，还应注意当数组或引⽤
输⼊
frequency函数计算频数结果
输⼊
结果
包含⽂本、逻辑值或空⽩单元格，这些数值将被忽略。
Guess
该参数为对IRR 函数计算结果的估计值。在使⽤参数Guess 时应注意Excel 使⽤迭代法计算IRR 函数。从参数guess 开
始，IRR 函数进⾏循环计算，直⾄结果的精度达到0.00001%。如果IRR 函数经过20次迭代，仍未到结果，则返回错误值#NUM!。
另外，在⼤多数情况下，并不需要为IRR 函数的计算提供guess 值。如果省略guess ，则假设其值为0.1(10%)。如果IRR 函数返回错误值#NUM!，或结果没有靠近期望值，可⽤另⼀个guess 值再试⼀次。
例如，宇杰公司计划了⼀个投资项⽬，该投资项⽬的净现⾦流量表如图9-11所⽰，要求分别计算该项⽬投资3年、5年后的内部收益率，并估算2年后的内部报酬率（包含⼀个估计值）。
如图9-11 创建内部收益率表格图9-12 计算3年后的内部收益率
选择B8单元格，在弹出的【函数参数】对话框中，设置参数，如图9-12所⽰。
选择B9单元格，输⼊“=IRR(B2:B7)”公式，即可计算出“投资5年后的内部收益率”的值，如图9-13所⽰。
图9-13 计算投资5年后的内部收益率图9-14 估算2年后的内部收益率
选择B10单元格，输⼊“=IRR(B2:B4,-45%)”公式，即可求出所需的数值，如图9-14所⽰。
另外，除了上述介绍的与利率和利息有关的函数外，下⾯以列表的形式介绍其他的利率和利息函数，其功能如表9-2所⽰。
表9-2 利率和利息相关函数功能介绍创建表格
输⼊
计算结果
计算
结果