Excel学习网

做财务工作一定要看的四种excel财务函数应用

excel学习网 403

    像统计函数、工程函数同样,在Excel中还供应了许多财务函数。财务函数可以进行普通的财务查考,如确定贷款的开支额、投资的未来值或净现值,以及证卷或息票的价格。这些财政函数大体上可格外四类:投资计算函数、折旧查考函数、偿还率计算函数、证卷及其他金融函数。它们为财政剖析供应了极大的方便。任用这些函数未必明白高级财务知识,只要谱写变量值就可能了。在文中某句或某段以下的中,但凡投资的金额都以负数形式表现,收益以正数样式表示。

      在介绍详细的财务函数之前,咱们首先来精通一下财务函数中多见的参数:

      异日值 (fv)--在所有付款派生后的投资或贷款的价格。

      期间数 (nper)--为总投资(或贷款)期,即该项投资(或贷款)的付款期总数。

      付款 (pmt)--对待一项投资或贷款的定额支付数额。其用数目表示的一个量的在整个年金工夫保持不变。每每 pmt 包括本金和本钱,但不包括其他费用及税款。

      现值 (pv)--在投资期初的投资或贷款的价值。比方,贷款的现值为所借入的本金数额。

      利率 (rate)--投资或贷款的利率或贴现率。

      典范 (type)--付款期间内进行开支的间隔,如在月初或月末,用0或1表示。

      日计数基准类型(basis)--为日计数基准类型。Basis为0 或不详代表US (NASD) 30/360 ,为1代表实质天数/实际天数 ,为2代表实际天数/360 ,为3代表实质天数/365 ,为4代表欧洲30/360。

      接下来,我们将分别示范说明各式区别的财务函数的使用。在本文中主要推荐各类型的典型财务函数,更多的财政函数请参看附表及联系篇章。如果下文中所推荐的函数不可用,复归错误值 #NAME?,请安装并加载"剖析工具库"加载宏。操作方法为:

      1、在"对象"菜单上,单击"加载宏"。

      2、在"可用加载宏"制成表中,选中"剖析工具库"框,再单击"确定"。

      一、投资查考函数

      投资查考函数可格外与未来值fv有关,与付款pmt关涉,与现值pv关涉,与复利查考关涉及与期间数有关几类函数。

      1、与未来值fv有关的函数--FV、FVSCHEDULE

      2、与付款pmt有关的函数--IPMT、ISPMT、PMT、PPMT

      3、与现值pv关涉的函数--NPV、PV、XNPV

      4、与复利查考有关的函数--EFFECT、NOMINAL

      5、与期间数有关的函数--NPER

      在投资计算函数中,笔者将重点推荐FV、NPV、PMT、PV函数。

      (一) 求某项投资的未来值FV

      在日常处事与生活中,我们经常会遇到要查考某项投资的未来值的情况,此时利用Excel函数FV举行计算后,可以帮助咱们进行一些有策画、有目标、有效益的投资。FV函数基于固定利率及等额分期付款体例,复归某项投资的未来值。

      语法样式为FV(rate,nper,pmt,pv,type)。个中rate为各期利率,是一牢固值,nper为总投资(或贷款)期,即该项投资(或贷款)的付款期总数,pv为各期所草率给(或获取)的金额,其数值在一切年金期间(或投资期内)保持不变,通常Pv包容本金和利息,但不包括别的费用及税款,pv为现值,或一系列未来付款当前值的累积和,也叫做本金,譬如不详pv,则设想其值为零,type为数字0或1,用以指定各期的付款时间是在期初抑或期末,譬如不详t,则假设其值为零。

      例如:如果某人两年后需要一笔审度大的学习开支开支,策画从现在起每个月初存入2000元,如果按年利2.25%,按月计息(按月计算的利息为2.25%/12),那末两年以后该账户的存款额会是几何呢?



   公式写为:FV(2.25%/12, 24,-2000,0,1)       (二) 求投资的净现值NPV

      NPV函数基于一系列现金流和牢固的各期贴现率,复归一项投资的净现值。投资的净现值是指未来各期支出(负值)和收成(刚巧)的此刻值的总和。

      语法样式为:NPV(rate,value1,value2, ...) 其中,rate为各期贴现率,是一牢固值;value1,value2,...代表1到29笔支出及收入的参数值,value1,value2,...所属各期间的长度需要相等,并且开支及收入的时候都派生在期末。供给瞩目的是:NPV按次序使用value1,value2,来解释现金流的次序。所以一定要包管支出和收入的数额按确切的顺序媒介从外部到内部的传。譬如参数是用数目表示的一个量的、空缺单元格、逻辑值或表示用数目表示的一个量的的文字表示式,则都会计算在内;譬如参数是悖谬值或不能变动为数值的文字,则被疏忽,譬如参数是一个数组或引用,只有其中的数值部门计算在内。疏忽数组或抄录中的空缺单元格、逻辑值、文字及错误值。

      例如,设想开一家电器经销店。初期投资¥200,000,而希望异日五年中各年的收成分别为¥20,000、¥40,000、¥50,000、¥80,000和¥120,000。假定连年的贴现率是8%(相当于通贷膨胀率或争夺投资的利率),则投资的净现值的公式是:

      =NPV(A2, A4:A8)+A3

      在该例中,一开始投资的¥200,000其实不蕴藏在v参数中,由于此项付款派生在第一期的期初。假设该电器店的买卖到第六年时,要从头装修门面,衡量要付出¥40,000,则六年后书店投资的净现值为:



    =NPV(A2, A4:A8, A9)+A3 [NextPage]

    (三) 求贷款分期清偿额PMT

      PMT函数基于固定利率及等额分期付款体例,复归投资或贷款的每期付款额。PMT函数可能查考为清偿一笔贷款,要求在必然周期内开支完时,每次供给开支的清偿额,也即是咱们平淡所说的"分期付款"。比如借购房贷款或别的贷款时,可能计算每期的清偿额。

      其语法形式为:PMT(rate,nper,pv,fv,type) 其中,rate为各期利率,是一固定值,nper为总投资(或贷款)期,即该项投资(或贷款)的付款期总数,pv为现值,或一系列未来付款当前值的积累和,也称为本金,fv为异日值,或在最后一次付款后企图获取的现金余额,譬如省略fv,则设想其值为零(例如,一笔贷款的未来值即为零),type为0或1,用以指定各期的付款时间是在期初抑或期末。如果不详type,则设想其值为零。

      例如,供给10个月付清的年利率为8%的¥10,000贷款的月支额为:

      PMT(8%/12,10,10000) 计算原委为:-¥1,037.03。

      (四) 求某项投资的现值PV

      PV函数用来查考某项投资的现值。年金现值就是未来各期年金目前的价值的总和。如果投资回收的此刻价格大于投资的价值,则这项投资是有收益的。

      其语法形式为:PV(rate,nper,pmt,fv,type) 个中Rate为各期利率。Nper为总投资(或贷款)期,即该项投资(或贷款)的付款期总数。Pmt为各期所应支付的金额,其数值在整个年金期间保持不变。每每 pmt 包括本金和利息,但不包括其他开支及税款。Fv 为异日值,或在最后一次开支后企图得到的现金余额,如果不详 fv,则设想其值为零(一笔贷款的未来值即为零)。Type用以明确定各期的付款时间是在期初抑或期末。

      比方,设想要购买一项安全年金,该保险可以在将来二十年内于每月末回报¥600。此项年金的采办成本为80,000,假定投资回报率为8%。那末该项年金的现值为:

      PV(0.08/12, 12*20,600,0) 查考原委为:¥-71,732.58。


  负值表示这是一笔付款,也就是开支现金流。年金(¥-71,732.58)的现值小于实际开支的(¥80,000)。以是,这不是一项合算的投资




  二、 折旧查考函数

      折旧计算函数重要包容AMORDEGRC、AMORLINC、DB、DDB、SLN、SYD、VDB。这些函数都是用来计算财富折旧的,仅仅采用了不同的计算要领。这里,对于详细的计算公式没有赘述,详细选用哪种折旧要领,则须视各单位情况而定。

      三、偿还率计算函数

      偿还率计算函数重要用以查考内部收益率,包容IRR、MIRR、RATE和XIRR几个函数。

      (一) 返回内部收益率的函数--IRR

      IRR函数复归由用数目表示的一个量的代表的一组现金流的内部收益率。这些现金流不必然需要为均衡的,但行为年金,它们需要按固定的间隔发生,如按月或按年。内部收益率为投资的接受利率,其中蕴藏定额开支(负值)和收入(刚巧)。

      其语法形式为IRR(values,guess) 个中values为数组或单元格的抄录,蕴藏用来计算内部收益率的数字,values必须蕴藏至少一个刚巧和一个负值,以查考内部收益率,函数IRR按照数值的秩序来解释现金流的秩序,故应确定按需要的顺序输入了支付和收成的用数目表示的一个量的,如果数组或引用包含文本、逻辑值或空白单元格,这些数值将被忽略;guess为对函数IRR查考结果的衡量值,excel任用迭代法查考函数IRR从guess开始,函数IRR不绝批改收益率,直至结果的精度及0.00001%,譬如函数IRR始终20次迭代,仍未找到结果,则返回错误值#NUM!,在大多数情形下,并不需要为函数IRR的查考提供guess值,譬如不详guess,设想它为0.1(10%)。譬如函数IRR复归错误值#NUM!,或原委没有亲近期望值,可以给guess换一个值再试一下。

      例如,如果要开办一家服装铺户,预计投资为¥110,000,并希冀为今后五年的净收益为:¥15,000、¥21,000、¥28,000、¥36,000和¥45,000。分别求出投资两年、四年以及五年后的内部收益率。 



  。           在处事表的B1:B6媒介从外部到内部的传数据"函数.xls"所示,查考此项投资四年后的内部收益率IRR(B1:B5)为-3.27%;计算此项投资五年后的内部收益率IRR(B1:B6)为8.35%;计算两年后的内部收益率时需要在函数中蕴藏guess,即IRR(B1:B3,-10%)为-48.96%。

      (二) 用RATE函数计算某项投资的实际赢利

      在经济生涯中,经常要评估当前某项投资的运作情形,或某个新科研院所和高等学校合的近况。例如某承包人提议你贷给他30000元,用作公共工程扶植血本,并同意每年付给你9000元,共付五年,拿这行为这笔贷款的最低回报。那末你如何去决定这笔投资?如何识察这项投资的回报率呢?对待这种周期性偿付或是一趟偿付完的投资,用RATE函数可能很快地查考出实际的利润。其语法样式为RATE(nper,pmt,pv,fv,type,guess)。

      详细操纵步调以下:

      1、选取寄存事实或观察的结果的单元格,并按上述犹如的方法把此单元格指定为"百分数"的式样。

      2、挤入函数RATE,开创"粘贴函数"对话框。

      3、在"粘合函数"对话框中,在"Nper"中媒介从外部到内部的传偿还周期5(年),在"Pmt"中输入7000(每年的答覆额),在"Pv"中输入-30000(投资金额)。即公式为=RATE(5,9000,-30000)

      4、划定后查考原委为15.24%。这就是本项投资的每年实质赢利,你可以按照这个值判断这个赢利是否满意,或是决定投资其它项目,或是重新会商每年的回报。

