Excel财务函数.docx

Excel财务函数.docx

《Excel财务函数.docx》由会员分享，可在线阅读，更多相关《Excel财务函数.docx（52页珍藏版）》请在冰点文库上搜索。

Excel财务函数

第10章财务函数

财务运算是Excel的一个重要应用方向，这在Excel函数中也有体现。

Excel中的财务函数包含数量众多的函数。

根据实际应用，财务函数主要包括有价证券函数、投资函数、本金和利息函数、折旧函数和转换函数等。

在本章中，将对每类函数进行详细讲解，并给出具体的例子进行分析。

10.1有价证券函数

在Excel的财务函数中，有价证券函数的功能是计算证券的收益。

这类函数有许多相似之处，例如，函数的主要参数包括利息率、收益率、价格、成交日、发行日和证券价值等。

同时，有价证券函数还有相同的日计数标准，该计数标准主要用来计算证券的收益期限，用参数basis来表示。

为了方便本小节的讲解，首先列出这些共同的参数含义。

以ACCRINT函数为例，该函数的主要功能是计算定期付息有价证券的应计利息。

其对应的函数表达式为：

ACCRINT（issue,first_interest,settlement,rate,par,frequency,basis）

下面详细讲解其参数的含义：

•参数issue表示证券的发行日；

•参数first_interest表示证券的起息日；

•参数settlement表示证券的成交日（在发行日之后，投资者购买证券的日期）；

•参数rate表示证券的年息票利率；

•参数par表示证券的票面价值；

•参数frequency表示年付息次数；

•参数basis表示日计数基准类型。

在Excel中，所有有价证券函数中，其参数的含义和上面的参数类似，在具体讲解函数的时候，就不再重复表述。

10.1.1ACCRINT函数：

计算应付利息

【功能说明】有价证券的计息方式有定期分期计息和一次性计息两种，该函数计算采用定期分期付息方式时的应计利息。

【语法表达式】ACCRINT（issue，first_interest，settlement，rate，par，frequency，basis，

calc_method）

【使用说明】

•该函数中的所有日期都是用DATE函数输入的有效日期。

MicrosoftExcel是以可计算的序列号的形式存储日期的。

1900年1月1日的序列号为1，那么2008年1

月1日的序列号是39448，因为它距1900年1月1日有39448天。

•settlement表示结算日的日期。

使用函数时，其值必须晚于发行日的日期，否则返回错误值#NUM！

。

•frequency表示付息次数。

使用函数时，其值必须是1、2或4中的一个，其他值都会产生错误#NUM!

。

•日计数基准类型的值必须是0、1、2、3或4中的一个。

如果省略该参数，则默认其值为0。

【实际应用】

某投资机构在2009年8月15日购买了价值30000元的债券，债券的发行日是2009

年6月5日，起息日是2009年9月15日，息票利率为7.8%，按季支付期付息。

债券以US（NASD）30/360为日计数基准，基础数据表格如图10.1所示。

在单元格B9中输入函数表达式“=ACCRINT（B1,B2,B3,B4,B5,B6,B7）”，计算债券的到期收益，得到的结果如图10.2所示。

图10.1债券的基础信息图10.2计算债券的到期利息

【应用说明】ACCRINT函数可以计算每年、半年或每季度的应计利息。

10.1.2ACCRINTM函数：

计算应计利息

【功能说明】有价证券的计息方式有定期计息和一次性计息两种。

该函数计算采用一次性付息方式时应计的利息。

【语法表达式】ACCRINTM（issue，settlement，rate，par，basis）

【使用说明】

•结算日的日期必须晚于发行日的日期，否则函数将返回错误值#NUM！

。

•参数basis的值必须是0、1、2、3或4中的一个，如果省略该参数，那么默认其值是0。

【实际应用】

某投资结构购买了票面价值为12000元的债券，债券的发行日期是2009年6月5日，

到期日是2010年3月15日，息票利率是8.47%，债券以“实际天数/365”作为日计数基

