Excel财务分析函数及应用

Excel与数据处理本章教学目的与要求1、掌握折旧计算函数的应用2、掌握投资计算函数的应用3、掌握偿还率计算函数的应用4、掌握债券分析函数的应用本章重点、难点及学时数重点：折旧计算函数的使用方法投资计算函数的使用方法偿还率计算函数的使用方法债券分析函数的使用方法难点：债券分析函数的使用方法学时数：8学时（上机4学时）本章目录6.1折旧计算函数6.2投资计算函数6.3偿还率计算函数6.4债券分析函数小结思考与练习6.1折旧计算函数折旧计算函数简介折旧函数：SLN、DB、DDB、SYD及VDB。功能：可以确定指定时期内资产的折旧值。这5个折旧计算函数有4个参数是共有的，如表所示。cost为资产原值salvage为资产在折旧期末的价值（也称资产残值）life为折旧期限（有时也称作资产的使用寿命）period需要计算折旧值的期间(必须与life相同的单位)6.1折旧计算函数SLN函数功能:SLN函数计算一项资产每期的直线折旧值。注：每期折旧额是相等的。格式：SLN(cost,salvage,life)参数含义：Cost：为资产原值Salvage：为资产在折旧期末的价值（也称资产残值）life：为折旧期限（有时也称作资产的使用寿命）例：假设购买一辆价值300000元的小车，其折旧年限为8年，残值为20000，计算每年的折旧额。SLN(300000,20000,8)=￥35000.00例：[见财务函数分析.xls—SLN]6.1折旧计算函数DB函数功能：DB使用固定余额递减法，计算资产在给定期限的折旧值。格式：DB(cost,salvage,life,period,month)参数含义：Cost：为资产原值Salvage：为资产在折旧期末的价值（资产残值）life：为折旧期限（资产的使用寿命）Period：算折旧值的期间(必须与life相同单位)month：为第一年的月份数，如省略，则默认为126.1折旧计算函数例：某学校买了一批计算机，价值为500000元，使用期限为3年，第一年有月份数为6个月,报废价值为100000元。每年的折旧公式及结果值如下所示：DB(500000,100000,3,1,6)=￥103750.00DB(500000,100000,3,2,6)=￥164443.75DB(500000,100000,3,3,6)=￥96199.59例：[见财务函数分析.xls—DB]6.1折旧计算函数DDB函数功能:DDB函数使用双倍余额递减法或其他指定方法，计算一笔资产在给定期限内的折旧值。格式：DDB(cost,salvage,life,period,factor)参数含义：cost,salvage,life,period与DB同factor：为余额递减速率。factor省略默认为2（双倍余额递法）。注：5个参数都必须为正数6.1折旧计算函数例：学校花10万元购买了一台新设备，使用期限为10年，报废价值为1万，分别计算第一天、第一个月、第一年、第三年各期限内的折旧值。结果保留两位小数）DDB(100000,10000,3650,1)=￥54.79第一天的折旧值。DDB(100000,10000,120,1)=￥1666.67第一个月折旧值。DB(100000,10000,10,1)=￥20000.00第一年的折旧值。DDB(100000,10000,10,3,1.5)=￥10837.50第三年的折旧值。这里没有使用双倍余额递减法，factor=1.5。例：[见财务函数分析.xls—DDB]6.1折旧计算函数SYD函数功能：计算某项资产按年限总和折旧法计算的某期限内的折旧值。格式：SYD(cost,salvage,life,per)参数含义：Cost：为资产原值Salvage：为资产在折旧期末的价值（也称资产残值）life：为折旧期限（有时也称作资产的使用寿命）Period：为需要计算折旧值的期间(必须与life相同单位)6.1折旧计算函数例：假设购买一台价值500000元的交换机，使用期限为10年，报废价值为5000，计算第1年和第8年的折旧值。第1年的折旧值：SYD(500000,5000,10,1)=￥81818.18第8年的折旧值：SYD(500000,5000,10,8)=￥24545.45例：[见财务函数分析.xls—SYD]6.1折旧计算函数VDB函数功能：VDB函数代表可变余额递减法,可使用双倍递减余额法或其他指定的方法计算指定期间内或某一时间段内的资产折旧额。格式：VDB(cost,salvage,life,start_period,end_period,factor,no_switch)参数含义：start_period：为进行折旧计算的起始期次（与life单位相同end_period：为进行折旧计算的截止期次（与life单位相同）Factor：为余额递减折旧因子（省略为2，即双倍余额递减法）no_switch：是逻辑值。表示当折旧值大于余额递减计算值时，是否采用直线折旧法，值为TURE，即使折旧值大于余额递减计算值，也不采用直线折旧法；值为FALSE，当折旧值大于余额递减计算值，将转换到直线折旧法。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第6~18个月的折旧额：VDB(100000,10000,120,6,18,1.5)=￥12992.04例：[见财务函数分析.xls—VDB]目录6.2投资计算函数投资计算函数简介投资计算函数：PMT、IPMT、PPMT、PV、FV、XNPV、NPV及NPER。功能：计算不同形式的投资回报。这8个投资计算函数使用的参数大致相同，意义相近，如下表所示。参数说明rate为各期利率，是一固定值per用于计算其本金数额的期次，必须在1~nper之间6.2投资计算函数nper为总投资（或贷款）期次，即该项投资（或贷款）的付款期总数pv为现值，即从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零（如一笔贷款的未来值即为零）type数字0或1，用以指定各期的付款时间是在期初还是期末，0：期末；1：期初value1,value2value1,…,value29代表1~29个偿还金额不相等时的分期偿还额6.2投资计算函数PMT函数功能：PMT基于固定利率及等额分期付款方式，计算投资贷款的每期付款额。PMT返回的支付款项包括本金和利息，但不包括税款。格式：PMT(rate,nper,pv,fv,type)参数含义：见总表例：某人买房子，贷款200000元，利率为7%，分10个月付清，则他的月支付额为：PMT(7%/12,10,200000)=￥-20647.26例：[见财务函数分析.xls—PMT]6.2投资计算函数IPMT函数功能：计算偿还金额的利息部分。的方式是在指定的期限内，定期定额且以固定利率偿还贷款。格式：IPMT(rate,per,nper,pv,fv,type)参数含义：见总表注：支出的款项如银行存款，为负数，收入的款项如股息收入，为正数例：计算贷款期限为3年，本金￥8000元，年利率10%的银行贷款的第一个月的利息。IPMT（0.1/12,1,36,8000）=￥-66.67若按年支付，则第三年的利息：IPMT（0.1,3,3,8000）=￥-292.45例：[见财务函数分析.xls—IPMT]6.2投资计算函数PPMT函数功能：在固定利率用等额分期付款方式下，计算在指定期限内投资或贷款的本金金额。注:PMT计算每期付款额,且每期付款额相同.PPMT计算每期付款额,且可对指定期限进行计算.格式：PPMT(rate,per,nper,pv,fv,type)参数含义：见总表6.2投资计算函数例：计算贷款金额为120000元,年利率为7%的10年期贷款的第一个月和最后一年的本金支付额各为多少?第一个月的本金支付额为：PPMT(7%/12,1,12*10,120000)=-693.30最后一个月的本金支付额为：PPMT(7%/12,10,10,120000)=-15967.57例：[见财务函数分析.xls—PPMT]6.2投资计算函数FV函数功能：计算投资在将来某个日期的价值(未来值)。它可以计算出投资的一次性偿还金额，也可以计算出一系列数额相等的分期偿还金额。格式：FV(rate,nper,PMT,pv,type)参数含义：与PV同例：某人为保证退休后的生活，打算每年年初存入2000元，在整个投资期间，平均投资回报率为7%。此人今年30岁，到他60岁时，有多少存款？计算方法如下。FV(7%,30,-2000,0,1)=￥202146.08例：[见财务函数分析.xls—FV]6.2投资计算函数PV函数功能：计算投资的现值。现值是一系列未来付款当前值的累积和，如借入方的借入款即为贷出方贷款的现值。格式：PV(rate,nper,PMT,fv,type)参数含义：PMT：每期付款额其余见总表例：某人想买一笔养老保险，该保险可以在今后25年内于每月末回报500元。购买成本为75000，假定投资回报率为6.7%。该保险是否合算？该项投资的年金现值为：PV(0.067/12,12*25,500,0)=-72700.05由此可以看到这笔投资是不值得的，投资多，回报少。例：[见财务函数分析.xls—PV]6.2投资计算函数NPV函数功能：通过使用贴现率以及一系列未来支出（负值）和收入（正值），返回一项投资的净现值。投资净现值指未来各期支出和收入的当前值的总和。格式：NPV(rate,value1,value2,...)参数含义：Rate：为某一期间的贴现率（相当通货膨胀率和竞争投资率），是一固定值。value1,value2,……代表支出及收入，该参数在时间上必须具有相等间隔，且都发生在期末。例：假设某项投资，第一年须投入10000元,而未来3年中各年的收入分别为3000，5000，6000，假定每年的贴现率是11%，则投资的净现值是多少？NPV(11%,-10000,3000,5000,6000)=1034.20例：[见财务函数分析.xls—NPV]6.2投资计算函数NPV与PV相似。主要差别在：1．PV允许现金流在期初或期末开始，NPV发生在期末。2．PV的每一笔现金流在投资中必须是固定的，NPV现金流是可变的说明：NPV函数假设投资开始于value1现金流所在日期的前1期，并结束于最后一笔现金流的当期。如果第一笔现金流发生在第一个周期的期初，则第一笔现金流必须添加到NPV计算结果中，而不应包含在value参数中。6.2投资计算函数XNPV函数功能：计算一组现金流的净现值（当前纯利润），这些现金流不一定定期发生。投资净现值指未来各期支出和收入的当前值的总和。格式：XNPV(rate,values,dates)参数含义：rate：现金流的贴现率values：为与Dates中的支付日期相对应的一系列现金流转。若第一个值为成本或支付，则其必须是一个负数。数值系列必须至少要包含一个正数和一个负数。Dates：与现金流支付相对应的支付日期表。第一个支付日期代表支付表的开始。6.2投资计算函数例：假定某项投资需要在2002年3月2日支付现金30000元，并于下述时间获取以下金额的返回资金：2002年4月21日返回8750元；2002年6月10日