Excel-2000(第三讲)-财务函数.ppt

财务函数财务函数在Excel中提供了许多财务函数财务函数可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值，以及债券或股票的价值财务函数大体上可分为四类：•投资计算函数•折旧计算函数•偿还率计算函数•债券及其他金融函数它们为财务分析提供了极大的便利使用这些函数不必理解高级财务知识，只要填写变量值就可以了在下文中，凡是投资的金额都以负数形式表示，收益以正数形式表示一、折旧计算函数折旧：折旧：企业固定资产的使用寿命是有限的，在使用期间内会因为有形或无形的损耗而逐渐丧失其服务潜力，所以有必要将其成本在有限的使用年限内逐渐转化为费用，这个过程就叫固定资产折旧固定资产折旧与固定资产折旧折旧有关的几项因素：q成本成本：固定资产取得的成本q估计残值：估计残值：固定资产废弃时可收回的材料价值或处置价值，一般的扣除拆迁或处理费用q估计使用年限：估计使用年限：固定资产的总使用寿命、工作小时数或生产数量1．．平均法SLN（（cost,salvage,life）） 该函数使用平均法，每期提相同的折旧额，返回一项资产每期的折旧费其中cost为资产原值，salvage为资产在折旧期末的价值（也称为资产残值），1ife为折旧期限（有时也称作资产的生命周期）。

每期折旧额=（成本-残值）/使用期数例如，假设购买了一辆价值￥1,500,000的卡车，其折旧年限为10年，残值为￥250,000，那么每年的折旧额为： SLN（1500000,250000,10） 计算结果为：￥125,000平均法折旧的缺点：q各个使用周期所负担的使用成本前后不均匀因为：服务能力逐年下降，而维修费用则逐年上升这样，前几期享受较大的服务潜力，而负担较低的成本费用，后几期享受较小的服务潜力，而负担相同的成本费用 q只考虑使用年限，并没有考虑实际加班或减班的情况所以平均法（线性折旧法）较适合于生产情况大致相同，技术进步因素影响较少的资产，如：厂房、储藏柜等2．生产数量．生产数量法该方法是以固定资产来估计总生产量，再除其应折旧的总额，算出单位产量应负担的折旧额，然后再乘以每年的实际生产量，求得各期的折旧额这是一种根据产量的平均折旧法每年折旧额每年折旧额=当年实际生产量当年实际生产量*（成本（成本-残值）残值）/使用年限内估计总生产量使用年限内估计总生产量例如，假设购买了一台价值￥1,000,000的设备，估计可使用6年，总生产量为600,000,估计残值为￥250,000，那么每年的折旧额为： 当年实际生产量当年实际生产量* (1000000-250000)/600000=1.25*当年实际生产量当年实际生产量生产数量法的缺点：q只单纯以产量为依据，没考虑到有形或无形的损耗。

q总产量通常无法估计很准确3．工作时间．工作时间法该方法是以固定资产来估计总工作时间，再除其应折旧的总额，算出单位工作时间应负担的折旧额，然后再乘以每年的实际工作时间，求得各期的折旧额这是一种根据工作时间的平均折旧法每年折旧额每年折旧额=当年实际生产量当年实际生产量*（成本（成本-残值）残值）/使用年限内估计总生产量使用年限内估计总生产量例如，假设购买了一台价值￥1,000,000的设备，估计可使用6年，总生产量为600,000,估计残值为￥250,000，那么每年的折旧额为： 当年实际生产量当年实际生产量* (1000000-250000)/600000=1.25*当年实际生产量当年实际生产量工作时间法的缺点：q只单纯以工作时间为依据，没考虑到有形或无形的损耗q总工作时间通常无法估计很准确年数合计法：年数合计法：以固定资产的应折旧总额，乘以递减的分数，其分母为使用年数的合计数，分子则为各使用年次的相反顺序求得各项的折旧额公式为： （成本-残值）*（使用年限-期别+1）*2 —————————————————— 使用年限*（使用年限+1） 也叫加速折旧法，使前几期享受较大的服务潜力，而负担较高的成本。