准，基础信息表格如图10.3所示。

在单元格B9中输入函数“=ACCRINTM（B1,B2,B3,B4,B5）”，计算债券的应计利息。

得到的结果如图10.4所示。

图10.3基础债券信息图10.4计算应计利息

【应用说明】ACCRINTM可以计算任意有价证券的利息。

COUPDAYBS、COUPDAYS和COUPDAYSNC函数：

计算债券的天数信息

【功能说明】COUPDAYBS函数的功能是计算有价证券当前付息期内截止到成交日的天数。

COUPDAYS函数的功能是计算证券成交日所在的那一付息期的天数。

COUPDAYSNC函数的功能是计算有价证券从购买日到下一付息日之间的天数。

【语法表达式】COUPDAYBS（settlement，maturity，frequency，basis）COUPDAYS（settlement，maturity，frequency，basis）COUPDAYSNC（settlement，maturity，frequency，basis）

【使用说明】

•到期日的日期值要晚于结算日的日期，否则函数将返回错误值#NUM!

。

•参数frequency的值必须是1、2或4中的一个。

使用其他任何值均会产生错误

#NUM!

。

•参数basis的值必须是0、1、2、3或4中的一个。

如果省略该参数，那么默认其值为0。

【实际应用】

某投资结构在2009年6月5日购买了某债券，债券的到期日是2009年10月15日，按半年付息，以US（NASD）30/360为日计数基准。

根据上面的信息，投资结构可以计算债券的各种天数信息，如图10.5所示。

图10.5关于债券的基础信息

天数信息的计算具体步骤如下：

（1）在单元格B6中输入函数表达式“=COUPDAYBS（B1,B2,B3,B4）”，计算债券在付息期内到成交日的天数，如图10.6所示。

（2）在单元格B7中输入函数表达式“=COUPDAYS（B1,B2,B3,B4）”，计算债券在成交日所在的付息期天数，如图10.7所示。

图10.6计算付息期内到成交日的天数图10.7计算成交日所在的付息期天数

（3）在单元格B8中输入函数表达式“=COUPDAYSNC（B1,B2,B3,B4）”，计算债券成交日到下一付息日的天数；得到的结果如图10.8所示。

图10.8计算债券的天数信息

【应用说明】COUPDAYS函数计算的是包括成交日在内的付息期的天数，它是根据参数maturity的日期来计算的，计算区间是到期日的前1天倒推每一个付息期。

10.1.4COUPNCD和COUPPCD函数：

计算下个和上个付息日的日期

【功能说明】COUPNCD函数的功能是计算有价证券购买日之后的下一个付息的日期，返回一个日期形式的数值。

COUPPCD函数的功能是计算证券在购买日之前最近的付息日的日期，和COUPNCD函数是相对应的函数。

【语法表达式】COUPNCD（settlement，maturity，frequency，basis）COUPPCD（settlement，maturity，frequency，basis）

【使用说明】

•到期日的日期值要晚于成交日的日期，否则函数将返回错误值#NUM!

。

•参数frequency的值可以是1、2或4中的任意值。

如果使用其他任何数值均会产生错误#NUM!

。

•参数basis的值可以是0、1、2、3或4中的一个值。

如果省略该参数，那么默认其值为0。

•设置要存放计算结果的单元格的格式，将其内容格式设置为日期，否则返回的是日期的序列号。

【实际应用】

某投资机构在2009年6月15日购买了某债券，债券的到期日为2012年8月5日，按半年付息，以“实际天数/实际天数”作为日计数基准。

根据债券的信息，投资机构需要计算债券的上一个和下一个付息日，如图10.9所示。

图10.9关于债券的基础信息

其计算的具体步骤如下：

（1）计算下一个付息日。

在单元格B6中输入函数“=COUPNCD（B1,B2,B3,B4）”，计算债券的下一个付息日，如图10.10所示。

（1）计算上一个付息日。

在单元格B7中输入函数“=COUPPCD（B1,B2,B3,B4）”，计算债券的上一个付息日，如图10.11所示。

