excel数据处理与分析-第9章 Excel与财务分析

第9章Excel与财务分析认识Excel提供了许多有关财务、投资、偿还、利息及折旧方面的函数，在工作表中运用这些函数可以较松地完成相关的财务运算，或者对其他财务管理软件的运算输出数据进行验证。本章学习目标1、掌握常用折旧函数的用法2、了解常用投资函数的使用方法3、理解常用债券分析函数的使用方法4、了解常用计算偿还率函的用法5、能够用上述几类函数解决实际问题9.1计算折旧的函数1、折旧函数Excel折旧函数有DB、DDB、SLN、YDB及VDB。用这5个折旧函数可以确定指定时期内资产的折旧值。这5个折旧函数有4个参数是共有的，如表所示。cost为资产原值salvage为资产在折旧期末的价值（也称资产残值）life为折旧期限（有时也称作资产的生命周期）period必须使用与lIFe相同的单位9.1计算折旧的函数DB函数功能DB函数使用固定余额递减法，计算资产在给定期限的折旧值用法DB(cost,salvage,life,period,month)其中：life和period须使用相同的时间单位；month为第一年的月份数，如果省略，则默认为12。例如某学校买了一批计算机，价值为500000元使用期限为3年，报废价值为100000元。每年的折旧公式及结果值如下所示：DB(500000,100000,3,1,6)=￥103750.00DB(500000,100000,3,2,6)=￥164443.75DB(500000,100000,3,3,6)=￥96199.59

9.1计算折旧的函数DDB函数用法：DDB(cost,salvage,lIFe,period,factor)其中，factor为余额递减速率。如factor省略，则默认为2（双倍余额递减法）。5个参数都必须为正数功能DDB函数使用双倍余额递减法或其他指定方法，计算一笔资产在给定期限内的折旧值例如：学校花10万元购买了一台新设备，使用期限为10年，报废价值为1万。下面的例子给出几个期限内的折旧值（结果保留两位小数）。DDB(100000,10000,3650,1)=￥54.79，第一天的折旧值。factor被Excel默认设置设为2。DDB(100000,10000,120,1)=￥1666.67，第一个月折旧值。DDB(100000,10000,10,1)=￥20000.00，第一年的折旧值。DDB(100000,10000,10,3,1.5)=￥10837.50，第三年的折旧。这里没有使用双倍余额递减法，factor=1.5。9.1计算折旧的函数SYD、SLN、VDB函数

用法SYD(cost,salvage,life,per)SLN(cost,salvage,life)VDB(cost,salvage,life,start_period,end_period,factor,no_switch)

功能SYD函数计算某项资产按年限总和折旧法计算的某期限内的折旧值SLN函数计算一项资产每期的直线折旧值VDB函数代表可变余额递减法，可使用双倍递减余额法或其他指定的方法，计算指定期间内或某一时间段内的资产折旧额。9.2投资函数1、投资函数的参数Excel投资分析使用的参数大致相同，意义相近，如下表所示。参数说明rate为各期利率，为固定值per用于计算其本金数额的期次，必须在1~nper之间nper为总投资（或贷款）期次，即该项投资（或贷款）的付款期总数pv为现值，即从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零（如一笔贷款的未来值即为零）type数字0或1，用以指定各期的付款时间是在期初还是期末，0：期末；1：期初value1,value2value1,…,value29代表1~29个偿还金额不相等时的分期偿还额9.2投资函数2、PMT函数

用法PMT(rate,nper,pv,fv,type)说明，PMT返回的支付款项包括本金和利息，但不包括税款。rate和nper单位要一致。功能PMT基于固定利率及等额分期付款方式，计算投资或贷款的每期付款额。例如：某人买房了，贷款200000元，利率为7%，分10个月付清，则他的月支付额为：PMT(7%/12,10,200000)=￥-20647.269.2投资函数3、PV函数用法PV(rate,nper,PMT,fv,type)功能PV函数可以计算投资的现值。现值是一系列未来付款当前值的累积和，如借入方的借入款即为贷出方贷款的现值。例如：某人想买一笔养老保险，该保险可以在今后25年内于每月末回报500元。该保险的购买成本为75000，假定投资回报率为6.7%。该保险是否合算？现在可以通过函数PV计算一下这笔投资是否值得。该项投资的年金现值为：PV(0.067/12,12*25,500,0)=-72700.059.2投资函数4、FV函数

