Excel22 – 第133页

Excel 计算第一期为奇数的债券收益：ODDFYIELD函数

ODDFYIELD函数用于计算首期付息日不固定的有价证券（长期或短期）的收益率。ODDFYIELD函数的语法如下：

ODDFYIELD(settlement,maturity,issue,fi rst_coupon,rate,pr,redemption,frequency,basis)

其中，settlement参数为有价证券的结算日，结算日是有价证券结算日是在发行日之后，有价证券卖给购买者的日期。maturity参数为有价证券的到期日，到期日是有价证券有效期截止时的日期。issue参数为有价证券的发行日。first_coupon参数为有价证券的首期付息日。rate参数为有价证券的利率。pr参数为有价证券的价格。redemption参数指的是面值￥100的有价证券的清偿价值。frequency参数为年付息次数，如果按年支付，frequency=1；按半年期支付，frequency=2；按季支付，frequency=4。basis参数为要使用的日计数基准类型。下面通过实例详细讲解该函数的使用方法与技巧。

已知某债券的结算日、到期日、发行日、首期付息日、息票利率、价格、清偿价值等信息，要求计算在这些条件下的债券首期付息日不固定的有价证券的收益率。打开“ODDFYIELD函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-45所示。具体的计算步骤如下。

选中A12单元格，在编辑栏中输入公式“=ODDFYIELD(A2,A3,A4,A5,A6,A7,A8,A9,A10)”，然后按“Enter”键返回，即可计算出首期付息日不固定的有价证券的收益率，如图19-46所示。

图19-45　原始数据

图19-46　计算收益率

如果settlement参数、maturity参数、issue参数或first_coupon参数不是合法日期，函数ODDFYIELD返回错误值“#VALUE!”。如果参数rate<0或参数pr≤0，函数ODDFYIELD返回错误值“#NUM!”。如果参数basis<0或参数basis>4，函数ODDFYIELD返回错误值“#NUM!”。必须满足下列日期条件，否则，函数ODDFYIELD返回错误值“#NUM!”。

maturity＞fi rst_coupon＞settlement＞issue

Excel使用迭代法计算函数ODDFYIELD。该函数基于ODDFPRICE中的公式进行牛顿迭代演算。在100次迭代过程中，收益率不断变化，直到按给定收益率导出的估计价格接近实际价格。

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 计算现金流内部收益率：IRR函数详解

IRR函数用于计算由数值代表的一组现金流的内部收益率。这些现金流不必为均衡的，但作为年金，它们必须按固定的间隔产生，如按月或按年。内部收益率为投资的回收利率，其中包含定期支付（负值）和定期收入（正值）。IRR函数的语法如下：

IRR(values,guess)

其中，values参数为数组或单元格的引用，包含用来计算返回的内部收益率的数字。guess参数为对函数IRR计算结果的估计值。下面通过实例详细讲解该函数的使用方法与技巧。

已知某公司某项业务的初期成本费用、前5年的净收入，需要计算投资若干年后的内部收益率。打开“IRR函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-37所示。具体的计算步骤如下。

STEP01：选中A9单元格，在编辑栏中输入公式“=IRR(A2:A6)”，然后按“Enter”键返回，即可计算出投资4年后的内部收益率，如图19-38所示。

图19-37　原始数据

图19-38　计算4年后的内部收益率

STEP02：选中A10单元格，在编辑栏中输入公式“=IRR(A2:A7)”，然后按“Enter”键返回，即可计算出投资5年后的内部收益率，如图19-39所示。

STEP03：选中A11单元格，在编辑栏中输入公式“=IRR(A2:A4,-10%)”，然后按“Enter”键返回，即可计算出两年后的内部收益率（使用了一个估计值），如图19-40所示。

图19-39　计算五年后的内部收益率

图19-40　计算两年后的内部收益率

Values参数必须包含至少一个正值和一个负值，以计算返回的内部收益率。函数IRR根据数值的顺序来解释现金流的顺序，故应确定按需要的顺序输入了支付和收入的数值。如果数组或引用包含文本、逻辑值或空白单元格，这些数值将被忽略。Excel使用迭代法计算函数IRR。从guess参数开始，函数IRR进行循环计算，直至结果的精度达到0.00001%。如果函数IRR经过20次迭代，仍未找到结果，则返回错误值“#NUM!”。

