excel数据处理与-第9章Excel与财务概述讲义

1、认识认识nExcel提供了许多有关财务、投资、偿还、利息及折旧方面的函数，在工作表中运用这些函数可以较松地完成相关的财务运算，或者对其他财务管理软件的运算输出数据进行验证。本章本章学习目标学习目标n1、掌握常用、掌握常用折旧函数折旧函数的用法的用法n2、了解常用、了解常用投资函数投资函数的使用方法的使用方法n3、理解常用、理解常用债券分析函数债券分析函数的使用方法的使用方法n4、了解常用、了解常用计算偿还率函计算偿还率函的用法的用法n5、能够用上述几类函数解决实际问题、能够用上述几类函数解决实际问题9.1 计算计算折旧的函数折旧的函数n1、折旧函数、折旧函数 nExcel折旧函数有折旧函数有D

2、B、DDB、SLN、YDB及及VDB。用这用这5个折旧函数可以确定指定时期内资产的折旧个折旧函数可以确定指定时期内资产的折旧值。值。n这这5个折旧函数有个折旧函数有4个参数是共有的，如表所示。个参数是共有的，如表所示。cost为资产原值salvage为资产在折旧期末的价值（也称资产残值）life为折旧期限（有时也称作资产的生命周期）period必须使用与lIFe相同的单位9.1 计算计算折旧的函数折旧的函数nDB函数函数 n功能功能nDB函数使用固定余额递减法，计算资产在给定期限的折旧值函数使用固定余额递减法，计算资产在给定期限的折旧值 n用法用法nDB(cost, salvage, life

3、, period, month)n其中：其中：life和和period须使用相同的时间单位；须使用相同的时间单位；month为第一为第一年的月份数，如果省略，则默认为年的月份数，如果省略，则默认为12。n例如例如n某学校买了一批计算机，价值为某学校买了一批计算机，价值为500 000元使用期限为元使用期限为3年，年，报废价值为报废价值为100 000元。每年的折旧公式及结果值如下所示：元。每年的折旧公式及结果值如下所示：nDB(500000,100000,3,1,6) =￥103 750.00nDB(500000,100000,3,2,6) =￥164 443.75nDB(500000,100

4、000,3,3,6) =￥96 199.59 9.1 计算计算折旧的函数折旧的函数nDDB函数函数 n用法：用法：DDB(cost, salvage, lIFe, period, factor)n其中，其中，factor为余额递减速率。如为余额递减速率。如factor省略，则默认为省略，则默认为2（双倍余（双倍余额递减法）。额递减法）。5个参数都必须为正数个参数都必须为正数n功能功能nDDB函数使用双倍余额递减法或其他指定方法，计算一笔资产在给函数使用双倍余额递减法或其他指定方法，计算一笔资产在给定期限内的折旧值定期限内的折旧值 n例如：例如：n学校花学校花10万元购买了一台新设备，使用期限为

5、万元购买了一台新设备，使用期限为10年，报废价值为年，报废价值为1万。下面的例子给出几个期限内的折旧值（结果保留两位小数）。万。下面的例子给出几个期限内的折旧值（结果保留两位小数）。nDDB(100000,10000,3650,1) =￥54.79，第一天的折旧值。，第一天的折旧值。factor被被Excel默认设置设为默认设置设为2。nDDB(100000,10000,120,1) =￥1 666.67，第一个月折旧值。，第一个月折旧值。nDDB(100000,10000,10,1) =￥20 000.00，第一年的折旧值。，第一年的折旧值。nDDB(100000,10000,10,3,1.

6、5) =￥10 837.50，第三年的折旧。，第三年的折旧。n这里没有使用双倍余额递减法，这里没有使用双倍余额递减法，factor=1.5。 9.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函数计算某项资产按年限总和折旧法计算的某期限内的函数计算某项资产按年限总和折旧法计算的某期限内的折旧值折旧值 n

