Excel财务分析函数及应用

1、Excel财务分析函数及应用Excel财务分析函数及应用本章教学目的与要求1、掌握折旧计算函数的应用2、掌握投资计算函数的应用3、掌握偿还率计算函数的应用4、掌握债券分析函数的应用本章教学目的与要求1、掌握折旧计算函数的应用本章重点、难点及学时数重点：折旧计算函数的使用方法投资计算函数的使用方法偿还率计算函数的使用方法债券分析函数的使用方法难点：债券分析函数的使用方法学时数： 8学时（上机4学时）本章重点、难点及学时数重点：本章目录6.1 折旧计算函数6.2 投资计算函数6.3 偿还率计算函数6.4 债券分析函数小结思考与练习本章目录6.1 折旧计算函数6.1 折旧计算函数折旧计算函数简介折旧

2、函数：SLN、DB、DDB、SYD及VDB。功能：可以确定指定时期内资产的折旧值。这5个折旧计算函数有4个参数是共有的，如表所示。cost为资产原值salvage为资产在折旧期末的价值（也称资产残值）life为折旧期限（有时也称作资产的使用寿命 ）period需要计算折旧值的期间(必须与life相同的单位)6.1 折旧计算函数折旧计算函数简介cost为资产原值sa6.1 折旧计算函数SLN函数功能: SLN函数计算一项资产每期的直线折旧值。 注：每期折旧额是相等的。格式：SLN(cost, salvage,life)参数含义：Cost：为资产原值 Salvage：为资产在折旧期末的价值（也称资

3、产残值） life： 为折旧期限（有时也称作资产的使用寿命 ）例： 假设购买一辆价值300 000元的小车，其折旧年限为8年，残值为20 000，计算每年的折旧额。 SLN(300000, 20000,8)=￥35000.00例：见财务函数分析.xlsSLN6.1 折旧计算函数SLN函数6.1 折旧计算函数DB函数 功能： DB使用固定余额递减法，计算资产在给定期限 的折旧值。格式：DB(cost, salvage, life, period, month)参数含义：Cost：为资产原值 Salvage：为资产在折旧期末的价值（资产残值） life： 为折旧期限（资产的使用寿命 ） Perio

4、d： 算折旧值的期间(必须与life相同单位) month：为第一年的月份数，如省略，则默认为126.1 折旧计算函数DB函数 6.1 折旧计算函数例： 某学校买了一批计算机，价值为500000元，使用期限为3年，第一年有月份数为6个月,报废价值为100000元。每年的折旧公式及结果值如下所示：DB(500000,100000,3,1,6) =￥103 750.00DB(500000,100000,3,2,6) =￥164 443.75DB(500000,100000,3,3,6) =￥96 199.59 例：见财务函数分析.xlsDB6.1 折旧计算函数例：6.1 折旧计算函数DDB函数 功

5、能:DDB函数使用双倍余额递减法或其他指定方法，计算一笔资产在给定期限内的折旧值。格式：DDB(cost, salvage, life, period, factor)参数含义：cost, salvage, life, period与DB 同 factor：为余额递减速率。factor省略默认为2（双倍余额递法）。 注：5个参数都必须为正数 6.1 折旧计算函数DDB函数 6.1 折旧计算函数例： 学校花10万元购买了一台新设备，使用期限为10年，报废价值为1万，分别计算第一天、第一个月、第一年、第三年各期限内的折旧值。结果保留两位小数） DDB(100000,10000,3650,1) =￥

6、54.79 第一天的折旧值。DDB(100000,10000,120,1) =￥1 666.67 第一个月折旧值。DB(100000,10000,10,1) =￥20 000.00 第一年的折旧值。DDB(100000,10000,10,3,1.5) =￥10 837.50第三年的折旧值。 这里没有使用双倍余额递减法，factor=1.5。例：见财务函数分析.xlsDDB6.1 折旧计算函数例：6.1 折旧计算函数SYD函数 功能：计算某项资产按年限总和折旧法计算的某期限内的折旧值。格式：SYD(cost, salvage,life, per)参数含义：Cost：为资产原值 Salvage：为

7、资产在折旧期末的价值（也称资产残值） life：为折旧期限（有时也称作资产的使用寿命 ） Period： 为需要计算折旧值的期间(必须与life相同单位)6.1 折旧计算函数SYD函数 6.1 折旧计算函数例： 假设购买一台价值500000元的交换机，使用期限为10年，报废价值为5000，计算第1年和第8年的折旧值。 第1年的折旧值： SYD(500000, 5000,10, 1)=￥81818.18 第8年的折旧值： SYD(500000, 5000,10, 8)=￥24545.45例：见财务函数分析.xlsSYD6.1 折旧计算函数例：6.1 折旧计算函数VDB函数功能：VDB函数代表可变

