Excel与数据处理财务分析函数及应用课件

Excel与数据处理第六章Excel与数据处理第六章本章教学目的与要求1、掌握折旧计算函数的应用2、掌握投资计算函数的应用3、掌握偿还率计算函数的应用4、掌握债券分析函数的应用本章教学目的与要求1、掌握折旧计算函数的应用本章重点、难点及学时数重点：折旧计算函数的使用方法投资计算函数的使用方法偿还率计算函数的使用方法债券分析函数的使用方法难点：债券分析函数的使用方法学时数：8学时（上机4学时）本章重点、难点及学时数重点：本章目录6.1折旧计算函数6.2投资计算函数6.3偿还率计算函数6.4债券分析函数小结思考与练习本章目录6.1折旧计算函数6.1折旧计算函数折旧计算函数简介折旧函数：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：为资产在折旧期末的价值（也称资产残值）life：为折旧期限（有时也称作资产的使用寿命）例：假设购买一辆价值300000元的小车，其折旧年限为8年，残值为20000，计算每年的折旧额。SLN(300000,20000,8)=￥35000.00例：[见财务函数分析.xls—SLN]6.1折旧计算函数SLN函数6.1折旧计算函数DB函数

功能：DB使用固定余额递减法，计算资产在给定期限的折旧值。格式：DB(cost,salvage,life,period,month)参数含义：Cost：为资产原值Salvage：为资产在折旧期末的价值（资产残值）life：为折旧期限（资产的使用寿命）Period：算折旧值的期间(必须与life相同单位)month：为第一年的月份数，如省略，则默认为126.1折旧计算函数DB函数6.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折旧计算函数例：6.1折旧计算函数DDB函数

功能: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)=￥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折旧计算函数例：6.1折旧计算函数SYD函数

功能：计算某项资产按年限总和折旧法计算的某期限内的折旧值。格式：SYD(cost,salvage,life,per)参数含义：Cost：为资产原值

Salvage：为资产在折旧期末的价值（也称资产残值）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例：[见财务函数分析.xls—SYD]6.1折旧计算函数例：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折旧计算函数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第6~18个月的折旧额：VDB(100000,10000,120,6,18,1.5)=￥12992.04例：[见财务函数分析.xls—VDB]目录6.1折旧计算函数例：目录6.2投资计算函数投资计算函数简介投资计算函数：PMT、IPMT、PPMT、PV、FV、XNPV、NPV及NPER。功能：计算不同形式的投资回报。这8个投资计算函数使用的参数大致相同，意义相近，如下表所示。参数说明rate为各期利率，是一固定值per用于计算其本金数额的期次，必须在1~nper之间6.2投资计算函数投资计算函数简介参数说6.2投资计算函数nper为总投资（或贷款）期次，即该项投资（或贷款）的付款期总数pv为现值，即从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零（如一笔贷款的未来值即为零）type数字0或1，用以指定各期的付款时间是在期初还是期末，0：期末；1：期初value1,value2value1,…,value29代表1~29个偿还金额不相等时的分期偿还额6.2投资计算函数nper为总投资（或贷款）期次，即该项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投资计算函数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投资计算函数IPMT函数6.2投资计算函数

PPMT函数功能：在固定利率用等额分期付款方式下，计算在指定期限内投资或贷款的本金金额。

注:PMT计算每期付款额,且每期付款额相同.PPMT计算每期付款额,且可对指定期限进行计算.格式：PPMT(rate,per,nper,pv,fv,type)参数含义：见总表6.2投资计算函数PPMT函数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投资计算函数例：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投资计算函数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投资计算函数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函数6.2投资计算函数NPV与PV相似。主要差别在：1．PV允许现金流在期初或期末开始，NPV发生在期末。2．PV的每一笔现金流在投资中必须是固定的，NPV现金流是可变的说明：NPV函数假设投资开始于value1现金流所在日期的前1期，并结束于最后一笔现金流的当期。如果第一笔现金流发生在第一个周期的期初，则第一笔现金流必须添加到NPV计算结果中，而不应包含在value参数中。6.2投资计算函数NPV与PV相似。主要差别在：6.2投资计算函数XNPV函数

