《Excel在财务与会计中的应用》课件：学习情境三Excel在资金时间价值分析中的应用

学习情境学习情境三三 Excel在资金时间价值分析中的应用在资金时间价值分析中的应用学习任务3-1 Excel在现值分析中的应用学习任务3-2 Excel在净现值分析中的应用学习任务3-3 Excel在终值分析中的应用学习任务3-4 Excel在利率分析中的应用学习任务3-5 Excel在内含报酬率分析中的应用学习任务3-6 Excel在投资期或还贷期分析中的应用学习任务3-7 Excel在年金分析中的应用学习任务3-8 利用Excel分析付款方案学习任务3-9 利用Excel设计分期还贷查询系统学习目标学习目标1认识资金时间价值的意义。2应用PV、NPV、FV、RATE、IRR、MIRR、NPER、PMT3养成思考问题习惯，触类旁通，培养方法能力。4能够应用资金时间价值函数解决财务与会计问题。学习任务3-1 Excel在现值分析中的应用一、知识点 现值二、技能点 PV函数的应用三、重点 PV函数的语法格式及应用注意事项四、难点 IF函数的应用资金时间价值 财务与会计涉及到资金时间价值问题 什么是资金时间价值？资金时间价值的表现？现值及现值函数PV 现值（Present Value）：一笔资金按规定的折现率，折算成现在或指定起始日期的数值，或者是一系列未来付款的当前值的累积和。现值函数现值函数PV功能功能返回某项投资的一系列将来偿还额的当前总值，或一次性偿还额的现值。返回某项投资的一系列将来偿还额的当前总值，或一次性偿还额的现值。语法语法PV(rate,nper,pmt,fv,type)Rate为各期利率。为各期利率。Nper为年金的付款期总数为年金的付款期总数。Pmt为各期应支付的金额（包括本金和为各期应支付的金额（包括本金和利息利息）Fv为未来值，或在最后一次支付后希望得到的现金余额为未来值，或在最后一次支付后希望得到的现金余额。Type为数字为数字0或者或者1。付款。付款时间发生在时间发生在期末期末还是还是期初。期初。注意：注意：PV函数值与函数值与Pmt参数和参数和fv参数值的正负方向相反。参数值的正负方向相反。【学习引例3-1】某企业计划在5年后积累资金达到1 000 000元，假设年投资报酬率为10%，现在应该一次性投入多少资金？分析：已知投资报酬率、投资期和投资终值，计算现值。【学习引例3-2】某基金的购买成本为80 000元，该基金可以在今后20年内于每月月末回报600元，假定投资机会的最低年报酬率为8%，投资该项基金是否合算？分析：涉及跨期收支比较，与现值分析相关。换算到相同时间基础上，再进行评价。学习任务3-1教学效果检测 有一笔5年期分期付款购买设备的业务，每年年初付500000元，银行实际年利率为6%，则该项分期付款总额相当于现在一次支付的价款是多少？要求：1、列出计算公式 2、回答老师提问 提问：PV函数各参数意义及其排列顺序 3、操作练习学习任务3-2 Excel在净现值分析中的应用一、知识点 净现值二、技能点 NPV函数的应用三、重点 NPV函数的功能和语法净现值 净现值（Net Present Value）是指投资方案所产生的现金净流量以资金成本为贴现率折现之后与原始投资额现值的差额，其实质是一系列现金流的总现值。NPV函数的函数的功能和功能和语法语法功能功能基于一系列基于一系列将来收支将来收支现金流和一贴现率，返回一项投资的净现值。现金流和一贴现率，返回一项投资的净现值。语法语法NPV(rate,value1,value2,.)rate为某一期间的贴现率。为某一期间的贴现率。value1,value2,.为一系列现金流，在时间上必须具有相等间隔，并且为一系列现金流，在时间上必须具有相等间隔，并且都发生在期末。现金流中，支出用负数表示，收入用正数表示。都发生在期末。现金流中，支出用负数表示，收入用正数表示。Value1是必需的，后续值是可选的。是必需的，后续值是可选的。【学习引例3-3】甲公司2012年1月1日从乙公司购买一台设备，该设备已投入使用。合同约定，该设备的总价款为1000万元，设备款分3年付清，2012年12月31日支付500万元，2013年12月31日支付300万元，2014年12月31日支付200万元，3年期银行借款年利率为6%。确定该设备入账价值。1、分析 设备的入账价值计算设备价款现值。利用PV函数可以解决，但如果利用净现值函数NPV解决，更为便利。2、操作 结果：906.62万元学习任务3-2教学效果检测 某项目初始投资为206000元，第1年至第6年的每年年末现金流量分别为50000元、50000元、50000元、50000元、48000元、106000元。如果贴现率要求是12%，计算该项目投资净现值。先让学生书写公式，再进行操作。结果提示：26806.86元学习任务3-3 Excel在终值分析中的应用一知识点 终值二、技能点 FV函数的应用三、重点 FV函数的语法格式及应用注意事项终值和终值函数FV 终值（future value）又称将来值或本利和，是指现在一定量的资金在未来某一时点上的价值。终值函数终值函数FV功能功能基于固定利率和等额分期付款方式，返回某项投资的未来值。基于固定利率和等额分期付款方式，返回某项投资的未来值。语法语法FV(rate,nper,pmt,pv,type)Rate为各期利率。为各期利率。Nper为年金的付款总期数。为年金的付款总期数。Pmt为各期应支付的金额（包括本金和利息），其数值在整个年金期为各期应支付的金额（包括本金和利息），其数值在整个年金期间保持不变。如果省略间保持不变。如果省略pmt参数，则不能省略参数，则不能省略pv参数。参数。Pv为现值，或一系列未来付款的当前值的累积和。如果省略参数为现值，或一系列未来付款的当前值的累积和。如果省略参数pv，则假设其值为则假设其值为0，此时不能省略，此时不能省略pmt参数。参数。Type参数值为参数值为1或或0，用以指定付款时间发生在期初，还是在期末，如，用以指定付款时间发生在期初，还是在期末，如果省略果省略type，则假设值为，则假设值为0，即默认付款时间在期末。，即默认付款时间在期末。注意：注意：FV函数值与参数函数值与参数pmt、pv的值，正负方向相反。的值，正负方向相反。【学习引例3-4】存入银行10000元，存期5年，银行按5%的5年期单利利率计息，5年后可一次性从银行取出多少钱？其中利息是多少？分析：计算单利终值及利息。【学习引例3-5】向银行借款1000万元，年利率8%，期限5年，到期一次还本付息，5年后应偿还多少？其中有多少利息？分析：计算复利终值及利息。【学习引例3-6】某企业计划从现在起每月月末存入20000元，如果按月利息0.353%计算，那么两年以后该账户的存款余额会是多少？分析：计算普通年金终值学习任务3-3教学效果检测 某企业计划从现在起每月月初存入20000元，如果按月利息0.353%计算，那么两年以后该账户的存款余额会是多少？要求：1、列出计算公式 2、回答老师提问 提问：FV函数各参数意义及其排列顺序 3、操作练习 结果提示：501764.50元学习任务3-4 Excel在利率分析中的应用一、知识点 利率二、技能点 RATE函数的应用三、重点 RATE函数的功能和语法利率 利率（rate）又称利息率。表示一定时期内利息量与本金的比率，通常用百分比表示。按计算利率的期限单位可划分为：年利率、月利率与日利率，分别以%、表示。利率计算公式：利息率=（利息量本金时间）100%利率在另一种情形下又称为贴现率或折现率，贴现率是指将未来支付变为现值所使用的利率，或指持票人以没有到期的票据向银行要求兑现，银行将利息先行扣除所使用的利率。利率利率函数函数RATE的功能和语法的功能和语法功能功能返回投资或贷款的每期实际利率。返回投资或贷款的每期实际利率。语法语法RATE(nper,pmt,pv,fv,type,guess)Nper为年金的付款总期数。为年金的付款总期数。Pmt为各期应支付的金额（包括本金和利息），其数值在整个年金期为各期应支付的金额（包括本金和利息），其数值在整个年金期间保持不变。如果省略间保持不变。如果省略pmt，则必须包含，则必须包含fv参数。参数。Pv为现值，即一系列未来付款当前值的累积和。为现值，即一系列未来付款当前值的累积和。Fv为终值，或在最后一次付款后希望得到的现金余额。如果省略为终值，或在最后一次付款后希望得到的现金余额。如果省略fv，则假设其值为零。则假设其值为零。Type为为1或或0，数字，数字0（缺省）说明支付发生在期末，数字（缺省）说明支付发生在期末，数字1说明支付发说明支付发生在期初。生在期初。Guess为预期利率，如果省略，则假设该值为为预期利率，如果省略，则假设该值为10%。注意注意1：参数：参数pmt与参数与参数pv，数值的正负方向相反；参数，数值的正负方向相反；参数pv与参数与参数fv，数值的正负方向相反。数值的正负方向相反。【学习引例3-7】某人建议你贷给他30000元，并同意每年末付给你9000元，共付五年。那么你如何去决策这笔投资？分析：如果测算利率高于其他投资项目的报酬率，可以接受该建议；反之，不接受该建议。操作：依据教材提示进行【学习引例3-8】某公司出售一台套设备，协议约定采用分期收款方式，从销售当年末分5年分期收款，每年200万元，合计1000万元。假定购货方在销售成立日支付货款，付800万元即可。分析：购货方在销售成立日支付800万元，可以看作是应收金额的公允价值为800万元。该笔业务的账务处理，涉及到折现率的计算问题。要计算每年末的“未实现融资收益”和“财务费用”数据，就需要计算年金为200万元、期数为5年、现值为800万元的折现率数据。操作：依据教材提示进行学习任务3-4教学效果检测 现有15000元，要想在10年后达到50000元，那么在选择投资机会时，最低可接受的报酬率是多少？要求：1、列出计算公式 2、回答老师提问 提问：RATE函数的参数意义及其排列顺序 3、操作练习 结果提示：12.795%学习任务3-5 Excel在内含报酬率分析中的应用一、知识点 内含报酬率二、技能点 IRR函数合MIRR函数的应用三、重点 IRR函数和MIRR函数的功能和语法四、难点 MIRR函数的应用内含报酬率和修正内含报酬率 （一）内含报酬率的概念 内含报酬率也称内部报酬率，是投资方案净现值为零时的贴现率，净现值为零说明现金流入的现值等于现金流出的现值。内含报酬率法是根据投资方案本身内含报酬率来评价方案优劣的一种方法。如果内含报酬率大于资金成本率，则方案可行，且内含报酬率越高，方案越优。内含报酬率法的优点是能够把项目寿命期内的收益与其投资总额联系起来，揭示这个项目的收益率，通过将它与同行业投资收益率对比，以确定这个项目是否值得建设。使用借款进行建设，在利率还不很明确时，内含报酬率法可以避开借款条件，先求得内含报酬率，作为可以接受借款利率的高限。当然，内含报酬率表现的是比率，不是绝对值，一个内含报酬率较低的方案，可能由于其规模较大而有较大的净现值，更值得建设。所以优选方案时，必须将内含报酬率与净现值结合起来考虑。（二）修正内含报酬率的概念 修正内含报酬率是考虑投资成本以及现金再投资利率时一系列现金流的内部报酬率。内含报酬率函数IRR功能与语法格式功能功能返回一系列现金流的内部报酬率。这些现金流不必为均衡的，但作返回一系列现金流的内部报酬率。这些现金流不必为均衡的，但作为年金，它们必须按固定的间隔产生。内部收益率为投资的回收利为年金，它们必须按固定的间隔产生。内部收益率为投资的回收利率，其中包含定期支付（负值）和定期收入（正值）。率，其中包含定期支付（负值）和定期收入（正值）。语法语法IRR(values,guess)Values为数组或单元格的引用，包含用来计算返回的内部收益率的为数组或单元格的引用，包含用来计算返回的内部收益率的数字。数字。values必须包含至少一个正值和一个负值，以计算返回的内部必须包含至少一个正值和一个负值，以计算返回的内部收益率。函数收益率。函数IRR根据数值的顺序来解释现金流的顺序。根据数值的顺序来解释现金流的顺序。Guess为为IRR计算结果的估计值。计算结果的估计值。Excel使用迭代法计算函数使用迭代法计算函数IRR。如果。如果IRR经过经过20次迭代，仍未找到结次迭代，仍未找到结果，则返回错误值果，则返回错误值#NUM!。如果省略。如果省略guess，则假设为，则假设为10%。如果函。如果函数数IRR返回错误值返回错误值#NUM!，或结果没有靠近期望值，可用另一个，或结果没有靠近期望值，可用另一个guess值再试。值再试。修正内含报酬率函数MIRR功能与语法格式功能功能返回在考虑投资成本以及现金再投资利率下一系列分返回在考虑投资成本以及现金再投资利率下一系列分期现金流的内部报酬率期现金流的内部报酬率。同时。同时考虑了投资的成本和现金考虑了投资的成本和现金再投资的收益率。再投资的收益率。语法语法MIRR(values,finance_rate,reinvest_rate)Values为一个数组或对包含数字的单元格的引用。这为一个数组或对包含数字的单元格的引用。这些数值代表着各期的一系列支出（负值）及收入（正些数值代表着各期的一系列支出（负值）及收入（正值）。值）。参数参数values中必须至少包含一个正值和一个负值，才能中必须至少包含一个正值和一个负值，才能计算修正后的内部收益率，否则函数计算修正后的内部收益率，否则函数MIRR会返回错误值会返回错误值#DIV/0!。如果数组或引用参数包含文本、逻辑值或空白。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略，但包含零值的单元格将计单元格，则这些值将被忽略，但包含零值的单元格将计算在内。算在内。Finance_rate为现金流中使用的资金支付的利率。为现金流中使用的资金支付的利率。Reinvest_rate为将现金流再投资的收益率。为将现金流再投资的收益率。【学习引例3-10】某公司以8%的年利率向银行贷款1 500 000元，然后以所筹资金签约投资某一项目，合同约定该项目投资期为5年，每年的净收益分别是投资额的20%、30%、30%、30%、20%，假如每年将所得的投资收益用于再投资，再投资报酬率为10%，那么该公司前4年的投资报酬率和修正报酬率分别是多少？5年后的投资报酬率和修正报酬率又分别是多少？分析：根据本例所提供的资料，利用内含报酬率函数IRR和修正内含报酬率函数MIRR，可以很方便地计算出不同投资年份的内含报酬率和修正内含报酬率。操作：依据教材提示进行学习任务3-5教学效果检测 某企业计划投资一项目，如果第1年年初投入1 000 000元，第1年可得净收益200 000元，以后3年每年可获净收益400 000元，假如每年折现率为5%，计算该投资项目的净现值和4年后的内含报酬率。学习任务3-6 Excel在投资期或还贷期分析中的应用一、知识点 投资期、还贷期二、技能点 NPER函数的应用三、重点 NPER函数的功能和语法NPER函数的功能和语法功能功能基于基于固定利率和等额分期付款方式，返回某项投资或贷款的期数。固定利率和等额分期付款方式，返回某项投资或贷款的期数。语法语法NPER(rate,pmt,pv,fv,type)Rate为各期利率。为各期利率。Pmt为各期所应支付的金额（包括本金和利息），其数值在整个年为各期所应支付的金额（包括本金和利息），其数值在整个年金期间保持不变。金期间保持不变。Pv为现值，或一系列未来付款的当前值的累积和。为现值，或一系列未来付款的当前值的累积和。Fv为未来值，或在最后一次付款后希望得到的现金余额。如果省略为未来值，或在最后一次付款后希望得到的现金余额。如果省略fv，则假设其值为零。，则假设其值为零。Type为数字为数字0或或1，指定各，指定各期付款期付款时间是在期末还是期初。时间是在期末还是期初。注意：注意：pmt、pv参数与参数与fv参数，数值的正负方向相反。参数，数值的正负方向相反。【学习引例3-11】某企业现有资金100000元，投资机会的年报酬率为8%，多少年后可以使现有资金增加到200000元。分析：计算资金积累期，用NPER函数解决。操作：依据教材提示进行【学习引例3-12】按揭方式购房，首付后欠款600000元，假设欠款的年利率为7.95%，每月还款能力为5000元，多少年能够还清？分析：计算还款期，用NPER函数解决。操作：依据教材提示进行学习任务3-6教学效果检测 某企业拟购置的一台动力设备，要么是柴油机，要么是汽油机。柴油机比汽油机每月可以节约燃料费5000元，但柴油机的价格比汽油机高出50000元。假设资金的年报酬率为18%，年资金周转次数12次以上（每月复利一次），试测算柴油机应至少使用多少年才合算？要求：1、列出计算公式 2、回答老师提问 提问：NPER函数的参数意义及其排列顺序 3、操作练习 结果提示：1年 学习任务3-7 Excel在年金分析中的应用一、知识点 年金二、技能点 PMT函数的应用三、重点 PMT函数的功能和语法格式年金年金是指在一定时期内每隔相同时间就发生等额的系列收付款项。年金具备的条件：等额性，连续性，间隔期相同。年金有普通年金、预付年金、递延年金和永续年金，其中普通年金最为常用。普通年金又称后付年金，是指各期期末收付的年金。普通年金现值，是指为在每期期末取得相等金额的款项，现在需要投入的金额。预付年金是指在每期期初支付的年金。递延年金是指第一次支付发生在第二期或第二期以后的年金。无限期定额支付的年金称为永续年金。存本取息可视为永续年金的一个例子。PMT函数的功能和语法函数的功能和语法格式格式功能功能计算在固定利率下，贷款的等额分期偿还额。计算在固定利率下，贷款的等额分期偿还额。语法语法PMT(rate,nper,pv,fv,type)Rate为贷款利率。为贷款利率。Nper为该项贷款的付款期总数。为该项贷款的付款期总数。Pv为现值，或一系列未来付款的当前值的累积和，也称为本为现值，或一系列未来付款的当前值的累积和，也称为本金。金。Fv为未来值，或在最后一次付款后希望得到的现金余额，如为未来值，或在最后一次付款后希望得到的现金余额，如果省略果省略fv，则假设其值为零。，则假设其值为零。Type为数字为数字0或或1，指定，指定各期的付款时间是在期末还是期初。各期的付款时间是在期末还是期初。注意：注意：PMT函数的值与函数的值与pv参数，数值的正负方向相反。参数，数值的正负方向相反。【学习引例3-13】假设以10%年利率借款20000元，投资于寿命为10年的某个项目，每年至少要收回多少资金才有利？分析：投资回收的年金测算，利用年金计算函数PMT。操作：依据教材提示进行。【学习引例3-14】按揭购房欠款600000元，假设25年还清，欠款年利率为8%，每月底需要支付的还本付息额是多少？如果在每月初还款，则每月还款额又为多少？分析：投资回收的年金测算，利用年金计算函数PMT。操作：依据教材提示进行。学习任务3-7教学效果检测 某企业计划为30年后退休的一批员工制订养老金计划，这些员工退休后每月月底可以从银行领取2500元，连续领取25年。若存款的复利年利率为3%，那么该企业从今年开始，需要为这批中的每位员工每年等额存入多少钱到银行？操作提示：=PMT(3%,30,0,-PV(3%/12,25*12,-2500)难点：涉及函数嵌套。先让学生讨论，再进行解答。学习情境三阶段小结 现代信息技术的发展，为货币时间价值的相关计量提供了广阔的应用前景，许多手工不便于解决的问题，或者难以解决的问题甚至无法解决的问题，都可以通过计算机轻而易举地加以解决，巧妙地利用Excel是最好的选择。学习任务3-8 利用Excel分析付款方案一、知识点 按揭销售业务 资金时间价值二、技能点 资金时间价值函数的应用三、学习要求 培养学生分析问题的能力和灵活应用Excel函数的能力。【学习引例3-16】A公司计划租用某营业用房，有两种方案可选：一次性付现1 800万元买断6年使用权，或者在每年末支付租金350万元。一次性付现需要全部借款，借款的年利息率为5.423%，而每年年末支付租金则无需借款。对于A公司来说，哪种方案划算？不考虑租金和利息所涉及的纳税差异问题。分析与操作（一）方法之一：利率对比 当现值为1 800万元、付款期为6年、每年末支付350万元时，所对应的利息率是多少？利用Rate函数测算每年支付租金所对应的利息率。将该利息率与向银行借款1 800万元的利息率5.423%进行对比，若测算出每年支付租金的利息率比银行借款利息率小，则每年支付租金的方案划算，否则相反。分析与操作（二）方法之二：年金对比 当利息率为5.423%、付款期为6年、现值为1 800万元时，所对应的每年偿还额是多少？利用Pmt函数测算借款的每年偿还额。将测算出的每年偿还额与每年支付的租金（即两个年金）进行对比，若每年支付的租金额比每年的借款偿还额少，则每年支付租金的方案划算，否则相反。分析与操作（三）方法之三：总现值对比 当利息率为5.423%、支付期为6年、每年末支付350万元时，所对应的总现值是多少？利用PV函数测算6年租金的总现值。将此总现值与银行借款1 800万元（一次性投入的现值）进行对比，若6年租金的总现值比1 800万元的银行借款小，则每年支付租金的方案划算，否则相反。模型学习任务3-利用Excel设计分期还贷查询系统一、知识点 按揭销售业务 资金时间价值 PMT函数的功能和语法格式 INDEX函数的功能和语法格式二、技能点 PMT函数的应用 INDEX函数的应用 控件按钮的设置三、学习要求 进一步强化培养学生分析问题的能力和灵活应用Excel函数的能力。INDEX函数的功能和语法格式功能功能在给定单元格在给定单元格区域中，返回特定行列交叉处单元格的值或引用。区域中，返回特定行列交叉处单元格的值或引用。语法语法INDEX(array,row_num,column_num)Array为单元格区域或数组常量。为单元格区域或数组常量。Row_num为数组中某行的行号，函数从该行返回数值。如果省略为数组中某行的行号，函数从该行返回数值。如果省略row_num，则必须有，则必须有column_num。Column_num为数组中某列的列标，函数从该列返回数值。如果省略为数组中某列的列标，函数从该列返回数值。如果省略column_num，则必须有，则必须有row_num。如果同时使用参数如果同时使用参数row_num和和column_num，函数，函数INDEX返回返回row_num和和column_num交叉处的单元格中的值。交叉处的单元格中的值。如果将如果将row_num或或column_num设置为设置为0，函数，函数INDEX则分别返回整个则分别返回整个列或行的数组数值。列或行的数组数值。分期还贷查询系统设计 1、了解按揭销售业务 2、根据需要设计模型 3、定义模型调节按钮 4、巧妙利用函数以及工作表之间的数据动态链接关系设计公式，实现分期还贷的动态分析。掌握本技能的途径：掌握本技能的途径：依据教材提示，边操作，边理解、边认识。学习情境三总结 利用Excel解决资金时间价值分析问题的思路：首先，分析要解决的问题是什么，有什么已知条件可以利用；然后，思考如何在结果与已知之间建立关联，需要借助Excel的什么工具或功能才能解决问题；最后，尝试建立解决问题的模型，经过改进和完善模型，最终实现问题的解决。