8、余额递减法,可使用双倍递减余额法或其他指定的方法计算指定期间内或某一时间段内的资产折旧额。格式： VDB(cost, salvage, life, start_period, end_period, factor, no_switch) 参数含义： start_period：为进行折旧计算的起始期次（与life 单位相同 end_period：为进行折旧计算的截止期次（与life单位相同）Factor：为余额递减折旧因子（省略为2，即双倍余额递减法）no_switch： 是逻辑值。表示当折旧值大于余额递减计算值 时，是否采用直线折旧法，值为TURE，即使折 旧值大于余额递减计算值，也不采用直线

9、折旧 法；值为FALSE，当折旧值大于余额递减计算 值，将转换到直线折旧法。6.1 折旧计算函数VDB函数功能：VDB函数代表可变余额6.1 折旧计算函数例： 学校新购一批价值100000元的机器，使用期限为10年，报废价值为10000，计算第一天、第一个月、第一年和第六到第十八个月的折旧值。 第1天的折旧额： VDB(100000, 10000,3650, 0,1)=￥1.32 第1个月的折旧额： VDB(100000, 10000,120, 0,1)=￥1666.67 第1个年的折旧额： VDB(100000, 10000,10, 0,1)=￥20000.00 第618个月的折旧额： VD

10、B(100000, 10000,120, 6,18,1.5)=￥12992.04例：见财务函数分析.xlsVDB目录6.1 折旧计算函数例：目录6.2 投资计算函数 投资计算函数简介投资计算函数：PMT、IPMT、PPMT、 PV、FV、XNPV、NPV及NPER。功能：计算不同形式的投资回报。这8个投资计算函数使用的参数大致相同，意义相近 ，如下表所示。参 数说 明rate为各期利率，是一固定值per用于计算其本金数额的期次，必须在 1nper 之间6.2 投资计算函数 投资计算函数简介参 数说 6.2 投资计算函数nper为总投资（或贷款）期次，即该项投资（或贷款）的付款期总数pv为现值，

11、即从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略 fv，则假设其值为零（如一笔贷款的未来值即为零）type数字 0 或 1，用以指定各期的付款时间是在期初还是期末，0：期末；1：期初value1,value2value1,value29代表129个偿还金额不相等时的分期偿还额6.2 投资计算函数nper为总投资（或贷款）期次，即该项6.2 投资计算函数 PMT函数 功能：PMT基于固定利率及等额分期付款方式，计算投资 贷款的每期付款额 。 PMT返回的支付款项包括本金和利息，但不包括税款。格式

12、：PMT(rate, nper, pv, fv, type)参数含义：见总表例： 某人买房子，贷款200 000元，利率为7%，分10个月 付清，则他的月支付额为： PMT(7%/12, 10, 200000) =￥-20 647.26 例：见财务函数分析.xlsPMT6.2 投资计算函数 PMT函数 6.2 投资计算函数 IPMT函数功能：计算偿还金额的利息部分。的方式是在指定的期限内，定期定额且以固定利率偿还贷款。格式：IPMT(rate, per, nper, pv, fv, type)参数含义：见总表注：支出的款项如银行存款，为负数，收入的款项如股息收入，为正数例：计算贷款期限为3年，

13、本金￥8000元，年利率10%的银行贷款的第一个月的利息。IPMT（0.1/12,1,36, 8000）=￥-66.67若按年支付，则第三年的利息：IPMT（0.1,3,3,8000）=￥-292.45例：见财务函数分析.xlsIPMT6.2 投资计算函数 IPMT函数6.2 投资计算函数 PPMT 函数功能：在固定利率用等额分期付款方式下，计算在指定期限内投资或贷款的本金金额。 注: PMT计算每期付款额,且每期付款额相同. PPMT计算每期付款额,且可对指定期限进行计算.格式：PPMT(rate, per, nper, pv, fv, type)参数含义：见总表6.2 投资计算函数 PPM

