标签：利率

Excel 实例：使用单变量求解计算银行贷款利率

在计算贷款利率时，需要使用PMT函数，而如果反过来计算符合目标月还款额的贷款利率，则可以使用单变量求解和PMT函数来实现，下面通过实例说明具体操作步骤。

步骤1：首先准备运算工作表。新建一个空白工作表，然后按下列要求输入数值或公式，具体如图22-94所示。

图22-94　准备运算工作表

  • 在单元格A1中，输入“贷款总额”。
  • 在单元格A2中，输入“期限（月）”。
  • 在单元格A3中，输入“利率”。
  • 在单元格A4中，输入“月还款额”。
  • 在单元格B1中，输入“100000”，即需要贷入的总金额。
  • 在单元格B2中，输入“180”，即需要还款的月数。
  • 在单元格B4中，输入“=PMT（B3/12，B2，B1）”，该公式用于计算月还款金额。

提示：在本例中，已知每月需要还款额为“￥900”，但并不需要在此处输入该金额，因为下一步需要使用单变量求解确定利率，而单变量求解需要以公式开头。由于单元格B3中不含有数值，Excel会假设利率为0%，并使用本例中的值返回月还款金额“￥555.56”，此时可以忽略该值。有关PMT函数的用法，参见前面章节该函数的介绍。

步骤2：接下来设置B3单元格的格式。右键单击B3单元格，然后单击弹出菜单中的“设置单元格格式”对话框，将数字格式设置为“百分比”，并将小数位数设置为2，如图22-95所示。

步骤3：下面使用“单变量求解”计算贷款利率。单击“数据”选项卡，然后单击“预测”组中的“模拟分析”|“单变量求解”命令，打开“单变量求解”对话框。

步骤4：在“目标单元格”框中，输入要求解的公式所在单元格的引用，本例中输入“$B$4”。

步骤5：在“目标值”框中输入所需要的公式结果，本例中结果为-900（负数表示为还款金额）。

步骤6：在“可变单元格”框中输入要调整的值所在单元格的引用，本例中输入“$B$3”，具体设置如图22-96所示。

图22-95　设置单元格格式

步骤7：单击“确定”按钮，查看“单变量求解状态”，如图22-97所示。

图22-96　“单变量求解”对话框

图22-97　单变量求解状态

步骤8：单击“确定”按钮，单变量求解功能运行并产生结果，当月还款额为900时，利率为7.02%，如图22-98所示。

图22-98　使用单变量求解功能计算出的利率

Excel 应用RATE函数计算年金的各期利率

RATE函数用于计算年金的各期利率。函数RATE通过迭代法计算得出，并且可能无解或有多个解。如果在进行20次迭代计算后，函数RATE的相邻两次结果没有收敛于0.0000001，函数RATE将返回错误值“#NUM！”。RATE函数的语法如下。

RATE(nper,pmt,pv,fv,type,guess)

有关参数nper、pmt、pv、fv及type的详细说明，可以参阅函数PV。参数nper为总投资期，即该项投资的付款期总数。pmt为各期所应支付的金额，其数值在整个年金期间保持不变。通常，pmt包括本金和利息，但不包括其他费用或税款。如果忽略pmt，则必须包含fv参数。pv为现值，即从该项投资开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金。

fv为未来值，或在最后一次付款后希望得到的现金余额。如果省略fv，则假设其值为零。type为数字0或1，用以指定各期的付款时间是在期初还是期末。guess为预期利率。如果省略预期利率，则假设该值为10%。如果函数RATE不收敛，则需要改变guess的值。通常当guess位于0到1之间时，函数RATE是收敛的。

典型案例

已知贷款期限、每月支付额和贷款额，计算这些条件下的贷款月利率和年利率。基础数据如图17-19所示。

步骤1：打开例子工作簿“RATE.xlsx”。

步骤2：在单元格A6中输入公式“=RATE（A2*12，A3，A4）”，用于计算在上述条件下贷款的月利率。

步骤3：在单元格A7中输入公式“=RATE（A2*12，A3，A4）*12”，用于计算在上述条件下贷款的年利率。计算结果如图17-20所示。

图17-19　基础数据

图17-20　计算结果

使用指南