7、SLN函数计算一项资产每期的直线折旧值函数计算一项资产每期的直线折旧值 nVDB函数代表可变余额递减法，可使用双倍递减余额法或其函数代表可变余额递减法，可使用双倍递减余额法或其他指定的方法，计算指定期间内或某一时间段内的资产折旧他指定的方法，计算指定期间内或某一时间段内的资产折旧额。额。9.2 投资投资函数函数 1、投资函数的参数、投资函数的参数nExcel投资分析使用的参数大致相同，意义相近投资分析使用的参数大致相同，意义相近 ，如下表所示。，如下表所示。参参 数数说说 明明rate为各期利率，为固定值为各期利率，为固定值per用于计算其本金数额的期次，必须在用于计算其本金数额的期次，必须在

8、 1nper 之间之间nper为总投资（或贷款）期次，即该项投资（或贷款）的付款期总数为总投资（或贷款）期次，即该项投资（或贷款）的付款期总数pv为现值，即从该项投资（或贷款）开始计算时已经入账的款项，或一系为现值，即从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金列未来付款当前值的累积和，也称为本金fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略为未来值，或在最后一次付款后希望得到的现金余额，如果省略 fv，则，则假设其值为零（如一笔贷款的未来值即为零）假设其值为零（如一笔贷款的未来值即为零）type数字数字 0 或或 1，用以指定各期的付

9、款时间是在期初还是期末，用以指定各期的付款时间是在期初还是期末，0：期末；：期末；1：期初期初value1,value2value1,value29代表代表129个偿还金额不相等时的分期偿还额个偿还金额不相等时的分期偿还额9.2 投资投资函数函数2、PMT函数 n用法用法nPMT(rate, nper, pv, fv, type)n说明，说明，PMT返回的支付款项包括本金和利息，但不包括税款。返回的支付款项包括本金和利息，但不包括税款。rate和和nper单位要一致单位要一致 。n功能功能nPMT基于固定利率及等额分期付款方式，计算投资或贷款的基于固定利率及等额分期付款方式，计算投资或贷款的每

10、期付款额每期付款额 。n例如：某人买房了，贷款例如：某人买房了，贷款200 000元，利率为元，利率为7%，分分10个月付清，则他的月支付额为个月付清，则他的月支付额为：nPMT(7%/12, 10, 200000) =￥-20 647.26 9.2 投资投资函数函数3、PV函数函数 n用法用法nPV(rate, nper, PMT, fv, type)n功能功能nPV函数可以计算投资的现值。现值是一系列未来付款当前值函数可以计算投资的现值。现值是一系列未来付款当前值的累积和，如借入方的借入款即为贷出方贷款的现值。的累积和，如借入方的借入款即为贷出方贷款的现值。n例如：例如：n某人想买一笔养老

11、保险，该保险可以在今后某人想买一笔养老保险，该保险可以在今后25年内于每月末年内于每月末回报回报500元。该保险的购买成本为元。该保险的购买成本为75 000，假定投资回报率，假定投资回报率为为6.7%。该保险是否合算？。该保险是否合算？n现在可以通过函数现在可以通过函数PV计算一下这笔投资是否值得。该项投资计算一下这笔投资是否值得。该项投资的年金现值为：的年金现值为：nPV(0.067/12,12*25,500,0)=-72 700.059.2 投资投资函数函数4、FV函数 n用法用法nFV(rate, nper, PMT, pv, type)n功能功能nFV函数计算投资在将来某个日期的价值

12、，它可以计算出投资的一次函数计算投资在将来某个日期的价值，它可以计算出投资的一次性偿还金额，也可以计算出一系列数额相等的分期偿还金额。性偿还金额，也可以计算出一系列数额相等的分期偿还金额。 n例如例如n 某人为保证退休后的生活，打算每年年初存入某人为保证退休后的生活，打算每年年初存入3 000元，元，在整个投资期间，平均投资回报率为在整个投资期间，平均投资回报率为7%。此人今年。此人今年30岁，到他岁，到他60岁时，有多少存款？计算方法如下。岁时，有多少存款？计算方法如下。nFV(7%, 30,-2000,1)=￥202 146.08 9.2 投资投资函数函数5、XNPV函数函数 n用法用法n