14、T 函数6.2 投资计算函数例： 计算贷款金额为120000元,年利率为7%的10年期贷款的第一 个月和最后一年的本金支付额各为多少? 第一个月的本金支付额为： PPMT(7%/12,1,12*10,120000)=-693.30 最后一个月的本金支付额为： PPMT(7%/12,10,10,120000)=-15967.57例：见财务函数分析.xlsPPMT6.2 投资计算函数例： 6.2 投资计算函数 FV函数 功能：计算投资在将来某个日期的价值(未来值)。它可以 计算出投资的一次性偿还金额，也可以计算出一系 列数额相等的分期偿还金额。格式：FV(rate, nper, PMT, pv,

15、type)参数含义：与PV同 例： 某人为保证退休后的生活，打算每年年初存入2 000元，在整个投资期间，平均投资回报率为7%。此人今年30岁，到他60岁时，有多少存款？计算方法如下。 FV(7%, 30,-2000,0,1)=￥202 146.08 例：见财务函数分析.xlsFV6.2 投资计算函数 FV函数 6.2 投资计算函数 PV函数 功能：计算投资的现值。现值是一系列未来付款当前值的 累积和，如借入方的借入款即为贷出方贷款的现值。格式：PV(rate, nper, PMT, fv, type)参数含义：PMT： 每期付款额 其余见总表例： 某人想买一笔养老保险，该保险可以在今后25年

16、内于每月末回报500元。购买成本为75 000，假定投资回报率为6.7%。该保险是否合算？该项投资的年金现值为： PV(0.067/12,12*25,500,0)=-72 700.05 由此可以看到这笔投资是不值得的，投资多，回报少。例：见财务函数分析.xlsPV6.2 投资计算函数 PV函数 6.2 投资计算函数 NPV函数功能：通过使用贴现率以及一系列未来支出（负值）和收入（正值），返回一项投资的净现值。投资净现值指未来各期支出和收入的当前值的总和。格式：NPV(rate, value1, value2, .)参数含义：Rate：为某一期间的贴现率（相当通货膨 胀率和竞争投资率） ，是一固

17、定值。 value1,value2,代表支出及收入，该参数在时间上必须具有相等间隔，且都发生在期末。例： 假设某项投资，第一年须投入10000元,而未来3年中各年的收入分别为3000，5000，6000，假定每年的贴现率是11%，则投资的净现值是多少？ NPV(11%,-10000,3000,5000,6000)=1034.20例：见财务函数分析.xlsNPV6.2 投资计算函数 NPV函数6.2 投资计算函数 NPV与PV相似。主要差别在： 1PV允许现金流在期初或期末开始，NPV发生在期末。 2PV的每一笔现金流在投资中必须是固定的，NPV现金流是可变的 说明：NPV函数假设投资开始于va

18、lue1现金流所在日期的前1期，并结束于最后一笔现金流的当期。如果第一笔现金流发生在第一个周期的期初，则第一笔现金流必须添加到NPV计算结果中，而不应包含在value参数中。6.2 投资计算函数 NPV与PV相似。主要差别在：6.2 投资计算函数 XNPV函数 功能：计算一组现金流的净现值（当前纯利润），这些现金流不一定定期发生。投资净现值指未来各期支出和收入的当前值的总和。格式：XNPV(rate, values, dates)参数含义：rate： 现金流的贴现率 values：为与Dates中的支付日期相对应的一系列现金流转。 若第一个值为成本或支付，则其必须是一个负数。 数值系列必须至少

19、要包含一个正数和一个负数。 Dates： 与现金流支付相对应的支付日期表。第一个支付日 期代表支付表的开始。6.2 投资计算函数 XNPV函数 6.2 投资计算函数例： 假定某项投资需要在2002年3月2日支付现金30 000元，并于下述时间获取以下金额的返回资金：2002年4月21日返回8 750元；2002年6月10日返回7 250元；2002年7月30日返回16 250元；2002年9月18日返回9750元。假设资金流转折扣为7%，则净现值为：XNPV(0.07,-30000,8750,7250,16250,9750,37317,37367,37417,37467,37517)=￥109

20、85.67 注：37317是2002年3月2日的时间序列值。时间序列求法：对该单元格中的日期数据设置单元格格式为数值例：见财务函数分析.xlsXNPV6.2 投资计算函数例：6.2 投资计算函数 NPER 函数功能：计算按指定偿还金额分期偿还贷款的总期数。格式：NPER (rate, PMT, pv, fv, type)参数含义：见总表例： 某人从银行贷款150 000元，利率为6%，每月可 偿还1200元，多久才能还清？ NPER(6%/12,-1200,150000)=196.66（月）例：见财务函数分析.xlsNPER 目录6.2 投资计算函数 NPER 函数目录6.3 偿还率计算函数偿