[NextPage]

 

      四、债券及另外金融函数

      债券及另外金融函数又可格外查考本金、本钱的函数,与本钱支付时候有关的函数、与利率收益率有关的函数、与批改限期关涉的函数、与有价证券关涉的函数以及与期货价格表现关涉的函数。

      1、查考本金、利息的函数--CUMPRINC、ACCRINT、ACCRINTM、CUMIPMT、COUPNUM

      2、与本钱支付时间有关的函数--COUPDAYBS、COUPDAYS、COUPDAYSNC、COUPNCD、COUPPCD

      3、 与利率收益率有关的函数--INTRATE、ODDFYIELD、ODDLYIELD、TBILLEQ、TBILLPRICE、TBILLYIELD、YIELD、YIELDDISC、YIELDMAT

      4、与批改期限有关的函数--DURATION、MDURATION

      5、与有价证券关涉的函数--DISC、ODDFPRICE、ODDLPRICE、PRICE、PRICEDISC、PRICEMAT、RECEIVED

      6、与证券价值表示有关的函数--DOLLARDE、DOLLARFR

      在证卷及另外金融函数中,笔者将重点推荐函数ACCRINT、CUMPRINC、DISC。

      (一)求定额付息有价证券的应计利息的函数ACCRINT

      ACCRINT函数可以返回定额付息有价证券的应计利息。

      其语法样式为ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis)

      个中issue为有价证券的发行日,first_interest为有价期货的起息日,settlement为有价期货的成交日,即在发行日然后,有价证券卖给购买者的日期,rate为有价证券的年息票利率,par为有价证券的票面价格,如果不详par,函数ACCRINT就会自动将par设置为¥1000,frequency为年付息次数,basis为日计数基准类型。

      例如,某国库券的商贸情形为:发行日为2008年3月1日;起息日为2008年8月31日;成交日为2008年5月1日,息票利率为10.0%;票面价格为¥1,000;按半年期付息;日计数基准为30/360,那末应计利息为: 

  (二)求本金数额CUMPRINC

      CUMPRINC函数用于复归一笔货款在给定的st到en工夫累计清偿的本金数额。其语法样式为CUMPRINC(rate,nper,pv,start_period,end_period,type) 其中rate为利率,nper为总付款期数,pv为现值,start_period为计算中的首期,付款期数从1起初计数,end_period为计算中的晚年,type为付款时间类型。

      例如,一笔住房抵押贷款的交易情形如下:年利率为9.00%;限期为30年;现值为¥125,000。由上述已知要求可以计算出:r=9.00%/12=0.0075,np=30*12=360。 

     那末该笔贷款在第下半年清偿的全部本金当中(第7期到第12期)为: =CUMPRINC(A2/12,A3*12,A4,7,12,0)查考原委为:-436.568194。

      该笔贷款在第一个月清偿的本金为:=CUMPRINC(A2/12,A3*12,A4,1,1,0)查考原委为:-68.27827118。

