第十一章EXCEL在会计中的应用2

1、（1 1）函数格式：）函数格式：=NPV(rate=NPV(rate，inflow1inflow1，inflow2inflow2，inflow29)inflow29)RateRate：表示利息率：表示利息率inflow1inflow1，inflow2inflow2，inflow29inflow29：各个时期现金流：各个时期现金流量，最多量，最多2929个个inflowinflow值。值。（2 2）函数功能：）函数功能：根据给定的折现率，求出未来各期根据给定的折现率，求出未来各期支出（负值）和收入（正值）的当前值的总和。支出（负值）和收入（正值）的当前值的总和。 niiirvaluesNPV1)

2、1(1净现值函数净现值函数NPV：将未来现金流量折算为现值：将未来现金流量折算为现值的函数。的函数。例如：例如：某一投资项目预期经济寿命为某一投资项目预期经济寿命为6年，资金成年，资金成本率为本率为7%，期初需投资，期初需投资250万元，万元，1到到6年年末年年末的现金流入分别为：的现金流入分别为：40，50，60，70，80，90，100万元，评价项目是否可行万元，评价项目是否可行。=NPV（7%，40，50，60，70，80，90，100）-250函数格式：函数格式：=IRR(values=IRR(values，guess)guess)ValuesValues：现金流入、流出值，它是一个数

3、组或者是含有数组的单元：现金流入、流出值，它是一个数组或者是含有数组的单元格区域的引用，它必须有一个正数和一个负数。格区域的引用，它必须有一个正数和一个负数。GuessGuess：是是对函数对函数 IRR IRR 计算结果的估计值，默认计算结果的估计值，默认0.10.1。例如上例，计算内部报酬率。例如上例，计算内部报酬率。0)1(0 niiirvaluesNPV2内部报酬率：内部报酬率：IRR函数函数,是使投资净现值等于零的是使投资净现值等于零的投资报酬率。即投资回收的当前价值正好等于投资投资报酬率。即投资回收的当前价值正好等于投资成本的利息率。成本的利息率。 3 3内部利息率内部利息率RAT

4、ERATE：用来确定投资（或贷款）的利率用来确定投资（或贷款）的利率 。函数格式：函数格式：RATE(nper,pmt,pv,fv,type,guess)RATE(nper,pmt,pv,fv,type,guess)NperNper：总投资（或贷款）期，即该项投资（或贷款）的付款：总投资（或贷款）期，即该项投资（或贷款）的付款期总数。期总数。PmtPmt：每期付款额，其数值在整个投资期内保持不变。：每期付款额，其数值在整个投资期内保持不变。PvPv ：现值，即从该项投资（或贷款）开始时已经入账的款：现值，即从该项投资（或贷款）开始时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金。项

5、，或一系列未来付款当前值的累积和，也称为本金。FvFv：为未来值，或在最后一次付款后希望得到的现金余额，：为未来值，或在最后一次付款后希望得到的现金余额，缺省为零。缺省为零。TypeType：表示何时开始偿还，：表示何时开始偿还，0 0表示投资结束时偿还，表示投资结束时偿还，1 1表示投表示投资开始时偿还，默认为资开始时偿还，默认为0 0。GuessGuess：表示初始值。：表示初始值。例如：金额为例如：金额为 200000 元的元的 15年期贷款，月末还款年期贷款，月末还款额为额为 1500元，该笔贷款的年利率为：元，该笔贷款的年利率为：=RATE(15*12,-1500,200000)*1

6、2例如：每例如：每月末月末存款存款700元，要想在元，要想在10年后获得年后获得100000，则要求年利率为：，则要求年利率为：=RATE(10*12, -700, 0,100000)*12每每月初月初存款存款=RATE(10*12, -700, 0,100000，1)*124.4.分期偿还额函数分期偿还额函数PMT：基于固定利率及等额分期付款方式，返回投资或贷款的每基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。期付款额。函数格式：函数格式：= = PMT(rate,nper,pv,fv,type)Rate ：各期利率，是一固定值。：各期利率，是一固定值。Nper ：总投资（或贷

7、款）期，即该项投资（或贷款）的：总投资（或贷款）期，即该项投资（或贷款）的付款期总数。付款期总数。Pv ：现值，即从该项投资（或贷款）开始计算时已经入：现值，即从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本账的款项，或一系列未来付款当前值的累积和，也称为本金。金。Fv ：为未来值，或在最后一次付款后希望得到的现金余：为未来值，或在最后一次付款后希望得到的现金余额，如果省略额，如果省略 fv，则假设其值为零。，则假设其值为零。例如：向银行贷款例如：向银行贷款180,000 元，需要按元，需要按15年付清，年付清，年率为年率为.02% ，则月支付额为：，则月

8、支付额为：=PMT(0.0502/12, 15*12, -180000)例如：如果需要以按月定额存款方式在例如：如果需要以按月定额存款方式在 10 年中存年中存款款 50,000，假设存款年利率为，假设存款年利率为 %，则月应存款，则月应存款额为：额为：=PMT(0.03/12, 10*12, 0, -50000)5 5 现值函数现值函数PVPV：将各期现金折算到当前的价值。：将各期现金折算到当前的价值。函数格式：函数格式：=PV(rate,nper,pmt,fv,type)=PV(rate,nper,pmt,fv,type) 例如：设要购买一项保险例如：设要购买一项保险年金，该保险可以在年金

9、，该保险可以在今后二十年内于每今后二十年内于每月月末月月末回报回报10001000元。此项年金元。此项年金的购买成本为的购买成本为120,000120,000元，假定要求投资回报率元，假定要求投资回报率为为 8.1%8.1%。现在可以通过函数。现在可以通过函数 PV PV 计算一下这笔计算一下这笔投资是否值得。该项年金的现值为投资是否值得。该项年金的现值为: : =PV(0.081/12,12 =PV(0.081/12,12* *20,1000,0)20,1000,0)6 利息额利息额IPMT:基于固定利率及等额分期付款方式，返回基于固定利率及等额分期付款方式，返回给定期数内对投资的利息偿还额

10、。给定期数内对投资的利息偿还额。IPMT(rate,per,nper,pv,fv,type)Rate：各期利率。：各期利率。Per ：用于计算利息数额的期数，必须在：用于计算利息数额的期数，必须在 1 到到 nper 之间。之间。Nper ：总投资期，即该项投资的付款总期数。：总投资期，即该项投资的付款总期数。Pv ：现值，即从该项投资开始计算时已经入帐的款项，：现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。或一系列未来付款的当前值的累积和，也称为本金。Fv ：未来值，或在最后一次付款后希望得到的现金余额。：未来值，或在最后一次付款后希望得到的现金余

11、额。如果省略如果省略 fv为零（例如，一笔贷款的未来值即为零）。为零（例如，一笔贷款的未来值即为零）。Type：数字：数字 0 或或 1，用以指定各期的付款时间是在期初，用以指定各期的付款时间是在期初还是期末。如果省略还是期末。如果省略 type，则假设其值为零。，则假设其值为零。例如例如:假如向银行贷款假如向银行贷款180000万元万元,分分5年每年末等额还款年每年末等额还款,年利率年利率5.02%,求求各年还款总额、本金及利息分别为多少各年还款总额、本金及利息分别为多少? 每期还款每期还款 各期利息各期利息 每期本金每期本金1年年:=PMT(0.0502, 5, -180000) = IP

12、MT(5.02%, 1, 5,-180000) (1)-(2) 2年年:=PMT(0.0502, 5, -180000) = IPMT(5.02%, 2, 5,-180000) (1)-(2) 3年年:=PMT(0.0502, 5, -180000) = IPMT(5.02%, 3, 5,-180000) (1)-(2) 4年年:=PMT(0.0502, 5, -180000) = IPMT(5.02%, 4, 5,-180000) (1)-(2) 5年年:=PMT(0.0502, 5, -180000) = IPMT(5.02%, 5, 5,-180000) (1)-(2) 7双倍余额递减法

13、折旧法双倍余额递减法折旧法DDB：使用双倍余额递减法或其他指：使用双倍余额递减法或其他指定方法，计算一笔资产在给定期间内的折旧值。定方法，计算一笔资产在给定期间内的折旧值。DDB(cost,salvage,life,period,factor)Cost :资产原值。资产原值。Salvage :资产残值资产残值Life :折旧期限折旧期限Period ：需要计算折旧值的期间。：需要计算折旧值的期间。Period 必须使用与必须使用与 life 相相同的单位。同的单位。Factor ：余额递减速率。：余额递减速率。如果如果 factor 被省略，默认为被省略，默认为 2（双倍余额递减法）。（双倍余

14、额递减法）。例如：例如：=DDB(10000,1000,5*12,5)8其他函数其他函数FV ISPMT MIRR MPER SLN SYDVDB例例:已知已知A产品的目标利润，根据其售价、单产品的目标利润，根据其售价、单位变动成本、固定成本、销售数量中的任意位变动成本、固定成本、销售数量中的任意四个值求另一个值。四个值求另一个值。利润销售数量利润销售数量*(售价售价-单位变动成本单位变动成本)-固定成本固定成本（）建立本量利分析模型（）建立本量利分析模型（）工具（）工具单变量求解单变量求解例例2 假定某公司原有一套生产设备的主机是假定某公司原有一套生产设备的主机是5年前购入的，年前购入的，原

15、购置成本为原购置成本为200000元，估计尚可使用元，估计尚可使用5年，期满无残值，年，期满无残值，已提折旧已提折旧100000元元(直线折旧法直线折旧法)，账面价值为，账面价值为120000元。元。若继续使用旧主机，每年可获销售收入净额若继续使用旧主机，每年可获销售收入净额298000元，每元，每年付现的直接材料、直接人工和变动制造费用等营业成本年付现的直接材料、直接人工和变动制造费用等营业成本为为218000元。现该公司为了提高产品的产量和质量，准备元。现该公司为了提高产品的产量和质量，准备更换一台装有电脑自动控制设备的新主机，设备价款为更换一台装有电脑自动控制设备的新主机，设备价款为30

16、0000元，估计可使用元，估计可使用6年，期满估计残值为年，期满估计残值为15000元。购元。购入新主机时，旧主机可作价入新主机时，旧主机可作价70000元。使用新主机后，每元。使用新主机后，每年销售收入年销售收入350000元，付现的营业成本为元，付现的营业成本为220000元。假定元。假定基本贴现率为基本贴现率为9%。要求：采用净现值法和内部报酬率法，。要求：采用净现值法和内部报酬率法，为该公司作出是继续使用旧主机，还是更换新主机的决策为该公司作出是继续使用旧主机，还是更换新主机的决策分析。分析。(1) (1) 计算结果为净现值计算结果为净现值21167.1721167.17元大于零，元大

17、于零，故更新投资方案是可行的。故更新投资方案是可行的。(2)(2)计算结果为内部报酬率计算结果为内部报酬率14.74%14.74%，大于，大于9%9%证明更新方案是可行的。证明更新方案是可行的。例例4 4 华神公司计划在浦东建汽车零配件厂，在这一投资华神公司计划在浦东建汽车零配件厂，在这一投资项目中涉及一项项目中涉及一项5050亩土地亩土地3030年使用权的决策。方案一，年使用权的决策。方案一，付现付现10001000万元买断使用权，因资金紧缺，需向银行借款，万元买断使用权，因资金紧缺，需向银行借款，借款利率借款利率8%8%。方案二，每年末支付租金。方案二，每年末支付租金100100万元。要求

18、：万元。要求：为实达公司作出是借款买断还是租赁的决策。为实达公司作出是借款买断还是租赁的决策。该题可用两个思路解答：该题可用两个思路解答：（1 1）求出每年末支付）求出每年末支付100100万元租金的内部利息率低于银万元租金的内部利息率低于银行利率则应租赁，否则应买断使用权。行利率则应租赁，否则应买断使用权。（2 2）以银行利率）以银行利率8%8%将将10001000万元贷款折算为分期偿还贷款万元贷款折算为分期偿还贷款本金与每年末支付租金本金与每年末支付租金100100万元比较，比万元比较，比100100万元大则应万元大则应租赁，否则应买断使用权。租赁，否则应买断使用权。651 计算分期付款利

19、息率计算分期付款利息率=RATE(B3,B2,-B1)=9.31% 8%选择一次付款选择一次付款2 计算一次性付款折算成分期偿还银行贷款计算一次性付款折算成分期偿还银行贷款:=PMT(B4,30,-1000)=88.83100选择一次付款选择一次付款例例5 5 假定远大公司准备购买一间办公楼，现有两个假定远大公司准备购买一间办公楼，现有两个方案供选择方案供选择: :一是购入，需付款一是购入，需付款200200万元。另一方万元。另一方案是，先支付案是，先支付6060万元，每月末付万元，每月末付3 3万元，万元，5 5年内付年内付清，总共支付清，总共支付240240万元。假定基准贴现率为万元。假定

20、基准贴现率为6%6%。要。要求求:为远大公司购买办公楼是一次付款还是分期付为远大公司购买办公楼是一次付款还是分期付款的决策分析。款的决策分析。 思路：思路：(1)(1)将一次性付款金额当作投资额或者借款将一次性付款金额当作投资额或者借款额，那么，按照基本贴现率求出的分期偿还额与分额，那么，按照基本贴现率求出的分期偿还额与分期付款额进行比较，数额小的方案为优。期付款额进行比较，数额小的方案为优。(2)(2)将分期付款额折算成现值，与一次性付款额比将分期付款额折算成现值，与一次性付款额比较，较小者为优。较，较小者为优。(1)(1)一次性付款与分期付款相比，前一方案一次性一次性付款与分期付款相比，前

21、一方案一次性支出支出140140万元万元(200-60)(200-60)，按照资金成本，按照资金成本6%6%投资其投资其他项目，那么，每月可以获得多少收益呢他项目，那么，每月可以获得多少收益呢? ?如果如果此项收益大于分期付款额，应该选择后一种方案。此项收益大于分期付款额，应该选择后一种方案。 公式如下公式如下: : =PMT(6%/12,5 =PMT(6%/12,5* *12,-(200-60)12,-(200-60) 结果显示每月分期偿还额为结果显示每月分期偿还额为2.712.71万元，该值小万元，该值小于分期付款额于分期付款额3 3万元。因此选择一次性付款有利。万元。因此选择一次性付款有

22、利。 (2)(2)将分期付款额在未来将分期付款额在未来5 5年内所付出的金额年内所付出的金额折算成当前值，与一次性付款额进行比较，折算成当前值，与一次性付款额进行比较，选择值小的方案为优。公式如下选择值小的方案为优。公式如下: : =PV(6%/12=PV(6%/12，5 5* *1212，-3)+60-3)+60 计算结果为计算结果为215.18万元，也就是说分期付万元，也就是说分期付款额折算成现值大于一次性付款款额折算成现值大于一次性付款200万元，万元， 例例6 6：在例：在例5 5中应选择一次性付款较为有利，中应选择一次性付款较为有利，这是基于贴现率这是基于贴现率6%6%为前提的。如果

23、基准贴为前提的。如果基准贴现率发生变化，会得出怎样的结果。现率发生变化，会得出怎样的结果。 这便需要用到敏感性分析，它主要用来探这便需要用到敏感性分析，它主要用来探讨如果与决策有关的某一个或某两个因素讨如果与决策有关的某一个或某两个因素发生变动，那么该项决策的预期结果会发发生变动，那么该项决策的预期结果会发生怎样的结果。生怎样的结果。 例例7 7：在例：在例5 5中分期付款额折算成现值，如果中分期付款额折算成现值，如果大于大于200200万元，采用分期付款方式是不利的。万元，采用分期付款方式是不利的。反之，小于反之，小于200200万元则是有利的。影响现值万元则是有利的。影响现值的因素有贴现率

24、、分期付款额、分期付款的因素有贴现率、分期付款额、分期付款期数。假定分期数。假定分6060期付完，这一因素不变，期付完，这一因素不变，那么，在不同的利率水平，不同分期付款那么，在不同的利率水平，不同分期付款额才下决策？额才下决策？例例8：假定甲公司从某租赁公司融资租入固定资产，该固：假定甲公司从某租赁公司融资租入固定资产，该固定定700000（元）。甲公司在每期期末向该租赁公司支付（元）。甲公司在每期期末向该租赁公司支付租金租金150000 元，共需支付元，共需支付6 期。租赁期满，甲公司享有期。租赁期满，甲公司享有该设备的优惠购买选择权，价格为该设备的优惠购买选择权，价格为 100元。租赁合

25、同规定元。租赁合同规定7%利率。利率。一一 、未确定融资租赁的计算、未确定融资租赁的计算（1）计算最低租赁付款额。计算最低租赁付款额）计算最低租赁付款额。计算最低租赁付款额=各期各期租金之和租金之和+期末购买价格期末购买价格=150000*6+100=900100（2）计算最低租赁付款额现值。最低租赁付款额现值）计算最低租赁付款额现值。最低租赁付款额现值=150000*（P/A，7%，6）+100*（P/F，7%，6）=715116.6（ 3）计算未确认融资费用。由于最低租赁付款额）计算未确认融资费用。由于最低租赁付款额现值现值715116.6 元大于该固定资产账面价值元大于该固定资产账面价值

26、 700000元，所以该固定资产以元，所以该固定资产以 700000元入账。元入账。长期应付款为长期应付款为900100元，元，未确认融资费用未确认融资费用=900100-700000=200100元。元。借：固定资产借：固定资产-融资租入固定资产融资租入固定资产700000 未确认融资费用未确认融资费用 200100 贷：长期应付款贷：长期应付款-应付融资租赁款应付融资租赁款900100二二 未确认融资费用分摊未确认融资费用分摊（1）确定未确认融资费用分摊率。最低租赁付款额现值）确定未确认融资费用分摊率。最低租赁付款额现值=租租赁资产入账价值赁资产入账价值=150000*（P/A，R，6）+100*（P/F，R，6）=700000。用插值法计算。用插值法计算R=7.7%（2）未确认融资费用的分摊。在计算出分摊率以后，采用实