excel財(cái)務(wù)函數(shù)應(yīng)用有哪些需要了解的
excel財(cái)務(wù)函數(shù)應(yīng)用有哪些需要了解的
像統(tǒng)計(jì)函數(shù)、工程函數(shù)一樣，在Excel中還提供了許多財(cái)務(wù)函數(shù)。財(cái)務(wù)函數(shù)可以進(jìn)行一般的財(cái)務(wù)計(jì)算，如確定貸款的支付額、投資的未來(lái)值或凈現(xiàn)值，以及債券或息票的價(jià)值。以下是學(xué)習(xí)啦小編為您帶來(lái)的關(guān)于財(cái)務(wù)必看的四類excel財(cái)務(wù)函數(shù)應(yīng)用，希望對(duì)您有所幫助。
財(cái)務(wù)必看的四類excel財(cái)務(wù)函數(shù)應(yīng)用
這些財(cái)務(wù)函數(shù)大體上可分為四類：投資計(jì)算函數(shù)、折舊計(jì)算函數(shù)、償還率計(jì)算函數(shù)、債券及其他金融函數(shù)。它們?yōu)樨?cái)務(wù)分析提供了極大的便利。使用這些函數(shù)不必理解高級(jí)財(cái)務(wù)知識(shí)，只要填寫變量值就可以了。在下文中，凡是投資的金額都以負(fù)數(shù)形式表示，收益以正數(shù)形式表示。
在介紹具體的財(cái)務(wù)函數(shù)之前，我們首先來(lái)了解一下財(cái)務(wù)函數(shù)中常見(jiàn)的參數(shù)：
未來(lái)值 (fv)--在所有付款發(fā)生后的投資或貸款的價(jià)值。
期間數(shù) (nper)--為總投資(或貸款)期，即該項(xiàng)投資(或貸款)的付款期總數(shù)。
付款 (pmt)--對(duì)于一項(xiàng)投資或貸款的定期支付數(shù)額。其數(shù)值在整個(gè)年金期間保持不變。通常 pmt 包括本金和利息，但不包括其他費(fèi)用及稅款。
現(xiàn)值 (pv)--在投資期初的投資或貸款的價(jià)值。例如，貸款的現(xiàn)值為所借入的本金數(shù)額。
利率 (rate)--投資或貸款的利率或貼現(xiàn)率。
類型 (type)--付款期間內(nèi)進(jìn)行支付的間隔，如在月初或月末，用0或1表示。
日計(jì)數(shù)基準(zhǔn)類型(basis)--為日計(jì)數(shù)基準(zhǔn)類型。Basis為0 或省略代表US (NASD) 30/360 ，為1代表實(shí)際天數(shù)/實(shí)際天數(shù) ，為2代表實(shí)際天數(shù)/360 ，為3代表實(shí)際天數(shù)/365 ，為4代表歐洲30/360。
接下來(lái)，我們將分別舉例說(shuō)明各種不同的財(cái)務(wù)函數(shù)的應(yīng)用。在本文中主要介紹各類型的典型財(cái)務(wù)函數(shù)，更多的財(cái)務(wù)函數(shù)請(qǐng)參看附表及相關(guān)書籍。如果下文中所介紹的函數(shù)不可用，返回錯(cuò)誤值 #NAME?，請(qǐng)安裝并加載"分析工具庫(kù)"加載宏。操作方法為：
1、在"工具"菜單上，單擊"加載宏"。
2、在"可用加載宏"列表中，選中"分析工具庫(kù)"框，再單擊"確定"。
一、投資計(jì)算函數(shù)
投資計(jì)算函數(shù)可分為與未來(lái)值fv有關(guān)，與付款pmt有關(guān)，與現(xiàn)值pv有關(guān)，與復(fù)利計(jì)算有關(guān)及與期間數(shù)有關(guān)幾類函數(shù)。
1、與未來(lái)值fv有關(guān)的函數(shù)--FV、FVSCHEDULE
2、與付款pmt有關(guān)的函數(shù)--IPMT、ISPMT、PMT、PPMT
3、與現(xiàn)值pv有關(guān)的函數(shù)--NPV、PV、XNPV
4、與復(fù)利計(jì)算有關(guān)的函數(shù)--EFFECT、NOMINAL
5、與期間數(shù)有關(guān)的函數(shù)--NPER
在投資計(jì)算函數(shù)中，筆者將重點(diǎn)介紹FV、NPV、PMT、PV函數(shù)。
(一) 求某項(xiàng)投資的未來(lái)值FV
在日常工作與生活中，我們經(jīng)常會(huì)遇到要計(jì)算某項(xiàng)投資的未來(lái)值的情況，此時(shí)利用Excel函數(shù)FV進(jìn)行計(jì)算后，可以幫助我們進(jìn)行一些有計(jì)劃、有目的、有效益的投資。FV函數(shù)基于固定利率及等額分期付款方式，返回某項(xiàng)投資的未來(lái)值。
語(yǔ)法形式為FV(rate,nper,pmt,pv,type)。其中rate為各期利率，是一固定值，nper為總投資(或貸款)期，即該項(xiàng)投資(或貸款)的付款期總數(shù)，pv為各期所應(yīng)付給(或得到)的金額，其數(shù)值在整個(gè)年金期間(或投資期內(nèi))保持不變，通常Pv包括本金和利息，但不包括其它費(fèi)用及稅款，pv為現(xiàn)值，或一系列未來(lái)付款當(dāng)前值的累積和，也稱為本金，如果省略pv，則假設(shè)其值為零，type為數(shù)字0或1，用以指定各期的付款時(shí)間是在期初還是期末，如果省略t，則假設(shè)其值為零。
例如：假如某人兩年后需要一筆比較大的學(xué)習(xí)費(fèi)用支出，計(jì)劃從現(xiàn)在起每月初存入2000元，如果按年利2.25%，按月計(jì)息(月利為2.25%/12)，那么兩年以后該賬戶的存款額會(huì)是多少呢?
公式寫為：FV(2.25%/12, 24,-2000,0,1)
(二) 求投資的凈現(xiàn)值NPV
NPV函數(shù)基于一系列現(xiàn)金流和固定的各期貼現(xiàn)率，返回一項(xiàng)投資的凈現(xiàn)值。投資的凈現(xiàn)值是指未來(lái)各期支出(負(fù)值)和收入(正值)的當(dāng)前值的總和。
語(yǔ)法形式為：NPV(rate,value1,value2, ...) 其中，rate為各期貼現(xiàn)率，是一固定值;value1,value2,...代表1到29筆支出及收入的參數(shù)值，value1,value2,...所屬各期間的長(zhǎng)度必須相等，而且支付及收入的時(shí)間都發(fā)生在期末。需要注意的是：NPV按次序使用value1,value2，來(lái)注釋現(xiàn)金流的次序。所以一定要保證支出和收入的數(shù)額按正確的順序輸入。如果參數(shù)是數(shù)值、空白單元格、邏輯值或表示數(shù)值的文字表示式，則都會(huì)計(jì)算在內(nèi);如果參數(shù)是錯(cuò)誤值或不能轉(zhuǎn)化為數(shù)值的文字，則被忽略，如果參數(shù)是一個(gè)數(shù)組或引用，只有其中的數(shù)值部分計(jì)算在內(nèi)。忽略數(shù)組或引用中的空白單元格、邏輯值、文字及錯(cuò)誤值。
例如，假設(shè)開(kāi)一家電器經(jīng)銷店。初期投資￥200,000，而希望未來(lái)五年中各年的收入分別為￥20,000、￥40,000、￥50,000、￥80,000和￥120,000。假定每年的貼現(xiàn)率是8%(相當(dāng)于通貸膨脹率或競(jìng)爭(zhēng)投資的利率)，則投資的凈現(xiàn)值的公式是：
=NPV(A2, A4:A8)+A3
在該例中，一開(kāi)始投資的￥200,000并不包含在v參數(shù)中，因?yàn)榇隧?xiàng)付款發(fā)生在第一期的期初。假設(shè)該電器店的營(yíng)業(yè)到第六年時(shí)，要重新裝修門面，估計(jì)要付出￥40,000，則六年后書店投資的凈現(xiàn)值為：
=NPV(A2, A4:A8, A9)+A3
[NextPage]
(三) 求貸款分期償還額PMT
PMT函數(shù)基于固定利率及等額分期付款方式，返回投資或貸款的每期付款額。PMT函數(shù)可以計(jì)算為償還一筆貸款，要求在一定周期內(nèi)支付完時(shí)，每次需要支付的償還額，也就是我們平時(shí)所說(shuō)的"分期付款"。比如借購(gòu)房貸款或其它貸款時(shí)，可以計(jì)算每期的償還額。
其語(yǔ)法形式為：PMT(rate,nper,pv,fv,type) 其中，rate為各期利率，是一固定值，nper為總投資(或貸款)期，即該項(xiàng)投資(或貸款)的付款期總數(shù)，pv為現(xiàn)值，或一系列未來(lái)付款當(dāng)前值的累積和，也稱為本金，fv為未來(lái)值，或在最后一次付款后希望得到的現(xiàn)金余額，如果省略fv，則假設(shè)其值為零(例如，一筆貸款的未來(lái)值即為零)，type為0或1，用以指定各期的付款時(shí)間是在期初還是期末。如果省略type，則假設(shè)其值為零。
例如，需要10個(gè)月付清的年利率為8%的￥10,000貸款的月支額為：
PMT(8%/12,10,10000) 計(jì)算結(jié)果為：-￥1,037.03。
(四) 求某項(xiàng)投資的現(xiàn)值PV
PV函數(shù)用來(lái)計(jì)算某項(xiàng)投資的現(xiàn)值。年金現(xiàn)值就是未來(lái)各期年金現(xiàn)在的價(jià)值的總和。如果投資回收的當(dāng)前價(jià)值大于投資的價(jià)值，則這項(xiàng)投資是有收益的。
其語(yǔ)法形式為：PV(rate,nper,pmt,fv,type) 其中Rate為各期利率。Nper為總投資(或貸款)期，即該項(xiàng)投資(或貸款)的付款期總數(shù)。Pmt為各期所應(yīng)支付的金額，其數(shù)值在整個(gè)年金期間保持不變。通常 pmt 包括本金和利息，但不包括其他費(fèi)用及稅款。Fv 為未來(lái)值，或在最后一次支付后希望得到的現(xiàn)金余額，如果省略 fv，則假設(shè)其值為零(一筆貸款的未來(lái)值即為零)。Type用以指定各期的付款時(shí)間是在期初還是期末。
例如，假設(shè)要購(gòu)買一項(xiàng)保險(xiǎn)年金，該保險(xiǎn)可以在今后二十年內(nèi)于每月末回報(bào)￥600。此項(xiàng)年金的購(gòu)買成本為80,000，假定投資回報(bào)率為8%。那么該項(xiàng)年金的現(xiàn)值為：
PV(0.08/12, 12*20,600,0) 計(jì)算結(jié)果為：￥-71,732.58。
負(fù)值表示這是一筆付款，也就是支出現(xiàn)金流。年金(￥-71,732.58)的現(xiàn)值小于實(shí)際支付的(￥80,000)。因此，這不是一項(xiàng)合算的投資。
二、 折舊計(jì)算函數(shù)
折舊計(jì)算函數(shù)主要包括AMORDEGRC、AMORLINC、DB、DDB、SLN、SYD、VDB。這些函數(shù)都是用來(lái)計(jì)算資產(chǎn)折舊的，只是采用了不同的計(jì)算方法。這里，對(duì)于具體的計(jì)算公式不再贅述，具體選用哪種折舊方法，則須視各單位情況而定。
三、償還率計(jì)算函數(shù)
償還率計(jì)算函數(shù)主要用以計(jì)算內(nèi)部收益率，包括IRR、MIRR、RATE和XIRR幾個(gè)函數(shù)。
(一) 返回內(nèi)部收益率的函數(shù)--IRR
IRR函數(shù)返回由數(shù)值代表的一組現(xiàn)金流的內(nèi)部收益率。這些現(xiàn)金流不一定必須為均衡的，但作為年金，它們必須按固定的間隔發(fā)生，如按月或按年。內(nèi)部收益率為投資的回收利率，其中包含定期支付(負(fù)值)和收入(正值)。
其語(yǔ)法形式為IRR(values,guess) 其中values為數(shù)組或單元格的引用，包含用來(lái)計(jì)算內(nèi)部收益率的數(shù)字，values必須包含至少一個(gè)正值和一個(gè)負(fù)值，以計(jì)算內(nèi)部收益率，函數(shù)IRR根據(jù)數(shù)值的順序來(lái)解釋現(xiàn)金流的順序，故應(yīng)確定按需要的順序輸入了支付和收入的數(shù)值，如果數(shù)組或引用包含文本、邏輯值或空白單元格，這些數(shù)值將被忽略;guess為對(duì)函數(shù)IRR計(jì)算結(jié)果的估計(jì)值，excel使用迭代法計(jì)算函數(shù)IRR從guess開(kāi)始，函數(shù)IRR不斷修正收益率，直至結(jié)果的精度達(dá)到0.00001%，如果函數(shù)IRR經(jīng)過(guò)20次迭代，仍未找到結(jié)果，則返回錯(cuò)誤值#NUM!，在大多數(shù)情況下，并不需要為函數(shù)IRR的計(jì)算提供guess值，如果省略guess，假設(shè)它為0.1(10%)。如果函數(shù)IRR返回錯(cuò)誤值#NUM!，或結(jié)果沒(méi)有靠近期望值，可以給guess換一個(gè)值再試一下。
例如，如果要開(kāi)辦一家服裝商店，預(yù)計(jì)投資為￥110,000，并預(yù)期為今后五年的凈收益為：￥15,000、￥21,000、￥28,000、￥36,000和￥45,000。分別求出投資兩年、四年以及五年后的內(nèi)部收益率。
在工作表的B1：B6輸入數(shù)據(jù)"函數(shù).xls"所示，計(jì)算此項(xiàng)投資四年后的內(nèi)部收益率IRR(B1：B5)為-3.27%;計(jì)算此項(xiàng)投資五年后的內(nèi)部收益率IRR(B1：B6)為8.35%;計(jì)算兩年后的內(nèi)部收益率時(shí)必須在函數(shù)中包含guess，即IRR(B1：B3，-10%)為-48.96%。
(二) 用RATE函數(shù)計(jì)算某項(xiàng)投資的實(shí)際贏利
在經(jīng)濟(jì)生活中，經(jīng)常要評(píng)估當(dāng)前某項(xiàng)投資的運(yùn)作情況，或某個(gè)新企業(yè)的現(xiàn)狀。例如某承包人建議你貸給他30000元，用作公共工程建設(shè)資金，并同意每年付給你9000元，共付五年，以此作為這筆貸款的最低回報(bào)。那么你如何去決策這筆投資?如何知道這項(xiàng)投資的回報(bào)率呢?對(duì)于這種周期性償付或是一次償付完的投資，用RATE函數(shù)可以很快地計(jì)算出實(shí)際的贏利。其語(yǔ)法形式為RATE(nper,pmt,pv,fv,type,guess)。
具體操作步驟如下：
1、選取存放數(shù)據(jù)的單元格，并按上述相似的方法把此單元格指定為"百分?jǐn)?shù)"的格式。
2、插入函數(shù)RATE，打開(kāi)"粘貼函數(shù)"對(duì)話框。
3、在"粘貼函數(shù)"對(duì)話框中，在"Nper"中輸入償還周期5(年),在"Pmt"中輸入7000(每年的回報(bào)額)，在"Pv"中輸入-30000(投資金額)。即公式為=RATE(5,9000,-30000)
4、確定后計(jì)算結(jié)果為15.24%。這就是本項(xiàng)投資的每年實(shí)際贏利，你可以根據(jù)這個(gè)值判斷這個(gè)贏利是否滿意，或是決定投資其它項(xiàng)目，或是重新談判每年的回報(bào)。
[NextPage]
四、債券及其他金融函數(shù)
債券及其他金融函數(shù)又可分為計(jì)算本金、利息的函數(shù)，與利息支付時(shí)間有關(guān)的函數(shù)、與利率收益率有關(guān)的函數(shù)、與修正期限有關(guān)的函數(shù)、與有價(jià)證券有關(guān)的函數(shù)以及與證券價(jià)格表示有關(guān)的函數(shù)。
1、計(jì)算本金、利息的函數(shù)--CUMPRINC、ACCRINT、ACCRINTM、CUMIPMT、COUPNUM
2、與利息支付時(shí)間有關(guān)的函數(shù)--COUPDAYBS、COUPDAYS、COUPDAYSNC、COUPNCD、COUPPCD
3、 與利率收益率有關(guān)的函數(shù)--INTRATE、ODDFYIELD、ODDLYIELD、TBILLEQ、TBILLPRICE、TBILLYIELD、YIELD、YIELDDISC、YIELDMAT
4、與修正期限有關(guān)的函數(shù)--DURATION、MDURATION
5、與有價(jià)證券有關(guān)的函數(shù)--DISC、ODDFPRICE、ODDLPRICE、PRICE、PRICEDISC、PRICEMAT、RECEIVED
6、與證券價(jià)格表示有關(guān)的函數(shù)--DOLLARDE、DOLLARFR
在債券及其他金融函數(shù)中，筆者將重點(diǎn)介紹函數(shù)ACCRINT、CUMPRINC、DISC。
(一)求定期付息有價(jià)證券的應(yīng)計(jì)利息的函數(shù)ACCRINT
ACCRINT函數(shù)可以返回定期付息有價(jià)證券的應(yīng)計(jì)利息。
其語(yǔ)法形式為ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis)
其中issue為有價(jià)證券的發(fā)行日，first_interest為有價(jià)證券的起息日，settlement為有價(jià)證券的成交日，即在發(fā)行日之后，有價(jià)證券賣給購(gòu)買者的日期，rate為有價(jià)證券的年息票利率，par為有價(jià)證券的票面價(jià)值，如果省略par，函數(shù)ACCRINT就會(huì)自動(dòng)將par設(shè)置為￥1000，frequency為年付息次數(shù)，basis為日計(jì)數(shù)基準(zhǔn)類型。
例如，某國(guó)庫(kù)券的交易情況為：發(fā)行日為2008年3月1日;起息日為2008年8月31日;成交日為2008年5月1日，息票利率為10.0%;票面價(jià)值為￥1,000;按半年期付息;日計(jì)數(shù)基準(zhǔn)為30/360，那么應(yīng)計(jì)利息為：
(二)求本金數(shù)額CUMPRINC
CUMPRINC函數(shù)用于返回一筆貨款在給定的st到en期間累計(jì)償還的本金數(shù)額。其語(yǔ)法形式為CUMPRINC(rate,nper,pv,start_period,end_period,type) 其中rate為利率，nper為總付款期數(shù)，pv為現(xiàn)值，start_period為計(jì)算中的首期，付款期數(shù)從1開(kāi)始計(jì)數(shù)，end_period為計(jì)算中的末期，type為付款時(shí)間類型。
例如，一筆住房抵押貸款的交易情況如下：年利率為9.00%;期限為30年;現(xiàn)值為￥125，000。由上述已知條件可以計(jì)算出：r=9.00%/12=0.0075，np=30*12=360。
那么該筆貸款在第下半年償還的全部本金之中(第7期到第12期)為： =CUMPRINC(A2/12,A3*12,A4,7,12,0)計(jì)算結(jié)果為：-436.568194。
該筆貸款在第一個(gè)月償還的本金為：=CUMPRINC(A2/12,A3*12,A4,1,1,0)計(jì)算結(jié)果為：-68.27827118。
[NextPage]
猜你喜歡：
1.怎么用excel宏命令完成財(cái)務(wù)指標(biāo)的計(jì)算
2.Excel中if、len、mid、iseven四個(gè)函數(shù)的組合操作方法