[NextPage]

 

      (三) 求有价证券的贴现率DISC

      DISC函数返回有价证券的贴现率。

      其语法样式为DISC(settlement,maturity,pr,redemption,basis) 其中settlement为有价期货的成交日,即在发行日然后,有价证券卖给购买者的日期,maturity为有价证券的到日期,到期日是有价证券有效期结束时的日期,pr为面值为"¥100"的有价证券的价格,redemption为面值为"¥100"的有价证券的归还价值,basis为日计数基准类型。

      例如:某证卷的交易情形如下:成交日为99年3月18日,到期日为99年8月7日,价值为¥48.834,归还价值为¥52,日计数基准为实际天数/360。那么该债券的贴现率为: DISC("99/3/18","99/8/7",48.834,52,2) 查考结果为:0.154355363。


  
     

函数名称函数说明语法形式
ACCRINT返回定期付息有价证券的应计利息。ACCRINT(issue,first_interest, settlement,rate,par,frequency, basis)
ACCRINTM返回到期一次性付息有价证券的应计利息。ACCRINTM(issue,maturity,rate, par,basis)
AMORDEGRC返回每个会计期间的折旧值。此函数是为法国会计系统提供的。AMORDEGRC(cost,date_purchased, first_period,salvage,period, rate,basis)
AMORLINC返回每个会计期间的折旧值,该函数为法国会计系统提供。AMORLINC(cost,date_purchased, first_period,salvage,period, rate,basis)
COUPDAYBS返回当前付息期内截止到成交日的天数。COUPDAYBS(settlement,maturity, frequency, basis)
COUPDAYS返回成交日所在的付息期的天数。COUPDAYS(settlement,maturity, frequency, basis)
COUPDAYSNC返回从成交日到下一付息日之间的天数。COUPDAYSNC(settlement,maturity, frequency, basis)
COUPNCD返回成交日过后的下一付息日的日期。COUPNCD(settlement,maturity, frequency, basis)
COUPNUM返回成交日和到期日之间的利息应付次数,向上取整到最近的整数。COUPNUM(settlement,maturity, frequency, basis)
COUPPCD返回成交日之前的上一付息日的日期。COUPPCD(settlement,maturity, frequency, basis)
CUMIPMT返回一笔贷款在给定的start-period 到end-period 期间累计偿还的利息数额。CUMIPMT(rate,nper,pv,start_period, end_period,type)
CUMPRINC返回一笔贷款在给定的start-period 到end-period 期间累计偿还的本金数额。CUMPRINC(rate,nper,pv,start_period, end_period,type)
DB使用固定余额递减法,计算一笔资产在给定期间内的折旧值。DB(cost,salvage,life,period,month)
DDB使用双倍余额递减法或其他指定方法,计算一笔资产在给定期间内的折旧值。DDB(cost,salvage,life,period,factor)
DISC返回有价证券的贴现率。DISC(settlement,maturity,pr, redemption,basis)
DOLLARDE将按分数表示的价格转换为按小数表示的价格,如证券价格,转换为小数表示的数字。DOLLARDE(fractional_dollar, fraction)
DOLLARFR将按小数表示的价格转换为按分数表示的价格。如证券价格,转换为分数型数字。DOLLARFR(decimal_dollar, fraction)
DURATION返回假设面值$100 的定期付息有价证券的修正期限。期限定义为一系列现金流现值的加权平均值,用于计量债券价格对于收益率变化的敏感程度。DURATION(settlement,maturity, coupon yld,frequency,basis)
EFFECT利用给定的名义年利率和一年中的复利期次,计算实际年利率。EFFECT(nominal_rate,npery)
FV基于固定利率及等额分期付款方式,返回某项投资的未来值。FV(rate,nper,pmt,pv,type)
FVSCHEDULE基于一系列复利返回本金的未来值。函数FVSCHDULE 用于计算某项投资在变动或可调利率下的未来值。FVSCHEDULE(principal,schedule)
INTRATE返回一次性付息证券的利率。INTRATE(settlement,maturity, investment,redemption,basis)
IPMT基于固定利率及等额分期付款方式,返回投资或贷款在某一给定期次内的利息偿还额。IPMT(rate,per,nper,pv,fv,type)
IRR返回由数值代表的一组现金流的内部收益率。IRR(values,guess)
ISPMT计算特定投资期内要支付的利息。ISPMT(rate,per,nper,pv)
MDURATION返回假设面值$100 的有价证券的Macauley 修正期限。MDURATION(settlement,maturity, coupon,yld,frequency,basis)
MIRR返回某一连续期间内现金流的修正内部收益率。MIRR(values,finance_rate, reinvest_rate)
NOMINAL基于给定的实际利率和年复利期数,返回名义年利率。NOMINAL(effect_rate,npery)
NPER基于固定利率及等额分期付款方式,返回某项投资(或贷款)的总期数。NPER(rate, pmt, pv, fv, type)
NPV通过使用贴现率以及一系列未来支出(负值)和收入(正值),返回一项投资的净现值。NPV(rate,value1,value2, ...)
ODDFPRICE返回首期付息日不固定的面值$100 的有价证券的价格ODDFPRICE(settlement,maturity, issue,first_coupon,rate,yld, redemption, frequency,basis)
ODDFYIELD返回首期付息日不固定的有价证券(长期或短期)的收益率。ODDFYIELD(settlement,maturity, issue,first_coupon,rate,pr, redemption, frequency,basis)
ODDLPRICE返回末期付息日不固定的面值$100 的有价证券(长期或短期)的价格。ODDLPRICE(settlement,maturity, last_interest,rate,yld,redemption, frequency,basis)
ODDLYIELD返回末期付息日不固定的有价证券(长期或短期)的收益率。ODDLYIELD(settlement,maturity, last_interest,rate,pr,redemption, frequency,basis)
PMT基于固定利率及等额分期付款方式,返回贷款的每期付款额。PMT(rate,nper,pv,fv,type)
PPMT基于固定利率及等额分期付款方式,返回投资在某一给定期间内的本金偿还额。PPMT(rate,per,nper,pv,fv,type)
PRICE返回定期付息的面值$100 的有价证券的价格。PRICE(settlement,maturity, rate,yld,redemption,frequency, basis)
PRICEDISC返回折价发行的面值$100 的有价证券的价格。PRICEDISC(settlement,maturity, discount,redemption,basis)
PRICEMAT返回到期付息的面值$100 的有价证券的价格。PRICEMAT(settlement,maturity, issue,rate,yld,basis)
PV返回投资的现值。现值为一系列未来付款的当前值的累积和。例如,借入方的借入款即为贷出方贷款的现值。PV(rate,nper,pmt,fv,type)
RATE返回年金的各期利率。函数 RATE 通过迭代法计算得出,并且可能无解或有多个解。RATE(nper,pmt,pv,fv,type,guess)
RECEIVED返回一次性付息的有价证券到期收回的金额。RECEIVED(settlement,maturity, investment,discount,basis)
SLN返回某项资产在一个期间中的线性折旧值。SLN(cost,salvage,life)
SYD返回某项资产按年限总和折旧法计算的指定期间的折旧值。SYD(cost,salvage,life,per)
TBILLEQ返回国库券的等效收益率。TBILLEQ(settlement,maturity, discount)
TBILLPRICE返回面值$100 的国库券的价格。TBILLPRICE(settlement,maturity, discount)
TBILLYIELD返回国库券的收益率。TBILLYIELD(settlement,maturity,pr)
VDB使用双倍余额递减法或其他指定的方法,返回指定的任何期间内(包括部分期间)的资产折旧值。函数VDB 代表可变余额递减法。VDB(cost,salvage,life,start_period, end_period,factor,no_switch)
XIRR返回一组现金流的内部收益率,这些现金流不一定定期发生。若要计算一组定期现金流的内部收益率,请使用函数IRR。XIRR(values,dates,guess)
XNPV返回一组现金流的净现值,这些现金流不一定定期发生。若要计算一组定期现金流的净现值,请使用函数NPV。XNPV(rate,values,dates)
YIELD返回定期付息有价证券的收益率,函数YIELD 用于计算债券收益率。YIELD(settlement,maturity,rate, pr,redemption,frequency,basis)
YIELDDISC返回折价发行的有价证券的年收益率。YIELDDISC(settlement,maturity, pr,redemption,basis)
YIELDMAT返回到期付息的有价证券的年收益率。YIELDMAT(settlement,maturity, issue,rate,pr,basis)

本文链接:http://168361.com/excelhanshu/2406.html (转载请保留)