图10.10计算下一个付息日图10.11计算上一个付息日

【应用说明】使用COUPNCD函数计算下一付息日的功能，用户可以方便了解下一付息日的日期。

10.1.5COUPNUM函数：

计算利息应付次数

【功能说明】计算有价证券从购买日至到期日之间的总的付息次数。

如果返回的结果不是整数，那么需要向上舍入为最接近的整数。

【语法表达式】COUPNUM（settlement，maturity，frequency，basis）

【使用说明】

•到期日的日期值要晚于成交日的日期，否则函数将返回错误值#NUM!

。

•参数frequency的值可以是1、2或4中的任意一个值。

如果使用其他值，函数均会返回错误值#NUM!

。

•参数basis的值可以是0、1、2、3或4中的任意一个值。

如果省略该参数，那么

默认其值为0。

【实际应用】

某投资机构在2009年6月15日购买了某债券，债券的到期日为2012年8月5日，

按半年付息，债券以“实际天数/实际天数”作为日计数基准，信息如图10.12所示。

在单元格B6中输入函数表达式“=COUPNUM（B1,B2,B3,B4），计算债券在成交日和到

期日之间的付息次数，如图10.13所示。

图10.12债券的基础信息图10.13计算付息次数

【应用说明】COUPNUM函数根据付息方式，计算出某一证券的总的付息次数。

10.1.6DISC函数：

计算有价证券的贴现率

【功能说明】计算有价证券的贴现率。

【语法表达式】DISC（settlement，maturity，pr，redemption，basis）

【参数说明】

•Pr：

票面价值为￥100的有价证券的价格。

使用函数时，其值必须大于0。

•Redemption：

票面价值为￥100的有价证券的清偿价值，其值必须大于0。

【使用说明】

•该函数中的所有日期都是用DATE函数输入的有效合法日期。

MicrosoftExcel是以可计算的序列号的形式存储日期的，1900年1月1日的序列号为1，那么2008

年1月1日的序列号是39448，因为它距1900年1月1日有39448天。

•到期日的日期值要晚于成交日的日期值，否则函数将返回错误值#NUM!

。

•日计数基准类型basis的值可以是0、1、2、3或4中的任意一个。

如果省略该参数，那么默认其值为0。

【实际应用】

某投资人在2009年6月5日购买了价格为96.45的100元面值债券，债券的到期日期

为2010年11月15日，清偿价值为100元以“实际天数/365”为日计数基准，基础数据表

格如图10.14所示。

在单元格B7中输入函数“=DISC（B1,B2,B3,B4,B5）”，计算债券的贴现率，如图10.15

所示。

图10.14债券的基础信息图10.15计算债券的贴现率

【应用说明】当用户的债券没有到期，而去兑换现金。

因为没有到期，所以用户需要贴付给银行一定的利息才能取出现金，这种利息的比率就是贴现率。

10.1.7DURATION函数：

计算有价证券的久期

【功能说明】计算票面价值为￥100的定期付息的有价证券的修正期限。

【语法表达式】DURATION（settlement，maturity，coupon，yld，frequency，basis）

【参数说明】

•Coupon：

一个数值，代表有价证券的年息票利率。

使用函数时，其值必须大于0。

•Yld：

一个数值，代表有价证券的年收益率，也就是反映收益情况好坏的。

使用函数时，其值必须大于0。

【使用说明】

•该函数中的所有日期都是用DATE函数输入的有效合法日期。

MicrosoftExcel是以可计算的序列号的形式存储日期的，1900年1月1日的序列号为1，那么2008

年1月1日的序列号是39448，因为它距1900年1月1日有39448天。

•到期日的日期值要晚于成交日的日期，否则函数将返回错误值#NUM!

。

•参数frequency的值可以是1、2或4中的任意一个。

使用其他值函数均会返回错误#NUM!

。

•参数basis的值可以是0、1、2、3或4中的任意一个。

