财务应用-excel

1、Excel财务应用01本金利息累计求和(2009-11-27 10:43:00) 转载标签： 杂谈 企业在财务管理过程中，利息比较、本息累计、贴现、分期偿还等计算是必不可少的，在Excel中进行这些基本计算是非常方便的。下面以几个基本例子来说明如何应用。【例1】年利率为10%，银行存款60000元，计算10年后的本息合计金额。操作： 建立excel表格并输入相关数据。 在计算单元中输入公式“=-FV(B2,B3,0,B4,0)”。 确认计算，即可得到10年后的本息合计。见图：公式说明：FV是基于固定利率及等额分期付款的方式返回某项投资的未来值的函数。语法：FV(rate,nper,pmt,pv

2、,type)其中：rate为年利率，nper为偿还期或投资期，pmt为各期所应支付的金额，pv为现值，即从开始计算时的本金，type表示付款时间是期初还是期末，用0或1表示。本公式中的“-”号表示是从储户的角度来计算。【例2】公司要进行某项投资，一次性投入资金80万元，要求在15年内使资本增值为240万元，计算年投资回报率。 建立excel表格并输入相关数据。 在计算单元中输入公式“=RATE(B3,0,-B2,B4,0)”。 确认计算，即可计算出年投资回报率的最低限度。见图：公式说明：RATE 返回投资或贷款的每期实际利率。语法：RATE(nper,pmt,pv,fv,type)其中各参数的

3、表示含义与FV函数相同。【例3】在上例中，如果投资回报率为20%，投资不变的情况下，公司的投资回收期需要多少年。 建立excel表格并输入相关数据。 在计算单元中输入公式“=NPER(B4,-B2,B3,0)”。 确认计算，即可计算投资回收期。见图：公式说明：NPER 基于固定利率和等额分期付款方式，返回某项投资或贷款的期数。语法：NPER(rate,pmt,pv,fv,type)其中各参数的表示含义与FV函数相同。【例4】上例中如果期望未来值为800万元，投资回收率为40%，则公司在期初应投入多少资金。 建立excel表格并输入相关数据。 在计算单元中输入公式“=PV(B3,B4,0,-B2

4、,0)”。 确认计算，即可计算出起初投资金额。见图：公式说明：PV 函数返回投资的现值，现值为一系列未来付款的当前值的累积和。语法：PV(rate,nper,pmt,fv,type)其中各参数的表示含义与FV函数相同。Excel财务应用02计算贴现和回报(2009-11-29 10:22:00) 转载标签： 杂谈贴现问题实际上是累计问题的逆运算，它不是把现值累计成一个期望值，而是根据某个期望值来推定现值。【例1】设贴现率为10%，如果想在20年后获得本利和为15万元，现在需要存入银行多少钱。操作： 建立excel表格并输入相关数据。 在计算单元中输入公式“=PV(B3,B4,0,-B2,0)”

5、。 确认计算，即可计算出现值金额。见图：【例2】公司欲租用一套厂房，租期20年，年租金10万元，设年贴现率为6%，求为租用该厂房实际需要支付的金额。操作： 建立excel表格并输入相关数据。 在计算单元中输入公式“=PV(B3,B4,-B2,0,1)”。 确认计算，即可计算出实际支付金额。见图： 【例3】公司购进一台设备，价格为300万元，以3万元/月的租金出租，租期为3年，然后将设备转手，可以得到500万元，求所购设备的回报率。此例的贴现问题比前面多出了一个参数，具体的操作步骤如下： 建立excel表格并输入相关数据。 在计算单元中输入公式“=RATE(B4*12,B2,-B3,B5,1)”

6、。 确认计算，即可计算出回报率。见图： 公式及函数说明见前文Excel财务应用本金利息累计求和 Excel财务应用03计算分期偿还(2009-12-01 10:02:00) 转载标签： 杂谈分期偿还问题是指贷款的偿还过程，其实质还是累计计算。在日常生活中，人们越来越多地同银行的存贷业务打交道，比如房贷、车贷、教育贷款等。很多人对于月偿还金额的计算往往感到束手无策，通过正确使用Excel函数，则可以轻松搞定。【例1】欲采用按月分期付款的方式购买一套住房，房屋总价50万元，首付20万元，银行贷款的月息为0.8%，偿还期为20年，计算每月月末偿还的金额。操作： 建立excel表格并输入相关数据。 在

