Ecel函數(shù)運用之財務函數(shù)

1、Excel函數(shù)應用之財務函數(shù) 像統(tǒng)計函數(shù)、工程函數(shù)一樣，在Excel中還提供了許多財務函數(shù)。財務函數(shù)能夠進行一般的財務計算，如確定貸款的支付額、投資的以后值或凈現(xiàn)值，以及債券或息票的價值。這些財務函數(shù)大體上可分為四類：投資計算函數(shù)、折舊計算函數(shù)、償還率計算函數(shù)、債券及其他金融函數(shù)。它們?yōu)樨攧辗治鎏峁┝藰O大的便利。使用這些函數(shù)不必理解高級財務知識，只要填寫變量值就能夠了。在下文中，凡是投資的金額都以負數(shù)形式表示，收益以正數(shù)形式表示。 在介紹具體的財務函數(shù)之前，我們首先來了解一下財務函數(shù)中常見的參數(shù)： 以后值 (fv)-在所有付款發(fā)生后的投資或貸款的價值。 期間數(shù) (nper)-為總投資（或貸款）

2、期，即該項投資（或貸款）的付款期總數(shù)。 付款 (pmt)-關于一項投資或貸款的定期支付數(shù)額。其數(shù)值在整個年金期間保持不變。通常 pmt 包括本金和利息，但不包括其他費用及稅款。 現(xiàn)值 (pv)-在投資期初的投資或貸款的價值。例如，貸款的現(xiàn)值為所借入的本金數(shù)額。 利率 (rate)-投資或貸款的利率或貼現(xiàn)率。 類型 (type)-付款期間內(nèi)進行支付的間隔，如在月初或月末，用0或1表示。 日計數(shù)基準類型（basis）-為日計數(shù)基準類型。Basis為0 或省略代表US (NASD) 30/360 ，為1代表實際天數(shù)/實際天數(shù) ，為2代表實際天數(shù)/360 ，為3代表實際天數(shù)/365 ，為4代表歐洲30

3、/360。 接下來，我們將分不舉例講明各種不同的財務函數(shù)的應用。在本文中要緊介紹各類型的典型財務函數(shù)，更多的財務函數(shù)請參看附表及相關書籍。假如下文中所介紹的函數(shù)不可用，返回錯誤值 #NAME?，請安裝并加載分析工具庫加載宏。操作方法為： 1、在工具菜單上，單擊加載宏。 2、在可用加載宏列表中，選中分析工具庫框，再單擊確定。 一、投資計算函數(shù) 投資計算函數(shù)可分為與以后值fv有關，與付款pmt有關，與現(xiàn)值pv有關，與復利計算有關及與期間數(shù)有關幾類函數(shù)。 1、與以后值fv有關的函數(shù)-FV、FVSCHEDULE 2、與付款pmt有關的函數(shù)-IPMT、ISPMT、PMT、PPMT 3、與現(xiàn)值pv有關的函

4、數(shù)-NPV、PV、XNPV 4、與復利計算有關的函數(shù)-EFFECT、NOMINAL 5、與期間數(shù)有關的函數(shù)-NPER 在投資計算函數(shù)中，筆者將重點介紹FV、NPV、PMT、PV函數(shù)。 （一） 求某項投資的以后值FV 在日常工作與生活中，我們經(jīng)常會遇到要計算某項投資的以后值的情況，現(xiàn)在利用Excel函數(shù)FV進行計算后，能夠關心我們進行一些有打算、有目的、有效益的投資。FV函數(shù)基于固定利率及等額分期付款方式，返回某項投資的以后值。 語法形式為FV(rate,nper,pmt,pv,type)。其中rate為各期利率，是一固定值，nper為總投資（或貸款）期，即該項投資（或貸款）的付款期總數(shù)，pv為

5、各期所應付給（或得到）的金額，其數(shù)值在整個年金期間（或投資期內(nèi)）保持不變，通常Pv包括本金和利息，但不包括其它費用及稅款，pv為現(xiàn)值，或一系列以后付款當前值的累積和，也稱為本金，假如省略pv，則假設其值為零，type為數(shù)字0或1，用以指定各期的付款時刻是在期初依舊期末，假如省略t，則假設其值為零。 例如：假如某人兩年后需要一筆比較大的學習費用支出，打算從現(xiàn)在起每月初存入2000元，假如按年利2.25%，按月計息（月利為2.25%/12），那么兩年以后該賬戶的存款額會是多少呢？ 公式寫為：FV(2.25%/12, 24,-2000,0,1) 圖1 （二） 求投資的凈現(xiàn)值NPV NPV函數(shù)基于一系

