Excel22 – 第132页

Excel 计算首期付息日不固定债券现价：ODDFPRICE函数

ODDFPRICE函数用于计算首期付息日不固定（长期或短期）的面值￥100的有价证券价格。ODDFPRICE函数的语法如下：

ODDFPRICE(settlement,maturity,issue,fi rst_coupon,rate,yld,redemption,frequency,basis)

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

打开“ODDFPRICE函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-66所示。该工作表记录了一组债券数据，包括债券的结算日、到期日、发行日、首期付息日、息票利率、收益率、清偿价值等信息，要求计算在这些条件下首期付息日不固定（长期或短期）的面值￥100的有价证券的价格。具体的操作步骤如下。

选中A12单元格，在编辑栏中输入公式“=ODDFPRICE(A2,A3,A4,A5,A6,A7,A8,A9,A10)”，然后按“Enter”键返回，即可计算出首期付息日不固定（长期或短期）的面值￥100的有价证券的价格，如图19-67所示。

图19-66　原始数据

图19-67　计算有价证券的价格

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

maturity＞fi rst_coupon＞settlement＞issue

Excel 计算定期间内偿还本金：PPMT函数

PPMT函数用于计算一笔投资在给定期间内偿还的本金。PPMT函数的语法如下：

PPMT(rate,per,nper,pv,fv,type)

其中，rate参数为各期利率，per参数用于计算其本金数额的期数，必须为1～nper。nper参数为总投资期，即该项投资的付款期总数。pv参数为现值，即从该项投资开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金。fv参数为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv参数，则假设其值为零，也就是一笔贷款的未来值为零。type参数为数字0或1，用以指定各期的付款时间是在期初还是期末。下面通过实例详细讲解该函数的使用方法与技巧。

打开“PPMT函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-64所示。该工作表中记录了一组贷款数据，包括贷款的年利率、贷款期限和贷款额，要求计算该笔贷款第1个月的本金支付。具体的操作步骤如下。

选中A6单元格，在编辑栏中输入公式“=PPMT(A2/12,1,A3*12,A4)”，然后按“Enter”键返回，即可计算出该笔贷款第1个月的本金支付，如图19-65所示。

图19-64　原始数据

图19-65　计算第1个月的本金支付

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

Excel 计算付款期间贷款累积支付本金：CUMPRINC函数

CUMPRINC函数用于计算一笔贷款在给定的start_period到end_period期间累计偿还的本金数额。CUMPRINC函数的语法如下：

CUMPRINC(rate,nper,pv,start_period,end_period,type)

其中，rate参数为利率，nper参数为总付款期数，pv参数为现值，start_period参数为计算中的首期，付款期数从1开始计数。end_period参数为计算中的末期，type参数为付款时间类型。下面通过实例详细讲解该函数的使用方法与技巧。

已知贷款的年利率、贷款期限和现值，要求计算该笔贷款在第2年偿还的全部本金之和（第13期～第24期）和第1个月偿还的本金。打开“CUMPRINC函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-61所示。具体的操作步骤如下。

STEP01：选中A6单元格，在编辑栏中输入公式“=CUMPRINC(A2/12,A3*12,A4,13,24,0)”，然后按“Enter”键返回，即可计算出该笔贷款在第2年偿还的全部本金之和（第13期～第24期），如图19-62所示。

图19-61　原始数据

图19-62　计算本金之和

STEP02：选中A7单元格，在编辑栏中输入公式“=CUMPRINC(A2/12,A3*12,A4,1,1,0)”，然后按“Enter”键返回，即可计算出该笔贷款在第1个月偿还的本金，如图19-63所示。

图19-63　计算第1个月的偿还本金

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

如果参数rate≤0、参数nper≤0或参数pv≤0，函数CUMPRINC返回错误值“#NUM!”。如果参数start_period<1，参数end_period<1或参数start_period>end_period参数，函数CUMPRINC返回错误值“#NUM!”。如果type参数为0或1之外的任何数，函数CUMPRINC返回错误值“#NUM!”。

Excel 计算现金流内部收益率：XIRR函数详解

XIRR函数用于计算一组现金流的内部收益率，这些现金流不一定定期发生。如果要计算一组定期现金流的内部收益率，则需要使用函数IRR。XIRR函数的语法如下：

XIRR(values,dates,guess)