13、XNPV(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月月18日返回日返回9 750元。假设资金流转折扣为元。假设资金流转折扣

14、为7%，则净现，则净现值为。值为。nXNPV(0.07,-30000,8750,7250,16250,9750,37317,37667,38017,38367,38717)=￥5 465.45 9.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)nNPV为净现值函数，IPMT为利息偿还额计算函数，PPMT为计算本金偿还额函数，NPER为计算投资总

15、期数的函数9.3 计算计算偿还率的函数偿还率的函数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% 9.3 计算计算

16、偿还率的函数偿还率的函数2、IRR函数函数 n用法用法nIRR(values, guess)n功能功能nIRR函数计算由数值代表的一组现金流的内部收益率。内部收益函数计算由数值代表的一组现金流的内部收益率。内部收益率是指投资偿还的固有率，它是引起投资的净现值等于零的比率率是指投资偿还的固有率，它是引起投资的净现值等于零的比率 n例如例如n某人投资某人投资70 000元开设一家录像带租借店，并预期今后元开设一家录像带租借店，并预期今后5年的净年的净收益为：收益为：12 000元、元、15 000元、元、18 000元、元、21 000元和元和26 000元。试计算该项投资的内部收益率。元。试计算

17、该项投资的内部收益率。n在在Excel工作表的工作表的B1:B6中分别输入下面的数值中分别输入下面的数值-70 000、12 000、15 000、18 000、21 000和和26 000。该投资。该投资4年后的内年后的内部收益率为。部收益率为。nIRR(B1:B5)=-2.12% 9.4 债券债券分析函数分析函数n1、债券分析函数的参数、债券分析函数的参数nExcel提供了许多计算与分析债券的函数，运提供了许多计算与分析债券的函数，运用这些函数可以较方便地进行各种类型的债券用这些函数可以较方便地进行各种类型的债券分析。债券分析函数与数据分析工具有关，它分析。债券分析函数与数据分析工具有关，

18、它们由们由“分析工具库分析工具库”加载宏工具提供，如果在加载宏工具提供，如果在Excel的工作表中引用债券分析函数时发现函的工作表中引用债券分析函数时发现函数不存在，可以通过工具中的数不存在，可以通过工具中的“加载宏加载宏”命令，命令，把把“分析工具库分析工具库”加载到加载到Excel系统中，只有系统中，只有这样才能使用债券分析函数。这样才能使用债券分析函数。n多数债券分析所使用的参数都基本相同，如下多数债券分析所使用的参数都基本相同，如下表所示。表所示。 9.4 债券债券分析函数分析函数last_interest债券的末期付息日债券的末期付息日settlement是证券的成交日，即在发行日之

19、后，证券卖给购买者的日期是证券的成交日，即在发行日之后，证券卖给购买者的日期rate债券发放日的利息率债券发放日的利息率first_interest是证券的起息日是证券的起息日coupon债券的年利息率债券的年利息率par有价证券的票面价值，如果省略有价证券的票面价值，如果省略 par，视，视 par 为为 $1000frequency年付息次数。如果按年支付，年付息次数。如果按年支付，frequency = 1；按半年期付，；按半年期付，frequency = 2；按季支付，；按季支付，frequency = 4discount债券的贴现率债券的贴现率first_coupon债券的首期付息日

20、债券的首期付息日investment债券的最初购买价格债券的最初购买价格maturity债券到期日期债券到期日期yld债券年收益债券年收益redemption债券兑换价格债券兑换价格basis日计数基准日计数基准 0 或省略或省略 US (NASD) 30 / 360 1 实际天数实际天数/实际天数实际天数 2 实际天数实际天数/360 3 实际天数实际天数/365 4 欧洲欧洲 30 9.4 债券债券分析函数分析函数2、ACCRINT/ACCRINTM函数n功能功能nACCRINT函数计算定期付息有价证券的应计利息。函数计算定期付息有价证券的应计利息。ACCRINTM函数用于计算到期一次性付