功能：计算一组现金流的净现值（当前纯利润），这些现金流不一定定期发生。投资净现值指未来各期支出和收入的当前值的总和。格式：XNPV(rate,values,dates)参数含义：rate：现金流的贴现率values：为与Dates中的支付日期相对应的一系列现金流转。若第一个值为成本或支付，则其必须是一个负数。数值系列必须至少要包含一个正数和一个负数。Dates：与现金流支付相对应的支付日期表。第一个支付日期代表支付表的开始。6.2投资计算函数XNPV函数6.2投资计算函数例：

假定某项投资需要在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,37367,37417,37467,37517})=￥10985.67注：37317是2002年3月2日的时间序列值。时间序列求法：对该单元格中的日期数据设置单元格格式为数值例：[见财务函数分析.xls—XNPV]6.2投资计算函数例：6.2投资计算函数NPER函数功能：计算按指定偿还金额分期偿还贷款的总期数。格式：NPER(rate,PMT,pv,fv,type)参数含义：见总表例：

某人从银行贷款150000元，利率为6%，每月可偿还1200元，多久才能还清？NPER(6%/12,-1200,150000)=196.66（月）例：[见财务函数分析.xls—NPER]

目录6.2投资计算函数NPER函数目录6.3偿还率计算函数偿还率计算函数简介偿还率计算函数：RATE、IRR及MIRR。功能：计算偿还率。这3个偿还率计算函数使用的参数见下文具体分析。6.3偿还率计算函数偿还率计算函数简介6.3偿还率计算函数RATE函数

功能:用于计算投资的各期利率。可以计算连续分期等额投资的偿还率，也可以计算一次性偿还的投资利率.格式：RATE(nper,pmt,pv,fv,type,guess)参数含义：nper：总投资（或贷款）期

pmt：每期付款额(包括本金和利息)

pv：现值(本金),从总投资(贷款)开始计算已经入账的款项，为一系列未来付款当前值的累积和。fv：未来值或在最后一次付款后希望得到的现金余额，省略默认为是0

6.3偿还率计算函数RATE函数6.3偿还率计算函数type：用于指定各期的付款时间是在期初还是期末，取0期末1期初guess：预期利率。若省略，则假设该值为10%,guess和

nper单位应相同。例：某人修房贷款100000元的6年期贷款，月支付额为2200元，计算该笔贷款的月利率。RATE(12*6,-2200,100000)=1.38%例：[见财务函数分析.xls—RATE]6.3偿还率计算函数type：用于指定各期的付款6.3偿还率计算函数IRR函数功能：计算由数值代表的一组现金流的内部收益率。内部收益率是引起投资净现值等于零的比率，即投资回收的当前值正好等于投资成本。格式：IRR(values,guess)参数含义：value:为数组或单元格的引用，包含用来计算返回的内部收益率的数字。必须包含至少一个定期收入（正值）和定期支付（负值）Guess：为对函数IRR计算结果的估计值。省略则默认为10%6.3偿还率计算函数IRR函数6.3偿还率计算函数例：某人投资70000元开设一家录像带租借店，并预期今后5年的净收益为：12000元、15000元、18000元、21000元和26000元。试计算该项投资的内部收益率。设在B1:B6中分别输入下面的数值-70000、12000、15000、18000、21000和26000。该投资4年后的内部收益率为：IRR(B1:B5)=-2.12%投资5年后的内部收益率为IRR(B1:B6)=8.66%投资2年后的内部收益率为：IRR(B1:B3,-10%)=-44.35%可知前4年的回报率低于投资,前5年的回报率已高于投资。例：[见财务函数分析.xls—IRR]6.3偿还率计算函数例：6.3偿还率计算函数MIRR函数