在大多数情况下，并不需要为函数IRR的计算提供guess值。如果省略guess参数，假设它为0.1(10%)。如果函数IRR返回错误值“#NUM!”，或结果没有靠近期望值，可用另一个guess值再试一次。

Excel 计算上一付息日：COUPPCD函数

COUPPCD函数用于计算成交日之前的上一付息日。COUPPCD函数的语法如下：

COUPPCD(settlement,maturity,frequency,basis)

其中，settlement参数为证券的结算日，结算日是在发行日之后，证券卖给购买者的日期。maturity参数为有价证券的到期日，到期日是有价证券有效期截止时的日期。frequency参数为年付息次数，如果按年支付，frequency=1；按半年期支付，frequency=2；按季支付，frequency=4。basis参数为日计数基准类型。下面通过实例详细讲解该函数的使用方法与技巧。

打开“COUPPCD函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-35所示。该工作表中记录了已知某债券的结算日、到期日、支付方式等信息，要求根据给定的条件计算在这些条件下的债券成交日之前的上一付息日。具体的操作步骤如下。

选中A7单元格，在编辑栏中输入公式“=COUPPCD(A2,A3,A4,A5)”，然后按“Enter”键返回，即可计算出债券结算日之前的付息日，如图19-36所示。

图19-35　原始数据

图19-36　计算结算日之前的付息日

如果settlement参数或maturity参数不是合法日期，则函数COUPPCD将返回错误值“#VALUE!”。如果frequency参数不为1、2或4，则函数COUPPCD将返回错误值“#NUM!”。如果参数basis<0或者参数basis>4，则函数COUPPCD返回错误值“#NUM!”。如果参数settlement≥maturity参数，则函数COUPPCD返回错误值“#NUM!”。

Excel 计算下一付息日：COUPNCD函数

COUPNCD函数用于计算一个表示在结算日之后下一个付息日的数字。COUPNCD函数的语法如下：

COUPNCD(settlement,maturity,frequency,basis)

打开“COUPNCD函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-33所示。该工作表中记录了某债券的结算日、到期日、支付方式等信息，要求根据给定的条件计算在这些条件下的债券为结算日之后的下一个付息日。具体的操作步骤如下。

选中A7单元格，在编辑栏中输入公式“=COUPNCD(A2,A3,A4,A5)”，然后按“Enter”键返回，即可计算出结算日之后的下一个付息日，如图19-34所示。

图19-33　原始数据

图19-34　计算下一付息日

如果settlement参数或maturity参数不是合法日期，则函数COUPNCD将返回错误值“#VALUE!”。如果frequency参数不为1、2或4，则函数COUPNCD将返回错误值“#NUM!”。如果参数basis<0或者参数basis>4，则函数COUPNCD返回错误值“#NUM!”。如果参数settlement≥maturity参数，则函数COUPNCD返回错误值“#NUM!”。

Excel 计算从成交日到付息日之间的天数：COUPDAYSNC函数

COUPDAYSNC函数用于计算从结算日到下一付息日之间的天数。COUPDAYSNC函数的语法如下：

COUPDAYSNC(settlement,maturity,frequency,basis)

打开“COUPDAYSNC函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-31所示。该工作表中记录了某债券的结算日、到期日、支付方式等信息，要求根据给定的条件计算在这些条件下某债券从结算日到下一个付息日的天数。具体的操作步骤如下。

选中A7单元格，在编辑栏中输入公式“=COUPDAYSNC(A2,A3,A4,A5)”，然后按“Enter”键返回，即可计算出某债券从结算日到下一个付息日的天数，如图19-32所示。

图19-31　原始数据

图19-32　计算结算日到付息日的天数

如果settlement参数或maturity参数不是合法日期，函数COUPDAYSNC返回错误值“#VALUE!”。如果frequency参数不是数字1、2或4，函数COUPDAYSNC返回错误值“#NUM!”。如果参数basis<0或参数basis>4，函数COUPDAYSNC返回错误值“#NUM!”。如果参数settlement≥maturity参数，函数COUPDAYSNC返回错误值“#NUM!”。