6、列現(xiàn)金流和固定的各期貼現(xiàn)率，返回一項投資的凈現(xiàn)值。投資的凈現(xiàn)值是指以后各期支出（負值）和收入（正值）的當前值的總和。 語法形式為：NPV(rate,value1,value2, .) 其中，rate為各期貼現(xiàn)率，是一固定值；value1,value2,.代表1到29筆支出及收入的參數(shù)值，value1,value2,.所屬各期間的長度必須相等，而且支付及收入的時刻都發(fā)生在期末。需要注意的是：NPV按次序使用value1,value2，來注釋現(xiàn)金流的次序。因此一定要保證支出和收入的數(shù)額按正確的順序輸入。假如參數(shù)是數(shù)值、空白單元格、邏輯值或表示數(shù)值的文字表示式，則都會計算在內(nèi)；假如參數(shù)是錯誤值或不能

7、轉化為數(shù)值的文字，則被忽略，假如參數(shù)是一個數(shù)組或引用，只有其中的數(shù)值部分計算在內(nèi)。忽略數(shù)組或引用中的空白單元格、邏輯值、文字及錯誤值。 例如，假設開一家電器經(jīng)銷店。初期投資￥200,000，而希望以后五年中各年的收入分不為￥20,000、￥40,000、￥50,000、￥80,000和￥120,000。假定每年的貼現(xiàn)率是8%（相當于通貸膨脹率或競爭投資的利率），則投資的凈現(xiàn)值的公式是： =NPV(A2, A4:A8)+A3 在該例中，一開始投資的￥200,000并不包含在v參數(shù)中，因為此項付款發(fā)生在第一期的期初。假設該電器店的營業(yè)到第六年時，要重新裝修門面，可能要付出￥40,000，則六年后書

8、店投資的凈現(xiàn)值為： =NPV(A2, A4:A8, A9)+A3 假如期初投資的付款發(fā)生在期末，則 投資的凈現(xiàn)值的公式是： =NPV(A2, A3:A8) 圖2（三） 求貸款分期償還額PMT PMT函數(shù)基于固定利率及等額分期付款方式，返回投資或貸款的每期付款額。PMT函數(shù)能夠計算為償還一筆貸款，要求在一定周期內(nèi)支付完時，每次需要支付的償還額，也確實是我們平常所講的分期付款。比如借購房貸款或其它貸款時，能夠計算每期的償還額。 其語法形式為：PMT(rate,nper,pv,fv,type) 其中，rate為各期利率，是一固定值，nper為總投資（或貸款）期，即該項投資（或貸款）的付款期總數(shù)，pv

9、為現(xiàn)值，或一系列以后付款當前值的累積和，也稱為本金，fv為以后值，或在最后一次付款后希望得到的現(xiàn)金余額，假如省略fv，則假設其值為零（例如，一筆貸款的以后值即為零），type為0或1，用以指定各期的付款時刻是在期初依舊期末。假如省略type，則假設其值為零。 例如，需要10個月付清的年利率為8%的￥10,000貸款的月支額為： PMT（8%/12,10,10000） 計算結果為：-￥1,037.03。 （四） 求某項投資的現(xiàn)值PV PV函數(shù)用來計算某項投資的現(xiàn)值。年金現(xiàn)值確實是以后各期年金現(xiàn)在的價值的總和。假如投資回收的當前價值大于投資的價值，則這項投資是有收益的。 其語法形式為：PV(rat

10、e,nper,pmt,fv,type) 其中Rate為各期利率。Nper為總投資（或貸款）期，即該項投資（或貸款）的付款期總數(shù)。Pmt為各期所應支付的金額，其數(shù)值在整個年金期間保持不變。通常 pmt 包括本金和利息，但不包括其他費用及稅款。Fv 為以后值，或在最后一次支付后希望得到的現(xiàn)金余額，假如省略 fv，則假設其值為零（一筆貸款的以后值即為零）。Type用以指定各期的付款時刻是在期初依舊期末。 例如，假設要購買一項保險年金，該保險能夠在今后二十年內(nèi)于每月末回報￥600。此項年金的購買成本為80,000，假定投資回報率為8%。那么該項年金的現(xiàn)值為： PV(0.08/12, 12*20,600

11、,0) 計算結果為：￥-71,732.58。 負值表示這是一筆付款，也確實是支出現(xiàn)金流。年金（￥-71,732.58）的現(xiàn)值小于實際支付的（￥80,000）。因此，這不是一項合算的投資。 圖3二、 折舊計算函數(shù) 折舊計算函數(shù)要緊包括AMORDEGRC、AMORLINC、DB、DDB、SLN、SYD、VDB。這些函數(shù)差不多上用來計算資產(chǎn)折舊的，只是采納了不同的計算方法。那個地點，關于具體的計算公式不再贅述，具體選用哪種折舊方法，則須視各單位情況而定。 三、償還率計算函數(shù) 償還率計算函數(shù)要緊用以計算內(nèi)部收益率，包括IRR、MIRR、RATE和XIRR幾個函數(shù)。 （一） 返回內(nèi)部收益率的函數(shù)-IRR