也可使固定资产的帐面价值较接近于市价；前期多提折旧，可以降低企业早期应税金额，将应税时间往后递延，可以获得货币的时间价值SYD（（成本，残值成本，残值,使用年限使用年限,期别）期别） 例如，假设购买了一辆价值￥30,000的卡车，其折旧年限为10年，残值为￥7,500，那么第一年的折旧额为： 年 数 公式 计算结果 1 SYD（30000,7500,10，1） 4090.91 4．年数合计法．年数合计法固定余额递减法：依据固定余额递减法：依据固定资产的估计使用年数，按公式求出其折旧率，每年以固定资产的帐面价值，乘以折旧率来计算其当年的折旧额公式为： （成本-上一期累计折旧）*比率 其中：比率=1-5．固定余额递减法．固定余额递减法例如:成本为2000000元,残值为300000元,使用6年折旧比率= 0.271076626392523第一年折旧：2000000*0.271076626392523第二年折旧：(2000000-542153）* 0.271076626392523 第三年折旧: (2000000-542153-395188)* 0.271076626392523 使用函数使用函数DBDB（成本，残值（成本，残值, ,使用年限使用年限, ,期次，第一年的月数）期次，第一年的月数）最后一年的折旧额为：成本最后一年的折旧额为：成本-残值残值-前几期累计折旧前几期累计折旧DB(2000000,300000,6,1)DB(2000000,300000,6,2)DB(2000000,300000,6,3)DB(2000000,300000,6,4)DB(2000000,300000,6,5)DB（（成本，残值成本，残值,使用年限使用年限,期次，第一年的月数）期次，第一年的月数）第一年：第一年：DB(2000000,300000,6,1,5)第二年：第二年：DB(2000000,300000,6,2,5)第三年：第三年：DB(2000000,300000,6,3,5)第六年：第六年：DB(2000000,300000,6,6,5)第七年：第七年：DB(2000000,300000,6,7,5) 问题：如果使用期限问题：如果使用期限6年，第一年使用不满一年，例年，第一年使用不满一年，例如如5个月，第个月，第7个年头使用了个年头使用了7个月，折旧怎么算？个月，折旧怎么算？财务函数中常见的参数财务函数中常见的参数年金年金 : :在某一段连续时间内，一系列的固定金额给付活动。

例如汽车、房屋的分期付款就是年金的一种未来值未来值 (fv)(fv)--在所有付款发生后的投资或贷款的价值例如：零存整取的期末领回金额期间数期间数 ( (npernper) )--为总投资（或贷款）期，即该项投资或贷款的付款期总数付款付款 (pmt(pmt)--对于一项投资或贷款的定期支付数额其数值在整个年金期间保持不变通常 pmt 包括本金和利息，但不包括其他费用及税款现值现值 ( (pvpv) )--在投资期初的投资或贷款的价值例如，贷款的现值为所借入的本金数额利率利率 (rate)(rate)--投资或贷款的利率或贴现率类型类型 (type)(type)--付款期间内进行支付的间隔，如在期初或期末，用0或1表示,省略或0时表示期末给付，1期初给付二、年金函数1 1、年金期付款函数、年金期付款函数PMTPMTPMT函数可以计算为偿还一笔贷款，要求在一定周期内支付完时，每次需要支付的偿还额，也就是我们平时所说的“分期付款”比如借购房贷款或其它贷款时，可以计算每期的偿还额PMT函数基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额语法：语法：PMT(PMT(利率，总投资期数，现值, ,未来值, ,期初或期末) ) 或者或者 PMT(ratePMT(rate，nper，pv,fv,type,fv,type) ) 例如：需要10个月付清的年利率为8%的￥10,000贷款的月支额为： PMT（8%/12,10，10000） 计算结果为：-￥1,037.03。

例如：若想在两年后能存款100，000元以便出国留学，假设活期存款利率为3.0%,问：每个月要存入多少金额，才能于两年后领回100，000元？ PMT（3%/12,24,0，100000，1） 计算结果为：-￥4,0382 2、付款利息、付款利息IPMTIPMT（）（）I IPMT函数基于固定利率及等额分期付款方式，返回投资或贷款的某期付款中的利息金额语法：语法：IPMT(IPMT(利率，第几期，总期数，现值, ,未来值, ,期初或期末) ) 例如：需要10个月付清的年利率为8%的￥10,000贷款的第3月支付利息额为： IPMT（8%/12,3，10，10000） 计算结果为：￥-53.69 3、付款本金、付款本金PPMTPPMT（）（）P PPMT函数基于固定利率及等额分期付款方式，返回投资或贷款的某期付款中的本金金额语法：语法：PPMT(PPMT(利率，第几期，总期数，现值, ,未来值, ,期初或期末) )例如：需要10个月付清的年利率为8%的￥10,000贷款的第3月支付本金额为： PPMT（8%/12,3，10，10000） 计算结果为：￥-983.35。

4 4、累计利息、累计利息CUMIPMTCUMIPMT（）（）CUMIPMT函数基于固定利率及等额分期付款方式，返回开始期次到结束期次间累计偿还的利息金额语法语法：：CUMIPMT(CUMIPMT(利率，总期数，现值,开始期次，结束期次，未来值, ,期初或期末) ) 例如：需要10个月付清的年利率为8%的￥10,000贷款的第3月到第8月支间累计偿还的利息金额为： CUMIPMT（8%/12,10，10000，3，8,0） 计算结果为：-222.899  5 5、累计本金、累计本金CUMPRINCCUMPRINC（）（）CUMPRINC函数基于固定利率及等额分期付款方式，返回开始期次到结束期次间累计偿还的本金金额语法：语法：CUMPRINC(CUMPRINC(利率，总期数，现值,开始期次，结束期次，未来值, ,期初或期末) ) 例如：需要10个月付清的年利率为8%的￥10,000贷款的第3月到第8月支间累计偿还的本金金额为： CUMPRINC（8%/12,10，10000，3，8,0）计算结果为：-5999.29 6 6、期次、期次NPERNPER（）（）NPERNPER函数基于固定利率及等额分期付款方式，返回偿还全部贷款的期次。

语法：语法：NPER(NPER(利率，每期给付金额，现值,未来值, ,期初或期末) ) 例如：年利率为8%的￥100,000贷款，每月给付1000元，需要多少个月才能还清？NPER（8%/12,-1000，100000，0，1） 计算结果为：163.36 注意注意:公式中每月给付是负值公式中每月给付是负值. 7 7、利率、利率RATERATE（）（）RATERATE函数返回年金的利率RATE(RATE(总期数，每次给付金额，现值, 未来值, ,期初或期末，猜测值) ) 猜测值如果省略，默认值为10%例如：100,000贷款，每月给付1000元，163个月还清,问这项贷款的年利率为多少？RATE（163，-1000，100000，0，1）= 0.6667%年利率=12*0.6667%=8% 注意注意:公式中每月给付是负值公式中每月给付是负值.例如：100,000贷款，163个月后一次性还120000,问这项贷款的年利率为多少?RATE(163,0,100000,-120000,1)=0.1119%年利率=12*0.1119%=1.34% RATE(总期数，每次给付金额，现值, 未来值,期初或期末，猜测值) 三、未来值三、未来值1 1、、FVFV函数函数: :固定利率的未来值固定利率的未来值FV函数基于固定利率及等额分期付款方式，返回某项投资的未来值。

语法：语法：FV(rate,nper,pmt,pv,typeFV(rate,nper,pmt,pv,type) )Rate：各期利率，是一固定值；Nper：总投资（或贷款）期，即该项投资（或贷款）的付款期总数；Pmt:各期所应付给（或得到）的金额，其数值在整个年金期间（或投资期内）保持不变；Pv:现值，也称为本金，如果省略，则假设其值为零;Type:期初或期末付款,为数字1或0，如果省略t，则假设其值为零语法：语法：FV(rate,nper,pmt,pv,typeFV(rate,nper,pmt,pv,type) )例如：假如某人两年后需要一笔比较大的学习费用支出，计划从现在起每月初存入2000元，如果按年利2.25%，按月计息（月利为2.25%/12），那么两年以后该账户的存款额会是多少呢？公式写为：FV(2.25%/12, 24,-2000,0,1)该例中没有的现值为0,如果该例中该帐户原有金额20000元,其它条件不变,那么两年以后该账户的存款额会是多少呢？公式为：公式为：FV(2.25%/12, 24,-2000,20000,1)2 2、、FVSCHEDULEFVSCHEDULE函数函数计算基于非固定利率投资的未来值。

语法：语法： FVSCHEDULE(FVSCHEDULE(本金本金, ,利率数组利率数组) )例如：假如存入1,000,000元,利息以月计算,存入后的一年内,各月的利率如表所示,一年后本利合计多少钱?月份月份月利率月利率1 10.300%0.300%2 20.292%0.292%3 30.308%0.308%4 40.292%0.292%5 50.300%0.300%6 60.288%0.288%7 70.296%0.296%8 80.296%0.296%9 90.292%0.292%10100.300%0.300%11110.300%0.300%12120.304%0.304%四、现值四、现值1 1、、PV PV 函数函数PV函数用来计算某项固定年金的现值年金现值就是未来各期年金现在的价值的总和投资分析思想投资分析思想:如果投资收益的现值大于投资的成本如果投资收益的现值大于投资的成本价值，则这项投资是有合算的，否则不合算价值，则这项投资是有合算的，否则不合算例如:今年的一万元一定比明年的一万元有价值,所以,如果在未来连续的20年间,每年年底给10000元,以现在的角度来看,其值并非200,000元要想知道到底为多少,就可使用PV函数,假设年利率4.5%.则其现值为:PV(4.5%,20,10000,0,0)=-130079.36语法：语法：PV(rate,nper,pmt,fv,typePV(rate,nper,pmt,fv,type) ) Rate:各期利率。

Nper:投资（或贷款）的付款期总数Pmt:各期所应支付的金额，整个年金期间保持不变Fv: 未来值，或在最后一次支付后希望得到的现金余额，如果省略fv，则假设其值为零（一笔贷款的未来值即为零）Type:用以指定各期的付款时间是在期初还是期末语法：语法：PV(rate,nper,pmt,fv,typePV(rate,nper,pmt,fv,type) ) 例1，假设要购买一项保险年金，该保险可以在今后二十年内于每月末回报￥600此项年金的购买成本为80,000，假定投资回报率为8%该项年金的现值为：PV(0.08/12, 12*20,600,0) 计算结果为：￥-71,732.58负值表示这是一笔付款，也就是支出现金流年金（￥-71,732.58）的现值小于实际支付的（￥80,000）因此，这不是一项合算的投资例2 某房屋当前售价3,500,000元,预计4年后售出,扣除税金和手续费后可净得5,000,000元,问此屋是否值得购买(当前的年利率为5.2%)?问题实质是根据未来值求现值Pv(5.2%,4,0,5000000,0)=-4,082,320也可以用未来值进行比较FV(5.2%,4,0,3500000,0)=-4,286,778.10例3，假设要购买一项保险年金，该保险可以在第1年末回报￥3000,第2年末回报￥5000,第3到第7年每年末领回8000元,第8年和第9年没有收益,第10年末领回20000元。

此项年金的购买成本为50,000，假定投资回报率为8%那么该项年金的是否值得购买?语法：语法：PV(rate,nper,pmt,fv,type)需要用NPV函数来计算上述收益的现值.NPV函数基于一系列现金流和固定的各期贴现率，返回一项投资的现值2 2、求非固定年金的现值、求非固定年金的现值NPVNPVNPV函数基于一系列现金流和固定的各期贴现率，返回一项投资的现值语法：NPV(rate,value1,value2, ...)Rate:为各期贴现率，是一固定值；value1,value2,...代表1到29笔支出及收入的参数值，value1,value2,...所属各期间的长度必须相等，而且支付及收入的时间都发生在期末NPV按次序使用value1,value2，来注释现金流的次序所以一定要保证支出和收入的数额按正确的顺序输入例1.假设要购买一项保险年金，该保险可以在第1年末回报￥3000,第2年末回报￥5000,第3到第7年每年末领回8000元,第8年和第9年没有收益,第10年末领回20000元此项年金的购买成本为50,000，假定投资回报率为8%那么该项年金的是否值得购买?NPV(8%,3000,5000,8000, 8000, 8000, 8000, 8000,0,0,20000)=43,713.1843,713.18<50000,不值得购买。

例2.假设开一家电器经销店初期投资￥200,000，而未来五年中各年的预期收入分别为￥20,000、￥40,000、￥50,000、￥80,000和￥120,000假定每年的贴现率是8%则投资收益的现值公式是：NPV(8%,20000,40000,50000,80000,120000)= 232,976.063 3、净现值、净现值净现值:一项投资净收益的现值.XNPV函数基于一系列现金流和固定的各期贴现率，返回一项投资的净现值语法：XNPV(rate,values,dates) Rate:各期贴现率，是一固定值；Values:现金流量；Dates:代表日期在现金流量中第一个数字为购入成本，为负值，若无购入成本应该以0代替，且必须输入开始日期例如：您于2000/2/5花了200，000买了一项投资计划，可按下表获得收益，年利率为5.2%该投资的净现值为：XNPV(B1,B4:B9,A4:A9)=83329.79日期收益2002/2/545，0002002/8/555，0002003/2/560，0002004/2/570，0002005/2/575，000报酬率:某项投资所能获得的利率,也称为内部报酬率。

如果某项投资的内部报酬率>当前的存款利率,则此项投资是合算的1、定期现金流量的内部报酬率IRR（现金流量，猜测值）每期的现金流量不一定相等，但必须是等间距发生，如：同为每月一次或每年一次；现金流量的第一个值必须是购入成本的支出，以负值表示，后接几个属于收入的现金流量例如：用200,000买了一个投资计划，可分五年领回下列金额：50，000，60，000， 65，000 70，000 80，000，请计算其内部报酬率 五、报酬率五、报酬率2、非定期现金流量的内部报酬率XIRR（现金流量，日期，猜测值）每期的现金流量不一定相等，现金流的发生可以是非定期发生；现金流量的第一个值必须是购入成本的支出，以负值表示，后接的现金流量必须按顺序排列例如：您于2000/2/5花了200，000买了一项投资计划，可按下表获得收益，年利率为5.2%,该投资是否合算? 内部报酬率为:XIRR(B5:B10,A5:A10)日期收益2002/2/545，0002002/8/555，0002003/2/560，0002004/2/570，0002005/2/575，0003、融资及转投资的内部报酬率以融资方式取得资金,进行一项投资,又将所获取的收入转投到另一投资,前一项要付给别人融资利息,后一项获取转投资利息,有支出也有收入,收益如何不知道,则可用MIRR函数来计算内部报酬率。

MIRR（现金流量，融资利率，转投资利率） 。