Excel函数应用之财务函数61149.docx

Excel函数应用之财务函数像统计函数、工程函数一样,在EXCel中还供应了很多财务函数,财务函数可以进行般的财务计算，如确定贷款的支付欷、投资的将来值或净现值，以及债券或息票的价值.这"财务函数大体上可分为四类：投资计算函数、折旧计算函数、偿还率计算函数、债券及其他金融函数.它们为财务分析供应了极大的便利.运用这些函数不必理解高欲财芬学问，只要填写变Iii值就可以。在下文中，凡是投资的金额都以负数形式表示,收益以正数形式表示.在介绍详细的财务函数之前，我们首先来了解一下财分函数中常见的参数：将来假（fv）一在全部付款发生后的投资或贷款的价值，期间数SPer）一为总投资（或贷款）期即该项投资（或贷款）的付款期总数.付款（Pmt）对于项投资或贷款的定期支付数额，其数他在整个年金期间保持不变。通常pint包括本金和利息,但不包括其他费用及税款，现值（PT）在投资期初的投资或贷款的价值.例如，贷款的现值为所借入的本金数额。利率（rale）一投资或贷款的利率或贴现率。类型（type）一付款期间内进行支付的间隔，如在月初或月末，用。或1表示。日计数基准类型（basis）一为日计数基准类型。的SiS为O或省略代表US（NASD）30/360,为1代表实际天数/实际天数,为2代表实际天数/360,为3代表实际无数/365,为4代奏欧洲30/360,接卜.来,我们将分别举例说明各种不同的财务函数的应用.在本文中主要介绍各类里的典型财务函数,更多的财务函数请参看附表及相关书籍.检如下文中所介绍的函数不行用，返回错误但NAME?,请安装并加载”分析工具库“加我宏。操作方法为：1,在"工具”菜单上,单击"加载宏二2,在可用加载宏.列表中，选中”分析工具库”枢，再单击”确定.。一、投费计算函数投资计算函数可分为与将来值fv有关，与付款Pmt有关，与现值Pv有关，与复利计口有关及与期间数有关几类函数.1,与将来值fv有关的函数一FV'FVSaiEDU1.E2、与付款Pmt有关的函数-TPMT、ISPMT.PMT,PPMT3.与现值PY有关的函数一NPY、PV、XNPV4、与笈利计算有关的函数一EFFEcT、NOMINA1.5、与期间数有关的函数-TPER在投资计算函数中,笔者将承点介绍FV、NPV.PMT、PY函数。（一）求某项投资的将来值FY在日常工作与生活中，我们常常公遇到要计算某项投资的将来值的状况,此时利用EXCel函数FY进行计犯后，可以帮助我们进行一些有安排'有目的、有效益的投资.FV函数基于固定利率及等额分期付款方式，返回某项投资的将来值。语法形式为IrV（rate,npertpint,pv,type）<,其中rale为各期利率,是一固定值.nper为总投资（或贷款）期,即该项投资（或贷款）的付款期总数PY为各期所应付给（或得到）的金额，其数值在整个年金期间（或投资期内）保持不变，通常PY包括本金和利息,但不包括其它费用及税款.pv为现值,或一系列将来付款当前值的累枳和.也称为本金，假如省略pv,则假设其他为零，type为数字0或1,用以指定各期的付款时同是在期初还是期末,钗如省略I，则假设其值为零。例如：假如某人两年后须要一笔比较大的学习费用支出，安排从现在起每月初存入2000元，假如按年利2.25%,按月计息（月利为2.25%/12）,那么两年以后该账户的存款额会是多少呢？公式写为:FV（2.25V12,24,-2000,0,1）图1二)求投资的净现值NPYNPV函数基于系列现金流和固定的各期贴现率,返回顶投资的净现值,投资的净现值是指将来各期支出(负值)和收入(正值)的当前值的总和。语法形式为：NPV(rate,valel,vale2,.)其中,rate为各期贴现率，是一固定值：valuel,VaIUe2,.代表1到29笔支出及收入的参数值,value),ValUe2,.所届各期间的长度必需相等,而且支付及收入的时间都发生在期末.须要留意的是：NPV按次序运用VaIUeI,value2,来注择现金流的次序。所以肯定要保证支出和收入的数额按正确的依次输入假如参数是数值、空白单元格、逻辑值或表示数值的文字表示式,则都会计算在内：假如参数是错误值或不能转化为数值的文字，则被忽视，假如参数是个数组或引用，只有其中的数值部分计算在内忽视数组或引用中的空白单元格、逻辑值、文字及错误值。例如,林设开一家电经销店.初期投资¥200,000,而希里将来五年中各年的收入分别为¥20,000、¥40,000、Y50,000,Y80,000和Y120,000,暇定年年的贴现率是8%(相当于通贷膨胀率或竞争投资的利率),则投资的净现值的公式是：=NPV(A2,A4:A8)+A3在该例中,一起先投资的¥200,000并不包含在V参数中,因为此项付款发生在第一期的期初.钱设该电战店的营业到第六年时,要由新装修门面，估计要付出¥40,000.则六年后书店投资的净现值为：NPV(A2,A4:A8.9)÷A3假如期初投资的付款发生在期末，则投资的净现值的公式是：=NPV(A2,A3:A8)AB1数据说明28%年贴现率。可表示整个投资的通货脱寐率或利率。3-200,000初期投贷420,000第一年的收Ji540,000第二年的收笈650,000第三年的收益780,000第四年的收苣8120,000第五年的收还9-40,000第六年装修费10公式说明(结果)U¥32,976.06该投贷的净现值(32,976.06)12¥7,769.27该投资的净现值，包括第六年中40,000的装修费(7,769.27)13¥30,533.38该按笠的净现值(30,533.38)(三)求贷款分期偿还额PMTPMT函数站于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。PMT函数可以计"为偿还一笔贷款，要求在肯定周期内支付完时，每次须要支付的偿还额，也就是我们平常所说的"分期付款:比如借啊房贷款或其它贷款时，可以计算每期的偿还额。其语法形式为:PMT(rnte,nper,pv,fv,type)其中,rate为各期利率，是一固定值，nper为总投资(或贷款)期,即该项投资(或贷款)的付款期总数，pv为现值，或一系列将来付款当前值的累枳和也称为本金,fv为将来值，或在最终一次付款后希望福到的现金余额，假如省略立，则假设其他为零(例如，一笔贷款的将来值即为零)，ty>c为O或1,用以指定各期的付款时间足在期初还是期末。假如省略ype,则假设其值为零.例如，须要10个月付清的年利率为8%的¥10,000贷款的月支额为：PMT(8%12,10,10000)计并结果为：-¥1,037.03.(四)求某项投资的现值PVPV函数用来计煤某项投资的现值.年金现值就是将来各期年金现在的价值的总和。假如投资回收的',rt价伯大于投资的价值，则这项投资是有收益的,其语法形式为:PV（rate,nper,pmt,fv,type）其中Rate为各期利率.Nper为总投资（或贷款）期，即该项投伊（或贷款）的付款期总数，Pmt为各期所应支付的金额,其数值在整个年金期间保持不变.通常Pmt包括本金和利息但不包括其他费用及税款,Fv为将来值，或在最终次支付后希望御到的现金余额，假如省略fv,则假设其值为零（一艳贷款的将来值即为零3Type用以指定各期的付款时间是在期初还是期末。例如假设要购买一项保险年金,该保唆可以在今后二十年内于每月末回报¥60C1.此项年金的l买成本为80,000,假定投资回报率为8%,那么该项年金的现值为：PV（0.08/12.12*20,600.0）计算结果为：¥-71,732.58。负债表示这是一笫付款，也就是支出现金流.年金（¥71,732.581的现值小于实际支付的（¥80,000）。因此，这不是一项合算的投资。图3二、折旧计算函数折旧计算函数主要包括AMORDEGRC、AMOR1.INC、DB、DDB,S1.N,SYD.VDB.这些函数都是用来计算资产折旧的，只是采纳r不同的计算方法.这里，对于详细的计算公式不再赘述,详细选用哪种折IH方法,则须视各单位状况而定。三、偿还率计算函数偿还率计舞函数主要用以计算内部收益率，包括IRhMlRR、RATE和XlRR几个函数。（一）返回内部收益率的函数-TRRIRR函数返I可由数位代表的一组现金流的内滞收益率.这些现金流不肯定必需为均衡的，但作为年金，它们必褂按固定的间隔发生，如按月或按年.内部收益率为投宽的收利率，其中包含定期支付（负值）和收入（正值）.其语法形式为IRR（values,guess）其中ValUeS为数组或中元格的引用，包含用来计算内部收益率的数字,values必需包含至少一个正值和一个负值,以计以内部收益率,函数IRR依据数值的依次来说明现金流的依次，故应确定按须要的依次输入了支付和收入的数值，假如数组或引用包含文本、逻辑值或空白单元格，这些数值将被忽视：guess为对函数IRR计舞结果的估计值,excel运用迭代法计算曲数IRR从guess起先,函数IRR不断修正收益率，直至结果的精度达到0.00001%,然如函数【RR经过20次迭代，仍未找到结果,则返回错误值#NUM!,在大多数状况卜.并不须要为函数IRR的计算供应guess值，假如省略guess,假设它为0.1<10%）.假如函数IRR返回错误值#NUM!,或结果没有帘近期里但，可以给guess换个值再试一下.例如,假如要开办一家服装商店,预料投资为¥110,000,并预期为今后五年的净收益为：¥15,000.¥21,000,¥28,000,¥36,000和¥45,000.分别求出投资两年、四年以及五年后的内部收益率。A投资-1100002预期第一年净收益150003预期第二年净收益210004预期第三年净收益280005预期第四年净收益360006预期第五年净收益450007二年后的内部收益率'-48.96%8四年后的内部收益率-3.27%Ka五年后的内部收益率8.35%图4在工作表的Bl：B6输入数据函数.xls”所示，计算此项投资四年后的内部收益率IRR<B1：B5）为-3.27%；计算此项投资五年后的内部收益率IRR（Bl：B6）为8.35乐计算两年后的内部收益率时必需在函数中包含guess,即IRR（BhB3,-10%）为-48.96%.（二）用RATE函数计算某项投资的实际赢利在经济生活中，常常要评估当前某项投资的运作状况，或某个新企业的现状.例如某承包人建议你贷给他30000元，用作公共工程建设资金，并同意每年付给你9000元.共付五年，以此作为这笔贷款的最低回报,那么你如何去决策这名投资？如何知道这项投资的回报率呢？对于这种周期性偿付或是一次偿付完的投资，用RATE函数可以很快地计并出实际的威利。其语法形式为RATE(npr.pmt,pv,fv,t>e.guess)“详细操作步骤如下：1、选取存放数据的单元格，并按上述相像的方法把此单元格指定为"百分数"的格式。2、插入函数RATE,打开”粘贴函数.对话框，3,在"粘贴函数”对话框中,在"Npr"中输入偿还周期5(年),在"PmJ中输入7000(每年的Ial报额)，在"P/中输入-30000(投资金额).即公式为=RATE(5,9000,-30000)4、确定后计算结果为15.24%。这就是本项投资的每年实际扇利,你可以依据这个值推断这个赢利是否满足，或是确定投资我它项目，或是由新谈判每年的13报“四、债券及其他金Itt的数债券及其他金融函数又可分为计算本金、利息的函数，与利息支付时间有关的函数、与利率收益率有关的函数、与修正期限有关的函数、与有价证券有关的函数以及与证券价格表示有关的函数。1、计算本金、利息的函数-YijMPRINC、ACCRlNT»ACCRNTM、CUMlPMT、CoUPNUM2、与利息支付时间有关的函数一COUPDAYBS、COUPDAYS.COUPDAYSNC、CoUPNCD、COUPPCO3,与利率收靛率有关的函数一1NTRATE、0DIYIE1.DsODD1.YIE1.DxTBI1.1.EQ、TBH.1.PRICE,tbillyield.yield,yielddisc,yiili>mt4、与修正期限有关的函数一DURATlON、MDllRATION5,与有价证券有关的函数一disc、Oddfprice,ODD1.PRice、price,pricedisc.PRICEMAT,RECEIVED6、与证券价格表示有关的函数-DO1.1.ARDE、D01.1.RHR在债券及其他金融函数中，隹者将重点介绍函数ACCRINT、ClJVPRINC、DISC.(一)求定期付息有价证券的应计利息的函数ACCRINTACCRINT函数可以返回定期付息有价证券的应计利息。其语法形式为ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis)其中issue为有价证券的发行Fl,firstinterest为有价证券的起息日,settlement为有价证券的成交日,即在发行日之后，有价证券卖给购买者的日期,rule为有价证券的年息票利率，Par为有价证券的票面价值，假如省略par,函数ACCRlNT就会自动将Par设置为¥1000,frequency为年付息次数，basis为口计数基准类型。例如,某国库券的交易状况为：发行日为2008年3月1日：起息日为2008年8月31H：成交Fl为2008年5月1H,息票利率为10.0%：票面价值为¥1,000：按半年期付息：日计数基准为30/360.那么应计利息为：图5(二)求本金数额CuMPRlNCOjmprinc函数用于返到一笔贷款在给定的St到e11期间累计偿还的本金数额.其语法形式为CVMPRINC(rate,nper,pv,startperiod,endjM>riod,type)其中rate为利率，nper为总付款期数，pv为现值,SSr1.PeriOd为计算中的首期,付款期数从1起先计数，endPeriod为计算中的末期，type为付款时间类型。例如，一整住房抵押贷款的交易状况如下：年利率为9.00%；期限为30年；现值为¥125,000.由上述已知条件可以计算出：r-9.00%/12-0.0075,np=30*l2=360.1 领据说明29.OOX年利率30货款期限4125,OOOJJJtg公式说明(结果)-436.568194该笔贷款在第一年下半年告压的全部本金之和(第7掰到6第12期)(-436.568194)-68.27827118该笔岱款在第一个月偿还的本7全(-68.27827)那么该笔贷款在第下半年偿还的全部本金之中(第7期到第12期)为：=CuMPRINC(A212,A3*12,A4f7,12,0)计算结果为：-436.568194.该笔贷款在第一个月偿还的本金为：-CUMPRNC(A2/12,A3*12,A4,1,1,0)计算结果为：-68.27827118.（三）求有价证券的贴现率DISCDlSC函数返回有价证券的贴现率.其语法形式为DlSC(scttlcmcnt,maturity,pr,redemption,basis)其中SelIIe碇nl为有价证券的成交日,即在发行日之后,有价证券卖给购买者的日期.maturity为有价证券的到H期，到期H您有价证券有效期截止时的H期，pr为面值为"¥100”的有价证券的价格，redemption为面值为"YIOtT的有价证券的清偿价格，basis为日计数基准类型.例如：某债券的交易状况如下：成交日为99年3月18H,到期H为99年8月7日，价格为¥48.834,清借价格为¥52,日计数基准为实际天数/360。那么该债券的贴现率为：DISC("99/3/18,”99/8/7”,48,834,52,2)计算结果为：0.154355363,函数名林函数说明语法形式ACCRlNTAccrintw返回定期付息有价证券的应计利息。返回到期一次性付息有价证券的应计利息。ACCRINT(issue,firstinterest,settIement1rate,par,frequency,basis)CCRINTM(issue,mnturity,rate,par,basis)Amordegrc返回每个会计期间的折旧值.此函数是为法国会计系统供应的。MORDEGRC(cost.date_purchased,firstperiod,salvage,period,rate,basis)AMOR1.INC返回每个会计期间的折旧值，该函数为法国公AMOR1.INC(cost,datepurchased.计系统供应.first_period,salvage,period,rate,basis)COuPDAYBS返阿当前付息期内截止到成交日的天数.COUPDAYBS(set11Qmenl.maturity,frequency,basis)COUPDAYS返回成交日所在的付息期的天数.COITDAYS(Selllemeu.maturity,frequency,basis)COU1,DAYSNC返回从成交口到下一付息日之间的天数，COlPDAYSNC(set11emcn11maturity,frequency,basis)COUPNCD返回成交日过后的下一付息日的口期，COUPNC)(se111ement,maturity,frequency,basis)COUPNUM返回成交日和到期日之间的利息应付次数，向上取整到最近的整数。CO1.TN1.M(se111ement.maturity,frequency,basis)COUPPCD返mi成交日之前的上一付息日的日期.COUPPCD(SRlllument.naturity.frequency,basis)CIKIPMT返回一笔贷款在给定的Star1.PeriOd到endperiod期间素计偿还的利息数额.CIMIPMT(rale,riper,pvtstartj>eriod.endperiod,type)CUMPRINCDB返回一笔贷款在给定的start-period到end-period期间限计偿还的本金数额。运用固定余额递战法，计算哇资产在给定期间内的折旧值。CUWPRiNC(rate,nper,pv1Starjperiod,end_period,type)DB(cost,Sa1vage,life,period,month)DDB运用双倍余额递战法或其他指定方法，计算笔资产在给定期间内的折山值。D0B(cost,salvage,life,period,factor)DISC返回有价证券的贴现率.DISC(settlement,11kiturity.prtredemption,basis)D01.1.ARDE将按分数表示的价格转换为按小数表示的价格,如证券价格.转换为小数表示的数字。DO1.1.R1)E(fractiona1dollar,fraction)DO1.1.RIR将按小数表示的价格转换为按分数衣示的价格。如证券价格，转换为分数型数字。I)O1.1.AKHR(decimaldollar,fraction)DURATION返回假设面值3100的定期付息有价证券的修正期限。期限定义为系列现金流现值的加权平均假.用于计盘债券价格时于收益率改变的敏感程度.DliWTION(settlement,maturity,couponyld.frequency,basis)EFFECT利用给定的名义年利率和一年中的发利期次，计算实际年利率。EHTCT(nominalrate,npcry)FV庙于固定利率及等额分期付款方式,返回某项投资的将来值。FV(rate,nper,Pfflt,pv,type)Fvschediile基于一系列史利返Wl本金的将来值“函数Fvschoule用于计竟某项投资在变动或可两利率下的将来值。FVSCHEDU1.E(principa1,schedule)INTRATE返回一次性付息证券的利率，IXTUTE(settIement,maturity,investment,redemption,basis)IPWr基于固定利率及等额分期付款方式，返IUl投资或贷款在某给定期次内的利息偿还额：IF,MT(rate,per,nper,pv,fv,type)IRR返回由数值代表的一组现金流的内部收慈率.IRR(VHlues,guess)ISPMT计算特定投资期内要支付的利息。ISpMT(rate,per,nj>er,PV)Mduration返回假设面值$100的有价证券的Macauley修正期限。MD1.RTION(se111ement,maturity,coupon,yld,frcquency,basis)MIRR返回某一连续期间内现金流的修正内部收益率.MIRR(values,financerate,reinvestrate)NOMINA1.灵丁给定的实际利率和年笈利期数，返回名义年利率。NOMlNA1.(Crfecljdle.rery)NPERNPV基于固定利率及等额分期付款方式，返回某项投资（或贷款）的总期数。通过运用贴现率以及系列将来支出（负值）和收入（正值），返回一项投资的净现值。NPER(rate,pmt,pv,fv,type)NPV(rate,valuel,value2,.)ODDFPRlCE返13首期付息日不固定的而值SlOO的有价证券的价格ODWrPRICE(settlenient,11uturity,issue,firstcoupon,rate,yld,redemption,frequency,basis)O)l)FYIEU)返回首期付息日不固定的有价证券（长期或短期）的收益率。O)DIrYIEl.D(settIement,nuilurity,issue,firstcoupon,rate,pr,redemption,frequency,basis)O()1.PR1CE返回末期付息日不固定的面值$100的有价证券（K期或短期）的价格，OI)1.PRICE(settIement,11uturity,lastinterest,rate,yld,redemption,frequency,basis)OddlyieldPMT返回末期付息日不固定的有价证券（长期或短期）的收益率。基于固定利率及等额分期付款方式，返mi贷款的每期付款额。ODD1.YIE1.D(settle11>cnt,11uturity,lastinterest,rate,pr,redemption,frequency,basis)PMT(rate,nper,pv,fv,type)PPMT基于固定利率及等额分期付款方式，返IUl投资在某一给定期间内的本金偿还额，PPMT(rate,per,nper,pv,fv,type)PRICE返回定期付息的面值5100的有价证券的价格，PRICE(settIeinvntrmaturity,rate,yld,redemption,frequency,basis)PRICEDISC返回折价发行的面值5100的有价证券的价格，PRICEDISC(set11ement,matuity.discount,redemption,basis)PRICEMATPV返回到期付息的面值$100的有价证券的价格.返回投资的现值。现值为一系列将来付款的当前值的累积和。例如，借入方的借入款即为贷出方贷款的现值”PRlCEK>T(scttlement,maturity,issue,rate,yld,basis)PV(rate,npcr,p11,fv,type)RATE返回年金的冬期利率。函数RATE通过迭代法计算得出，并且可能无解或有多个解,RATE(nper,pmt,pv,fv,type,guess)received返回次性付息的件价证券到期收回的金额，REClt1VED(set11ement,maturity,investment,discount,basis).v.返回某项资产在一个期间中的线性折IH值。S1.N(cost,salvage,life)SYDTB11.1.EQ返回某项资产按年限总和折旧法计算的指定期间的折旧值.返回国陈券的等效收益率。SYD(cost,salvage,life,per)TBl1.1.EQ(SettIement,maturity,discount)Tbiiiprice返网面值SlOO的国库券的价格.TBI1.1.PRICE(settIement.mnturity,discount)Tbiiiyield返回国库券的收益率.TBI1.1.YIE1.D(se111emenI.11aIurity,r)VDB运用双倍余虢递战法或其他指定的方法,返回指定的任何期可内（包括部分期间）的资产折旧值.函数VDB代表可变余额递减法。VDB(cost,salvage,IifelStartperiod,endperiod,factor,noswitch)XIRR返回组现金流的内部收益率，这些现金流不肯定定期发生。若要计算-组定期现金流的内部收益率，请运用函数IRR.XIRR(values,(kites,guess)XNPV返回一组现金流的净现值，这些现金流不肯定定期发生.若要计算-组定期现金流的净现fit.请运用函数NPV.XNI,V(rate»values,dates)YIE1.DYIE1.DDISC返回定期付息有价证券的收益率，函数YlE1.D用于计舞债券收益率0返回折价发行的有价证券的年收益率。YIE1.D(settIement1maturity,rate,pr,redemplion,frequency,basis)YIE1.)I>ISC(settIement1maturity,pr,rcdemption,basis)YIE1.DMAT返回到期付息的有价证券的年收益率，YIBl.Dy1.T(settlement,maturity,issue,rate,pr,basis)