财务函数的应用

陈海烽财务函数的应用《Excel在财务中的应用》6、财务函数的应用；Excel提供了许多有关财务、投资、偿还、利息及折旧方面的函数，在工作表中运用这些函数可以较松地完成相关的财务运算，或者对其他财务管理软件的运算输出数据进行验证。《Excel在财务中的应用》6、财务函数的应用；6.1投资计算函数6.2折旧计算函数6.3偿还率计算函数《Excel在财务中的应用》6.1投资计算函数投资计算函数常见参数说明贷款定额还款期数计算函数NPER贷款分期偿还金额计算函数PMT贷款利息、本金金额计算函数IPMT、PPMT投资未来值计算函数FV投资偿还额现值计算函数PV投资定期现金流净现值计算函数NPV投资不定期现金流净现值计算函数XNPV《Excel在财务中的应用》投资计算函数常见参数说明Excel投资分析方面的函数，这些函数使用的参数大致相同，意义相近，如下表所示。参数说明rate为各期利率，为固定值per用于计算其本金数额的期次，必须在1~nper之间nper为总投资（或贷款）期次，即该项投资（或贷款）的付款期总数pv为现值，即从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零（如一笔贷款的未来值即为零）type数字0或1，用以指定各期的付款时间是在期初还是期末，0：期末；1：期初value1,value2value1,…,value29代表1~29个偿还金额不相等时的分期偿还额《Excel在财务中的应用》一、贷款定额还款期数计算函数NPER1.NPER函数知识介绍1）NPER函数功能：基于固定利率及等额分期付款方式，返回一项投资或贷款的期数。2）语法形式：NPER（rate，pmt，pv[，fv，type]）3）参数说明4）注意点：第一所有期数的单位要统一第二所有的入帐用正数表示，支付额用负数表示《Excel在财务中的应用》一、贷款定额还款期数计算函数NPER2.实例１超市贷款还款期数预测【实例内容】：由于拓展业务需要，欲向银行贷款1,000,000元。根据新风超市目前的经济与经营状况进行判断，再综合其他各种因素考虑，新风超市每月有能力且最适合的还款额为12,000元，若以7.50%的平均年利率作为参考，选择期（月）末付款方式，至少需要多久能还清贷款？《Excel在财务中的应用》一、贷款定额还款期数计算函数NPER【实例操作步骤】：步骤1：构造数据模型。如图6-1所示。步骤2：选定B4单元格，在辑栏中输入“=NPER(B2/12,-B3,B1)”。步骤3：单击“输入”按钮，此时可以这笔贷款方案需要偿还的时间（月）。图6-1例6.1数据模型《Excel在财务中的应用》二、贷款分期偿还金额计算函数PMT1.PMT函数知识介绍1）PMT函数功能：基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。2）语法形式：PMT(rate，nper，pv[，fv，type])3）参数说明《Excel在财务中的应用》二、贷款分期偿还金额计算函数PMT2.实例2超市贷款分期偿还金额计算【实例内容】：君华银行向新风超市提供了四种基于固定利率及等额分期期末付款的贷款方案，还款期数及年利率如表6-2所示，新风超市应该选择哪一种贷款方案比较合适？表6-2君华银行贷款方案表银行名称还款期数（年）贷款利率（年）君华银行57.20%107.48%157.66%207.92%《Excel在财务中的应用》二、贷款分期偿还金额计算函数PMT【实例操作步骤】：步骤1：构造数据模型。如图6-3所示。步骤2：选定D8单元格，在编辑栏中输入“=PMT(C8/12,B8*12,$B$1)”。步骤3：单击“输入”按钮，此时可以看到第一种贷款方案的每月还款额。图6-3例6.2数据模型《Excel在财务中的应用》三、贷款利息、本金金额计算函数IPMT、PPMT1.IPMT函数知识介绍1）IPMT函数功能：基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的利息偿还额。2）语法形式：IPMT(rate，per，nper，pv[，fv，type])3）参数说明《Excel在财务中的应用》三、贷款利息、本金金额计算函数IPMT、PPMT2.PPMT函数知识介绍1）PPMT函数功能：基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的本金偿还额。2）语法形式：PPMT(rate，per，nper，pv[，fv，type])3）参数说明《Excel在财务中的应用》三、贷款利息、本金金额计算函数IPMT、PPMT3.实例3超市贷款分期偿还利息与本金金额计算【实例内容】：在实例2中新风超市确定贷款方案后，第一年每月偿还的利息和本金分别是多少？《Excel在财务中的应用》三、贷款利息、本金金额计算函数IPMT、PPMT【实例操作步骤】：步骤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数据模型《Excel在财务中的应用》四、投资未来值计算函数FV1.FV函数知识介绍1）FV函数功能：基于固定利率及等额分期付款方式，返回某项投资的未来值。2）语法形式：FV(rate，nper，pmt[，pv，type])3）参数说明《Excel在财务中的应用》四、投资未来值计算函数FV2.实例4超市存款投资未来值计算【实例内容】：新风超市计划在5年后创办一所新风希望小学，需要资金200,000元。从本年开始筹备资金，打算在每月月初存入5,000元，在整个投资期间内，平均年投资回报率为7.6%，3年后这笔存款是否足够创办新风希望小学？《Excel在财务中的应用》四、投资未来值计算函数FV【实例操作步骤】：步骤1：构造数据模型。如图6-7所示。步骤2：选定B30单元格，在编辑栏中输入“=FV(B27/12,B28*12,-B29,,1)”。步骤3：单击“输入”按钮，此时可以看到这笔存款3年后的价值。图6-7例6.4数据模型《Excel在财务中的应用》五、投资偿还额现值计算函数PV1.PV函数知识介绍1）PV函数功能：返回投资的现值。现值为一系列未来付款当前值的累积和，可以作为一种权衡长期投资的方法。2）语法形式：PV(rate，nper，pmt[，fv，type])3）参数说明《Excel在财务中的应用》五、投资偿还额现值计算函数PV2.实例5超市保险投资决策【实例内容】：新风超市计划为高级员工提高福利，购买一笔医疗保险，购买的成本为28,000元，该保险可以在今后30年中每月末回报150元，投资年回报率为5.6%。新风超市购买这份保险是否划算？《Excel在财务中的应用》五、投资偿还额现值计算函数PV【实例操作步骤】：步骤1：构造数据模型。如图6-9所示。步骤2：选定B35单元格，在编辑栏中输入“=PV(B32/12,B33*12,B34)”。步骤3：单击“输入”按钮，此时可以看到这份保险的现值。图6-9例6.5数据模型《Excel在财务中的应用》六、投资定期现金流净现值计算函数NPV1.NPV函数知识介绍1）NPV函数功能：计算一组定期现金流的净现值。通过使用贴现率以及一系列未来支出（负值）和收入（正值），返回一项投资的净现值（当前纯利润）。2）语法形式：NPV(rate，value1，value2，...)3）参数说明：Rate是应用于现金流的贴现率，为一固定值。value1，value2，...代表1到29笔支出及收入的参数值，value1，value2，...所属各期间的长度必须相等，而且支付及收入的时间都发生在期末。如果支付的时间发生在期初，不能作为value参数，而是将收入转换成净现值后再扣除支付金额。《Excel在财务中的应用》六、投资定期现金流净现值计算函数NPV2.实例6超市收购投资决策【实例内容】：新风超市计划收购一小超市，收购成本为500,000元，必须预先付清，经过市场调查，预计今后6年的营业收入可达到110,000元，130,000元，160,000元，180,000元，190,000元和210,000元，每年的贴现率为8.7%。这项收购计划是否可行？《Excel在财务中的应用》六、投资定期现金流净现值计算函数NPV【实例操作步骤】：步骤1：构造数据模型。如图6-11所示。步骤2：选定B45单元格，在编辑栏中输入“=NPV(B38,B39:B44)-B37”。步骤3：单击“输入”按钮，此时可以看到收购小超市投资计划的当前纯利润。图6-11例6.6数据模型《Excel在财务中的应用》七、投资不定期现金流净现值计算函数XNPV1.XNPV函数知识介绍1）XNPV函数功能：返回一组现金流的净现值，这些现金流不一定定期发生。2）语法形式：XNPV(rate，values，dates)3）参数说明：rate是应用于现金流的贴现率，为一固定值。dates是与现金流支付相对应的支付日期表。values是与dates中的支付时间相对应的一系列现金流转。《Excel在财务中的应用》七、投资不定期现金流净现值计算函数XNPV2.实例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%，这项投资是否划算？《Excel在财务中的应用》七、投资不定期现金流净现值计算函数XNPV【实例操作步骤】：步骤1：构造数据模型。如图6-13所示。图6-13例6.7数据模型《Excel在财务中的应用》步骤2：将A50：A54单元格区域格式设置为“日期”。结果如图6-14所示。步骤3：选定B55单元格，在编辑栏中输入“=XNPV(B47,B50:B54,A50:A54)”。步骤4：单击“输入”按钮，此时可以看到这项投资计划的当前纯利润。图6-14转换日期序列《Excel在财务中的应用》6.2折旧计算函数折旧计算函数常见参数说明直线折旧函数SLN固定余额递减折旧函数DB双倍余额递减折旧函数DDB年限总和折旧函数SYD可变余额递减折旧函数VDB《Excel在财务中的应用》折旧计算函数常见参数说明Cost：固定资产原值Salvage：固定资产使用年限终了时的估计残值Life：固定资产进行折旧计算的周期总数Period：进行折旧计算的期次，必须与life使用的单位相同上一页《Excel在财务中的应用》一、五种折旧计算函数１.实例8计算超市货架的折旧金额【实例内容】：新风超市计划将现有一批已使用3年的货架转手卖出，3年前的购买价值为50,000元，使用年限为8年，预计报废价值为15,000元，使用直线折旧，固定余额递减折旧，双倍余额递减折旧，年限总和折旧和可变余额递减折旧5种不同的折旧方法计算出这批货架这3年的折旧金额。《Excel在财务中的应用》一、五种折旧计算函数【实例操作步骤】：步骤1：构造数据模型。如图6-16所示。图6-16例6.8数据模型《Excel在财务中的应用》一、五种折旧计算函数步骤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