21、息有价证券的应函数用于计算到期一次性付息有价证券的应计利息。计利息。n格式：格式：nACCRINT(issue, first_interest, settlement, rate, par, frequency, basis)nACCRINTM(issue, maturity, rate, par, basis)9.4 债券债券分析函数分析函数n案例案例【例例】某国库券某国库券2000年年1月月8日发行，日发行，2000年年10月月1日日为起息日，成交日期是为起息日，成交日期是2000年年6月月1日，息票利率日，息票利率9.0%，票面价值，票面价值2 000元，按半年期付息，日计数基元，按半年

22、期付息，日计数基准准30/360，则应计利息为：，则应计利息为：nACCRINT(2000/1/8,2000/10/1,2000/6/1,0.1,2000,2,0)=79.4444n表明表明2000年年6月月1日的利息为日的利息为79.4444元。元。【例例】 一短期债券的交易情况如下：一短期债券的交易情况如下：2001年年4月月1日发日发行，行，2001年年6月月15 日到期，息票利息日到期，息票利息9.0%，票面价，票面价值是值是1 000元，日计数基准元，日计数基准“实际天数实际天数/ 365”。则应。则应计利息：计利息：nACCRINTM(2001/4/1,2001/6/15,0.1,

23、1000,3)=20.547959.4 债券债券分析函数分析函数3、INTRATE/RECEIVED函数函数 n功能功能nINTRATE函数计算一次性付息证券的利率。函数计算一次性付息证券的利率。RECEIVED函数计算一次性付息的有价证券到期收函数计算一次性付息的有价证券到期收回的金额。回的金额。n格式：格式：nINTRATE(settlement, maturity, investment, redemption, basis)nRECEIVED(settlement, maturity, investment, discount, basis)9.4 债券债券分析函数分析函数n案例案例【

24、例例】 某债券于某债券于2001年年2月月15日成交，日成交，2001年年5月月15日到期，日到期，总投资额为总投资额为1000 000万元，清偿价值为万元，清偿价值为1 014 420元，日计元，日计数基准为：实际天数数基准为：实际天数/ 360，则该债券贴现率为：，则该债券贴现率为：nINTRATE(2001/2/15,2001/5/15,1000000,1014420,2)=0.058328【例例】 某债券于某债券于2001年年2月月15日发行，日发行，2001年年5月月15日到期，日到期，总投资额为：总投资额为：1 000 000，贴现率为，贴现率为5.75%，日计数基准：，日计数基准

25、：实际天数实际天数/ 360，则该证券到期日可回收的总金额为：，则该证券到期日可回收的总金额为：nRECEIVED(2001/2/15,2001/5/15,1000000,0.0575,2)= 1 014 420.266。 9.4 债券债券分析函数分析函数4、PRICE/PRICEDISC/PRICEMATn功能功能nPRICE函数计算定期付息的面值函数计算定期付息的面值 100元的有价证券的价格。元的有价证券的价格。PRICEDISC函数计算折价发行的面值函数计算折价发行的面值 100元的有价证券的价元的有价证券的价格。格。PRICEMAT函数计算到期付息的面值函数计算到期付息的面值 100

26、元的有价证券元的有价证券的价格的价格 n格式：格式：nPRICE(settlement, maturity, rate, yld, redemption, frequency, basis)nPRICEDISC(settlement, maturity, discount, redemption, basis)nPRICEMAT(settlement, maturity, issue, rate, yld, basis) 9.4 债券债券分析函数分析函数n案例案例【例例】 某债券于某债券于2000年年11月月11日发行，日发行，2001年年2月月15日成交，日成交，2002年年4月月13日到期