7、计算单元中输入公式“=PMT(B4,B5*12,-(B3-B2),0,0)”。 确认计算，即可计算出月末支付金额。见图：公式说明：PMT是基于固定利率及等额分期付款的方式，返回贷款的每期付款金额函数。语法：PMT(rate,nper,pv,fv,type)其中：rate贷款利率，nper为偿还期，pv为现值，即从开始计算时的本金。Fv为未来值，如果省略 fv，则假设其值为零，也就是一笔贷款的未来值为零。type表示付款时间是期初还是期末，用0或1表示。PMT函数返回的支付款项包括本金和利息，但不包括税款或某些与贷款有关的费用。rate和nper的单位应该一致，例如4年期利率12%的贷款，如果按

8、月支付，rate应该为12%/12，nper为4*12，而如果是按年支付，则rate应该为12%，nper为4。【例2】某公司银行贷款投资项目，银行规定分期偿还部分的金额为200万元。公司可以月支付28000元，贷款期限20年，当前利率为0.8%/月，如果采用抵押贷款的方式，计算可以从银行取得贷款的最高金额。由于采用抵押方式贷款，按实际贷款规则，在支付期和当前利率下，每月支付不足28000元，则多出来的部分就是抵押金额。操作： 建立excel表格并输入相关数据。 在B5单元中输入公式“=PMT(B3,B4*12,-B2,0,0)”，计算出月末需支付金额。 在B8单元中输入公式“=B7-B5”，

9、计算出每月可提供抵押金额。 在B10单元中输入公式“=B8/B3”，计算出抵押部分可贷款金额。 在B11单元中输入公式“=B10+B2”，计算出可贷款最高金额。见图： Excel财务应用04利率转换与计算(2009-12-06 10:11:00) 转载标签： 杂谈 利率（rate）是指投资或贷款的利率或贴现率。大多数的报价都是以月复利为名义利率，但当投资报告涉及回报或利率比较时，则经常使用年度实际回报进行比较，因此需要对利率进行转换。 【例1】某银行6个月的实际利率是8%，转换为等价的年度实际利率是多少。操作： 建立excel表格并输入相关数据。 在B4单元中输入公式“=12/B3”，计算出利

10、率计算频率。 在B5单元中输入公式“=EFFECT(B2*B4,B4)”。 确认计算，即可计算出年度实际利率。见图： 这个例子说明了如何将频率低于一年的实际利率转换成等价的年度实际利率，计算结果显然比28%=16%大，因为它不是简单的运算，其中包含了变换偿还期而不变换利率。公式说明：EFFECT 利用给定的名义利率和每年的复利期数，计算有效的年利率。表达式：EFFECT(nominal_rate,npery)其中 nominal_rate为名义利率，npery为每年的复利期数。这里需要注意的是，npery将被取整，尾数会被截掉。 【例2】上例中，求转换成的等价的名义利率为多少。操作： 建立ex

11、cel表格并输入相关数据。 在B6单元中输入公式“=NOMINAL(EFFECT(B2*B4,B4),B4)”。确认计算，即可计算出等价的名义利率。见图： 公式说明：NOMINAL 基于给定的实际利率和年复利期数，返回名义利率。表达式：NOMINAL(effect_rate,npery)其中 effect_reta为实际利率，npeay为每年的复利期数。注释：如果找不到这些函数，则需要加载分析工具库。操作步骤如下： 在Excel主窗口点击【工具】【加载宏】。 勾选【分析工具库VBA函数】，确定。Excel财务应用05计算贷款的实际成本(2009-12-11 09:49:00) 转载标签： 杂谈

