利率 – 第2页

Excel 实战：计算银行贷款利率

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

STEP01：打开“银行贷款.xlsx”工作簿，将要处理的数据输入工作表中，本例中的原始数据如图22-62所示。

STEP02：选中B4单元格，在编辑栏中输入公式“=PMT(B3/12,B2,B1)”，用于计算月还款金额，然后按“Enter”键返回工作表页面，如图22-63所示。

图22-62　原始数据

图22-63　设置月还款额计算公式

在本例中，已知每月需要还款额为900，但并不需要在此处输入该金额，因为下一步需要使用单变量求解确定利率，而单变量求解需要以公式开头。由于单元格B3中不含有数值，Excel会假设利率为0%，并使用本例中的值返回月还款金额555.56，此时可以忽略该值。

STEP03：选择B3单元格，单击鼠标右键，在弹出的隐藏菜单中选择“设置单元格格式”选项，打开如图22-64所示的“设置单元格格式”对话框。切换至“数字”选项卡，在“分类”列表框中选择“百分比”选项，并将小数位数设置为“2”，最后单击“确定”按钮完成对B3单元格数字格式的设置。

STEP04：在工作表页面将功能区切换至“数据”选项卡，单击“预测”组中的“模拟分析”下三角按钮，在展开的下拉列表中选择“单变量求解”选项，打开“单变量求解”对话框，如图22-65所示。

图22-64　设置数字格式

图22-65　选择“单变量求解”选项

STEP05：打开“单变量求解”对话框后，在“目标单元格”文本框中输入要求解的公式所在单元格的引用，本例中输入“$B$4”；在“目标值”文本框中输入所需要的公式结果，本例中结果为“-900”（负数表示为还款金额）；在“可变单元格”文本框中输入要调整的值所在单元格的引用，本例中输入“$B$3”，最后单击“确定”按钮，如图22-66所示。

STEP06：随后会弹出如图22-67所示的“单变量求解状态”对话框。在对话框中单击“确定”按钮，单变量求解功能就可以运行并产生结果。当月还款额为900时，利率为7.02%，如图22-68所示。

图22-66　设置参数

图22-67　单变量求解状态

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

Excel 计算不同利率内部收益率：MIRR函数

MIRR函数用于计算某一连续期间内现金流的修正内部收益率。函数MIRR同时考虑了投资的成本和现金再投资的收益率。MIRR函数的语法如下：

MIRR(values,fi nance_rate,reinvest_rate)

其中，values参数为一个数组或对包含数字的单元格的引用。这些数值代表着各期的一系列支出（负值）及收入（正值）。values参数中必须至少包含一个正值和一个负值，才能计算修正后的内部收益率，否则函数MIRR会返回错误值“#DIV/0!”。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。finance_rate参数为现金流中使用的资金支付的利率。reinvest_rate参数为将现金流再投资的收益率。下面通过实例详细讲解该函数的使用方法与技巧。

已知某公司某项资产的原值、前5年每年的收益，要求计算5年后投资的修正收益率、3年后的修正收益率及基于14%的再投资收益率的5年修正收益率。打开“MIRR函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-41所示。具体的计算步骤如下。

STEP01：选中A11单元格，在编辑栏中输入公式“=MIRR(A2:A7,A8,A9)”，然后按“Enter”键返回，即可计算出5年后投资的修正收益率，如图19-42所示。

STEP02：选中A12单元格，在编辑栏中输入公式“=MIRR(A2:A5,A8,A9)”，然后按“Enter”键返回，即可计算出3年后的修正收益率，如图19-43所示。

图19-41　原始数据

图19-42　计算5年后的修正收益率

STEP03：选中A13单元格，在编辑栏中输入公式“=MIRR(A2:A7,A8,14%)”，然后按“Enter”键返回，即可计算出基于14%的再投资收益率的5年修正收益率，如图19-44所示。

图19-43　计算3年后的修正收益率

图19-44　计算结果

函数MIRR根据输入值的次序来解释现金流的次序。所以，务必按照实际的顺序输入支出和收入数额，并使用正确的正负号（现金流入用正值，现金流出用负值）。如果现金流的次数为n，finance_rate为frate，而reinvest_rate为rrate，则函数MIRR的计算公式为：

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

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

NOMINAL(effect_rate,npery)

其中，effect_rate参数为实际利率，npery参数为每年的复利期数。下面通过实例详细讲解该函数的使用方法与技巧。

打开“NOMINAL函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-22所示。该工作表中记录了某债券的实际利率、每年的复利期数，要求根据给定的数据计算在这些条件下的名义利率。具体的操作步骤如下。

选中A5单元格，在编辑栏中输入公式“=NOMINAL(A2,A3)”，然后按“Enter”键返回，即可计算出名义利率，如图19-23所示。

图19-22　原始数据

图19-23　计算名义利率

如果任一参数为非数值型，函数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参数为日计数基准类型。下面通过实例详细讲解该函数的使用方法与技巧。

打开“INTRATE函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-14所示。该工作表中记录了某债券的结算日、到期日、投资额、清偿价值等信息，要求根据给定的数据计算在此债券期限的贴现率。具体的操作步骤如下。

选中A8单元格，在编辑栏中输入公式“=INTRATE(A2,A3,A4,A5,A6)”，然后按“Enter”键返回，即可计算出在此债券期限的贴现率，如图19-15所示。

图19-14　原始数据

图19-15　计算贴现率

如果settlement参数或maturity参数不是合法日期，函数INTRATE返回错误值“#VALUE!”。如果参数investment≤0或参数redemption≤0，函数INTRATE返回错误值“#NUM!”。如果参数basis<0或参数basis>4，函数INTRATE返回错误值“#NUM!”。如果参数settlement≥maturity，函数INTRATE返回错误值“#NUM!”。函数INTRATE的计算公式如下：