12、 IRR函數(shù)返回由數(shù)值代表的一組現(xiàn)金流的內(nèi)部收益率。這些現(xiàn)金流不一定必須為均衡的，但作為年金，它們必須按固定的間隔發(fā)生，如按月或按年。內(nèi)部收益率為投資的回收利率，其中包含定期支付（負值）和收入（正值）。 其語法形式為IRR(values,guess) 其中values為數(shù)組或單元格的引用，包含用來計算內(nèi)部收益率的數(shù)字，values必須包含至少一個正值和一個負值，以計算內(nèi)部收益率，函數(shù)IRR依照數(shù)值的順序來解釋現(xiàn)金流的順序，故應確定按需要的順序輸入了支付和收入的數(shù)值，假如數(shù)組或引用包含文本、邏輯值或空白單元格，這些數(shù)值將被忽略；guess為對函數(shù)IRR計算結果的可能值，excel使用迭代法計算函

13、數(shù)IRR從guess開始，函數(shù)IRR不斷修正收益率，直至結果的精度達到0.00001%，假如函數(shù)IRR通過20次迭代，仍未找到結果，則返回錯誤值#NUM！，在大多數(shù)情況下，并不需要為函數(shù)IRR的計算提供guess值，假如省略guess，假設它為0.1（10%）。假如函數(shù)IRR返回錯誤值#NUM！，或結果沒有靠近期望值，能夠給guess換一個值再試一下。 例如，假如要開辦一家服裝商店，可能投資為￥110,000，并預期為今后五年的凈收益為：￥15,000、￥21,000、￥28,000、￥36,000和￥45,000。分不求出投資兩年、四年以及五年后的內(nèi)部收益率。 圖4在工作表的B1：B6輸入數(shù)

14、據(jù)函數(shù).xls所示，計算此項投資四年后的內(nèi)部收益率IRR（B1：B5）為-3.27%；計算此項投資五年后的內(nèi)部收益率IRR（B1：B6）為8.35%;計算兩年后的內(nèi)部收益率時必須在函數(shù)中包含guess，即IRR（B1：B3，-10%）為-48.96%。 （二） 用RATE函數(shù)計算某項投資的實際贏利 在經(jīng)濟生活中，經(jīng)常要評估當前某項投資的運作情況，或某個新企業(yè)的現(xiàn)狀。例如某承包人建議你貸給他30000元，用作公共工程建設資金，并同意每年付給你9000元，共付五年，以此作為這筆貸款的最低回報。那么你如何去決策這筆投資？如何明白這項投資的回報率呢？關于這種周期性償付或是一次償付完的投資，用RATE函

15、數(shù)能夠專門快地計算出實際的贏利。其語法形式為RATE(nper,pmt,pv,fv,type,guess)。 具體操作步驟如下： 1、選取存放數(shù)據(jù)的單元格，并按上述相似的方法把此單元格指定為百分數(shù)的格式。 2、插入函數(shù)RATE，打開粘貼函數(shù)對話框。 3、在粘貼函數(shù)對話框中，在Nper中輸入償還周期5(年),在Pmt中輸入7000(每年的回報額)，在Pv中輸入30000(投資金額)。即公式為=RATE(5,9000,-30000) 4、確定后計算結果為15.24。這確實是本項投資的每年實際贏利，你能夠依照那個值推斷那個贏利是否中意，或是決定投資其它項目，或是重新談判每年的回報。 四、債券及其他金

16、融函數(shù) 債券及其他金融函數(shù)又可分為計算本金、利息的函數(shù)，與利息支付時刻有關的函數(shù)、與利率收益率有關的函數(shù)、與修正期限有關的函數(shù)、與有價證券有關的函數(shù)以及與證券價格表示有關的函數(shù)。 1、計算本金、利息的函數(shù)-CUMPRINC、ACCRINT、ACCRINTM、CUMIPMT、COUPNUM 2、與利息支付時刻有關的函數(shù)-COUPDAYBS、COUPDAYS、COUPDAYSNC、COUPNCD、COUPPCD 3、 與利率收益率有關的函數(shù)-INTRATE、ODDFYIELD、ODDLYIELD、TBILLEQ、TBILLPRICE、TBILLYIELD、YIELD、YIELDDISC、YIELD