应确认所指定的guess和nper单位的一致性，对于年利率为12%的四年期贷款，如果按月支付，guess为12%/12，nper为4*12；如果按年支付，guess为12%，nper为4。

Excel 应用NOMINAL函数计算年度的名义利率

NOMINAL函数用于基于给定的实际利率和年复利期数，计算名义年利率。NOMINAL函数的语法如下。

NOMINAL(effect_rate,npery)

其中参数effect_rate为实际利率，npery为每年的复利期数。

典型案例

已知某债券的实际利率、每年的复利期数，计算这些条件下的名义利率。基础数据如图17-17所示。

步骤1：打开例子工作簿“NOMINAL.xlsx”。

步骤2：在单元格A5中输入公式“=NOMINAL（A2，A3）”，用于计算名义利率。计算结果如图17-18所示。

图17-17　基础数据

图17-18　计算结果

使用指南

npery若非整数将被截尾取整。如果任一参数为非数值型，函数NOMINAL返回错误值“#VALUE！”；如果effect_rate≤0或npery<1，函数NOMINAL返回错误值“#NUM！”。函数NOMINAL与函数EFFECT相关，如下式所示：

Excel 应用INTRATE函数计算完全投资型证券的利率

INTRATE函数用于计算一次性付息证券的利率。INTRATE函数的语法如下：

INTRATE(settlement,maturity,investment,redemption,basis)

其中参数settlement为证券的结算日，结算日是在发行日之后，证券卖给购买者的日期。maturity为有价证券的到期日，到期日是有价证券有效期截止时的日期。investment为有价证券的投资额。redemption为有价证券到期时的清偿价值。basis为日计数基准类型。

典型案例

已知某债券的结算日、到期日、投资额、清偿价值等信息，计算在此债券期限的贴现率。基础数据如图17-11所示。

步骤1：打开例子工作簿“INTRATE.xlsx”。

步骤2：在单元格A8中输入公式“=INTRATE（A2，A3，A4，A5，A6）”，用于计算在此债券期限的贴现率。计算结果如图17-12所示。

图17-11　基础数据

图17-12　计算结果

使用指南

参数settlement、maturity和basis若非整数将被截尾取整。如果settlement或maturity不是合法日期，函数INTRATE返回错误值“#VALUE！”；如果investment≤0或redemption≤0，函数INTRATE返回错误值“#NUM！”；如果basis<0或basis>4，函数INTRATE返回错误值“#NUM！”；如果settlement≥maturity，函数INTRATE返回错误值“#NUM！”。函数INTRATE的计算公式如下。

式中：

B为一年之中的天数，取决于年基准数。

DIM为结算日与到期日之间的天数。

Excel 应用EFFECT函数计算年有效利率

EFFECT函数利用给定的名义利率和每年的复利期数，计算有效的年利率。EFFECT函数的语法如下。

EFFECT(nominal_rate,npery)

其中参数nominal_rate为名义利率，npery为每年的复利期数。

典型案例

已知某贷款的名义利率与每年的复利期数，计算满足这些条件的有效利率。基础数据如图17-9所示。

步骤1：打开例子工作簿“EFFECT.xlsx”。

步骤2：在单元格A5中输入公式“=EFFECT（A2，A3）”，用于计算。计算结果如图17-10所示。

图17-9　基础数据

图17-10　计算结果

使用指南

npery若非整数将被截尾取整。如果任一参数为非数值型，函数EFFECT返回错误值“#VALUE！”；如果nominal_rate≤0或npery<1，函数EFFECT返回错误值“#NUM！”。函数EFFECT的计算公式为：

Excel 计算投资回收率函数RATE

函数RATE的功能是返回某项投资各期的利率（或回收率）。它的语法格式为：

RATE（nper,pmt,pv,fv,type）

它一共有5个参数，nper表示偿还期或投资期，pmt为各期所应支付的金额，其数值在整个年金期间保持不变，通常包括本金和利息，但不包括其他费用和税款；pv表示现值，即从该项投资开始计算时已经入账的款项，或一系列未来付款的当前值的累积和，也称为本金；fv表示在最后一次付款后希望得到的现金余额；type用以指定各期的付款时间是在期初还是期末。

例如，某公司欲进行一项投资，一次性投入资金约为20万元，如果要求在5年投资回收期中使资本增值为50万元，则年投资回收率应该不低于多少？

