Excel 2016数据处理与分析应用教程 课件 第8-10章 宏、财务分析函数及应用、模拟分析

第8章宏主要内容Excel宏指的是一系列可以在Excel环境中执行的操作指令。这些操作指令实际上就是VBA（VisualBasicforApplication）代码。用户可以通过录制宏的方式来创建宏，从而实现一些重复操作的自动化。8.1录制宏8.2执行宏8.3编辑宏8.4宏按钮8.1录制宏录制宏就是将所有在Excel中的操作过程都采用VBA代码记录下来。【例8-1】录制宏并命名为“设置单元格字体和背景色”，实现将某个单元格的字体设置为楷体，背景色为蓝色。8.1录制宏宏的保存位置。位置一：当前工作簿。

该宏只能在当前工作簿中使用。位置二：新工作簿。

新建一个工作簿来保存该宏。位置三：个人宏工作簿。

该宏在多个工作簿都能使用。8.1录制宏保存包含了宏的Excel文件。文件类型为“Excel启用宏的工作簿”，扩展名为.xlsm

否则，所有的宏代码都会丢失。8.2执行宏录制完“设置单元格字体和背景色”宏后就可以重复执行它。①

在Excel工作表中任意选择一个单元格或单元格区域，例如，A3单元格。②

单击“开发工具”选项卡“代码”选项组中的“宏”按钮，打开“宏”对话框。③

选择“设置单元格字体和背景色”宏，单击“执行”按钮，则A3单元格的字体和颜色变为同A1的一样。8.3编辑宏编辑查看“设置单元格字体和背景色”宏代码：①

单击“开发工具”选项卡“代码”选项组中的“宏”按钮，打开“宏”对话框。②

选择“设置单元格字体和背景色”宏，单击“编辑”按钮，打开VBA的编辑器窗口，并同时显示该宏的代码。8.4宏按钮将宏指定给某个按钮，这样可以很方便地直接通过按钮来执行相应的宏。【例8-2】在工作表中创建一个按钮，并给它指定“设置单元格字体和背景色”宏。然后通过单击该按钮来执行宏。第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,period)函数功能：计算某项资产按年限总和折旧法计算的指定期间的折旧值。参数说明：cost资产原值。salvage资产残值。life折旧期限（使用寿命）。period需要计算折旧值的期间（必须使用与life相同的单位）。9.1.4折旧函数【例9-20】某用户购买了一台计算机，如果计算机原值8500元，报废价值为1000元，使用寿命5年，则第一年和第四年的折旧值分别是多少？分析：计算机原值8500元，残值1000元，折旧期限5年，计算第一年折旧值period值为1，计算第四年折旧值period值为4。SYD(cost,salvage,life,period)9.1.4折旧函数6.AMORDEGRC函数语法格式：AMORDEGRC(cost,date_purchased,first_period,salvage,period,rate[,basis])函数功能：计算每个结算期间的折旧值参数说明：

date_purchased购入资产的日期。

first_period第一个期间结束时的日期。

salvage资产残值。

period需要计算折旧值的期间。

rate折旧率。