功能：计算某一连续期间内现金流的修正内部收益率，同时考虑了投资的成本和现金再投资的收益率。格式：IRR(values,finance_rate,reinvest_rate)参数含义：value:为数组或单元格的引用，代表各期支出（负值）和收入（正值）。必须包含至少一个正值和负值finance_rate：为投入资金的融资利率

reinvest_rate:各期收入净额再投资的收益率6.3偿还率计算函数MIRR函数6.3偿还率计算函数例：某人5年前以年利率10%的借款120000买了一辆车，5年来他以此车做出租生意，这5年每年的收入分别为39000，30000，21000，37000和46000，期间又将所获利润进行重新投资，每年报酬率为12%。试计算他在不同年限的修正收益。设在B1中输入贷款总数120000，这5年的利润在单元格B2:B6中分别输入该投资3年后的修正收益率为：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%例：[见财务函数分析.xls—MIRR]目录6.3偿还率计算函数投资5年后的内部收益率为：MIRR6.4债券分析函数债券分析函数Excel提供了许多计算与分析债券的函数，运用这些函数可以较方便地进行各种类型的债券分析。债券分析函数与数据分析工具有关，它们由“分析工具库”加载宏工具提供，如果在Excel的工作表中引用债券分析函数时发现函数不存在，可以通过工具中的“加载宏”命令，把“分析工具库”加载到Excel系统中，只有这样才能使用债券分析函数。多数债券分析函数所使用的参数都基本相同，如下表所示。

last_interest债券的末期付息日settlement是证券的成交日，即在发行日之后，证券卖给购买者的日期rate债券发放日的利息率6.4债券分析函数债券分析函数last_interest6.4债券分析函数first_interest是证券的起息日coupon债券的年利息率par有价证券的票面价值，如果省略par，视par为$1000frequency年付息次数。如果按年支付，frequency=1；按半年期付，frequency=2；按季支付，frequency=4discount债券的贴现率first_coupon债券的首期付息日investment债券的最初购买价格maturity债券到期日期6.4债券分析函数first_interest是证券的起6.4债券分析函数yld债券年收益redemption债券兑换价格basis日计数基准

0或省略

US(NASD)30/3601

实际天数/实际天数

2

实际天数/3603

实际天数/3654

欧洲306.4债券分析函数yld债券年收益redemption债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：证券的成交日。即在发行日之后证券卖给购买者的日期rate：有价证券的年息票利率。Par：有价证券的票面价值。若省略，视Par为100元时票面价值为0Frequency：年付息次数:若按年支付，

Frequency=1，按半年期支付，Frequency=2，按季支付，Frequency=4Basis：日计数基准类型。（取值如下表）Maturity：债券到期日期6.4债券分析函数参数含义：issue：有价证券的发行日6.4债券分析函数数值

含义

0或省略US（NASD）30/360

1实际天数/实际天数

2实际天数/360

3实际天数/365

4欧洲30/360

6.4债券分析函数数值含义0或省略US（NASD）36.4债券分析函数例：