步骤01：新建一工作簿，在工作表中创建如图4-39所示的表格并输入数据。

图4-39　创建表格

步骤02：在单元格B5中输入公式“=RATE（B3,0,-B2,B4,0）”，按下Enter键后，计算结果为20.11%，如图4-40所示。意思是只有在回收率为20.11%的情况下，投资20万元在5年后才可能增值为50万。

图4-40　输入公式计算回收率

高手支招：在Excel中显示公式详细计算过程

对于Excel中的公式，特别是对于一些较为复杂的公式，在对公式进行错误检查时，可以使用“公式求值”命令查看公式中每一步的求值过程，它有助于用户读懂复杂的公式运算。

步骤01：选中要求值的公式所在的单元格，在“公式”选项卡中的“公式审核”组中单击“公式求值”按钮打开“公式求值”对话框。在该对话框中的“求值”框中带下画线的部分代表当前要求值的表达式，单击“求值”按钮对带下画线的表达式进行求值，如图4-41所示。

图4-41　单击“求值”按钮

步骤02：下画线会移到新的表达式，再次单击“求值”按钮，直到得到最终的结果，如图4-42所示。

图4-42　显示表达式求值结果

Excel 计算一次性付息证券的利率：INTRATE函数

如果需要计算一次性付息证券的利率，可通过“INTRATE”函数实现。其语法为：=INTRATE(settlement, maturity, investment, redemption, basis)，其中各参数的含义介绍如下。

※　settlement：证券的成交日。

※　maturity：有价证券的到期日。

※　investment：有价证券的投资额。

※　redemption：有价证券到期时的清偿价值。

※　basis：日计数基准类型（0、1、2、3、4）。

计算一次性付息证券利率的方法如下。

01　在“B1:B5”单元格区域中依次输入证券的成交日、到期日、投资额、清偿价值以及日计数基准类型。

02　在单元格中输入公式：=INTRATE(B1,B2,B3,B4,B5)，然后按下“Enter”键确认即可。

alt

Excel 计算年金的各期利率：RATE函数

如果需要计算年金的各期利率，可通过“RATE”函数实现。RATE函数的语法为：=RATE(nper,pmt,pv,fv,type, guess)，其中各参数的含义介绍如下。

※　nper：总投资期。

※　pmt：各期付款额。

※　pv：现值。

※　fv：未来值。

※　type：用以指定各期的付款时间是在期初（1）还是期末（0）。

※　guess：预期利率。

假设贷款金额为1万元，贷款期限为10年，每月还款500元，下面分别计算贷款的月利率和年利率。

计算贷款的月利率

01　在“B1:B3”单元格区域中分别输入贷款期限、每月支付额和贷款额。

02　在单元格中输入公式：=RATE(B1*12,B2,B3)，然后按下“Enter”键确认即可。

alt

计算贷款的年利率

01　在“B1:B3”单元格区域中分别输入贷款期限、每月支付额和贷款额。

02　在单元格中输入公式：=RATE(B1*12,B2,B3)*12，然后按下“Enter”键确认即可。

alt

Excel 计算名义年利率：NOMINAL函数

如果需要计算某条件下的名义利率，可通过“NOMINAL”函数实现。NOMINAL函数的语法为：=NOMINAL (effect_rate,npery)，各参数的含义介绍如下。

※　effect_rate：实际利率。

※　npery：每年的复利期数。

假设指定实际利率为5.35％，每年的复利期数为4，计算名义年利率。

01　在“B1”单元格中指定实际利率，在“B2”单元格中指定每年的复利期数。

02　在单元格中输入公式：=NOMINAL(B1, B2)，然后按下“Enter”键确认即可。

alt

Excel 计算有效的年利率：EFFECT函数

如果需要利用给定的名义年利率和每年的复利期数，计算有效的年利率，可通过“EFFECT”函数实现。“EFFECT”函数的语法为：= EFFECT (nominal_rate, npery)，各参数的含义介绍如下。

※　nominal_rate：名义利率。

※　npery：每年的复利期数。

下面举例计算指定条件下的有效年利率。

01　在“B1”单元格中输入名义利率，在“B2”单元格中输入每年的复利期数。

02　在要显示结果的单元格中输入公式：=EFFECT(B1, B2)，按下“Enter”键确认。

alt