21、还率计算函数简介偿还率计算函数：RATE、IRR及MIRR。功能：计算偿还率。这3个偿还率计算函数使用的参数见下文具体分析。6.3 偿还率计算函数偿还率计算函数简介6.3 偿还率计算函数RATE函数 功能:用于计算投资的各期利率。可以计算连续分期等额投资的偿还率，也可以计算一次性偿还的投资利率 .格式：RATE(nper, pmt, pv, fv, type, guess)参数含义：nper：总投资（或贷款）期 pmt：每期付款额(包括本金和利息) pv： 现值(本金), 从总投资(贷款)开始计算已经入 账的款项，为一系列未来付款当前值的累积和。 fv： 未来值或在最后一次付款后希望得到的现金

22、余 额，省略默认为是0 6.3 偿还率计算函数RATE函数 6.3 偿还率计算函数 type：用于指定各期的付款时间是在期初还是期末，取 0期末1期初 guess：预期利率。若省略，则假设该值为10%, guess和 nper单位应相同。例： 某人修房贷款100 000元的6年期贷款，月支付额为2200元，计算该笔贷款的月利率。 RATE(12*6, -2200, 100000)=1.38% 例：见财务函数分析.xlsRATE6.3 偿还率计算函数 type：用于指定各期的付款6.3 偿还率计算函数IRR函数 功能：计算由数值代表的一组现金流的内部收益率。内部收益率是引起投资净现值等于零的比率

23、，即投资回收的当前值正好等于投资成本。格式：IRR(values, guess)参数含义：value:为数组或单元格的引用，包含用来计算返 回的内部收益率的数字。必须包含至少一个定期 收入（正值）和定期支付（负值） Guess：为对函数IRR计算结果的估计值。省略则 默认为10%6.3 偿还率计算函数IRR函数 6.3 偿还率计算函数例： 某人投资70 000元开设一家录像带租借店，并预期今后5年的净收益为：12 000元、15 000元、18 000元、21 000元和26 000元。试计算该项投资的内部收益率。 设在B1:B6中分别输入下面的数值-70 000、12 000、15 000、

24、18 000、21 000和26 000。该投资4年后的内部收益率为：IRR(B1:B5)=-2.12% 投资5年后的内部收益率为IRR(B1:B6)=8.66%投资2年后的内部收益率为： IRR(B1:B3,-10%)=-44.35%可知前4年的回报率低于投资 ,前5年的回报率已高于投资 。例：见财务函数分析.xlsIRR6.3 偿还率计算函数例：6.3 偿还率计算函数MIRR函数 功能：计算某一连续期间内现金流的修正内部收益率，同时考虑了投资的成本和现金再投资的收益率。格式：IRR(values, finance_rate,reinvest_rate)参数含义：value:为数组或单元格的

25、引用，代表各期支 出（负值）和收入（正值）。必须包含至少一 个正值和负值 finance_rate：为投入资金的融资利率 reinvest_rate:各期收入净额再投资的收益率6.3 偿还率计算函数MIRR函数 6.3 偿还率计算函数例： 某人5年前以年利率10%的借款120 000买了一辆车，5年来他以此车做出租生意，这5年每年的收入分别为39 000，30 000，21 000，37 000和46 000，期间又将所获利润进行重新投资，每年报酬率为12%。试计算他在不同年限的修正收益。 设在B1中输入贷款总数120 000，这5年的利润在单元格B2:B6中分别输入 该投资3年后的修正收益率

26、为：MIRR(B1:B4，10%，12%)=-4.80% 6.3 偿还率计算函数例：6.3 偿还率计算函数投资5年后的内部收益率为： MIRR(B1:B6，10%，12%)=12.61%若以14%的reinvest_ratejisuan 则投资5年后的内部收益率为： MIRR(B1:B6，10%，14%)=13.48% 例：见财务函数分析.xlsMIRR目录6.3 偿还率计算函数投资5年后的内部收益率为： MIRR6.4 债券分析函数债券分析函数Excel提供了许多计算与分析债券的函数，运用这些函数可以较方便地进行各种类型的债券分析。债券分析函数与数据分析工具有关，它们由“分析工具库”加载宏工