1、某国库券2000年1月8日发行，2000年10月1日为起息日，成交日期(购买日)是2000年6月1日，息票利率10.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元。2、一短期债券的交易情况如下：2001年4月1日发行，2001年6月15日到期，息票利息10.0%，票面价值是1000元，日计数基准“实际天数/365”。则应计利息：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：证券的成交日Maturity：债券到期日期Investment：债券的最初购买价格redemption：债券兑换价格discount：债券的贴现率Basis：日计数基准类型6.4债券分析函数INTRATE/RECEIVED函数6.4债券分析函数例：1、某债券于2001年2月15日成交，2001年5月15日到期，总投资额为1000000万元，清偿价值为1014420元，日计数基准为：实际天数/360，则该债券贴现率为：INTRATE("2001/2/15","2001/5/15",1000000,1014420,2)=0.0583282、某债券于2001年2月15日发行，2001年5月15日到期，总投资额为：1000000，贴现率为5.75%，日计数基准：实际天数/360，则该证券到期可回收的总金额为：RECEIVED("2001/2/15","2001/5/15",1000000,0.0575,2)=1014420.2666.4债券分析函数例：6.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)6.4债券分析函数**PRICE/PRICEDISC/P6.4债券分析函数参数含义：Settlement：证券的成交日Maturity：债券到期日期rate：有价证券的年息票利率Yld：债券年收益Frequency：年付息次数。若按年支付，Frequency=1，按半年期支付，Frequency=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函数计算有价证券的贴现率。格式：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债券分析函数**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)6.4债券分析函数**YIELD/YIELDDISC/Y6.4债券分析函数**COUPDAYBS/COUPDAYSNC/COUPDAYS/COUPNUM/COUPPCD功能：COUPDAYBS函数计算当前付息期内截止到成交日的天数。COUPDAYSNC函数计算从成交日到下一付息日之间的天数。COUPDAYS函数计算成交日所在的付息期的天数。COUPNUM函数计算成交日和到期日之间的利息应付次数，向上取整到最近的整数。COUPPCD函数计算成交日之前的上一付息日的日期。6.4债券分析函数**COUPDAYBS/COUP6.4债券分析函数格式：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)6.4债券分析函数格式：COUPDAYBS(settle6.4债券分析函数**DURATION函数功能DURATION函数计算面值100元的定期付息有价证券的修正期限。期限定义为一系列现金流现值的加权平均值，用于计量债券价格对于收益率变化的敏感程度。格式：DURATION(settlement,maturity,coupon,yld,frequency,basis)目录6.4债券分析函数**DURATION函数目录综合实例１.实例１超市贷款还款期数预测【实例内容】：由于拓展业务需要，欲向银行贷款1,000,000元。根据新风超市目前的经济与经营状况进行判断，再综合其他各种因素考虑，新风超市每月有能力且最适合的还款额为12,000元，若以7.50%的平均年利率作为参考，选择期（月）末付款方式，至少需要多久能还清贷款？综合实例１.实例１超市贷款还款期数预测综合实例【实例操作步骤】：步骤1：构造数据模型。如图6-1所示。步骤2：选定B4单元格，在辑栏中输入“=NPER(B2/12,-B3,B1)”。步骤3：单击“输入”按钮，此时可以这笔贷款方案需要偿还的时间（月）。图6-1例6.1数据模型综合实例【实例操作步骤】：图6-1例6.1数据模型综合实例１.实例2超市贷款分期偿还金额计算【实例内容】：君华银行向新风超市提供了四种基于固定利率及等额分期期末付款的贷款方案，还款期数及年利率如表6-2所示，新风超市应该选择哪一种贷款方案比较合适？表6-2君华银行贷款方案表银行名称还款期数（年）贷款利率（年）君华银行

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超市贷款分期偿还利息与本金金额计算综合实例【实例操作步骤】：步骤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元。从本年开始筹备资金，打算在每月月初存入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元，该保险可以在今后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年的营业收入可达到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日拿出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单元格，在编辑栏中输入“=XNPV(B47,B50:B54,A50:A54)”。步骤4：单击“输入”按钮，此时可以看到这项投资计划的当前纯利润。图6-14转换日期序列

综合实例步骤2：将A50：A54单元格区域格式设置为“日期”综合实例１.实例8计算超市货架的折旧金额

【实例内容】：新风超市计划将现有一批已使用3年的货架转手卖出，3年前的购买价值为50,000元，使用年限为8年，预计报废价值为15,000元，使用直线折旧，固定余额递减折旧，双倍余额递减折旧，年限总和折旧和可变余额递减折旧5种不同的折旧方法计算出这批货架这3年的折旧金额。综合实例１.实例8计算超市货架的折旧金额综合实例【实例操作步骤】：步骤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)”。

步骤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”，单击“输入”按钮，此时可以看到银行的年收益率。图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：单击“输入”按钮，此时可以看到新风超市近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有价证券应计利息计算综合实例【实例操作步骤】：步骤1：构造数据模型。如图6-25所示。步骤2：选定B8单元格，在编辑栏中输入“=ACCRINT(B1,B2,B3,B4,B5,B6,B7)”。