12、 用于构建固定资产的大额长期贷款必须计算年还息额，并计入财务费用成本。贷款总额以银行的贷款合同为准，实际贷款利率有可能高于国家规定的贷款利率，成本核算时不考虑高于国家规定的利率部分。年利率乘以贷款总额即为还贷首期的还息金额，因贷款按年部分还本后，以后每年还息金额将逐年减少。为保证每年单位成本的均匀、合理性，有必要计算年平均还息额。 另外从银行贷款需要提供抵押，抵押要产生利息，而抵押利息最终归银行所有。加之银行还需要用户支付与贷款相关的服务费用，所有这些费用均要计算在贷款实际成本中。 【例1】某单位从银行取得一笔贷款，当前银行抵押贷款的名义利率为10%，该单位需要贷款250万元，银行允许偿还期不

13、超过6年，按月支付。此外银行还需要收取贷款前期安排费2%，账户服务费每月300元，求该单位年度实际应该支付贷款成本为多少。操作： 建立excel表格并输入相关数据。 在B10计算单元中输入公式“=B2*B6”，计算出开户费。 在B11计算单元中输入公式“=B7*B4*B5”，计算出账户服务费。 在B12计算单元中输入公式“=B2-B10”，计算出实际借款。 在B13计算单元中输入公式“=B4*B5”，计算出贷款期限。 在B14计算单元中输入公式“=NOMINAL(EFFECT(B3,1),B4)/12”，计算出实际利率。 在B15计算单元中输入公式“=PMT(B14,B13,-B2,0,0)”

14、，计算出月支付金额。 在B16计算单元中输入公式“=B15+B7”，计算出月实际支付金额。 在B17计算单元中输入公式“=RATE(B13,-B16,B12,0,0)”，计算出贷款月实际成本。见图： 从计算结果中可以看出，贷款人实际只得到了245万元的贷款，而实际支付由于包含月服务费等，因而变得很高。 Excel财务应用06计算实际支付利息(2010-01-01 10:16:00) 转载标签： 杂谈 在支付某具体贷款的过程中，往往需要了解具体还款中构成利息的部分是多少，利息在整个贷款过程中不是一个常数，它随着本金部分的变化而变化。 【例1】某公司在一项投资中获得一笔为期10年的贷款800万，年

15、息为9%，按年支付款项，求该公司最终还清所有贷款时，利息部分为多少。操作： 建立excel表格并输入相关数据。 在表中输入相关数据，其中各数据的时间段必须一致，即必须同时为年或同时为月。 在B6计算单元中输入公式“=IPMT(B2,B3,B4,-B5,0)”，计算出总利息。见图：函数说明：IPMT 函数是基于固定利率及等额分期付款方式，返回给定期数内对投资的利息偿还额的函数。语法：IMPT(rate,per,nper,pv,fv,type)其中，per用于计算其利息数额的期数，必须在1nper之间，nper为总投资期，即该项投资的付款期总数。其他参数的含义与前面介绍的函数相同。 有时候还需要知

16、道在某个连续的阶段中利息的金额，此时就需要使用另外一个函数。 【例2】某人欲从银行获得20年期贷款50万，已知银行的名义利率为8%，按月计复利，要求分期按月还贷，求用户第一年实际支付的利息为多少？操作： 建立excel表格并输入相关数据。 在B6计算单元中输入公式“=EFFECT(B5,B4)”，计算出实际利率。 在B9计算单元中输入公式“=CUMIPMT(B6,B3,B2,B7,B8,0)”，计算出期间利息额。见图：这里用到了CUMIPMT 函数，该函数返回一笔贷款在给定的期间累计偿还的利息数额。其中 start_period 为计算中的首期，end_period 为计算中的末期。语法：CU

