Excel22 – 第131页

Excel 计算修正期限：MDURATION函数

MDURATION函数用于计算假设面值￥100的有价证券的Macauley修正期限。MDURATION函数的语法如下：

MDURATION(settlement,maturity,coupon,yld,frequency,basis)

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

打开“MDURATION函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-86所示。该工作表中记录了一组债券数据，具体包括债券的结算日、到期日、息票利率、收益率等信息，要求根据给定的数据计算出债券的修正期限。具体的操作步骤如下。

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

图19-86　原始数据

图19-87　计算债券的修正期限

如果settlement参数或maturity参数不是合法日期，函数MDURATION返回错误值“#VALUE!”。如果参数yld<0或参数coupon<0，函数MDURATION返回错误值“#NUM!”。如果frequency参数不是数字1、2或4，函数MDURATION返回错误值“#NUM!”。如果参数basis<0或参数basis>4，函数MDURATION返回错误值“#NUM!”。如果参数settlement≥maturity参数，函数MDURATION返回错误值“#NUM!”。修正期限的计算公式如下：

Excel 计算定期支付利息债券每年期限：DURATION函数

DURATION函数用于计算假设面值￥100的定期付息有价证券的修正期限。期限定义为一系列现金流现值的加权平均值，用于计量债券价格对于收益率变化的敏感程度。DURATION函数的语法如下：

DURATION(settlement, maturity, coupon, yld, frenguency, basis)

打开“DURATION函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-84所示。该工作表中记录了一组有价证券数据，具体包括有价证券的结算日、到期日、息票利率、收益率等信息，要求根据给定的数据计算出有价证券的修正期限。具体的操作步骤如下。

选中A9单元格，在编辑栏中输入公式“=DURATION(A2,A3,A4,A5,A6,A7)”，然后按“Enter”键返回，即可计算出有价证券的修正期限，如图19-85所示。

图19-84　原始数据

图19-85　计算有价证券的修正期限

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

Excel 计算投资现值：PV函数详解

PV函数用于计算投资的现值。现值为一系列未来付款的当前值的累积和。例如，借入方的借入款即为贷出方贷款的现值。PV函数的语法如下：

PV(rate,nper,pmt,fv,type)

其中，rate参数为各期利率。例如，如果按10%的年利率借入一笔贷款来购买汽车，并按月偿还贷款，则月利率为10%/12（即0.83%）。可以在公式中输入10%/12、0.83%或0.0083作为rate的值。

nper参数为总投资期，即该项投资的付款期总数。例如，对于一笔四年期按月偿还的汽车贷款，共有4*12（即48）个偿款期数。可以在公式中输入48作为nper的值。

pmt参数为各期所应支付的金额，其数值在整个年金期间保持不变。通常，pmt参数包括本金和利息，但不包括其他费用或税款。例如，￥10 000的年利率为12%的四年期汽车贷款的月偿还额为￥263.33。可以在公式中输入-263.33作为pmt的值。如果忽略pmt，则必须包含fv参数。

fv参数为未来值，或在最后一次支付后希望得到的现金余额，如果省略fv参数，则假设其值为零（例如，一笔贷款的未来值即为零）。例如，如果需要在18年后支付￥50 000，则￥50 000就是未来值。可以根据保守估计的利率来决定每月的存款额。如果忽略fv，则必须包含pmt参数。

type参数为数字0或1，用以指定各期的付款时间是在期初还是期末。

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

打开“PV函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-82所示。该工作表中记录了一组数据，具体包括每月底一项保险年金的支出、投资收益率、付款的年限，要求根据给定的数据计算在这些条件下年金的现值。具体的操作步骤如下。

选中A6单元格，在编辑栏中输入公式“=PV(A3/12,12*A4,A2,,0)”，然后按“Enter”键返回，即可计算出年金的现值，如图19-83所示。

图19-82　原始数据

图19-83　计算年金的现值

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

年金是在一段连续期间内的一系列固定的现金付款。例如汽车贷款或购房贷款就是年金。在年金函数中，支出的款项，如银行存款，表示为负数；收入的款项，如股息收入，表示为正数。例如，对于储户来说，￥1000银行存款可表示为参数-1000，而对于银行来说该参数为1000。

下面列出的是Excel进行财务运算的公式，如果rate不为0，则：

如果rate为0，则：

(pmt*nper)+pv+fv=0

Excel 计算现金净现值：XNPV函数

XNPV函数用于计算一组现金流的净现值，这些现金流不一定定期发生。如果要计算一组定期现金流的净现值，则需要使用函数NPV。XNPV函数的语法如下：

XNPV(rate,values,dates)

其中，rate参数为应用于现金流的贴现率，即values参数与dates参数中的支付时间相对应的一系列现金流。首期支付是可选的，并与投资开始时的成本或支付有关。如果第1个值是成本或支付，则它必须是负值。所有后续支付都基于365天/年贴现。数值系列必须至少要包含一个正数和一个负数。dates参数为与现金流支付相对应的支付日期表。第1个支付日期代表支付表的开始，其他日期应迟于该日期，但可按任何顺序排列。下面通过实例详细讲解该函数的使用方法与技巧。