17、MAT 4、與修正期限有關的函數(shù)-DURATION、MDURATION 5、與有價證券有關的函數(shù)-DISC、ODDFPRICE、ODDLPRICE、PRICE、PRICEDISC、PRICEMAT、RECEIVED 6、與證券價格表示有關的函數(shù)-DOLLARDE、DOLLARFR 在債券及其他金融函數(shù)中，筆者將重點介紹函數(shù)ACCRINT、CUMPRINC、DISC。 （一）求定期付息有價證券的應計利息的函數(shù)ACCRINT ACCRINT函數(shù)能夠返回定期付息有價證券的應計利息。 其語法形式為ACCRINT(issue,first_interest,settlement,rate,par,freq

18、uency,basis) 其中issue為有價證券的發(fā)行日，first_interest為有價證券的起息日，settlement為有價證券的成交日，即在發(fā)行日之后，有價證券賣給購買者的日期，rate為有價證券的年息票利率，par為有價證券的票面價值，假如省略par，函數(shù)ACCRINT就會自動將par設置為￥1000，frequency為年付息次數(shù)，basis為日計數(shù)基準類型。 例如，某國庫券的交易情況為：發(fā)行日為2008年3月1日；起息日為2008年8月31日；成交日為2008年5月1日，息票利率為10.0%；票面價值為￥1,000；按半年期付息；日計數(shù)基準為30/360，那么應計利息為： 圖

19、5（二）求本金數(shù)額CUMPRINC CUMPRINC函數(shù)用于返回一筆貨款在給定的st到en期間累計償還的本金數(shù)額。其語法形式為CUMPRINC(rate,nper,pv,start_period,end_period,type) 其中rate為利率，nper為總付款期數(shù)，pv為現(xiàn)值，start_period為計算中的首期，付款期數(shù)從1開始計數(shù)，end_period為計算中的末期，type為付款時刻類型。 例如，一筆住房抵押貸款的交易情況如下：年利率為9.00%；期限為30年；現(xiàn)值為￥125，000。由上述已知條件能夠計算出：r=9.00%/12=0.0075，np=30*12=360。 圖6那

20、么該筆貸款在第下半年償還的全部本金之中（第7期到第12期）為： =CUMPRINC(A2/12,A3*12,A4,7,12,0)計算結果為：-436.568194。 該筆貸款在第一個月償還的本金為：=CUMPRINC(A2/12,A3*12,A4,1,1,0)計算結果為：-68.27827118。 （三） 求有價證券的貼現(xiàn)率DISC DISC函數(shù)返回有價證券的貼現(xiàn)率。 其語法形式為DISC(settlement,maturity,pr,redemption,basis) 其中settlement為有價證券的成交日，即在發(fā)行日之后，有價證券賣給購買者的日期，maturity為有價證券的到日期，到

21、期日是有價證券有效期截止時的日期，pr為面值為￥100的有價證券的價格，redemption為面值為￥100的有價證券的清償價格，basis為日計數(shù)基準類型。 例如：某債券的交易情況如下：成交日為99年3月18日，到期日為99年8月7日，價格為￥48.834，清償價格為￥52，日計數(shù)基準為實際天數(shù)/360。那么該債券的貼現(xiàn)率為： DISC(99/3/18,99/8/7,48.834,52,2) 計算結果為：0.154355363。 函數(shù)名稱函數(shù)講明語法形式ACCRINT返回定期付息有價證券的應計利息。ACCRINT(issue,first_interest, settlement,rate,p

22、ar,frequency, basis)ACCRINTM返回到期一次性付息有價證券的應計利息。ACCRINTM(issue,maturity,rate, par,basis)AMORDEGRC返回每個會計期間的折舊值。此函數(shù)是為法國會計系統(tǒng)提供的。AMORDEGRC(cost,date_purchased, first_period,salvage,period, rate,basis)AMORLINC返回每個會計期間的折舊值，該函數(shù)為法國會計系統(tǒng)提供。AMORLINC(cost,date_purchased, first_period,salvage,period, rate,basis)C

23、OUPDAYBS返回當前付息期內(nèi)截止到成交日的天數(shù)。COUPDAYBS(settlement,maturity, frequency, basis)COUPDAYS返回成交日所在的付息期的天數(shù)。COUPDAYS(settlement,maturity, frequency, basis)COUPDAYSNC返回從成交日到下一付息日之間的天數(shù)。COUPDAYSNC(settlement,maturity, frequency, basis)COUPNCD返回成交日過后的下一付息日的日期。COUPNCD(settlement,maturity, frequency, basis)COUPNUM返回