步骤3：单击“输入”按钮，此时可以看到小王所购买的债券的应计利息。

图6-25例6.12数据模型

综合实例【实例操作步骤】：图6-25例6.12数据模型综合实例１.实例13有价证券价格计算【实例内容】：新风超市员工小林于2003年5月14日购买了面值为100元的债券，该债券的到期日期是2008年5月31日，债券息票利率为13.2%，按半年期支付，收益率为15.3%，日计数基准为实际天数/365，计算该债券的发行价格。

综合实例１.实例13有价证券价格计算综合实例【实例操作步骤】：步骤1：构造数据模型。如图6-27所示。步骤2：选定B17单元格，在编辑栏中输入“=PRICE(B10,B11,B13,B14,B12,B15,B16)”。

步骤3：单击“输入”按钮，此时可以看到小林所购买的债券的发行价格。

图6-27例6.13数据模型

综合实例【实例操作步骤】：图6-27例6.13数据模型综合实例１.实例14一次性付息证券利率计算【实例内容】：新风超市员工小李于2002年3月19日购买了价值10,000元的债券，该债券的到期日期是2007年9月20日，到期时的返还金额为17,278元，日计数基准30/360，计算该债券的利率。综合实例１.实例14一次性付息证券利率计算综合实例【实例操作步骤】：步骤1：构造数据模型。如图6-29所示。步骤2：选定B17单元格，在编辑栏中输入“=INTRATE(B19,B20,B21,B22,B23)”。步骤3：单击“输入”按钮，此时可以看到小李所购买的债券的利率。

图6-29例6.14数据模型

综合实例【实例操作步骤】：图6-29例6.14数据模型综合实例１.实例15有价证券贴现率计算【实例内容】：新风超市员工小曹于2005年11月19日购买债券，该债券的到期日期是2009年9月15日，价格为70.32元，清偿价格为100元，日计数基准实际天数/360，计算该债券的贴现率。

综合实例１.实例15有价证券贴现率计算综合实例【实例操作步骤】：步骤1：构造数据模型。如图6-31所示。步骤2：选定B31单元格在编辑栏中输入“DISC(B26,B27,B28,B29,B30)”。

步骤3：单击“输入”按钮，此时可以看到小曹所购买的债券的贴现率。图6-31例6.15数据模型综合实例【实例操作步骤】：图6-31例6.15数据模型综合实例１.实例16有价证券收益率计算【实例内容】：新风超市员工小郑于2001年11月21日购买了价格为76元的票面价值100元的债券，该债券的到期日期是2007年9月15日，息票利率为7.98%，按半年期支付，日计数基准实际天数/365，计算该债券的收益率。综合实例１.实例16有价证券收益率计算综合实例【实例操作步骤】：步骤1：构造数据模型。如图6-33所示。步骤2：选定B40单元格，在编辑栏中输入“YIELD(B33,B34,B35,B36,B37,B38,B39)”。

步骤3：单击“输入”按钮，此时可以看到小曹所购买的债券的收益率。图6-33例6.16数据模型

综合实例【实例操作步骤】：图6-33例6.16数据模型本章小结