Excel 计算付息期与结算日之间的天数：COUPDAYS函数

COUPDAYS函数用于计算结算日所在的付息期的天数。COUPDAYS函数的语法如下：

COUPDAYS(settlement,maturity,frequency,basis)

其中，参数settlement为证券的结算日，结算日是在发行日之后，证券卖给购买者的日期。maturity为有价证券的到期日，到期日是有价证券有效期截止时的日期。frequency为年付息次数，如果按年支付，frequency=1；按半年期支付，frequency=2；按季支付，frequency=4。basis为日计数基准类型。下面通过实例详细讲解该函数的使用方法与技巧。

打开“COUPDAYS函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-29所示。该工作表中记录了某债券的结算日、到期日、支付方式等信息，要求根据给定的条件计算在这些条件下包含结算日的债券票息期的天数。具体的操作步骤如下。

选中A7单元格，在编辑栏中输入公式“=COUPDAYS(A2,A3,A4,A5)”，然后按“Enter”键返回，即可计算出包含结算日的债券票息期的天数，如图19-30所示。

图19-29　原始数据

图19-30　计算包含结算日的债券票息期的天数

如果settlement参数或maturity参数不是合法日期，函数COUPDAYS返回错误值“#VALUE!”。如果frequency参数不是数字1、2或4，函数COUPDAYS返回错误值“#NUM!”。如果参数basis<0或参数basis>4，函数COUPDAYS返回错误值“#NUM!”。如果参数settlement≥maturity参数，函数COUPDAYS返回错误值“#NUM!”。

Excel 计算付息期与结算日之间的天数：COUPDAYBS函数

COUPDAYBS函数用于计算当前付息期内截止到结算日的天数。COUPDAYBS函数的语法如下：

COUPDAYBS(settlement,maturity,frequency,basis)

打开“COUPDAYBS函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-27所示。该工作表中记录了某债券的结算日、到期日、支付方式等信息，要求根据给定的条件计算在这些条件下从债券付息期开始到结算日的天数。具体的操作步骤如下。

选中A7单元格，在编辑栏中输入公式“=COUPDAYBS(A2,A3,A4,A5)”，然后按“Enter”键返回，即可计算出从债券付息期开始到结算日的天数，如图19-28所示。

图19-27　原始数据

图19-28　计算天数

如果settlement参数或maturity参数不是合法日期，函数COUPDAYBS返回错误值“#VALUE!”。如果frequency参数不是数字1、2或4，函数COUPDAYBS返回错误值“#NUM!”。如果参数basis<0或参数basis>4，函数COUPDAYBS返回错误值“#NUM!”。如果参数settlement≥maturity参数，函数COUPDAYBS返回错误值“#NUM!”。

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

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

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

nper参数为总投资期，即该项投资的付款期总数。pmt参数为各期所应支付的金额，其数值在整个年金期间保持不变；通常，pmt参数包括本金和利息，但不包括其他费用或税款；如果忽略pmt参数，则必须包含fv参数。pv参数为现值，即从该项投资开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金。fv参数为未来值，或在最后一次付款后希望得到的现金余额；如果省略fv参数，则假设其值为零。type参数为数字0或1，用以指定各期的付款时间是在期初还是期末。guess参数为预期利率，如果省略，则假设该值为10%。如果函数RATE不收敛，则需要改变guess参数的值。通常当guess参数为0～1时，函数RATE是收敛的。

下面通过实例详细讲解该函数的使用方法与技巧。

打开“RATE函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-24所示。该工作表中记录了一组贷款数据，包括贷款期限、每月支付额和贷款额，要求根据给定的数据计算这些条件下的贷款月利率和年利率。具体的操作步骤如下。

图19-24　原始数据

STEP01：选中A6单元格，在编辑栏中输入公式“=RATE(A2*12,A3,A4)”，然后按“Enter”键返回，即可计算出贷款的月利率，如图19-25所示。

STEP02：选中A7单元格，在编辑栏中输入公式“=RATE(A2*12,A3,A4)*12”，然后按“Enter”键返回，即可计算出贷款的年利率，如图19-26所示。

图19-25　计算月利率

图19-26　计算年利率

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

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相关，如下式所示：