其中，values参数为与dates中的支付时间相对应的一系列现金流。首期支付是可选的，并与投资开始时的成本或支付有关。如果第1个值是成本或支付，则它必须是负值。所有后续支付都基于365天/年贴现。系列中必须包含至少一个正值和一个负值。dates参数为与现金流支付相对应的支付日期表。第1个支付日期代表支付表的开始，其他日期应迟于该日期，但可按任何顺序排列。应使用DATE函数输入日期，或者将函数作为其他公式或函数的结果输入。例如，使用函数DATE(2008,5,23)输入2008年5月23日。如果日期以文本形式输入，则会出现问题。guess参数为对函数XIRR计算结果的估计值。下面通过实例详细讲解该函数的使用方法与技巧。

打开“XIRR函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-59所示。该工作表记录了现金流的值与支付时间，要求根据给定的数据计算其内部收益率。具体的计算步骤如下。

选中A8单元格，在编辑栏中输入公式“=XIRR(A2:A6,B2:B6,0.1)”，然后按“Enter”键返回，即可计算出现金流的内部收益率，如图19-60所示。

图19-59　原始数据

图19-60　计算现金流内部收益率

函数XIRR要求至少有一个正现金流和一个负现金流，否则函数XIRR返回错误值“#NUM!”。如果dates参数中的任一数值不是合法日期，函数XIRR返回错误值“#VALUE”。如果dates参数中的任一数字先于开始日期，函数XIRR返回错误值“#NUM!”。如果values参数和dates参数所含数值的数目不同，函数XIRR返回错误值“#NUM!”。多数情况下，不必为函数XIRR的计算提供guess参数值，如果省略，guess参数值假定为0.1(10%)。函数XIRR与净现值函数XNPV密切相关。函数XIRR计算的收益率即为函数XNPV=0时的利率。Excel使用迭代法计算函数XIRR。通过改变收益率（从guess开始），不断修正计算结果，直至其精度小于0.000001%。如果函数XIRR运算100次，仍未找到结果，则返回错误值“#NUM!”。

Excel 计算到期付息年收益：YIELDMAT函数详解

YIELDMAT函数用于计算到期付息的有价证券的年收益率。YIELDMAT函数的语法如下：

YIELDMAT(settlement,maturity,issue,rate,pr,basis)

其中，settlement参数为证券的结算日，结算日是在发行日之后，证券卖给购买者的日期。maturity参数为有价证券的到期日，到期日是有价证券有效期截止时的日期。issue参数为有价证券的发行日，以时间序列号表示。rate参数为有价证券在发行日的利率。pr参数为面值￥100的有价证券的价格。basis参数为日计数基准类型。下面通过实例详细讲解该函数的使用方法与技巧。

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

选中A9单元格，在编辑栏中输入公式“=YIELDMAT(A2,A3,A4,A5,A6,A7)”，然后按“Enter”键返回，即可计算出债券的收益率，如图19-58所示。

图19-57　原始数据

图19-58　收益率计算结果

如果settlement参数、maturity参数或issue参数不是合法日期，函数YIELDMAT返回错误值“#VALUE!”。如果参数rate<0或参数pr≤0，函数YIELDMAT返回错误值“#NUM!”。如果参数basis<0或参数basis>4，函数YIELDMAT返回错误值“#NUM!”。如果参数settlement≥maturity参数，函数YIELDMAT返回错误值#NUM!。

Excel 计算已贴现债券年收益：YIELDDISC函数

YIELDDISC函数用于计算折价发行的有价证券的年收益率。YIELDDISC函数的语法如下：

YIELDDISC(settlement,maturity,pr,redemption,basis)

其中，settlement参数为证券的结算日，结算日是在发行日之后，证券卖给购买者的日期。maturity参数为有价证券的到期日，到期日是有价证券有效期截止时的日期。pr参数为面值￥100的有价证券的价格。redemption参数为面值￥100的有价证券的清偿价值。basis参数为日计数基准类型。下面通过实例详细讲解该函数的使用方法与技巧。

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

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

图19-55　原始数据

图19-56　债券收益率计算结果

如果settlement参数或maturity参数不是有效日期，函数YIELDDISC返回错误值“#VALUE!”。如果参数pr≤0或参数redemption≤0，函数YIELDDISC返回错误值“#NUM!”。如果参数basis<0或参数basis>4，函数YIELDDISC返回错误值“#NUM!”。如果参数settlement≥maturity参数，函数YIELDDISC返回错误值“#NUM!”。