27、具提供，如果在Excel的工作表中引用债券分析函数时发现函数不存在，可以通过工具中的“加载宏”命令，把“分析工具库”加载到Excel系统中，只有这样才能使用债券分析函数。多数债券分析函数所使用的参数都基本相同，如下表所示。 last_interest债券的末期付息日settlement是证券的成交日，即在发行日之后，证券卖给购买者的日期rate债券发放日的利息率6.4 债券分析函数债券分析函数last_interest6.4 债券分析函数first_interest是证券的起息日coupon债券的年利息率par有价证券的票面价值，如果省略 par，视 par 为 $1000frequency年

28、付息次数。如果按年支付，frequency = 1；按半年期付，frequency = 2；按季支付，frequency = 4discount债券的贴现率first_coupon债券的首期付息日investment债券的最初购买价格maturity债券到期日期6.4 债券分析函数first_interest是证券的起6.4 债券分析函数yld债券年收益redemption债券兑换价格basis日计数基准 0 或省略US (NASD) 30 / 360 1 实际天数/实际天数 2 实际天数/360 3 实际天数/365 4 欧洲 30 6.4 债券分析函数yld债券年收益redemption债

29、6.4 债券分析函数ACCRINT/ACCRINTM函数功能：ACCRINT函数计算定期付息有价证券的应计利息。 ACCRINTM函数用于计算到期一次性付息有价证券的应计利息。格式：ACCRINT(issue, first_interest, settlement, rate, par, frequency, basis) ACCRINTM(issue, maturity, rate, par, basis)6.4 债券分析函数ACCRINT/ACCRINTM函数6.4 债券分析函数参数含义：issue：有价证券的发行日 first_interest：证券的起息日 settlement：证券的

30、成交日。即在发行日之后证券 卖给购买者的日期 rate：有价证券的年息票利率。 Par：有价证券的票面价值。若省略，视Par为100 元时票面价值为0 Frequency：年付息次数:若按年支付， Frequency=1，按半年期支付，Frequency=2，按 季支付，Frequency=4 Basis：日计数基准类型。（取值如下表） Maturity：债券到期日期6.4 债券分析函数参数含义：issue：有价证券的发行日6.4 债券分析函数数值 含义 0或省略US（NASD）30/360 1实际天数/实际天数 2实际天数/360 3实际天数/365 4欧洲30/360 6.4 债券分析函数

31、数值 含义 0或省略US（NASD）36.4 债券分析函数例： 1、某国库券2000年1月8日发行，2000年10月1日为起息日，成交日期(购买日)是2000年6月1日，息票利率10.0%，票面价值2 000元，按半年期付息，日计数基准30/360，该债券的到期利息是多少？ACCRINT(2000/1/8,2000/10/1,2000/6/1,0.1,2000,2,0)=79.4444表明2000年6月1日的利息为79.4444元。2、一短期债券的交易情况如下：2001年4月1日发行，2001年6月15日到期，息票利息10.0%，票面价值是1 000元，日计数基准“实际天数/ 365”。则应计

32、利息：ACCRINTM(2001/4/1,2001/6/15,0.1,1000,3)=20.547956.4 债券分析函数例：6.4 债券分析函数INTRATE/RECEIVED函数 功能：INTRATE函数计算一次性付息证券的利率。 RECEIVED函数计算一次性付息的有价证券到期收回 的金额。格式：INTRATE(settlement, maturity, investment, redemption, basis) RECEIVED(settlement, maturity, investment, discount, basis) 参数含义：Settlement：证券的成交日 Matu

33、rity：债券到期日期 Investment：债券的最初购买价格 redemption：债券兑换价格 discount：债券的贴现率 Basis：日计数基准类型6.4 债券分析函数INTRATE/RECEIVED函数 6.4 债券分析函数例：1、某债券于2001年2月15日成交，2001年5月15日到期，总投资额为1000 000万元，清偿价值为1 014 420元，日计数基准为：实际天数/ 360，则该债券贴现率为：INTRATE(2001/2/15,2001/5/15,1000000,1014420,2)=0.0583282、某债券于2001年2月15日发行，2001年5月15日到期，总投

34、资额为：1 000 000，贴现率为5.75%，日计数基准：实际天数/ 360，则该证券到期可回收的总金额为：RECEIVED(2001/2/15,2001/5/15,1000000,0.0575,2)= 1 014 420.2666.4 债券分析函数例：6.4 债券分析函数*PRICE/PRICEDISC/PRICEMAT函数功能：PRICE函数计算定期付息的面值100元的有价证券的价格PRICEDISC函数计算折价发行的面值 100元的有价证券的价格PRICEMAT函数计算到期付息的面值 100元的有价证券的价格 格式：PRICE(settlement, maturity, rate, y

35、ld, redemption, frequency, basis)PRICEDISC(settlement, maturity, discount, redemption, basis)PRICEMAT(settlement, maturity, issue, rate, yld, basis) 6.4 债券分析函数*PRICE/PRICEDISC/P6.4 债券分析函数参数含义：Settlement：证券的成交日Maturity：债券到期日期 rate：有价证券的年息票利率Yld：债券年收益Frequency：年付息次数。若按年支付，Frequency=1，按半年期支付，Frequency=

