Excel第三讲财务函数省公共课一等奖全国赛课获奖课件.pptx

财务函数财务函数在Excel中提供了许多财务函数。财务函数能够进行一般财务计算，如确定贷款支付额、投资未来值或净现值，以及债券或股票价值。财务函数大致上可分为四类：投资计算函数折旧计算函数偿还率计算函数债券及其它金融函数。它们为财务分析提供了极大便利。使用这些函数无须了解高级财务知识，只要填写变量值就能够了。在下文中，凡是投资金额都以负数形式表示，收益以正数形式表示。第1页一、折旧计算函数折旧：折旧：企业固定资产使用寿命是有限，在使用期间内会因为有形或无形损耗而逐步丧失其服务潜力，所以有必要将其成本在有限使用年限内逐步转化为费用，这个过程就叫固定资产折旧。固定资产折旧。与固定资产折旧折旧相关几项原因：q成本成本：固定资产取得成本q预计残值：预计残值：固定资产废弃时可收回材料价值或处置价值，普通扣除拆迁或处理费用。q预计使用年限：预计使用年限：固定资产总使用寿命、工作小时数或生产数量。第2页1平均法SLN（cost,salvage,life）该函数使用平均法，每期提相同折旧额，返回一项资产每期折旧费。其中cost为资产原值，salvage为资产在折旧期末价值（也称为资产残值），1ife为折旧期限（有时也称作资产生命周期）。每期折旧额=（成本-残值）/使用期数比如，假设购置了一辆价值￥1,500,000卡车，其折旧年限为，残值为￥250,000，那么每年折旧额为：SLN（1500000,250000,10）计算结果为：￥125,000。平均法折旧缺点：q各个使用周期所负担使用成本前后不均匀。因为：服务能力逐年下降，而维修费用则逐年上升。这么，前几期享受较大服务潜力，而负担较低成本费用，后几期享受较小服务潜力，而负担相同成本费用。q只考虑使用年限，并没有考虑实际加班或减班情况。所以平均法（线性折旧法）较适合于生产情况大致相同，技术进步原因影响较少资产，如：厂房、储备柜等。第3页2生产数量生产数量法该方法是以固定资产来预计总生产量，再除其应折旧总额，算出单位产量应负担折旧额，然后再乘以每年实际生产量，求得各期折旧额。这是一个依据产量平均折旧法。每年折旧额每年折旧额=当年实际生产量当年实际生产量*（成本（成本-残值）残值）/使用年限内预计总生产量使用年限内预计总生产量比如，假设购置了一台价值￥1,000,000设备，预计可使用6年，总生产量为600,000,预计残值为￥250,000，那么每年折旧额为：当年实际生产量当年实际生产量*(1000000-250000)/600000=1.25*当年实际生产量当年实际生产量生产数量法缺点：q只单纯以产量为依据，没考虑到有形或无形损耗。q总产量通常无法预计很准确。第4页3工作时间工作时间法该方法是以固定资产来预计总工作时间，再除其应折旧总额，算出单位工作时间应负担折旧额，然后再乘以每年实际工作时间，求得各期折旧额。这是一个依据工作时间平均折旧法。每年折旧额每年折旧额=当年实际生产量当年实际生产量*（成本（成本-残值）残值）/使用年限内预计总生产量使用年限内预计总生产量比如，假设购置了一台价值￥1,000,000设备，预计可使用6年，总生产量为600,000,预计残值为￥250,000，那么每年折旧额为：当年实际生产量当年实际生产量*(1000000-250000)/600000=1.25*当年实际生产量当年实际生产量工作时间法缺点：q只单纯以工作时间为依据，没考虑到有形或无形损耗。q总工作时间通常无法预计很准确。第5页年数累计法：以固定资产应折旧总额，乘以递减分数，其分母为使用年数累计数，分子则为各使用年次相反次序求得各项折旧额。公式为：（成本-残值）*（使用年限-期别+1）*2 使用年限*（使用年限+1）也叫加速折旧法，使前几期享受较大服务潜力，而负担较高成本。也可使固定资产帐面价值较靠近于市价；前期多提折旧，能够降低企业早期应税金额，将应税时间往后递延，能够取得货币时间价值。SYD（成本，残值,使用年限,期别）比如，假设购置了一辆价值￥30,000卡车，其折旧年限为，残值为￥7,500，那么第一年折旧额为：年 数 公式 计算结果 1 SYD（30000,7500,10，1）4090.91。4年数累计法年数累计法第6页固定余额递减法：依据固定余额递减法：依据固定资产预计使用年数，按公式求出其折旧率，每年以固定资产帐面价值，乘以折旧率来计算其当年折旧额。公式为：（成本-上一期累计折旧）*比率 其中：比率=1-5固定余额递减法固定余额递减法比如:成本为000元,残值为300000元,使用6年。折旧比率=0.271076626392523第一年折旧：000*0.271076626392523第二年折旧：(000-542153）*0.271076626392523 第三年折旧:(000-542153-395188)*0.271076626392523 第7页使用函数使用函数DBDB（成本，残值（成本，残值,使用年限使用年限,期次，第一年月数）期次，第一年月数）最终一年折旧额为：成本最终一年折旧额为：成本-残值残值-前几期累计折旧前几期累计折旧DB(000,300000,6,1)DB(000,300000,6,2)DB(000,300000,6,3)DB(000,300000,6,4)DB(000,300000,6,5)第8页DB（成本，残值（成本，残值,使用年限使用年限,期次，第一年月数）期次，第一年月数）第一年：第一年：DB(000,300000,6,1,5)第二年：第二年：DB(000,300000,6,2,5)第三年：第三年：DB(000,300000,6,3,5)第六年：第六年：DB(000,300000,6,6,5)第七年：第七年：DB(000,300000,6,7,5)问题：假如使用期限问题：假如使用期限6年，第一年使用不满一年，比年，第一年使用不满一年，比如如5个月，第个月，第7个年头使用了个年头使用了7个月，折旧怎么算？个月，折旧怎么算？第9页财务函数中常见参数财务函数中常见参数年金年金:在某一段连续时间内，一系列固定金额给付活动。比如汽车、房屋分期付款就是年金一个。未来值未来值(fv)(fv)-在全部付款发生后投资或贷款价值。比如：零存整取期末领回金额。期间数期间数(nper)(nper)-为总投资（或贷款）期，即该项投资或贷款付款期总数。付款付款(pmt(pmt)-对于一项投资或贷款定时支付数额。其数值在整个年金期间保持不变。通常 pmt 包含本金和利息，但不包含其它费用及税款。现值现值(pv)(pv)-在投资期初投资或贷款价值。比如，贷款现值为所借入本金数额。利率利率(rate)(rate)-投资或贷款利率或贴现率。类型类型(type)(type)-付款期间内进行支付间隔，如在期初或期末，用0或1表示,省略或0时表示期末给付，1期初给付。二、年金函数第10页1 1、年金期付款函数、年金期付款函数PMTPMTPMT函数能够计算为偿还一笔贷款，要求在一定周期内支付完时，每次需要支付偿还额，也就是我们平时所说“分期付款”。比如借购房贷款或其它贷款时，能够计算每期偿还额。PMT函数基于固定利率及等额分期付款方式，返回投资或贷款每期付款额。语法：语法：PMT(PMT(利率，总投资期数，现值,未来值,期初或期末)或者或者 PMT(rate PMT(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。第11页2 2、付款利息、付款利息IPMTIPMT（）（）I IPMT函数基于固定利率及等额分期付款方式，返回投资或贷款某期付款中利息金额。语法：语法：IPMT(IPMT(利率，第几期，总期数，现值,未来值,期初或期末)比如：需要10个月付清年利率为8%￥10,000贷款第3月支付利息额为：IPMT（8%/12,3，10，10000）计算结果为：￥-53.69。第12页3、付款本金、付款本金PPMTPPMT（）（）P PPMT函数基于固定利率及等额分期付款方式，返回投资或贷款某期付款中本金金额。语法：语法：PPMT(PPMT(利率，第几期，总期数，现值,未来值,期初或期末)比如：需要10个月付清年利率为8%￥10,000贷款第3月支付本金额为：PPMT（8%/12,3，10，10000）计算结果为：￥-983.35。第13页4 4、累计利息、累计利息CUMIPMTCUMIPMT（）（）CUMIPMT函数基于固定利率及等额分期付款方式，返回开始期次到结束期次间累计偿还利息金额。语法：语法：CUMIPMT(CUMIPMT(利率，总期数，现值,开始期次，结束期次，未来值,期初或期末)比如：需要10个月付清年利率为9.8%￥3,000,000贷款第3月到第8月支间累计偿还利息金额为：CUMIPMT（8%/12,10，10000，3，8,0）计算结果为：-222.899。第14页 5 5、累计本金、累计本金CUMPRINCCUMPRINC（）（）CUMPRINC函数基于固定利率及等额分期付款方式，返回开始期次到结束期次间累计偿还本金金额。语法：语法：CUMPRINC(CUMPRINC(利率，总期数，现值,开始期次，结束期次，未来值,期初或期末)比如：需要10个月付清年利率为8%￥10,000贷款第3月到第8月支间累计偿还本金金额为：CUMPRINC（8%/12,10，10000，3，8,0）计算结果为：-5999.29。第15页6 6、期次、期次NPERNPER（）（）NPERNPER函数基于固定利率及等额分期付款方式，返回偿还全部贷款期次。语法：语法：NPER(NPER(利率，每期给付金额，现值,未来值,期初或期末)比如：年利率为8%￥100,000贷款，每个月给付1000元，需要多少个月才能还清？NPER（8%/12,-1000，100000，0，1）计算结果为：163.36。注意注意:公式中每个月给付是负值公式中每个月给付是负值.第16页 7 7、利率、利率RATERATE（）（）RATERATE函数返回年金利率。RATE(RATE(总期数，每次给付金额，现值,未来值,期初或期末，猜测值)猜测值假如省略，默认值为10%比如：100,000贷款，每个月给付1000元，163个月还清,问这项贷款年利率为多少？RATE（163，-1000，100000，0，1）=0.6667%年利率=12*0.6667%=8%注意注意:公式中每个月给付是负值公式中每个月给付是负值.第17页比如：100,000贷款，163个月后一次性还10,问这项贷款年利率为多少?RATE(163,0,100000,-10,1)=0.1119%年利率=12*0.1119%=1.34%RATE(总期数，每次给付金额，现值,未来值,期初或期末，猜测值)第18页三、未来值三、未来值1 1、FVFV函数函数:固定利率未来值固定利率未来值FV函数基于固定利率及等额分期付款方式，返回某项投资未来值。语法：语法：FV(rate,nper,pmt,pv,type)FV(rate,nper,pmt,pv,type)Rate：各期利率，是一固定值；Nper：总投资（或贷款）期，即该项投资（或贷款）付款期总数；Pmt:各期所应付给（或得到）金额，其数值在整个年金期间（或投资期内）保持不变；Pv:现值，也称为本金，假如省略，则假设其值为零;Type:期初或期末付款,为数字1或0，假如省略t，则假设其值为零。第19页语法：语法：FV(rate,nper,pmt,pv,type)FV(rate,nper,pmt,pv,type)比如：假如某人两年后需要一笔比较大学习费用支出，计划从现在起每个月初存入元，假如按年利2.25%，按月计息（月利为2.25%/12），那么两年以后该账户存款额会是多少呢？公式写为：FV(2.25%/12,24,-,0,1)该例中没有现值为0,假如该例中该帐户原有金额0元,其它条件不变,那么两年以后该账户存款额会是多少呢？公式为：公式为：FV(2.25%/12,24,-,0,1)第20页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%第21页三、现值三、现值1 1、PV PV 函数函数PV函数用来计算某项固定年金现值。年金现值就是未来各期年金现在价值总和。投资分析投资分析:假如投资回收当前价值大于投资价值，则假如投资回收当前价值大于投资价值，则这项投资是有收益。这项投资是有收益。比如:今年一万元一定比明年一万元有价值,所以,假如在未来连续间,每年年底给10000元,以现在角度来看,其值并非200,000元要想知道到底为多少,就可使用PV函数,假设年利率4.5%.则其现值为:PV(4.5%,20,10000,0,0)=-130079.36第22页语法：语法：PV(rate,nper,pmt,fv,type)PV(rate,nper,pmt,fv,type)Rate:各期利率。Nper:投资（或贷款）付款期总数。Pmt:各期所应支付金额，整个年金期间保持不变。Fv:未来值，或在最终一次支付后希望得到现金余额，假如省略fv，则假设其值为零（一笔贷款未来值即为零）。Type:用以指定各期付款时间是在期初还是期末。第23页语法：语法：PV(rate,nper,pmt,fv,type)PV(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）。所以，这不是一项合算投资。第24页例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第25页例3，假设要购置一项保险年金，该保险能够在第1年末回报￥3000,第2年末回报￥5000,第3到第7年每年末领回8000元,第8年和第9年没有收益,第末领回20000元。此项年金购置成本为50,000，假定投资回报率为8%。那么该项年金是否值得购置?语法：语法：PV(rate,nper,pmt,fv,type)需要用NPV函数来计算上述收益现值.NPV函数基于一系列现金流和固定各期贴现率，返回一项投资现值。第26页2 2、求非固定年金现值、求非固定年金现值NPVNPVNPV函数基于一系列现金流和固定各期贴现率，返回一项投资现值。语法：NPV(rate,value1,value2,.)Rate:为各期贴现率，是一固定值；value1,value2,.代表1到29笔支出及收入参数值，value1,value2,.所属各期间长度必须相等，而且支付及收入时间都发生在期末。NPV按次序使用value1,value2，来注释现金流次序。所以一定要确保支出和收入数额按正确次序输入。第27页例1.假设要购置一项保险年金，该保险能够在第1年末回报￥3000,第2年末回报￥5000,第3到第7年每年末领回8000元,第8年和第9年没有收益,第末领回20000元。此项年金购置成本为50,000，假定投资回报率为8%。那么该项年金是否值得购置?NPV(8%,3000,5000,8000,8000,8000,8000,8000,0,0,20000)=43,713.1843,713.18当前存款利率,则此项投资是合算1、定时现金流量内部酬劳率IRR（现金流量，猜测值）每期现金流量不一定相等，但必须是等间距发生，如：同为每个月一次或每年一次；现金流量第一个值必须是购入成本支出，以负值表示，后接几个属于收入现金流量。比如：用200,000买了一个投资计划，可分五年领回以下金额：50，000，60，000，65，000 70，000 80，000，请计算其内部酬劳率。四、酬劳率四、酬劳率第32页2、非定时现金流量内部酬劳率XIRR（现金流量，日期，猜测值）每期现金流量不一定相等，现金流发生能够是非定时发生；现金流量第一个值必须是购入成本支出，以负值表示，后接现金流量。必须按次序排列。比如：您于/2/5花了200，000买了一项投资计划，可按下表取得收益，年利率为5.2%,该投资是否合算?内部酬劳率为:XIRR(B5:B10,A5:A10)日期收益/2/545，000/8/555，000/2/560，000/2/570，000/2/575，000第33页3、融资及转投资内部酬劳率以融资方式取得资金,进行一项投资,又将所获取收入转投到另一投资,前一项要付给他人融资利息,后一项获取转投资利息,有支出也有收入,收益怎样不知道,则可用MIRR函数来计算内部酬劳率。MIRR（现金流量，融资利率，转投资利率）第34页