basis所使用的年基准，省略时默认采用“US（NASD）30/360”9.1.4折旧函数【例9-21】某用户购买了一台计算机，如果计算机原值8500元，报废价值1000元。使用寿命5年，购入资产的日期为2019年10月1日，折旧率为15%，使用的年基准为1（按实际天数），计算第一个结算期间（结束日期2019年12月31日）的折旧值为多少？AMORDEGRC(cost,date_purchased,first_period,salvage,period,rate[,basis])分析：计算机原值8500元，购入资产的日期为2019/10/1，第一个期间结束日期为2019/12/31，残值1000元，折旧期限5年，计算第一年折旧值period值为1。第10章模拟分析主要内容10.1单变量求解10.2模拟运算表10.3方案管理器10.1单变量求解单变量求解是对函数公式的逆运算，主要解决假定一个公式要取得某一结果值，公式中的某个变量应取值为多少的问题。10.1单变量求解【例10-1】简单函数y=2x+10的单变量求解。分析：在B2输入x的值5；B3输入函数的截距10；B4中是y值的计算公式“=2*B2+B3”，计算结果为20。如果想让B4（y）为某个特定值，那么B2（x）值应该是多少呢？即如何根据y值求出x的值呢？这是典型的逆运算问题。10.1单变量求解【例10-1】简单函数y=2x+10的单变量求解。假设y的目标值为100，通过单变量求解出x值：①单击“数据”选项卡“预测”选项组中的“模拟分析”按钮，选择“单变量求解”命令，弹出“单变量求解”对话框。②将目标单元格设置为$B$4，目标值设置为100，可变单元格设置为$B$2。10.1单变量求解【例10-1】简单函数y=2x+10的单变量求解。求解结果：10.1单变量求解【例10-2】贷款问题的单变量求解。某人计划贷款100万元，10年，采取每月等额偿还本息的方法，按年利率5.5%的方案，利用财务函数PMT可以计算出每月需还款额。但目前每月可用于还贷的资金只有8000元，可申请贷款的最大额度是多少？分析：B5的计算公式“=PMT(B4/12,B3*12,B2)”

B2是可变单元格，B5是目标单元格，目标值是-8000。10.1单变量求解【例10-2】贷款问题的单变量求解。求解结果：在年利率和贷款年限不变的条件下，可以申请贷款的最大额度。10.2模拟运算表10.2.1单变量模拟运算表

分析一个变量值的变化对公式运算结果的影响。10.2.2双变量模拟运算表

分析两个变量值同时变化对公式运算结果的影响。10.2.1单变量模拟运算表当需要分析单个决策变量变化对某个计算公式的影响时，可以使用单变量模拟运算表实现。例如，不同的年化收益率对理财产品收益的影响，不同的贷款年利率对还款额度的影响等。10.2.1单变量模拟运算表【例10-3】某公司计划贷款1000万元，年限为10年，采取每月等额偿还本息的方法归还贷款本金并支付利息，目前的年利率为4%，每月的偿还额为101245.14元。但根据宏观经济的发展情况，国家会通过调整利率对经济发展进行宏观调控。投资人为了更好地进行决策，需要全面了解利率变动对偿贷能力的影响。10.2.1单变量模拟运算表【例10-3】贷款问题的单变量模拟运算表。分析：B2贷款金额1000万元；B3贷款年限10年；B4年利率4%；B5的计算公式“=PMT(B4/12,B3*12,B2)”。使用单变量模拟运算表可以很直观地以表格的形式，将偿还贷款的能力与利率变化的关系列出来。10.2.1单变量模拟运算表【例10-3】贷款问题的单变量模拟运算表。操作步骤①选择模拟运算表存放区域。本例选择D1:E13。其中D2:D13列出了利率的所有取值，E1是计算每月偿还额的公式“=PMT(B4/12,B3*12,B2)”10.2.1单变量模拟运算表【例10-3】贷款问题的单变量模拟运算表。操作步骤①选择模拟运算表存放区域。本例选择D1:E13。其中D2:D13列出了利率的所有取值，E1是计算每月偿还额的公式“=PMT(B4/12,B3*12,B2)”10.2.1单变量模拟运算表【例10-3】贷款问题的单变量模拟运算表。操作步骤②选定D1:E13，单击“数据”选项卡“预测”选项组中的“模拟分析”按钮，选择“模拟运算表”选项，弹出“模拟运算表”对话框。③在该对话框的“输入引用列的单元格”框中输入“$B$4”。10.2.1单变量模拟运算表【例10-3】贷款问题的单变量模拟运算表。计算结果10.2.1单变量模拟运算表【例10-3】贷款问题的单变量模拟运算表。将利率值按行存放，A8中输入计算公式，$B$4为引用行的单元格，则计算结果：10.2.2双变量模拟运算表

单变量模拟运算表只能解决一个变量值变化对公式计算结果的影响，如果想查看两个变量值变化对公式计算结果的影响就需要用到双变量模拟运算表。10.2.2双变