36、2，按季支付，Frequency=4Basis：日计数基准类型redemption：债券兑换价格discount：债券的贴现率 issue：有价证券的发行日6.4 债券分析函数参数含义：6.4 债券分析函数例： 某债券于2000年11月11日发行，2001年2月15日成交，2002年4月13日到期，半年息票利率为6.1，收益率为6.1%，日计数基准为30/360。则该债券的价格为： PRICEMAT(2001/2/15,2002/4/13,2000/11/11,0.061,0.061,0)=99.894656.4 债券分析函数例：6.4 债券分析函数*DISC函数 功能：DISC函数计算有价证

37、券的贴现率。格式：DISC(settlement, maturity, pr, redemption, basis)参数含义： Settlement：证券的成交日 Maturity：债券到期日期 pr: 债券价格 redemption：债券兑换价格 issue：有价证券的发行日例： 某债券于2001年2月15日成交，2001年6月10日到期，价格为97.975元，清偿价格为100元，日计数基准：实际天数/ 360，则该债券的贴现率为：DISC(2001/2/15,2001/6/10,97.975,100,2)=0.063391（或6.3391%） 6.4 债券分析函数*DISC函数 6.4 债

38、券分析函数*YIELD/YIELDDISC/YIELDMAT功能：YIELD函数计算定期付息有价证券的收益率，YIELD函数用于计算债券收益率。YIELDDISC函数计算折价发行的有价证券的年收益率。YIELDMAT函数到期付息的有价证券的年收益率格式：YIELD(settlement, maturity, rate, pr, redemption, frequency, basis)YIELDDISC(settlement, maturity, pr, redemption, basis)YIELDMAT(settlement, maturity, issue, rate, pr, basi

39、s)6.4 债券分析函数*YIELD/YIELDDISC/Y6.4 债券分析函数* COUPDAYBS / COUPDAYSNC / COUPDAYS/COUPNUM/COUPPCD功能：COUPDAYBS函数计算当前付息期内截止到成交日的天数。COUPDAYSNC函数计算从成交日到下一付息日之间的天数。COUPDAYS函数计算成交日所在的付息期的天数。COUPNUM函数计算成交日和到期日之间的利息应付次数，向上取整到最近的整数。COUPPCD函数计算成交日之前的上一付息日的日期。 6.4 债券分析函数* COUPDAYBS / COUP6.4 债券分析函数格式：COUPDAYBS(settl

40、ement, maturity, frequency, basis) COUPDAYSNC(settlement, maturity, frequency, basis) COUPDAYS(settlement, maturity, frequency, basis) COUPNUM(settlement, maturity, frequency, basis) COUPPCD(settlement, maturity, frequency, basis)6.4 债券分析函数格式：COUPDAYBS(settle6.4 债券分析函数*DURATION函数功能 DURATION函数计算面值100

41、元的定期付息有价证券的修正期限。期限定义为一系列现金流现值的加权平均值，用于计量债券价格对于收益率变化的敏感程度。格式： DURATION(settlement, maturity, coupon, yld, frequency, basis)目录6.4 债券分析函数*DURATION函数目录综合实例 .实例 超市贷款还款期数预测 【实例内容】：由于拓展业务需要，欲向银行贷款1,000,000元。根据新风超市目前的经济与经营状况进行判断，再综合其他各种因素考虑，新风超市每月有能力且最适合的还款额为12,000元，若以7.50%的平均年利率作为参考，选择期（月）末付款方式，至少需要多久能还清贷款

42、？ 综合实例 .实例 超市贷款还款期数预测 综合实例【实例操作步骤】： 步骤1：构造数据模型 。如图6-1所示。 步骤2：选定B4单元格，在辑栏中输入“=NPER(B2/12,-B3,B1)”。步骤3：单击“输入”按钮，此时可以这笔贷款方案需要偿还的时间（月）。 图6-1 例6.1数据模型综合实例【实例操作步骤】： 图6-1 例6.1数据模型综合实例.实例2 超市贷款分期偿还金额计算 【实例内容】：君华银行向新风超市提供了四种基于固定利率及等额分期期末付款的贷款方案，还款期数及年利率如表6-2所示，新风超市应该选择哪一种贷款方案比较合适？ 表6-2 君华银行贷款方案表银行名称还款期数（年）贷款

43、利率（年）君华银行 57.20%107.48%157.665207.92%综合实例.实例2 超市贷款分期偿还金额计算 银行名称还款期综合实例【实例操作步骤】： 步骤1：构造数据模型 。如图6-3所示。 步骤2：选定D8单元格，在编辑栏中输入“=PMT(C8/12,B8*12,$B$1)”。 步骤3：单击“输入”按钮，此时可以看到第一种贷款方案的每月还款额。 图6-3例6.2数据模型 综合实例【实例操作步骤】： 图6-3例6.2数据模型 综合实例.实例3 超市贷款分期偿还利息与本金金额计算 【实例内容】：在实例2中新风超市确定贷款方案后，第一年每月偿还的利息和本金分别是多少？ 综合实例.实例3

44、超市贷款分期偿还利息与本金金额计算 综合实例【实例操作步骤】： 步骤1：构造数据模型 。如图6-5所示。 步骤2：选定B14单元格，在编辑栏中输入“=IPMT($C$9/12,A14,$B$9*12,$B$1)”。 步骤3：选定C14单元格，在编辑栏中输入“=PPMT($C$9/12,A14,$B$9*12,$B$1)”。 步骤4：用填充柄填充B和C两列，可以看到结果。图6-5 例6.3数据模型 综合实例【实例操作步骤】： 图6-5 例6.3数据模型 综合实例.实例4 超市存款投资未来值计算 【实例内容】：新风超市计划在5年后创办一所新风希望小学，需要资金200,000元。从本年开始筹备资金，

45、打算在每月月初存入5,000元，在整个投资期间内，平均年投资回报率为7.6%，3年后这笔存款是否足够创办新风希望小学？ 综合实例.实例4 超市存款投资未来值计算 综合实例【实例操作步骤】： 步骤1：构造数据模型 。如图6-7所示。 步骤2：选定B30单元格，在编辑栏中输入“=FV(B27/12,B28*12,-B29,1)”。 步骤3：单击“输入”按钮，此时可以看到这笔存款3年后的价值。 图6-7 例6.4数据模型 综合实例【实例操作步骤】： 图6-7 例6.4数据模型 综合实例.实例5 超市保险投资决策 【实例内容】：新风超市计划为高级员工提高福利，购买一笔医疗保险，购买的成本为28,000

46、元，该保险可以在今后30年中每月末回报150元，投资年回报率为5.6%。新风超市购买这份保险是否划算？ 综合实例.实例5 超市保险投资决策 综合实例【实例操作步骤】： 步骤1：构造数据模型 。如图6-9所示。 步骤2：选定B35单元格，在编辑栏中输入“=PV(B32/12,B33*12,B34)”。 步骤3：单击“输入”按钮，此时可以看到这份保险的现值。 图6-9 例6.5数据模型 综合实例【实例操作步骤】： 图6-9 例6.5数据模型 综合实例.实例6 超市收购投资决策 【实例内容】：新风超市计划收购一小超市，收购成本为500,000元，必须预先付清，经过市场调查，预计今后6年的营业收入可达

47、到110,000元，130,000元，160,000元，180,000元，190,000元和210,000元，每年的贴现率为8.7%。这项收购计划是否可行？ 综合实例.实例6 超市收购投资决策 综合实例【实例操作步骤】： 步骤1：构造数据模型 。如图6-11所示。 步骤2：选定B45单元格，在编辑栏中输入“=NPV(B38,B39:B44)-B37”。 步骤3：单击“输入”按钮，此时可以看到收购小超市投资计划的当前纯利润。 图6-11 例6.6数据模型 综合实例【实例操作步骤】： 图6-11 例6.6数据模型 综合实例.实例7 超市项目投资决策 【实例内容】：新风超市计划在2008年5月3日拿

48、出500,000元资金用于某项投资，预计可于如下时间内分4次获取返回资金：2008年12月3日返回120,000元，2009年2月3日返回160,000元，2009年5月3日返回160,000元，2009年12月3日返回90,000元，资金流转折扣为8.2%，这项投资是否划算？ 综合实例.实例7 超市项目投资决策 综合实例【实例操作步骤】： 步骤1：构造数据模型 。如图6-13所示。 图6-13 例6.7数据模型 综合实例【实例操作步骤】： 图6-13 例6.7数据模型 综合实例步骤2：将A50：A54单元格区域格式设置为“日期”。结果如图6-14所示。 步骤3：选定B55单元格，在编辑栏中输

49、入“=XNPV(B47,B50:B54,A50:A54)”。 步骤4：单击“输入”按钮，此时可以看到这项投资计划的当前纯利润。 图6-14 转换日期序列 综合实例步骤2：将A50：A54单元格区域格式设置为“日期”综合实例.实例8 计算超市货架的折旧金额 【实例内容】：新风超市计划将现有一批已使用3年的货架转手卖出，3年前的购买价值为50,000元，使用年限为8年，预计报废价值为15,000元，使用直线折旧，固定余额递减折旧，双倍余额递减折旧，年限总和折旧和可变余额递减折旧5种不同的折旧方法计算出这批货架这3年的折旧金额。 综合实例.实例8 计算超市货架的折旧金额 综合实例【实例操作步骤】：

50、步骤1：构造数据模型 。如图6-16所示。 图6-16 例6.8数据模型 综合实例【实例操作步骤】： 图6-16 例6.8数据模型 综合实例步骤2：选定B7单元格，在编辑栏中输入“=SLN($B$1,$B$2,$B$3)”。 步骤3：选定C7单元格，在编辑栏中输入“=DB($B$1,$B$2,$B$3,A7)”。 步骤4：选定D7单元格，在编辑栏中输入“=DDB($B$1,$B$2,$B$3,A7)”。 步骤5：选定E7单元格，在编辑栏中输入“=SYD($B$1,$B$2,$B$3,A7)”。 步骤6：选定F7单元格，在编辑栏中输入“=VDB($B$1,$B$2,$B$3,0,A7,1.5)”

51、。 步骤7：用填充柄工具填充B、C、D、E、F列，可以看到结果。 综合实例步骤2：选定B7单元格，在编辑栏中输入“=SLN($综合实例.实例9 银行的贷款偿还率计算 【实例内容】：新风超市于5年前创建，创建初期向银行贷款200,000元，期限5年，每个月偿还4,000元，这笔贷款银行的年收益率是多少？ 综合实例.实例9 银行的贷款偿还率计算 综合实例【实例操作步骤】： 步骤1：构造数据模型 。如图6-18所示。 步骤2：选定B4单元格，在编辑栏中输入“=RATE(B2*12,-B3,B1)”,单击“输入”按钮 。步骤3：选定B5单元格，在编辑栏中输入“=B4*12”，单击“输入”按钮，此时可以

52、看到银行的年收益率。 图6-18例6.9数据模型 综合实例【实例操作步骤】： 图6-18例6.9数据模型 综合实例.实例10 超市投资定期现金流内部收益率计算 【实例内容】：5年来新风超市每年的收入分别为50,000元，70,000元，100,000元，110,000元，150,000元和180,000元，期初投资的200,000元现在内部收益率是多少？ 综合实例.实例10 超市投资定期现金流内部收益率计算 综合实例【实例操作步骤】： 步骤1：构造数据模型 。如图6-21所示。 步骤2：选定B13单元格，在编辑栏中输入“=IRR(B7:B12)”。 步骤3：单击“输入”按钮，此时可以看到新风超

53、市近5年的内部收益率。 图6-21 例6.10数据模型 综合实例【实例操作步骤】： 图6-21 例6.10数据模型 综合实例.实例11 超市投资修正收益率计算 【实例内容】：5年来，新风超市将每年所获得的收益用于重新投资，每年的收益率为10%，那开业5年后的修正收益率是多少？ 综合实例.实例11 超市投资修正收益率计算 综合实例【实例操作步骤】： 步骤1：构造数据模型 。如图6-23所示。 步骤2：选定B16单元格，在编辑栏中输入“=MIRR(B7:B12,B5,B15)”。 步骤3：单击“输入”按钮，此时可以看到新风超市近5年的内部修正收益率。 图6-23 例6.11数据模型 综合实例【实例操作步骤】： 图6-23 例6.11数据模型 综合实例.实例12 有价证券应计利息计算 【实例内容】：新风超市员工小王于2007年5月16日购买了价值10,000元的债券，该债券的发行日期是2007年3月1日，起息日期为2007年7月31日，息票利率为15.0%，按半年期支付，日计数基准30/360，计算该债券的应计利息。 综合实例.实例12 有