打开“XNPV函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-80所示。该工作表中记录了一组数据，要求根据给定的数据计算出现金流的净现值。具体的操作步骤如下。

选中A8单元格，在编辑栏中输入公式“=XNPV(0.09,A2:A6,B2:B6)”，然后按“Enter”键返回，即可计算出现金流的净现值，如图19-81所示。

图19-80　原始数据

图19-81　计算现金净现值

如果任一参数为非数值型，函数XNPV返回错误值“#VALUE!”。如果dates参数中的任一数值不是合法日期，函数XNPV返回错误值“#VALUE”。如果dates参数中的任一数值先于开始日期，函数XNPV返回错误值“#NUM!”。如果values参数和dates参数所含数值的数目不同，函数XNPV返回错误值“#NUM!”。函数XNPV的计算公式如下：

式中：

d_i=第i个或最后一个支付日期。

d₁=第0个支付日期。

P_i=第i个或最后一个支付金额。

Excel 计算投资净现值：NPV函数

NPV函数用于通过使用贴现率以及一系列未来支出（负值）和收入（正值），计算一项投资的净现值。NPV函数的语法如下：

NPV(rate,value1,value2,...)

其中，rate参数为某一期间的贴现率，是一固定值。参数value1、value2……代表支出及收入的1～254个参数。参数value1、value2……在时间上必须具有相等间隔，并且都发生在期末。函数NPV使用value1、value2……的顺序来解释现金流的顺序，所以务必保证支出和收入的数额按正确的顺序输入。如果参数为数值、空白单元格、逻辑值或数字的文本表达式，则都会计算在内；如果参数是错误值或不能转化为数值的文本，则被忽略。如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。下面通过实例详细讲解该函数的使用方法与技巧。

已知某项投资的年贴现率、一年前的初期投资、第1年的收益、第2年的收益、第3年的收益，要求根据给定的数据计算该投资的净现值。打开“NPV函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-78所示。具体的操作步骤如下。

选中A8单元格，在编辑栏中输入公式“=NPV(A2,A3,A4,A5,A6)”，然后按“Enter”键返回，即可计算出该投资的净现值，如图19-79所示。

图19-78　原始数据

图19-79　计算净现值

函数NPV假定投资开始于value1现金流所在日期的前一期，并结束于最后一笔现金流的当期。函数NPV依据未来的现金流来进行计算。如果第1笔现金流发生在第1个周期的期初，则第1笔现金必须添加到函数NPV的结果中，而不应包含在values参数中。如果n是数值参数表中的现金流的次数，则NPV的公式如下：

函数NPV与函数PV（现值）相似。PV与NPV之间的主要差别在于：函数PV允许现金流在期初或期末开始。与可变的NPV的现金流数值不同，PV的每一笔现金流在整个投资中必须是固定的。函数NPV与函数IRR（内部收益率）也有关，函数IRR是使NPV等于零的比率：NPV(IRR(…),…)=0。

Excel 计算债券的贴现率：DISC函数详解

DISC函数用于计算有价证券的贴现率。DISC函数的语法如下：

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

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

打开“DISC函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-76所示。该工作表记录了一组有价证券数据，包括有价证券的结算日、到期日、价格、清偿价值，要求计算在这些条件下有价证券的贴现率。具体的操作步骤如下。

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

图19-76　原始数据

图19-77　计算贴现率

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

Excel 计算到期日支付利息债券现价：PRICEMAT函数

PRICEMAT函数用于计算到期付息的面值￥100的有价证券的价格。PRICEMAT函数的语法如下：

PRICEMAT(settlement,maturity,issue,rate,yld,basis)

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

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

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

图19-74　原始数据

图19-75　计算到期付息债券的价格

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

Excel 计算已贴现债券的现价：PRICEDISC函数

PRICEDISC函数用于计算折价发行的面值￥100的有价证券的价格。PRICEDISC函数的语法如下：

PRICEDISC(settlement,maturity,discount,redemption,basis)

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

打开“PRICEDISC函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-72所示。该工作表记录了一组债券数据，包括债券的结算日、到期日、贴现率、清偿价值，要求计算在这些条件下债券的价格。具体的操作步骤如下。

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

图19-72　原始数据

图19-73　计算债券价格

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

Excel 计算定期支付利息债券现价：PRICE函数

PRICE函数用于计算定期付息的面值￥100的有价证券的价格。PRICE函数的语法如下：

PRICE(settlement,maturity,rate,yld,redemption,frequency,basis)

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

打开“PRICE函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-70所示。该工作表记录了一组债券数据，包括债券的结算日、到期日、息票半年利率、收益率、清偿价值等信息，要求计算在这些条件下债券的价格。具体的操作步骤如下。

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

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

图19-70　原始数据

图19-71　计算债券的价格

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

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

ODDLPRICE(settlement,maturity,last_interest,rate,yld,redemption,frequency,basis)

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

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

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

图19-68　原始数据

图19-69　计算末期付息日有价证券的价格

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

maturity＞settlement＞last_interest