27、，半年息票利日到期，半年息票利率为率为6.1，收益率为，收益率为6.1%，日计数基准为，日计数基准为30/360。则该债券的价格为：则该债券的价格为：nPRICEMAT(2001/2/15,2002/4/13,2000/11/11,0.061,0.061,0)=99.894659.4 债券债券分析函数分析函数n5、DISC函数函数 n功能功能nDISC函数计算有价证券的贴现率。函数计算有价证券的贴现率。n格式：格式：nDISC(settlement, maturity, pr, redemption, basis)n案例案例n 某债券于某债券于2001年年2月月15日成交，日成交，2001年年

28、6月月10日到期，价日到期，价格为格为97.975元，清偿价格为元，清偿价格为100元，日计数基准：实际天数元，日计数基准：实际天数/ 360，则该债券的贴现率为：，则该债券的贴现率为：nDISC(2001/2/15,2001/6/10,97.975,100,2)=0.063391（或（或6.3391%） 9.4 债券债券分析函数分析函数6、YIELD/YIELDDISC/YIELDMATn功能功能nYIELD函数计算定期付息有价证券的收益率，函数计算定期付息有价证券的收益率，YIELD函数用函数用于计算债券收益率。于计算债券收益率。YIELDDISC函数计算折价发行的有价证函数计算折价发行的

29、有价证券的年收益率。券的年收益率。YIELDMAT函数到期付息的有价证券的年收函数到期付息的有价证券的年收益率益率n格式：格式：nYIELD(settlement, maturity, rate, pr, redemption, frequency, basis)nYIELDDISC(settlement, maturity, pr, redemption, basis)nYIELDMAT(settlement, maturity, issue, rate, pr, basis)9.4 债券债券分析函数分析函数7、TBILLEQ/TBILLYIELD/TBILLPRICE n功能功能nCOUP

30、DAYBS，计算当前付息期内截止到成交日的天数。计算当前付息期内截止到成交日的天数。COUPDAYSNC，计算从成交日到下一付息日之间的天数。，计算从成交日到下一付息日之间的天数。COUPDAYS，计算成交日所在的付息期的天数。，计算成交日所在的付息期的天数。 COUPNUM，计算成交日和到期日之间的利息应付次数，向计算成交日和到期日之间的利息应付次数，向上取整到最近的整数。上取整到最近的整数。 COUPPCD，计算成交日之前的上一付息日的日期。计算成交日之前的上一付息日的日期。 n格式格式nCOUPDAYBS(settlement, maturity, frequency, basis)nC

31、OUPDAYSNC(settlement, maturity, frequency, basis)nCOUPDAYS(settlement, maturity, frequency, basis)nCOUPNUM(settlement, maturity, frequency, basis)nCOUPPCD(settlement, maturity, frequency, basis)9.4 债券债券分析函数分析函数8、DURATION函数n功能功能nDURATION函数计算面值函数计算面值100元的定期付息有价证元的定期付息有价证券的修正期限。期限定义为一系列现金流现值的加券的修正期限。期限

32、定义为一系列现金流现值的加权平均值，用于计量债券价格对于收益率变化的敏权平均值，用于计量债券价格对于收益率变化的敏感程度。感程度。n格式：格式：nDURATION(settlement, maturity, coupon, yld, frequency, basis)9.5 Excel财务函数应用财务函数应用n案例案例1：内含报酬率计算：内含报酬率计算n某方案投资及营业现金流量如表某方案投资及营业现金流量如表10-5所示，求内含所示，求内含报酬率。报酬率。设所求内含报酬率为设所求内含报酬率为i，令，令年年012345投资投资-15 000现金流量现金流量3 8003 5603 3203 080

33、7 840015000)1 (7840)1 (3080)1 (3320)1 (3560)1 (380054321iiiii则方程的解为所求。则方程的解为所求。9.5 Excel财务函数应用财务函数应用n案例案例2n固定资产折旧计算固定资产折旧计算 直线法直线法n某项固定资产原值为某项固定资产原值为30 000元，预计净残值为元，预计净残值为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 8009.5 Excel财务函数应用财务函数应用ABCD1余额递减法折旧计算表余额递减法折旧计算表23年份年份期初账面余