24、成交日和到期日之間的利息應付次數(shù)，向上取整到最近的整數(shù)。COUPNUM(settlement,maturity, frequency, basis)COUPPCD返回成交日之前的上一付息日的日期。COUPPCD(settlement,maturity, frequency, basis)CUMIPMT返回一筆貸款在給定的 start-period 到 end-period 期間累計償還的利息數(shù)額。CUMIPMT(rate,nper,pv,start_period, end_period,type)CUMPRINC返回一筆貸款在給定的 start-period 到 end-period 期間累計

25、償還的本金數(shù)額。CUMPRINC(rate,nper,pv,start_period, end_period,type)DB使用固定余額遞減法，計算一筆資產(chǎn)在給定期間內(nèi)的折舊值。DB(cost,salvage,life,period,month)DDB使用雙倍余額遞減法或其他指定方法，計算一筆資產(chǎn)在給定期間內(nèi)的折舊值。DDB(cost,salvage,life,period,factor)DISC返回有價證券的貼現(xiàn)率。DISC(settlement,maturity,pr, redemption,basis)DOLLARDE將按分數(shù)表示的價格轉換為按小數(shù)表示的價格，如證券價格，轉換為小數(shù)表示的

26、數(shù)字。DOLLARDE(fractional_dollar, fraction)DOLLARFR將按小數(shù)表示的價格轉換為按分數(shù)表示的價格。如證券價格，轉換為分數(shù)型數(shù)字。DOLLARFR(decimal_dollar, fraction)DURATION返回假設面值 $100 的定期付息有價證券的修正期限。期限定義為一系列現(xiàn)金流現(xiàn)值的加權平均值，用于計量債券價格關于收益率變化的敏感程度。DURATION(settlement,maturity, coupon yld,frequency,basis)EFFECT利用給定的名義年利率和一年中的復利期次，計算實際年利率。EFFECT(nominal_

27、rate,npery)FV基于固定利率及等額分期付款方式，返回某項投資的以后值。FV(rate,nper,pmt,pv,type)FVSCHEDULE基于一系列復利返回本金的以后值。函數(shù) FVSCHDULE 用于計算某項投資在變動或可調利率下的以后值。FVSCHEDULE(principal,schedule)INTRATE返回一次性付息證券的利率。INTRATE(settlement,maturity, investment,redemption,basis)IPMT基于固定利率及等額分期付款方式，返回投資或貸款在某一給定期次內(nèi)的利息償還額。IPMT(rate,per,nper,pv,fv,

28、type)IRR返回由數(shù)值代表的一組現(xiàn)金流的內(nèi)部收益率。IRR(values,guess)ISPMT計算特定投資期內(nèi)要支付的利息。ISPMT(rate,per,nper,pv)MDURATION返回假設面值 $100 的有價證券的 Macauley 修正期限。MDURATION(settlement,maturity, coupon,yld,frequency,basis)MIRR返回某一連續(xù)期間內(nèi)現(xiàn)金流的修正內(nèi)部收益率。MIRR(values,finance_rate, reinvest_rate)NOMINAL基于給定的實際利率和年復利期數(shù)，返回名義年利率。NOMINAL(effect_r

29、ate,npery)NPER基于固定利率及等額分期付款方式，返回某項投資（或貸款）的總期數(shù)。NPER(rate, pmt, pv, fv, type)NPV通過使用貼現(xiàn)率以及一系列以后支出（負值）和收入（正值），返回一項投資的凈現(xiàn)值。NPV(rate,value1,value2, .)ODDFPRICE返回首期付息日不固定的面值 $100 的有價證券的價格ODDFPRICE(settlement,maturity, issue,first_coupon,rate,yld, redemption, frequency,basis)ODDFYIELD返回首期付息日不固定的有價證券（長期或短期）的收

30、益率。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,rat

31、e,pr,redemption, frequency,basis)PMT基于固定利率及等額分期付款方式，返回貸款的每期付款額。PMT(rate,nper,pv,fv,type)PPMT基于固定利率及等額分期付款方式，返回投資在某一給定期間內(nèi)的本金償還額。PPMT(rate,per,nper,pv,fv,type)PRICE返回定期付息的面值 $100 的有價證券的價格。PRICE(settlement,maturity, rate,yld,redemption,frequency, basis)PRICEDISC返回折價發(fā)行的面值 $100 的有價證券的價格。PRICEDISC(settlement,maturity, discount,redemption,basis)PRICEMAT返回到期付息的面值 $100 的有價證券的價格。PRICEMAT(settlement,maturity, issue,rate,yld,basis)PV返回投資的