如果省略该参数，那么默认其值为0。

【实际应用】

某投资机构在2009年8月5日购买了息票利率为6.85%的债券，债券的到期日期为

2012年11月15日，按半年期支付利息。

债券的收益率为8.45%，以“US（NASD）30/360”

为日计数基准，基础数据表格如图10.16所示。

在单元格B8中输入表达式“=DURATION（B1,B2,B3,B4,B5,B6）”，计算该债券的久期，结果如图10.17所示。

图10.16基础数据信息图10.17计算债券的久期

【应用说明】修正期限是指，证券在收益率变动的情况下的年持续时间。

反映的是证券价格对收益率的敏感程度。

10.1.8INTRATE函数：

计算证券的利率

【功能说明】计算一次性付息的有价证券的利率。

【语法表达式】INTRATE（settlement，maturity，investement，redemption，basis）

【参数说明】

•investement：

表示投资额。

•redemption：

表示有价证券到期时的清偿价值。

【使用说明】

•参数settlement和maturity是用DATE函数输入的日期值，或者是将函数作为其他公式或函数的结果输入。

•Excel将日期存储为可计算的序列数。

Excel默认1900年1月1日的序列号为1。

•证券的结算日是购买者购买者购买证券的日期，而到期日则表示证券有效期截止的日期。

•参数settlement、maturity和basis的值必须是整数，否则将被截尾取整。

•参数settlement和maturity表示的日期必须是合法有效的日期，否则函数将返回错误值#VALUE!

。

•参数investement和redemption的值必须为大于0的数，否则函数将返回错误值

#NUM!

。

•参数basis必须是0、1、2、3和4中一个数值，否则函数将返回错误值#NUM！

。

•证券的结算日不能大于证券的到期日的日期，否则函数将返回错误值#NUM!

。

【实际应用】

某投资机构在2009年7月5日购买了价值为150000元的债券，债券在2010年11月

15日到期，且到期时将会获得159500元，以“实际天数/实际天数”为日计数基准，计算该债券的利率。

在单元格B7中输入函数表达式“=INTRATE（B1,B2,B3,B4,B5）”，得到该

债券的利率，如图10.18所示。

图10.18计算债券的利率

【应用说明】INTRATE函数主要用来计算一次性付息证券的利率。

10.1.9MDURATION函数：

计算有价债券的修正久期

【功能说明】计算面值100的有价证券的Macauley修正期限。

【语法表达式】MDURATION（settlement,maturity,coupon,yld,frequency,basis）

【参数说明】

•Coupon：

一个数值，代表有价证券的年息票利率。

使用函数时，其值必须大于0。

•Yld：

一个数值，代表有价证券的年收益率，也就是反映收益情况好坏的。

使用函数时，其值必须大于0。

【使用说明】

•到期日的日期值要晚于成交日的日期，否则函数将返回错误值#NUM!

。

•参数frequency的值可以是1、2或4中的任意一个。

使用其他值函数均会返回错误#NUM!

。

•参数basis的值可以是0、1、2、3或4中的任意一个。

如果省略该参数，那么默认其值为0。

【实际应用】

某投资机构在2009年8月5日购买了息票利率为6.85%的债券，债券的到期日期为

2012年11月15日，按半年期支付利息。

债券的收益率为8.45%，以“US（NASD）30/360”

为日计数基准，基础数据表格如图10.19所示。

在单元格B8中输入函数表达式“=MDURATION（B1,B2,B3,B4,B5,B6）”，计算该债券的修正久期，如图10.20所示。

图10.19基础数据表格图10.20计算债券的修正久期

【应用说明】在实际应用中，修正久期的计算公式如下

ODDFPRICE和ODDLPRICE函数：

计算有价证券的价格

【功能说明】ODDFPRICE函数的功能是计算首期付息日不固定的面值￥100的有价证券的价格；ODDLPRICE函数的功能是计算末期付息日不固定，且面值是￥100的有价证券（长期或短期）的价格。

【语法表达式】