Excel 计算定期支付利息的债券收益：

YIELD函数用于计算定期支付利息的债券的收益率。YIELD函数的语法如下：

YIELD(settlement,maturity,rate,pr,redemption,frequency,basis)

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

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

选中A10单元格，在编辑栏中输入公式“=YIELD(A2,A3,A4,A5,A6,A7,A8)”，然后按“Enter”键返回，即可计算出债券的收益率，如图19-54所示。

图19-53　原始数据

图19-54　计算债券的收益率

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

如果在清偿日之前只有一个或是没有付息期间，函数YIELD的计算公式为：

式中：

A=付息期的第1天到结算日之间的天数（应计天数）。

DSR=结算日与清偿日之间的天数。

E=付息期所包含的天数。

如果在redemption参数之前尚有多个付息期间，则通过100次迭代来计算函数YIELD。基于函数PRICE中给出的公式，并使用牛顿迭代法不断修正计算结果，直到在给定的收益率下的计算价格逼近于实际价格。

Excel 计算国库券收益率：TBILLYIELD函数

TBILLYIELD函数用于计算国库券的收益率。TBILLYIELD函数的语法如下：

TBILLYIELD(settlement,maturity,pr)

其中，settlement参数为国库券的结算日，即在发行日之后，国库券卖给购买者的日期。maturity参数为国库券的到期日，到期日是国库券有效期截止时的日期。pr参数为面值￥100的国库券的价格。下面通过实例详细讲解该函数的使用方法与技巧。

已知国库券的结算日、到期日、每￥100面值的价格，要求计算在这些条件下国库券的等效收益率。打开“TBILLYIELD函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-51所示。具体的计算步骤如下。

选中A6单元格，在编辑栏中输入公式“=TBILLYIELD(A2,A3,A4)”，然后按“Enter”键返回，即可计算出国库券的等效收益率，如图19-52所示。

图19-51　原始数据

图19-52　计算国库券收益率

如果settlement参数或maturity参数不是合法日期，函数TBILLYIELD返回错误值“#VALUE”。如果参数pr≤0，则函数TBILLYIELD返回错误值“#NUM!”。如果参数settlement≥maturity参数或maturity在settlement一年之后，函数TBILLYIELD返回错误值“#NUM!”。函数TBILLYIELD的计算公式如下：

式中：

DSM=结算日与到期日之间的天数。如果结算日与到期日相隔超过一年，则无效。

Excel 计算国库券等价债券收益：TBILLEQ函数

TBILLEQ函数用于计算国库券的等价债券收益率。TBILLEQ函数的语法如下：

TBILLEQ(settlement,maturity,discount)

其中，settlement参数为国库券的结算日，即在发行日之后，国库券卖给购买者的日期。maturity参数为国库券的到期日，到期日是国库券有效期截止时的日期。discount参数为国库券的贴现率。下面通过实例详细讲解该函数的使用方法与技巧。

已知国库券的结算日、到期日、贴现率，要求计算国库券在这些条件下的等价债券收益率。打开“TBILLEQ函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-49所示。具体的计算步骤如下。

选中A6单元格，在编辑栏中输入公式“=TBILLEQ(A2,A3,A4)”，然后按“Enter”键返回，即可计算出国库券的等价债券收益率，如图19-50所示。

如果settlement参数或maturity参数不是合法日期，函数TBILLEQ返回错误值“#VALUE!”。如果参数discount≤0，函数TBILLEQ返回错误值“#NUM!”。如果参数settlement>maturity参数或maturity参数在settlement参数之后超过一年，函数TBILLEQ返回错误值“#NUM!”。函数TBILLEQ的计算公式为TBILLEQ=(365×rate)/(360-(rate×DSM))，式中DSM是按每年360天的基准计算的settlement与maturity之间的天数。

图19-49　原始数据

图19-50　计算国库券的等效收益率

Excel 计算末一期为奇数的债券收益：ODDLYIELD函数

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

ODDLYIELD(settlement,maturity,last_interest,rate,pr,redemption,frequency,basis)

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

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

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

图19-47　原始数据

图19-48　计算末期付息日不固定的收益率

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

maturity＞settlement＞last_interest