《《Excel与财务分析》PPT课件》由会员分享，可在线阅读，更多相关《《Excel与财务分析》PPT课件（33页珍藏版）》请在金锄头文库上搜索。
1、第6章 Excel与财务分析Excel在经济管理中的应用在经济管理中的应用认识认识nExcel提供了许多有关财务、投资、偿还、利息及折旧方面的函数，在工作表中运用这些函数可以较松地完成相关的财务运算，或者对其他财务管理软件的运算输出数据进行验证。本章本章学习目标学习目标n1、掌握常用、掌握常用折旧函数折旧函数的用法的用法n2、了解常用、了解常用投资函数投资函数的使用方法的使用方法n3、理解常用、理解常用债券分析函数债券分析函数的使用方法的使用方法n4、了解常用、了解常用计算偿还率函计算偿还率函的用法的用法n5、能够用上述几类函数解决实际问题、能够用上述几类函数解决实际问题6.1 计算计算折旧的
2、函数折旧的函数n1、折旧函数、折旧函数 nExcel折旧函数有折旧函数有DB、DDB、SLN、YDB及及VDB。用这用这5个折旧函数可以确定指定时期内资产的折旧值。个折旧函数可以确定指定时期内资产的折旧值。n这这5个折旧函数有个折旧函数有4个参数是共有的，如表所示。个参数是共有的，如表所示。cost为资产原值salvage为资产在折旧期末的价值（也称资产残值）lIFe为折旧期限（有时也称作资产的生命周期）period必须使用与lIFe相同的单位6.1 计算计算折旧的函数折旧的函数nDB函数 n用法nDB(cost, salvage, lIFe, period, month)n其中：lIFe和p
3、eriod须使用相同的时间单位；month为第一年的月份数，如果省略，则默认为12。n功能nDB函数使用固定余额递减法，计算资产在给定期限的折旧值 n例如n某学校买了一批计算机，价值为500 000元使用期限为3年，报废价值为100 000元。每年的折旧公式及结果值如下所示：nDB(500000,100000,3,1,6) =￥nDB(500000,100000,3,2,6) =￥nDB(500000,100000,3,3,6) =￥ 6.1 计算计算折旧的函数折旧的函数nDDB函数 n用法：DDB(cost, salvage, lIFe, period, factor)n其中，factor为
4、余额递减速率。如factor省略，则默认为2（双倍余额递减法）。5个参数都必须为正数n功能nDDB函数使用双倍余额递减法或其他指定方法，计算一笔资产在给定期限内的折旧值 n例如：n学校花10万元购买了一台新设备，使用期限为10年，报废价值为1万。下面的例子给出几个期限内的折旧值（结果保留两位小数）。nDDB(100000,10000,3650,1) =￥，第一天的折旧值。factor被Excel默认设置设为2。nDDB(100000,10000,120,1) =￥，第一个月折旧值。nDDB(100000,10000,10,1) =￥，第一年的折旧值。nDDB(100000,10000,10,3
5、,1.5) =￥，第三年的折旧。n这里没有使用双倍余额递减法，。 6.1 计算计算折旧的函数折旧的函数nSYD、SLN、VDB函数 n用法用法nSYD(cost, salvage,life, per)nSLN(cost, salvage, life) nVDB(cost, salvage, lIFe, start_period, end_period, factor, no_switch) n功能功能nSYD函数计算某项资产按年限总和折旧法计算的某期限内的函数计算某项资产按年限总和折旧法计算的某期限内的折旧值折旧值 nSLN函数计算一项资产每期的直线折旧值函数计算一项资产每期的直线折旧值 nV
6、DB函数代表可变余额递减法，可使用双倍递减余额法或其函数代表可变余额递减法，可使用双倍递减余额法或其他指定的方法，计算指定期间内或某一时间段内的资产折旧额。他指定的方法，计算指定期间内或某一时间段内的资产折旧额。6.2 投资投资函数函数 1、投资函数的参数、投资函数的参数nExcel投资分析方面的函数，这些函数使用的参数大致相同，意投资分析方面的函数，这些函数使用的参数大致相同，意义相近义相近 ，如下表所示。，如下表所示。参 数说 明rate为各期利率，为固定值per用于计算其本金数额的期次，必须在 1nper 之间nper为总投资（或贷款）期次，即该项投资（或贷款）的付款期总数pv为现值，即
7、从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略 fv，则假设其值为零（如一笔贷款的未来值即为零）type数字 0 或 1，用以指定各期的付款时间是在期初还是期末，0：期末；1：期初value1,value2value1,value29代表129个偿还金额不相等时的分期偿还额6.2 投资投资函数函数2、PMT函数 n用法用法nPMT(rate, nper, pv, fv, type)n说明，说明，PMT返回的支付款项包括本金和利息，但不返回的支付款项包括本金和利息，但不包括税款。包括税款。rat
8、e和和nper单位要一致单位要一致 。n功能功能nPMT基于固定利率及等额分期付款方式，计算投资基于固定利率及等额分期付款方式，计算投资或贷款的每期付款额或贷款的每期付款额 。n例如：某人买房了，贷款例如：某人买房了，贷款200 000元，利率为元，利率为7%，分，分10个月付清，则他的月支付额为：个月付清，则他的月支付额为：nPMT(7%/12, 10, 200000) =￥ 6.2 投资投资函数函数3、PV函数函数 n用法用法nPV(rate, nper, PMT, fv, type)n功能功能nPV函数可以计算投资的现值。现值是一系列未来付款当前值函数可以计算投资的现值。现值是一系列未来
9、付款当前值的累积和，如借入方的借入款即为贷出方贷款的现值。的累积和，如借入方的借入款即为贷出方贷款的现值。n例如：例如：n某人想买一笔养老保险，该保险可以在今后某人想买一笔养老保险，该保险可以在今后25年内于每月末年内于每月末回报回报500元。该保险的购买成本为元。该保险的购买成本为75 000，假定投资回报率，假定投资回报率为为6.7%。该保险是否合算？。该保险是否合算？n现在可以通过函数现在可以通过函数PV计算一下这笔投资是否值得。该项投资计算一下这笔投资是否值得。该项投资的年金现值为：的年金现值为：6.2 投资投资函数函数4、FV函数 n用法用法nFV(rate, nper, PMT,
10、pv, type)n功能功能nFV函数计算投资在将来某个日期的价值，它可以计算出函数计算投资在将来某个日期的价值，它可以计算出投资的一次性偿还金额，也可以计算出一系列数额相等投资的一次性偿还金额，也可以计算出一系列数额相等的分期偿还金额。的分期偿还金额。 n例如例如n 某人为保证退休后的生活，打算每年年初存入某人为保证退休后的生活，打算每年年初存入3 000元，元，在整个投资期间，平均投资回报率为在整个投资期间，平均投资回报率为7%。此人今年。此人今年30岁，到他岁，到他60岁时，有多少存款？计算方法如下。岁时，有多少存款？计算方法如下。nFV(7%, 30,-2000,1)=￥202 146
11、.08 6.2 投资投资函数函数5、XNPV函数函数 n用法用法nXNPV(rate, values, dates)n功能功能nXNPV函数计算一组现金流的净现值，这些现金流不一定定期发生函数计算一组现金流的净现值，这些现金流不一定定期发生 n例如例如n假定某项投资需要在假定某项投资需要在2002年年3月月2日支付现金日支付现金30 000元，并于下述元，并于下述时间获取以下金额的返回资金：时间获取以下金额的返回资金：2002年年4月月21日返回日返回8 750元；元；2002年年6月月10日返回日返回7 250元；元；2002年年7月月30日返回日返回16 250元；元；2002年年9月月1
12、8日返回日返回9 750元。假设资金流转折扣为元。假设资金流转折扣为7%，则净现，则净现值为。值为。nXNPV(0.07,-30000,8750,7250,16250,9750,37317,37667,38017,38367,38717)=￥5 465.45 6.2 投资投资函数函数6、其它投资函数nNPV(rate, value1, value2, .)nIPMT(rate, per, nper, pv, fv, type)nPPMT(rate, per, nper, pv, fv, type) nNPER(rate, PMT, pv, fv, type)6.3 计算计算偿还率的函数偿还率的
13、函数1、RATE函数函数 n用法用法nRATE(nper, PMT, pv, fv, type, guess)n功能功能nRATE函数用于计算投资的各期利率。可以计算连续分期函数用于计算投资的各期利率。可以计算连续分期等额投资的偿还率，也可以计算一次性偿还的投资利率等额投资的偿还率，也可以计算一次性偿还的投资利率 n例如例如n某人修房贷款某人修房贷款100 000元的元的6年期贷款，月支付额为年期贷款，月支付额为2 200元，该笔贷款的利率为。元，该笔贷款的利率为。n=RATE(12*6, -2200, 100000)=1.38% 6.3 计算计算偿还率的函数偿还率的函数2、IRR函数函数 n
14、用法用法nIRR(values, guess)n功能功能nIRR函数计算由数值代表的一组现金流的内部收益率。内部收函数计算由数值代表的一组现金流的内部收益率。内部收益率是指投资偿还的固有率，它是引起投资的净现值等于零的益率是指投资偿还的固有率，它是引起投资的净现值等于零的比率比率 n例如例如n某人投资某人投资70 000元开设一家录像带租借店，并预期今后元开设一家录像带租借店，并预期今后5年年的净收益为：的净收益为：12 000元、元、15 000元、元、18 000元、元、21 000元元和和26 000元。试计算该项投资的内部收益率。元。试计算该项投资的内部收益率。n在在Excel工作表的
15、工作表的B1:B6中分别输入下面的数值中分别输入下面的数值-70 000、12 000、15 000、18 000、21 000和和26 000。该投资。该投资4年后年后的内部收益率为。的内部收益率为。nIRR(B1:B5)=-2.12% 6.4 债券债券分析函数分析函数n1、债券分析函数的参数、债券分析函数的参数nExcel提供了许多计算与分析债券的函数，运提供了许多计算与分析债券的函数，运用这些函数可以较方便地进行各种类型的债券用这些函数可以较方便地进行各种类型的债券分析。债券分析函数与数据分析工具有关，它分析。债券分析函数与数据分析工具有关，它们由们由“分析工具库分析工具库”加载宏工具提
16、供，如果在加载宏工具提供，如果在Excel的工作表中引用债券分析函数时发现函的工作表中引用债券分析函数时发现函数不存在，可以通过工具中的数不存在，可以通过工具中的“加载宏加载宏”命令，命令，把把“分析工具库分析工具库”加载到加载到Excel系统中，只有系统中，只有这样才能使用债券分析函数。这样才能使用债券分析函数。n多数债券分析所使用的参数都基本相同，如下多数债券分析所使用的参数都基本相同，如下表所示。表所示。 6.4 债券债券分析函数分析函数last_interest债券的末期付息日券的末期付息日settlement是是证券的成交日，即在券的成交日，即在发行日之后，行日之后，证券券卖给购买者
17、的日期者的日期rate债券券发放日的利息率放日的利息率first_interest是是证券的起息日券的起息日coupon债券的年利息率券的年利息率par有价有价证券的票面价券的票面价值，如果省略，如果省略 par，视 par 为 $1000frequency年付息次数。如果按年支付，年付息次数。如果按年支付，frequency = 1；按半年期付，；按半年期付，frequency = 2；按季支付，；按季支付，frequency = 4discount债券的券的贴现率率first_coupon债券的首期付息日券的首期付息日investment债券的最初券的最初购买价格价格maturity债券到
18、期日期券到期日期yld债券年收益券年收益redemption债券券兑换价格价格basis日日计数基准数基准 0 或省略或省略 US (NASD) 30 / 360 1 实际天数天数/实际天数天数 2 实际天数天数/360 3 实际天数天数/365 4 欧洲欧洲 30 6.4 债券债券分析函数分析函数2、ACCRINT/ACCRINTM函数n功能功能nACCRINT函数计算定期付息有价证券的应计利息。函数计算定期付息有价证券的应计利息。ACCRINTM函数用于计算到期一次性付息有价证券函数用于计算到期一次性付息有价证券的应计利息。的应计利息。n格式：格式：nACCRINT(issue, firs
19、t_interest, settlement, rate, par, frequency, basis)nACCRINTM(issue, maturity, rate, par, basis)6.4 债券债券分析函数分析函数n案例案例【例例】某国库券某国库券2000年年1月月8日发行，日发行，2000年年10月月1日日为起息日，成交日期是为起息日，成交日期是2000年年6月月1日，息票利率日，息票利率10.0%，票面价值，票面价值2 000元，按半年期付息，日计数元，按半年期付息，日计数基准基准30/360，则应计利息为：，则应计利息为：n表明表明2000年年6月月1日的利息为元。日的利息为元
20、。【例例】 一短期债券的交易情况如下：一短期债券的交易情况如下：2001年年4月月1日发日发行，行，2001年年6月月15 日到期，息票利息日到期，息票利息10.0%，票面，票面价值是价值是1 000元，日计数基准元，日计数基准“实际天数实际天数/ 365”。则。则应计利息：应计利息：6.4 债券债券分析函数分析函数3、INTRATE/RECEIVED函数函数 n功能功能nINTRATE函数计算一次性付息证券的利率。函数计算一次性付息证券的利率。RECEIVED函数计算一次性付息的有价证券到期收函数计算一次性付息的有价证券到期收回的金额。回的金额。n格式：格式：nINTRATE(settlem
21、ent, maturity, investment, redemption, basis)nRECEIVED(settlement, maturity, investment, discount, basis)6.4 债券债券分析函数分析函数n案例案例【例例7】 某债券于某债券于2001年年2月月15日成交，日成交，2001年年5月月15日到日到期，总投资额为期，总投资额为1000 000万元，清偿价值为万元，清偿价值为1 014 420元，元，日计数基准为：实际天数日计数基准为：实际天数/ 360，则该债券贴现率为：，则该债券贴现率为：【例例7】 某债券于某债券于2001年年2月月15日发行
22、，日发行，2001年年5月月15日到日到期，总投资额为：期，总投资额为：1 000 000，贴现率为，贴现率为5.75%，日计数基，日计数基准：实际天数准：实际天数/ 360，则该证券到期日可回收的总金额为：，则该证券到期日可回收的总金额为：n。 6.4 债券债券分析函数分析函数4、PRICE/PRICEDISC/PRICEMATn功能功能nPRICE函数计算定期付息的面值函数计算定期付息的面值 100元的有价证券的价格。元的有价证券的价格。PRICEDISC函数计算折价发行的面值函数计算折价发行的面值 100元的有价证券的价元的有价证券的价格。格。PRICEMAT函数计算到期付息的面值函数计
23、算到期付息的面值 100元的有价证券元的有价证券的价格的价格 n格式：格式：nPRICE(settlement, maturity, rate, yld, redemption, frequency, basis)nPRICEDISC(settlement, maturity, discount, redemption, basis)nPRICEMAT(settlement, maturity, issue, rate, yld, basis) 6.4 债券债券分析函数分析函数n案例案例【例例】 某债券于某债券于2000年年11月月11日发行，日发行，2001年年2月月15日成交，日成交，20
24、02年年4月月13日到期，半年息票利日到期，半年息票利率为，收益率为率为，收益率为6.1%，日计数基准为，日计数基准为30/360。则。则该债券的价格为：该债券的价格为：6.4 债券债券分析函数分析函数n5、DISC函数函数 n功能功能nDISC函数计算有价证券的贴现率。函数计算有价证券的贴现率。n格式：格式：nDISC(settlement, maturity, pr, redemption, basis)n案例案例n 某债券于某债券于2001年年2月月15日成交，日成交，2001年年6月月10日到期，价日到期，价格为元，清偿价格为格为元，清偿价格为100元，日计数基准：实际天数元，日计数基
25、准：实际天数/ 360，则该债券的贴现率为：则该债券的贴现率为：n（或（或6.3391%） 6.4 债券债券分析函数分析函数6、YIELD/YIELDDISC/YIELDMATn功能功能nYIELD函数计算定期付息有价证券的收益率，函数计算定期付息有价证券的收益率，YIELD函数用函数用于计算债券收益率。于计算债券收益率。YIELDDISC函数计算折价发行的有价证函数计算折价发行的有价证券的年收益率。券的年收益率。YIELDMAT函数到期付息的有价证券的年收函数到期付息的有价证券的年收益率益率n格式：格式：nYIELD(settlement, maturity, rate, pr, redem
26、ption, frequency, basis)nYIELDDISC(settlement, maturity, pr, redemption, basis)nYIELDMAT(settlement, maturity, issue, rate, pr, basis)6.4 债券债券分析函数分析函数7、TBILLEQ/TBILLYIELD/TBILLPRICE n功能功能nCOUPDAYBS函数计算当前付息期内截止到成交日的天数。函数计算当前付息期内截止到成交日的天数。COUPDAYSNC函数计算从成交日到下一付息日之间的天数。函数计算从成交日到下一付息日之间的天数。COUPDAYS函数计算成
27、交日所在的付息期的天数。函数计算成交日所在的付息期的天数。COUPNUM函数计算成交日和到期日之间的利息应付次数，函数计算成交日和到期日之间的利息应付次数，向上取整到最近的整数。向上取整到最近的整数。COUPPCD函数计算成交日之前的上函数计算成交日之前的上一付息日的日期。一付息日的日期。 n格式格式nCOUPDAYBS(settlement, maturity, frequency, basis)nCOUPDAYSNC(settlement, maturity, frequency, basis)nCOUPDAYS(settlement, maturity, frequency, basis
28、)nCOUPNUM(settlement, maturity, frequency, basis)nCOUPPCD(settlement, maturity, frequency, basis)6.4 债券债券分析函数分析函数8、DURATION函数n功能功能nDURATION函数计算面值函数计算面值100元的定期付息有价证元的定期付息有价证券的修正期限。期限定义为一系列现金流现值的加券的修正期限。期限定义为一系列现金流现值的加权平均值，用于计量债券价格对于收益率变化的敏权平均值，用于计量债券价格对于收益率变化的敏感程度。感程度。n格式：格式：nDURATION(settlement, mat
29、urity, coupon, yld, frequency, basis)6.5 Excel财务函数应用财务函数应用n案例案例1：内含报酬率计算：内含报酬率计算n某方案投资及营业现金流量如表某方案投资及营业现金流量如表7-5所示，求内含报所示，求内含报酬率。酬率。设所求内含报酬率为设所求内含报酬率为i，令，令年年012345投投资-15 000现金流量金流量3 8003 5603 3203 0807 840则方程的解为所求。则方程的解为所求。6.5 Excel财务函数应用财务函数应用n案例案例2n固定资产折旧计算固定资产折旧计算 直线法直线法n某项固定资产原值为某项固定资产原值为30 000元
30、，预计净残值为元，预计净残值为1 800元，使用元，使用年限为年限为4年。选定年。选定Excel的一张工作表，输入的一张工作表，输入SLN函数及其他内函数及其他内容，如表所示容，如表所示 ABCD1直线法折旧计算表23年份期初账面余额折旧额期末账面余额4130 000=SLN(30000,1800,4)=B4-C452=D4=SLN(30000,1800,4)=B5-C563=D5=SLN(30000,1800,4)=B6-C674=D6=B7-D71 8006.5 Excel财务函数应用财务函数应用ABCD1余余额递减法折旧减法折旧计算表算表23年份年份期初期初账面余面余额折旧折旧额期末期末
31、账面余面余额414 000=DB(4000,400,8,1)=B4-C452=D4=DB(4000,400,8,2)=B5-C563=D5=DB(4000,400,8,3)=B6-C674=D6=DB(4000,400,8,4)=B7-C785=D7=DB(4000,400,8,5)=B8-C896=D8=DB(4000,400,8,6)=B9-C9107=D9=DB(4000,400,8,7)=B10-C10118=D10=B11-D11400固定资产折旧固定资产折旧余额递减法余额递减法例如，某项固定资产原值为例如，某项固定资产原值为4 000元，预计净残值为元，预计净残值为400元，使用期
32、限为元，使用期限为8年。年。选定选定Excel的一张工作表，输入的一张工作表，输入DB函数（可利用函数（可利用Excel的自动填充功能快捷输的自动填充功能快捷输入）及其他内容，如表所示。入）及其他内容，如表所示。 6.5 Excel财务函数应用财务函数应用n固定资产折旧固定资产折旧双倍余额递减法双倍余额递减法n例如，某项固定资产原值为例如，某项固定资产原值为16 000元，预计净残值为元，预计净残值为500元，使用元，使用期限为期限为5年。选定年。选定Excel的一张工作表，输入的一张工作表，输入DDB函数及其他内容，函数及其他内容，如表所示如表所示 ABCD1双倍余额递减法折旧计算表23年份
33、期初账面余额折旧额期末账面余额4116 000=DDB(16000,500,5,1)=B4-C452=D4=DDB(16000,500,5,2)=B5-C563=D5=DDB(16000,500,5,3)=B6-C674=D6=SLN(D6,500,2)=B7-C785=D7=B8-D85006.5 Excel财务函数应用财务函数应用ABCD1年限总和法折旧计算表23年份期初账面余额折旧额期末账面余额4110 000=SYD(10000,1000,5,1)=B4-C452=D4=SYD(10000,1000,5,2)=B5-C563=D5=SYD(10000,1000,5,3)=B6-C674=D6=SYD(10000,1000,5,4)=B7-C785=D7=B8-D81 000年限总和法年限总和法利用利用SYD函数。例如，某项固定资产原值为函数。例如，某项固定资产原值为10 000元，预计净残值为元，预计净残值为1 000元，使用年限为元，使用年限为5年。选定年。选定Excel的一张工作表，输入的一张工作表，输入SYD函数及其他内容，函数及其他内容，如表所示如表所示 The End
