红歌经典-自信的事例

Excel公式和函数 有关利息和利率计算
在日常生活中，经常遇到贷款买房或大 型设备的问题，此时需要根据具体情况来分析贷
款的利息，以确定哪种贷款方式最适合用户。本节从实际 生活出发，向用户介绍有关利息和
利率的计算方法。
1．ACCRINT和ACCRINTM函数
ACCRINT函数返回定期付息证券的应计利 息。ACCRINTM函数返回到期时，一次性付息
有价证券的应计利息。
语法：ACCRI NT(issue,first_interest,settlement,rate,par,freque ncy,basis,calc_method)
ACCRINTM(issue,settlement,rate,par,basis)
在这两个函数中，其参数功能介绍如下：







basis
0或省略
1
2


参数calc_method如果值为TRUE(1)，则返回从发行日到结算 日的总应计利息。如
果值为FALSE(0)，则返回从首次计息日到结算日的应计利息。如果省略此参 数，
则默认为TRUE。
Issue 为有价证券的发行日。
First_interest 为证券的首次计息日。
Settlement 为证券的结算日。结算日是在发行日之后,证券卖给购买者的日期。
Rate 为有价证券的年息票利率。
Par 为证券的票面值，如果省略此参数，则ACCRINT函数使用￥1000的票面值。
Frequency 为年付息次数，如果按年支付，frequency=1；按半年期支付，fr equency=2；按季支
付，frequency=4。
Basis 为日计数基准类型。下面介绍计数的基准及计算标准，如表9-1所示。
表9-1 日计数基准
日计数基准
US(NASD)30360
实际天数实际天数
实际天数360
basis
3
4

日计数基准
实际天数365
欧洲30360

calc_method 为逻辑值，指定当结算日期晚于首次计息日期时用于计算总应计利息的方法。

提 示

例如，某国库券的交易情况为：发行日为2008年3月1日；首次计息日为2008年8< br>月31日；结算日为2008年5月1日，票息利率为10.0%；票面价值为￥1,000；按半年期< br>付息；日计数基准为30360。
下面为了计算国债的应计利息及一次性应付利息，可以在工作表中，创建如图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、fi rst_interest、settlement、frequency和basis将被截尾取整。
如果参数issue、first_interest或settlement不是有效日期，则ACCRIN T函数将返回错误值#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函数中 ，对于所有参数，支出的款项，如银行存款，表示为负数；收入的
款项，如股息收入，表示为正数。 < br>例如，某人购一套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期间累计偿还的利息数额。
语法：CUMIPMT(rate,nper,pv,sta rt_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!。 < br>如果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返回错误值#VALUE！。
如果参数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包括本金和利息，但不包括其< br>他费用或税金。如果忽略了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函数
IR R函数返回由数值代表的一组现金流的内部收益率。这些现金流不一定必须为均衡的，
但作为年金，它们 必须按固定的间隔发生，如按月或按年。内部收益率为投资的回收利率，
其中包含定期支付（负值）和收 入（正值）。
语法：IRR(values,guess)
在该函数中，主要包含两上参数，其功能如下：
 Values
该参数为数组或 单元格的引用，包含用来计算返回的内部收益率的数字。在使用参数Values时应注意
参数Valu es必须包含至少一个正值和一个负值，以计算返回的内部收益率。还应注意IRR函数根据数值的
顺序 来解释现金流的顺序，应确定按需要的顺序输入了支付和收入的数值。另外，还应注意当数组或引用
包含 文本、逻辑值或空白单元格，这些数值将被忽略。
 Guess
该参数为对IRR函数计 算结果的估计值。在使用参数Guess时应注意Excel使用迭代法计算IRR函数。
从参数gue ss开始，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 利率和利息相关函数功能介绍
函数名称
EFFECT
MIRR
语法
EFFECT(nominal_rate,npery)
MIRR(values,finance_rate,reinvest_rate)
含义
利用给定的名义年利率和每年的复利期
数，计算有效的年利率。
返回某一连续期间内现金流的修正内部
收益率。MIR R函数同时考虑了投资的成
本和现金再投资的收益率
返回一组现金流的内部收益率，这些现金
流不一定定期发生。若要计算一组定期现
金流的内部收益率，可使用IRR函数。
基于给定的实际利率和年复利期数，返回
名义年利率。
计算特定投资期内要支付的利息。
计算
结果
XIRR XIRR(values,dates,guess)
NOMINAL NOMINAL(effect_rate,npery)
ISPMT
INTRATE
ISPMT(rate,per,nper,pv)
INTRATE(settlement,maturity,investment,
返回一次性付息证券的利率。
redemption,basis)