17、MIPMT(rate,nper,pv,start_period,end_period,type) Excel财务应用07计算实际还款本金(2010-01-10 09:31:00) 转载标签： 杂谈 在支付某一具体贷款的过程中，用户除了希望知道利息是多少之外，也需要知道构成本金的部分是多少。 【例1】某公司在一项投资中获得一笔为期10年的贷款800万，年息为9%，按年支付款项，该公司具体还清所有贷款时，共支付了多少本金。操作： 建立excel表格并输入相关数据。 在表中输入相关数据，其中各数据的时间段必须一致，即必须同时为年或同时为月。 在B6计算单元中输入公式“=PPMT(B2,B3,B4,B

18、5,0)”，计算出本金部分。见图：PPMT 函数基于固定利率及等额分期付款方式，返回投资在某一给定期间内的本金偿还额。语法：PPMT(rate,per,nper,pv,fv,type) 通常情况下，用户还需要知道在某个连续的阶段中本金的总额，那就要用到另外一个函数。 【例2】某人欲从银行获得20年期贷款50万，已知银行的名义利率为8%，按月计复利，要求分期按月还贷，求用户第一年实际支付的本金为多少？操作： 建立excel表格并输入相关数据。 在B6计算单元中输入公式“=EFFECT(B5,B4)”，计算出实际利率。 在B9计算单元中输入公式“=CUMPRINC(B6,B3,B2,B7,B8,0

19、)”，计算出期间本金额。见图：CUMPRINC 函数返回一笔贷款在给定的 start_period 到 end_period 期间累计偿还的本金数额。语法：CUMPRINC(rate,nper,pv,start_period,end_period,type)计算出的结果为负数，这是从用户的角度来看的，用户是支出，所以显示为负数。 Excel财务应用08计算固定资产折旧（直线法）(2010-01-14 16:59:00) 转载标签： 杂谈 固定资产的使用寿命是有限的，在计算实际成本时，有必要将费用在使用年限内转化为成本，这个过程就是折旧。 企业会计准则第4号固定资产第十七条规定：“企业应当根据与

20、固定资产有关的经济利益的预期实现方式，合理选择固定资产折旧方法。 可选用的折旧方法包括年限平均法、工作量法、双倍余额递减法和年数总和法等。 固定资产的折旧方法一经确定，不得随意变更。但是，符合本准则第十九条规定的除外。” 以下就各种不同折旧方法的Excel函数应用，做一个举例。 Excel提供了一系列的折旧函数，用于计算不同折旧方法下的固定资产折旧值。目前使用较多的是直线折旧法（年限平均法），这是一种按照固定资产预计使用年限，进行平均分摊折旧值的方法。 【例1】某公司新购置一台价值50万元的设备，折旧年限测定为15年，残值为5万元，按直线法计算，每年的折旧值是多少？操作： 建立excel表格并

21、输入相关数据。 在B5计算单元中输入公式“=SLN(B2,B3,B4)”，计算出年折旧值。见图：SLN 函数返回某项资产在一个期间内的线性折旧值。公式语法：SLN(cost,salvage,life)其中 cost为资产原值，salvage为资产残值，life为折旧期限。此公式中的折旧年限为15年，如果改为180个月，则计算结果为月折旧额。 Excel财务应用09计算固定资产折旧（固定余额递减法）(2010-01-17 09:42:00) 转载标签： 杂谈 余额递减法是一种加速折旧法，即在预定的使用年限内，将后期折旧的一部分转移到前期，在前期加速消化折旧。 【例1】某公司新购置一台价值50万元

22、的设备，折旧年限测定为15年，残值为5万元，采用固定余额递减法折旧，每年的折旧值是多少？操作： 建立excel表格并输入相关数据。 在E3计算单元中输入公式“=DB($B$3,$B$4,$B$5,D3,12)”，计算出第一年的折旧金额。 将E3单元填充复制到E4:E17单元格，即可显示出如图所示的计算结果。见图：这里用到了DB函数来计算固定资产折旧，该函数使用固定余额递减法计算资产在给定期限内的折旧额。语法：DB(cost,salvage,life,period,month)公式中 cost为资产原值，salvage为资产残值，life为折旧期限，period为需要计算折旧值的期间，month为第一年的月份数，如果缺省则默认为12。 Excel财务应用10计算固定资产折旧（可变余额递减法）(2010-01-19 19:10:00) 转载标签： 杂谈 与固定余额递减法对应，还有可变余额递减法。可变余额递减法是指以不同倍率的余额递减