Excel财务函数.docx
- 文档编号:6969148
- 上传时间:2023-05-10
- 格式:DOCX
- 页数:52
- 大小:2.87MB
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!
。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 财务 函数