用法FV(rate,nper,PMT,pv,type)功能FV函数计算投资在将来某个日期的价值，它可以计算出投资的一次性偿还金额，也可以计算出一系列数额相等的分期偿还金额。例如某人为保证退休后的生活，打算每年年初存入3000元，在整个投资期间，平均投资回报率为7%。此人今年30岁，到他60岁时，有多少存款？计算方法如下。FV(7%,30,-2000,1)=￥202146.089.2投资函数5、XNPV函数用法XNPV(rate,values,dates)功能XNPV函数计算一组现金流的净现值，这些现金流不一定定期发生例如假定某项投资需要在2002年3月2日支付现金30000元，并于下述时间获取以下金额的返回资金：2002年4月21日返回8750元；2002年6月10日返回7250元；2002年7月30日返回16250元；2002年9月18日返回9750元。假设资金流转折扣为7%，则净现值为。XNPV(0.07,{-30000,8750,7250,16250,9750},{37317,37667,38017,38367,38717})=￥5465.459.2投资函数6、其它投资函数NPV(rate,value1,value2,...)IPMT(rate,per,nper,pv,fv,type)PPMT(rate,per,nper,pv,fv,type)

NPER(rate,PMT,pv,fv,type)NPV为净现值函数，IPMT为利息偿还额计算函数，PPMT为计算本金偿还额函数，NPER为计算投资总期数的函数9.3计算偿还率的函数1、RATE函数用法RATE(nper,PMT,pv,fv,type,guess)功能RATE函数用于计算投资的各期利率。可以计算连续分期等额投资的偿还率，也可以计算一次性偿还的投资利率例如某人修房贷款100000元的6年期贷款，月支付额为2200元，该笔贷款的利率为。=RATE(12*6,-2200,100000)=1.38%

9.3计算偿还率的函数2、IRR函数用法IRR(values,guess)功能IRR函数计算由数值代表的一组现金流的内部收益率。内部收益率是指投资偿还的固有率，它是引起投资的净现值等于零的比率例如某人投资70000元开设一家录像带租借店，并预期今后5年的净收益为：12000元、15000元、18000元、21000元和26000元。试计算该项投资的内部收益率。在Excel工作表的B1:B6中分别输入下面的数值-70000、12000、15000、18000、21000和26000。该投资4年后的内部收益率为。IRR(B1:B5)=-2.12%9.4债券分析函数1、债券分析函数的参数Excel提供了许多计算与分析债券的函数，运用这些函数可以较方便地进行各种类型的债券分析。债券分析函数与数据分析工具有关，它们由“分析工具库”加载宏工具提供，如果在Excel的工作表中引用债券分析函数时发现函数不存在，可以通过工具中的“加载宏”命令，把“分析工具库”加载到Excel系统中，只有这样才能使用债券分析函数。多数债券分析所使用的参数都基本相同，如下表所示。9.4债券分析函数last_interest债券的末期付息日settlement是证券的成交日，即在发行日之后，证券卖给购买者的日期rate债券发放日的利息率first_interest是证券的起息日coupon债券的年利息率par有价证券的票面价值，如果省略par，视par为$1000frequency年付息次数。如果按年支付，frequency=1；按半年期付，frequency=2；按季支付，frequency=4discount债券的贴现率first_coupon债券的首期付息日investment债券的最初购买价格maturity债券到期日期yld债券年收益redemption债券兑换价格basis日计数基准

0或省略

US(NASD)30/3601

实际天数/实际天数

2

实际天数/3603

实际天数/3654

欧洲309.4债券分析函数2、ACCRINT/ACCRINTM函数功能ACCRINT函数计算定期付息有价证券的应计利息。ACCRINTM函数用于计算到期一次性付息有价证券的应计利息。格式：ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis)ACCRINTM(issue,maturity,rate,par,basis)9.4债券分析函数案例【例】某国库券2000年1月8日发行，2000年10月1日为起息日，成交日期是2000年6月1日，息票利率9.0%，票面价值2000元，按半年期付息，日计数基准30/360，则应计利息为：ACCRINT("2000/1/8","2000/10/1","2000/6/1",0.1,2000,2,0)=79.4444表明2000年6月1日的利息为79.4444元。【例】