ODDFPRICE（settlement，maturity，issue，first_coupon，rate，yld，redemption，frequency，basis）

ODDLPRICE（settlement，maturity，last_interest，rate，yld，redemption，frequency，

basis）

【参数说明】

•first_coupon：

表示有价证券的第一个付息日的日期。

•yld：

一个大于0的数值，表示有价证券的年收益率。

•redemption：

表示面值￥100的有价证券的清偿价值。

•Last_interest：

表示有价证券的最后一期的付息日期。

【使用说明】

•参数settlement、maturity和first_coupon是由DATE函数输入的有效日期。

如果日期以文本形式输入，那么函数将返回错误值。

•参数settlement、maturity和basis的值必须为整数，否则将被截尾取整。

•参数rate的值必须大于等于0，否则函数将返回错误值#NUM!

。

•参数basis的值必须是0、1、2、3或4中的一个数值，否则函数将返回错误值

#NUM!

。

•参数frequency必须是1、2和4中的一个数值。

•ODDFPRICE函数中的几个日期参数需满足的条件是：

matrrity>first_coupon>settlement>issue，否则函数将返回错误值。

•ODDLPRICE函数函数中日期参数需满足的条件是：

matrrity>settlement>last_interest，否则函数将返回错误值。

【实际应用】

某投资机构2008年10月15日购买了两种债券，债券的到期日是2012年3月15日，

发行日是2008年9月15日。

第一种债券的首期付息日为2009年3月15日，第二种债券

的末期付息日为2008年8月15日。

债券的票面利率为7.35%，年收益率为4.85%。

期末

债券售价105元，按季付息，以“US（NASD）30/360”为日计数基准。

现在需要分析两

种债券的价格，基础信息表格如图10.21所示。

图10.21基础数据表格

根据上面的基础数据，具体的求解步骤如下：

（1）在单元格B11中输入数表达式“=ODDFPRICE（B1,B2,B3,B4,B5,B6,B7,B8,B9）”，

计算首期付息日不固定的面值100的有价证券价格，结果如图10.22所示。

（2）在单元格B12中输入数表达式“=ODDLPRICE（E1,E2,E3,E4,E5,E6,E7,E8）”，计

算末期付息日不固定的面值10的有价证券价格，结果如图10.23所示。

图10.22求解首期付息不固定的债券价格图10.23求解末期付息不固定的债券价格

【应用说明】ODDFPRICE函数主要用来计算首期付息日不固定的有价证券的价格。

ODDLPRICE函数主要用来计算末期付息日不固定的有价证券的价格。

ODDFYIELD和ODDLYIELD函数：

计算证券的收益率

【功能说明】如果有价证券的第一期的付息日不固定，那么可以用ODDFYIELD函数计算该证券的收益率。

如果某证券的末期付息日不固定，不论证券是长期还是短期的，那么都可以用ODDLYIELD函数计算该证券的收益率。

【语法表达式】

ODDFYIELD（settlement，maturity，issue，first_coupon，rate，pr，redemption，frequency，basis）

ODDLYIELD（settlement，maturity，last_interest，rate，pr，redemption，frequency，

basis）

【参数说明】

•first_coupon：

表示有价证券首期付息日。

也就是，买进证券后第一次付息的日期。

使用函数时，该参数是由DATE函数输入的有效日期。

•pr：

一个大于0的数值，表示有价证券的价格。

•last_interest：

表示证券最后一期的付息日期。

也就是，最后的一个付息日的日期。

•redemption：

表示面值￥100的有价证券的清偿价值。

【使用说明】

•参数settlement、maturity和first_coupon是由DATE函数输入的有效日期。

如果日期以文本形式输入，那么函数将返回错误值。

•参数settlement、maturity和basis的值必须为整数，否则将被截尾取整。

•参数pr和rate的值必须大于等于0，否则函数将返回错误值#NUM!

。

•参数basis的值必须是0、1、2、3或4中的一个数值，否则函数将返回错误值

#NUM!

。