Excel具有很强的财务和证券投资分析能力，它不但可以对日常的财务数据、投资预测数据进行计算，还可以方便地做出分析图形。Excel提供了功能强大、覆盖面广的财务函数，可以进行财务预算、资产折旧等方面的计算。此外，Excel还有众多的金融分析及投资分析方面的函数，可以方便地进行投资利息、投资偿还率、内部收益等的计算。目录本章小结Excel具有很强的财务和证券投资分本章思考与练习1、SLN、DB、DDB、SYD、VDB分别用什么方法计算折旧值？2、PMT函数与IPMT、PPMT函数之间有什么关联吗？3、IRR和MIRR函数有什么区别？4、ACCRINT函数和ACCRINTM函数有什么区别？目录本章思考与练习1、SLN、DB、DDB、SYD、VDB分别用Excel与数据处理第六章Excel与数据处理第六章本章教学目的与要求1、掌握折旧计算函数的应用2、掌握投资计算函数的应用3、掌握偿还率计算函数的应用4、掌握债券分析函数的应用本章教学目的与要求1、掌握折旧计算函数的应用本章重点、难点及学时数重点：折旧计算函数的使用方法投资计算函数的使用方法偿还率计算函数的使用方法债券分析函数的使用方法难点：债券分析函数的使用方法学时数：8学时（上机4学时）本章重点、难点及学时数重点：本章目录6.1折旧计算函数6.2投资计算函数6.3偿还率计算函数6.4债券分析函数小结思考与练习本章目录6.1折旧计算函数6.1折旧计算函数折旧计算函数简介折旧函数：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：为资产在折旧期末的价值（也称资产残值）life：为折旧期限（有时也称作资产的使用寿命）例：假设购买一辆价值300000元的小车，其折旧年限为8年，残值为20000，计算每年的折旧额。SLN(300000,20000,8)=￥35000.00例：[见财务函数分析.xls—SLN]6.1折旧计算函数SLN函数6.1折旧计算函数DB函数

功能：DB使用固定余额递减法，计算资产在给定期限的折旧值。格式：DB(cost,salvage,life,period,month)参数含义：Cost：为资产原值Salvage：为资产在折旧期末的价值（资产残值）life：为折旧期限（资产的使用寿命）Period：算折旧值的期间(必须与life相同单位)month：为第一年的月份数，如省略，则默认为126.1折旧计算函数DB函数6.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折旧计算函数例：6.1折旧计算函数DDB函数

功能: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)=￥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折旧计算函数例：6.1折旧计算函数SYD函数

功能：计算某项资产按年限总和折旧法计算的某期限内的折旧值。格式：SYD(cost,salvage,life,per)参数含义：Cost：为资产原值

Salvage：为资产在折旧期末的价值（也称资产残值）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例：[见财务函数分析.xls—SYD]6.1折旧计算函数例：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折旧计算函数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第6~18个月的折旧额：VDB(100000,10000,120,6,18,1.5)=￥12992.04例：[见财务函数分析.xls—VDB]目录6.1折旧计算函数例：目录6.2投资计算函数投资计算函数简介投资计算函数：PMT、IPMT、PPMT、PV、FV、XNPV、NPV及NPER。功能：计算不同形式的投资回报。这8个投资计算函数使用的参数大致相同，意义相近，如下表所示。参数说明rate为各期利率，是一固定值per用于计算其本金数额的期次，必须在1~nper之间6.2投资计算函数投资计算函数简介参数说6.2投资计算函数nper为总投资（或贷款）期次，即该项投资（或贷款）的付款期总数pv为现值，即从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零（如一笔贷款的未来值即为零）type数字0或1，用以指定各期的付款时间是在期初还是期末，0：期末；1：期初value1,value2value1,…,value29代表1~29个偿还金额不相等时的分期偿还额6.2投资计算函数nper为总投资（或贷款）期次，即该项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投资计算函数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投资计算函数IPMT函数6.2投资计算函数

PPMT函数功能：在固定利率用等额分期付款方式下，计算在指定期限内投资或贷款的本金金额。

注:PMT计算每期付款额,且每期付款额相同.PPMT计算每期付款额,且可对指定期限进行计算.格式：PPMT(rate,per,nper,pv,fv,type)参数含义：见总表6.2投资计算函数PPMT函数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投资计算函数例：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投资计算函数FV函数6.2投资计算函数PV函数

功能：计算投资的现值。现值是一系列未来付款当前值的累积和，如借入方的借入款即为贷出方贷款的现值。格式：PV(rate,nper,PMT,fv,type)参数含义：PMT：每期付款额其余见总表例：某人想买一笔养老保险，该保险可以在