一短期债券的交易情况如下：2001年4月1日发行，2001年6月15日到期，息票利息9.0%，票面价值是1000元，日计数基准“实际天数/365”。则应计利息：ACCRINTM("2001/4/1","2001/6/15",0.1,1000,3)=20.547959.4债券分析函数3、INTRATE/RECEIVED函数功能INTRATE函数计算一次性付息证券的利率。RECEIVED函数计算一次性付息的有价证券到期收回的金额。格式：INTRATE(settlement,maturity,investment,redemption,basis)RECEIVED(settlement,maturity,investment,discount,basis)9.4债券分析函数案例【例】

某债券于2001年2月15日成交，2001年5月15日到期，总投资额为1000000万元，清偿价值为1014420元，日计数基准为：实际天数/360，则该债券贴现率为：INTRATE("2001/2/15","2001/5/15",1000000,1014420,2)=0.058328【例】

某债券于2001年2月15日发行，2001年5月15日到期，总投资额为：1000000，贴现率为5.75%，日计数基准：实际天数/360，则该证券到期日可回收的总金额为：RECEIVED("2001/2/15","2001/5/15",1000000,0.0575,2)=1014420.266。9.4债券分析函数4、PRICE/PRICEDISC/PRICEMAT功能PRICE函数计算定期付息的面值100元的有价证券的价格。PRICEDISC函数计算折价发行的面值100元的有价证券的价格。PRICEMAT函数计算到期付息的面值100元的有价证券的价格格式：PRICE(settlement,maturity,rate,yld,redemption,frequency,basis)PRICEDISC(settlement,maturity,discount,redemption,basis)PRICEMAT(settlement,maturity,issue,rate,yld,basis)9.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.894659.4债券分析函数5、DISC函数功能DISC函数计算有价证券的贴现率。格式：DISC(settlement,maturity,pr,redemption,basis)案例

某债券于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%）

9.4债券分析函数6、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,basis)9.4债券分析函数7、TBILLEQ/TBILLYIELD/TBILLPRICE功能COUPDAYBS，计算当前付息期内截止到成交日的天数。COUPDAYSNC，计算从成交日到下一付息日之间的天数。COUPDAYS，计算成交日所在的付息期的天数。

COUPNUM，计算成交日和到期日之间的利息应付次数，向上取整到最近的整数。COUPPCD，计算成交日之前的上一付息日的日期。格式COUPDAYBS(settlement,maturity,frequency,basis)COUPDAYSNC(settlement,maturity,frequency,basis)COUPDAYS(settlement,maturity,frequency,basis)COUPNUM(settlement,maturity,frequency,basis)COUPPCD(settlement,maturity,frequency,basis)9.4债券分析函数8、DURATION函数功能DURATION函数计算面值100元的定期付息有价证券的修正期限。期限定义为一系列现金流现值的加权平均值，用于计量债券价格对于收益率变化的敏感程度。格式：DURATION(settlement,maturity,coupon,yld,frequency,basis)9.5Excel财务函数应用案例1：内含报酬率计算某方案投资及营业现金流量如表10-5所示，求内含报酬率。设所求内含报酬率为i，令年012345投资-15000现金流量38003560332030807840则方程的解为所求。9.5Excel财务函数应用案例2固定资产折旧计算——直线法某项固定资产原值为30000元，预计净残值为1800元，使用年限为4年。选定Excel的一张工作表，输入SLN函数及其他内容，如表所示ABCD1直线法折旧计算表23年份期初账面余额折旧额期末账面余额4130000=SLN(30000,1800,4)=B4-C452=D4=SLN(30000,1800,4)=B5-C563=D5=SLN(30000,1800,4)=B6-C674=D6=B7-D718009.5Excel财务函数应用ABCD1余额递减法折旧计算表23年份期初账面余额折旧额期末账面余额414000=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