Excel 2016数据处理与分析应用教程 课件 第9章 财务分析函数及应用

第9章财务分析函数及应用

主要内容9.1.1投资函数9.1.2利率函数9.1.3利息与本金函数9.1.4折旧函数9.1.1投资函数功能是计算不同形式的投资回报。Excel中常用的投资函数有：1.PV函数2.FV函数3.NPV函数4.XNPV函数9.1.1投资函数1.PV函数语法格式：PV(rate,nper,pmt[,fv,type])函数功能：基于固定利率计算投资的现值，或者说总额。参数说明：rate投资利率。nper投资总期数（期间数）。pmt各期支付金额。fv未来值或在最后一次付款后希望得到的现金余额，省略时默认其值为0。type付款时间类型，省略时默认其值为0（期末）。9.1.1投资函数

【例9-1】假设要购买一份保险理财产品，一次性投资30万，投资回报率7%（年回报率），购买该理财产品后，可以在今后20年内于每月底返还1500元。该投资合算吗？

分析：表面看累计返还1500*12*20=36万，投资可行，但考虑到资金的时间价值，需要将每月等额收款1500元，按照每月回报率（7%/12），折现期20*12月进行折现，看其现值是否大于初始投资金额，如果大于，该投资合算，否则不合算。9.1.1投资函数【例9-1】假设要购买一份保险理财产品，一次性投资30万，投资回报率7%（年回报率），购买该理财产品后，可以在今后20年内于每月底返还1500元。该投资合算吗？PV(rate,nper,pmt[,fv,type])根据计算结果可知，该投资不合算9.1.1投资函数2.FV函数语法格式：FV(rate,nper,pmt[,fv,type])函数功能：基于固定利率及等额分期付款方式，计算某项投资在将来某个日期的价值（未来值）。参数说明：rate投资利率。nper投资总期数（期间数）。pmt各期支付金额。fv未来值或在最后一次付款后希望得到的现金余额，省略时默认其值为0。type付款时间类型，省略时默认其值为0（期末）。9.1.1投资函数【例9-2】假设一个家庭5年后需要一笔比较大的孩子教育费用支出，计划从现在起每月初存入2000元，如果按年利率6％，按月计息，那么5年以后该账户的存款额应该是多少呢？分析：按照每月利率6%/12，存期共5*12月，每月初等额存入2000元，现值为0进行计算。FV(rate,nper,pmt[,fv,type])9.1.1投资函数3.NPV函数语法格式：NPV(rate,value1[,value2,…]）函数功能：基于一系列现金流和固定的各期贴现率，计算一项投资的净现值（当前纯利润）。净现值越大，投资效益越好。参数说明：rate

某一期间的贴现率。value1，value2，…编号可以从1到254，代表支出或收入的现金流。要求所属各期间的长度必须相等，而且都发生在期末，一定要保证支出和收入的数额按正确的顺序输入。9.1.1投资函数【例9-3】假设某用户想开一家食品加工厂，打算初期投资20万元，而希望未来4年中每年的收入分别为5万元、10万元、20万元和40万元。假定每年的贴现率是8%，问投资的净现值是多少？分析：年贴现率为8%，第一笔20万元付款发生在期初，所以不应包含在value参数中。NPV(rate,value1[,value2,…]）9.1.1投资函数4.XNPV函数语法格式：XNPV(rate,values,dates)函数功能：计算一组现金流的净现值，这些现金流不一定定期发生。参数说明：rate应用于现金流的贴现率，所有后续付款都基于每年365天进行贴现。values一系列现金流。如果第一个values值是成本或付款，则它必须是负值，

而且values系列必须至少要包含一个正数和一个负数。dates与values中的现金流相对应的付款时间。9.1.1投资函数【例9-4】假定某项投资需要在2019-1-1支付现金3万元，并于下述时间获取以下金额的返回资金：2019-7-1返回8750元；2020-1-1返回7250元；2020-7-1返回16250元；2020-12-31返回9750元。假设资金流转贴现率为7％，则净现值为多少？分析：年贴现率为7%，values参数对应所有的金额，dates参数对应相应的日期。XNPV(rate,values,dates)9.1.2利率函数功能是计算不同形式的利率。常用的利率函数：1.RATE函数2.IRR函数3.MIRR函数4.XIRR函数9.1.2利率函数1.RATE函数语法格式：RATE(nper,pmt,pv[,fv,type,guess])函数功能：用于计算连续分期等额投资（或贷款）的利率，也可以计算一次性偿还的投资（或贷款）利率。参数说明：nper总投资（或贷款）期数。pmt各期支付金额。pv现值（本金）。fv未来值或在最后一次付款后希望得到的现金余额，省略时默认其值为0。type付款时间类型，省略时默认其值为0（期末）。guess预期利率，省略时默认其值为10%。9.1.2利率函数【例9-5】假设某用户计划贷款8万元装修房子，贷款5年，月还款额为1600元，计算该笔贷款的月利率及年利率。分析：按照贷款期数共5*12个月，每月末等额偿还1600元，贷款现值为8万元计算月利率。RATE(nper,pmt,pv[,fv,type,guess])9.1.2利率函数

【例9-6】假设有人建议你给他投资10万元，期限4年，那么是每年拿回3万元收益合适还是4年后一次性拿回13万元收益合适呢？分析：这实际上就是投资收益率多少的问题。（1）按照期数共4年，每年末等额投资收益3万元，投资现值为10万元计算投资收益率；（2）按照期数共4年，每年支付0元，投资现值为10万元，未来值13万元计算4年一次性的投资收益率。9.1.2利率函数【例9-6】假设有人建议你给他投资10万元，期限4年，那么是每年拿回3万元收益合适还是4年后一次性拿回13万元收益合适呢？RATE(nper,pmt,pv[,fv,type,guess])

显然，每年拿回3万元收益回报率更高9.1.2利率函数2.IRR函数语法格式：IRR(values[,guess])函数功能：计算由数值代表的一组现金流的内部收益率。参数说明：values一个数组或对包含数值的单元格的引用，包含用来计算返回的内部收益

率的数值。values值的顺序与现金流的顺序对应。guess对函数IRR计算结果的估计值，省略时默认其值为10%。9.1.2利率函数【例9-7】某用户计划开一个食品厂，预计投资为11万元，并预期今后5年的净收益分别为：1.5万元、2.1万元、3万元、4万元和5.5万元。分别求出投资2年、4年以及5年后的内部收益率。分析：values参数对应各个现金流，所以计算4年后的内部收益率应该包含初期投资和第1年、第2年、第3年、第4年的净收益。IRR(values[,guess])9.1.2利率函数3.MIRR函数语法格式：MIRR(values,finance_rate,reinvest_rate)函数功能：计算某一连续期限内现金流的修正内部收益率，同时考虑了投资的成本和现金再投资的收益率。参数说明：values一个数组或对包含数值的单元格的引用，包含各期的一系列支出及收入，

其中必须至少包含一个正值和一个负值，才能计算修正后的内部收益率。finance_rate现金流中使用的资金支付的利率。reinvest_rate将现金流再投资的收益率。9.1.2利率函数【例9-8】如果贷款12万元进行投资，5年的净收益分别为：3.9万元、3万元、2.1万元、3.7万元和4.6万元；12万元贷款的年利率为10％，再投资收益的年利率为12％，则3年、5年后投资的修正收益率是多少？分析：values参数对应各个现金流，所以计算5年后的修正收益率应该包含初期投资和第1年、第2年、第3年、第4年、第5年的净收益。MIRR(values,finance_rate,reinvest_rate)9.1.2利率函数4.XIRR函数语法格式：XIRR(values,dates[,guess])函数功能：计算一组现金流的内部收益率，这些现金流不一定定期发生。参数说明：values与dates中的付款时间相对应的一系列现金流。guess对函数XIRR计算结果的估计值，省略时默认其值为0.1（10%）。9.1.2利率函数【例9-9】假定在2018-1-1投资现金1万元，并于下述时间获取以下金额的返回资金：2018-3-1返回2750元；2018-10-30返回4250元；2019-2-15返回3250元；2019-4-1返回2750元，那么该笔投资的内部收益率为多少？分析：values参数对应所有的现金流，dates参数对应相应的付款日期。XIRR(values,dates[,guess])9.1.3利息与本金函数功能是计算不同形式的投资（或贷款）利息与本金。1.PMT函数2.IPMT函数3.PPMT函数4CUMPRINC函数5.CUMIPMT函数6.NPER函数9.1.3利息与本金函数1.PMT函数语法格式：PMT(rate,per,nper,pv[,fv,type])函数功能：基于固定利率及等额分期付款方式，计算投资（或贷款）的每期偿还额。参数说明：rate投资（或贷款）利率。nper总投资（或贷款）期数。pv现值（本金）。fv未来值或在最后一次付款后希望得到的现金余额，省略时默认其值为0。type付款时间类型，省略时默认其值为0（期末）。9.1.3利息与本金函数【例9-10】某用户准备向银行贷款10万元，期限为1年，假设银行给的年利率为8%，采用等额分期还款方式，那么每月需要向银行还多少钱？分析：按照每月利率为8%/12，共12个月，贷款现值10万元进行计算。PMT(rate,per,nper,pv[,fv,type])9.1.3利息与本金函数2.IPMT函数语法格式：IPMT(rate,per,nper,pv[,fv,type])函数功能：基于固定利率及等额分期付款方式，计算投资贷款在某一给定期间内的利息偿还额。参数说明：rate投资或贷款利率。per要计算利息数额的期数，必须在1到nper之间。nper总投资（或贷款）期数。pv现值（本金）。fv未来值或在最后一次付款后希望得到的现金余额，省略时默认其值为0。type付款时间类型，省略时默认其值为0（期末）。9.1.3利息与本金函数【例9-11】某用户在银行贷款1万元，假定年利率为10%，期限为5年，那该笔贷款在第一个月偿还的利息是多少？在上述条件下贷款最后一年的利息（按年支付）又是多少？分析：要计算第一个月偿还的利息，按照每月利率10%/12，第一个月即第1期，共5*12个月，贷款现值1万元进行计算。要计算最后一年偿还的利息，按照年利率10%，最后一年即为按年计算的第5期，共5年，贷款现值1万元进行计算。IPMT(rate,per,nper,pv[,fv,type])9.1.3利息与本金函数3.PPMT函数语法格式：PPMT(rate,per,nper,pv[,fv,type])函数功能：基于固定利率及等额分期付款方式，计算投资（或贷款）在某一给定期间内的本金偿还额。参数说明：rate投资或贷款利率。per要计算利息数额的期数，必须在1到nper之间。nper总投资（或贷款）期数。pv现值（本金）。fv未来值或在最后一次付款后希望得到的现金余额，省略时默认其值为0。type付款时间类型，省略时默认其值为0（期末）。9.1.3利息与本金函数【例9-12】如果年利率为8％，贷款年限为2年，贷款额为20万元，问贷款第一个月和第一年的本金偿还额分别是多少？分析：要计算第一个月偿还的本金，按照每月利率8%/12，第一个月即第1期，共2*12个月，贷款现值20万元进行计算。要计算第一年偿还的本金，按照年利率8%，第一年即为按年计算的第1期，共2年，贷款现值20万元进行计算。PPMT(rate,per,nper,pv,[fv],[type])9.1.3利息与本金函数4.CUMPRINC函数语法格式：

CUMPRINC(rate,nper,pv,start_period,end_period,type)函数功能：计算投资（或贷款）在给定的start_period到end_period期间累计偿还的本金数额。参数说明：rate投资或贷款利率。nper总投资（或贷款）期数。pv现值（本金）。start_period计算期间的首期（付款期数从1开始计数）。end_period计算期间的末期。type付款时间类型，省略时默认其值为0（期末）。9.1.3利息与本金函数【例9-13】某人在银行贷款12.5万元，假定年利率为9%，期限为30年，那该笔贷款在第一个月偿还的本金和第二年（第13到24期）偿还的全部本金分别是多少呢？分析：要计算第一个月偿还的本金，按照每月利率9%/12，共30*12个月，贷款现值12.5万元，首期1，末期1进行计算。要计算第二年偿还的本金，按照每月利率9%/12，共30*12个月，贷款现值12.5万元，首期13，末期24进行计算。

CUMPRINC(rate,nper,pv,start_period,end_period,type)9.1.3利息与本金函数5.CUMIPMT函数语法格式：CUMIPMT(rate,nper,pv,start_period,end_period,type)函数功能：返回一笔投资（或贷款）在给定的start_period到end_period期间累计偿还的利息数额。参数说明：rate投资或贷款利率。nper总投资（或贷款）期数。pv现值（本金）。start_period计算期间的首期（付款期数从1开始计数）。end_period计算期间的末期。type付款时间类型，省略时默认其值为0（期末）。9.1.3利息与本金函数【例9-14】某人在银行贷款12.5万元，假定年利率为9％，期限为30年，那该笔贷款在第一个月偿还的利息和第二年（第13到24期）要偿还的全部利息分别是多少呢？分析：要计算第一个月偿还的利息，按照每月利率9%/12，共30*12个月，贷款现值12.5万元，首期1，末期1进行计算。要计算第二年偿还的利息，按照每月利率9%/12，共30*12个月，贷款现值12.5万元，首期13，末期24进行计算。CUMIPMT(rate,nper,pv,start_period,end_period,type)9.1.3利息与本金函数6.NPER函数语法格式：NPER(rate,pmt,pv[,fv,type])函数功能：计算按指定偿还金额分期偿还贷款的总期数。参数说明：

rate投资或贷款利率。

pmt每一期支付额。

pv现值（本金）。

fv未来值，省略时默认其值为0。

type付款时间类型，省略时默认其值为0（期末）。9.1.3利息与本金函数【例9-15】某用户计划从银行贷款50万元，年利率为6%，采用等额分期付款方式，每月可偿还6000元，那么需要多久才能还清？分析：按照每月利率为6%/12，每期偿还6000元，贷款现值50万元进行计算。NPER(rate,pmt,pv[,fv,type])9.1.4折旧函数使用不同的方法计算资产折旧。具体选用哪种折旧方法，则须视情况而定。1.DB函数2.DDB函数3.VDB函数4.SLN函数5.SYD函数6.AMORDEGRC函数9.1.4折旧函数1.DB函数语法格式：DB(cost,salvage,life,period[,month])函数功能：使用固定余额递减法，计算指定的任何期间内的资产折旧值。参数说明：

cost资产原值。

salvage资产残值。

life折旧期限（使用寿命）。

period需要计算折旧值的期间（必须使用与life相同的单位）。

month第一年的月份数，省略时默认其值为12。。9.1.4折旧函数【例9-16】某用户于5年前的6月份花8500元购买了一台计算机，现在报废得到1000元，那么这台计算机每年的折旧值分别是多少？分析：计算机原值8500元，残值1000元，折旧期限5年，第一年使用的月份数是7。DB(cost,salvage,life,period[,month])9.1.4折旧函数2.DDB函数语法格式；DDB(cost,salvage,life,period[,factor])函数功能：使用双倍余额递减法，计算指定的任何期间内的资产折旧值。参数说明：

cost资产原值。

salvage资产残值。

life折旧期限（使用寿命）。

period需要计算折旧值的期间（必须使用与life相同的单位）。

factor余额递减速率（折旧因子），省略时默认其值为2（双倍余额递减法）9.1.4折旧函数【例9-17】某用户花10万元购买了一台小汽车从事出租运营，假定使用期限为10年，报废价值为1万元，分别计算第一天，第一个月、第一年、第三年各期间内的折旧值。分析：汽车原值10万元，残值1万元，折旧期限按天计算为10*365天，按月计算为10*12月，按年计算为10年。DDB(cost,salvage,life,period[,factor])9.1.4折旧函数3.VDB函数语法格式：VDB(cost,salvage,life,start_period,end_period[,factor,no_switch])函数功能：使用可变余额递减法，计算指定的start_period到end_period期间内的资产折旧值。参数说明：

cost资产原值。

salvage资产残值。

life折旧期限（使用寿命）。

start_period进行折旧计算的起始期间（必须与1ife单位相同）。

end_period进行折旧计算的截止期间（必须与life单位相同）。

factor余额递减速率（折旧因子），省略时默认其值为2（双倍余额递减法）。no_switch逻辑值，指定当折旧值大于余额递减计算值时，是否转用直线折旧法。9.1.4折旧函数【例9-18】某用户花10万元购买了一台小汽车从事出租运营，假定使用期限为10年，报废价值为1万元，分别计算第一天、第一年、第6～15个月各期间内的折旧值。VDB(cost,salvage,life,start_period,end_period[,factor,no_switch])分析：汽车原值10万元，残值1万元，折旧期限按天计算为10*365天，按月计算为10*12月，按年计算为10年。9.1.4折旧函数4.SLN函数语法格式：SLN(cost,salvage,life)函数功能：基于直线折旧法计算某项资产每期的线性折旧值，即平均折旧值。参数说明：cost

资产原值。salvage资产残值。life折旧期限（使用寿命）。9.1.4折旧函数【例9-19】某用户要购买一辆价值30万元的小汽车，假设其折旧年限为20年，残值为2万，那每年的线性折旧值是多少？？分析：汽车原值30万元，残值2万元，折旧期限按年计算为20年。SLN(cost,salvage,life)9.1.4折旧函数5.SYD函数语法格式：SYD(cost,salvage,life,