excel數(shù)據(jù)處理與分析-第9章 Excel與財務(wù)分析

第9章Excel與財務(wù)分析認(rèn)識Excel提供了許多有關(guān)財務(wù)、投資、償還、利息及折舊方面的函數(shù)，在工作表中運(yùn)用這些函數(shù)可以較松地完成相關(guān)的財務(wù)運(yùn)算，或者對其他財務(wù)管理軟件的運(yùn)算輸出數(shù)據(jù)進(jìn)行驗(yàn)證。本章學(xué)習(xí)目標(biāo)1、掌握常用折舊函數(shù)的用法2、了解常用投資函數(shù)的使用方法3、理解常用債券分析函數(shù)的使用方法4、了解常用計算償還率函的用法5、能夠用上述幾類函數(shù)解決實(shí)際問題9.1計算折舊的函數(shù)1、折舊函數(shù)Excel折舊函數(shù)有DB、DDB、SLN、YDB及VDB。用這5個折舊函數(shù)可以確定指定時期內(nèi)資產(chǎn)的折舊值。這5個折舊函數(shù)有4個參數(shù)是共有的，如表所示。cost為資產(chǎn)原值salvage為資產(chǎn)在折舊期末的價值（也稱資產(chǎn)殘值）life為折舊期限（有時也稱作資產(chǎn)的生命周期）period必須使用與lIFe相同的單位9.1計算折舊的函數(shù)DB函數(shù)功能DB函數(shù)使用固定余額遞減法，計算資產(chǎn)在給定期限的折舊值用法DB(cost,salvage,life,period,month)其中：life和period須使用相同的時間單位；month為第一年的月份數(shù)，如果省略，則默認(rèn)為12。例如某學(xué)校買了一批計算機(jī)，價值為500000元使用期限為3年，報廢價值為100000元。每年的折舊公式及結(jié)果值如下所示：DB(500000,100000,3,1,6)=￥103750.00DB(500000,100000,3,2,6)=￥164443.75DB(500000,100000,3,3,6)=￥96199.59

9.1計算折舊的函數(shù)DDB函數(shù)用法：DDB(cost,salvage,lIFe,period,factor)其中，factor為余額遞減速率。如factor省略，則默認(rèn)為2（雙倍余額遞減法）。5個參數(shù)都必須為正數(shù)功能DDB函數(shù)使用雙倍余額遞減法或其他指定方法，計算一筆資產(chǎn)在給定期限內(nèi)的折舊值例如：學(xué)?；?0萬元購買了一臺新設(shè)備，使用期限為10年，報廢價值為1萬。下面的例子給出幾個期限內(nèi)的折舊值（結(jié)果保留兩位小數(shù)）。DDB(100000,10000,3650,1)=￥54.79，第一天的折舊值。factor被Excel默認(rèn)設(shè)置設(shè)為2。DDB(100000,10000,120,1)=￥1666.67，第一個月折舊值。DDB(100000,10000,10,1)=￥20000.00，第一年的折舊值。DDB(100000,10000,10,3,1.5)=￥10837.50，第三年的折舊。這里沒有使用雙倍余額遞減法，factor=1.5。9.1計算折舊的函數(shù)SYD、SLN、VDB函數(shù)

用法SYD(cost,salvage,life,per)SLN(cost,salvage,life)VDB(cost,salvage,life,start_period,end_period,factor,no_switch)

功能SYD函數(shù)計算某項(xiàng)資產(chǎn)按年限總和折舊法計算的某期限內(nèi)的折舊值SLN函數(shù)計算一項(xiàng)資產(chǎn)每期的直線折舊值VDB函數(shù)代表可變余額遞減法，可使用雙倍遞減余額法或其他指定的方法，計算指定期間內(nèi)或某一時間段內(nèi)的資產(chǎn)折舊額。9.2投資函數(shù)1、投資函數(shù)的參數(shù)Excel投資分析使用的參數(shù)大致相同，意義相近，如下表所示。參數(shù)說明rate為各期利率，為固定值per用于計算其本金數(shù)額的期次，必須在1~nper之間nper為總投資（或貸款）期次，即該項(xiàng)投資（或貸款）的付款期總數(shù)pv為現(xiàn)值，即從該項(xiàng)投資（或貸款）開始計算時已經(jīng)入賬的款項(xiàng)，或一系列未來付款當(dāng)前值的累積和，也稱為本金fv為未來值，或在最后一次付款后希望得到的現(xiàn)金余額，如果省略fv，則假設(shè)其值為零（如一筆貸款的未來值即為零）type數(shù)字0或1，用以指定各期的付款時間是在期初還是期末，0：期末；1：期初value1,value2value1,…,value29代表1~29個償還金額不相等時的分期償還額9.2投資函數(shù)2、PMT函數(shù)

用法PMT(rate,nper,pv,fv,type)說明，PMT返回的支付款項(xiàng)包括本金和利息，但不包括稅款。rate和nper單位要一致。功能PMT基于固定利率及等額分期付款方式，計算投資或貸款的每期付款額。例如：某人買房了，貸款200000元，利率為7%，分10個月付清，則他的月支付額為：PMT(7%/12,10,200000)=￥-20647.269.2投資函數(shù)3、PV函數(shù)用法PV(rate,nper,PMT,fv,type)功能PV函數(shù)可以計算投資的現(xiàn)值?，F(xiàn)值是一系列未來付款當(dāng)前值的累積和，如借入方的借入款即為貸出方貸款的現(xiàn)值。例如：某人想買一筆養(yǎng)老保險，該保險可以在今后25年內(nèi)于每月末回報500元。該保險的購買成本為75000，假定投資回報率為6.7%。該保險是否合算？現(xiàn)在可以通過函數(shù)PV計算一下這筆投資是否值得。該項(xiàng)投資的年金現(xiàn)值為：PV(0.067/12,12*25,500,0)=-72700.059.2投資函數(shù)4、FV函數(shù)

用法FV(rate,nper,PMT,pv,type)功能FV函數(shù)計算投資在將來某個日期的價值，它可以計算出投資的一次性償還金額，也可以計算出一系列數(shù)額相等的分期償還金額。例如某人為保證退休后的生活，打算每年年初存入3000元，在整個投資期間，平均投資回報率為7%。此人今年30歲，到他60歲時，有多少存款？計算方法如下。FV(7%,30,-2000,1)=￥202146.089.2投資函數(shù)5、XNPV函數(shù)用法XNPV(rate,values,dates)功能XNPV函數(shù)計算一組現(xiàn)金流的凈現(xiàn)值，這些現(xiàn)金流不一定定期發(fā)生例如假定某項(xiàng)投資需要在2002年3月2日支付現(xiàn)金30000元，并于下述時間獲取以下金額的返回資金：2002年4月21日返回8750元；2002年6月10日返回7250元；2002年7月30日返回16250元；2002年9月18日返回9750元。假設(shè)資金流轉(zhuǎn)折扣為7%，則凈現(xiàn)值為。XNPV(0.07,{-30000,8750,7250,16250,9750},{37317,37667,38017,38367,38717})=￥5465.459.2投資函數(shù)6、其它投資函數(shù)NPV(rate,value1,value2,...)IPMT(rate,per,nper,pv,fv,type)PPMT(rate,per,nper,pv,fv,type)

NPER(rate,PMT,pv,fv,type)NPV為凈現(xiàn)值函數(shù)，IPMT為利息償還額計算函數(shù)，PPMT為計算本金償還額函數(shù)，NPER為計算投資總期數(shù)的函數(shù)9.3計算償還率的函數(shù)1、RATE函數(shù)用法RATE(nper,PMT,pv,fv,type,guess)功能RATE函數(shù)用于計算投資的各期利率。可以計算連續(xù)分期等額投資的償還率，也可以計算一次性償還的投資利率例如某人修房貸款100000元的6年期貸款，月支付額為2200元，該筆貸款的利率為。=RATE(12*6,-2200,100000)=1.38%

9.3計算償還率的函數(shù)2、IRR函數(shù)用法IRR(values,guess)功能IRR函數(shù)計算由數(shù)值代表的一組現(xiàn)金流的內(nèi)部收益率。內(nèi)部收益率是指投資償還的固有率，它是引起投資的凈現(xiàn)值等于零的比率例如某人投資70000元開設(shè)一家錄像帶租借店，并預(yù)期今后5年的凈收益為：12000元、15000元、18000元、21000元和26000元。試計算該項(xiàng)投資的內(nèi)部收益率。在Excel工作表的B1:B6中分別輸入下面的數(shù)值-70000、12000、15000、18000、21000和26000。該投資4年后的內(nèi)部收益率為。IRR(B1:B5)=-2.12%9.4債券分析函數(shù)1、債券分析函數(shù)的參數(shù)Excel提供了許多計算與分析債券的函數(shù)，運(yùn)用這些函數(shù)可以較方便地進(jìn)行各種類型的債券分析。債券分析函數(shù)與數(shù)據(jù)分析工具有關(guān)，它們由“分析工具庫”加載宏工具提供，如果在Excel的工作表中引用債券分析函數(shù)時發(fā)現(xiàn)函數(shù)不存在，可以通過工具中的“加載宏”命令，把“分析工具庫”加載到Excel系統(tǒng)中，只有這樣才能使用債券分析函數(shù)。多數(shù)債券分析所使用的參數(shù)都基本相同，如下表所示。9.4債券分析函數(shù)last_interest債券的末期付息日settlement是證券的成交日，即在發(fā)行日之后，證券賣給購買者的日期rate債券發(fā)放日的利息率first_interest是證券的起息日coupon債券的年利息率par有價證券的票面價值，如果省略par，視par為$1000frequency年付息次數(shù)。如果按年支付，frequency=1；按半年期付，frequency=2；按季支付，frequency=4discount債券的貼現(xiàn)率first_coupon債券的首期付息日investment債券的最初購買價格maturity債券到期日期yld債券年收益redemption債券兌換價格basis日計數(shù)基準(zhǔn)

0或省略

US(NASD)30/3601

實(shí)際天數(shù)/實(shí)際天數(shù)

2

實(shí)際天數(shù)/3603

實(shí)際天數(shù)/3654

歐洲309.4債券分析函數(shù)2、ACCRINT/ACCRINTM函數(shù)功能ACCRINT函數(shù)計算定期付息有價證券的應(yīng)計利息。ACCRINTM函數(shù)用于計算到期一次性付息有價證券的應(yīng)計利息。格式：ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis)ACCRINTM(issue,maturity,rate,par,basis)9.4債券分析函數(shù)案例【例】某國庫券2000年1月8日發(fā)行，2000年10月1日為起息日，成交日期是2000年6月1日，息票利率9.0%，票面價值2000元，按半年期付息，日計數(shù)基準(zhǔn)30/360，則應(yīng)計利息為：ACCRINT("2000/1/8","2000/10/1","2000/6/1",0.1,2000,2,0)=79.4444表明2000年6月1日的利息為79.4444元?！纠?/p>

一短期債券的交易情況如下：2001年4月1日發(fā)行，2001年6月15日到期，息票利息9.0%，票面價值是1000元，日計數(shù)基準(zhǔn)“實(shí)際天數(shù)/365”。則應(yīng)計利息：ACCRINTM("2001/4/1","2001/6/15",0.1,1000,3)=20.547959.4債券分析函數(shù)3、INTRATE/RECEIVED函數(shù)功能INTRATE函數(shù)計算一次性付息證券的利率。RECEIVED函數(shù)計算一次性付息的有價證券到期收回的金額。格式：INTRATE(settlement,maturity,investment,redemption,basis)RECEIVED(settlement,maturity,investment,discount,basis)9.4債券分析函數(shù)案例【例】

某債券于2001年2月15日成交，2001年5月15日到期，總投資額為1000000萬元，清償價值為1014420元，日計數(shù)基準(zhǔn)為：實(shí)際天數(shù)/360，則該債券貼現(xiàn)率為：INTRATE("2001/2/15","2001/5/15",1000000,1014420,2)=0.058328【例】

某債券于2001年2月15日發(fā)行，2001年5月15日到期，總投資額為：1000000，貼現(xiàn)率為5.75%，日計數(shù)基準(zhǔn)：實(shí)際天數(shù)/360，則該證券到期日可回收的總金額為：RECEIVED("2001/2/15","2001/5/15",1000000,0.0575,2)=1014420.266。9.4債券分析函數(shù)4、PRICE/PRICEDISC/PRICEMAT功能PRICE函數(shù)計算定期付息的面值100元的有價證券的價格。PRICEDISC函數(shù)計算折價發(fā)行的面值100元的有價證券的價格。PRICEMAT函數(shù)計算到期付息的面值100元的有價證券的價格格式：PRICE(settlement,maturity,rate,yld,redemption,frequency,basis)PRICEDISC(settlement,maturity,discount,redemption,basis)PRICEMAT(settlement,maturity,issue,rate,yld,basis)9.4債券分析函數(shù)案例【例】

某債券于2000年11月11日發(fā)行，2001年2月15日成交，2002年4月13日到期，半年息票利率為6.1，收益率為6.1%，日計數(shù)基準(zhǔn)為30/360。則該債券的價格為：PRICEMAT("2001/2/15","2002/4/13","2000/11/11",0.061,0.061,0)=99.894659.4債券分析函數(shù)5、DISC函數(shù)功能DISC函數(shù)計算有價證券的貼現(xiàn)率。格式：DISC(settlement,maturity,pr,redemption,basis)案例

某債券于2001年2月15日成交，2001年6月10日到期，價格為97.975元，清償價格為100元，日計數(shù)基準(zhǔn)：實(shí)際天數(shù)/360，則該債券的貼現(xiàn)率為：DISC("2001/2/15","2001/6/10",97.975,100,2)=0.063391（或6.3391%）

9.4債券分析函數(shù)6、YIELD/YIELDDISC/YIELDMAT功能YIELD函數(shù)計算定期付息有價證券的收益率，YIELD函數(shù)用于計算債券收益率。YIELDDISC函數(shù)計算折價發(fā)行的有價證券的年收益率。YIELDMAT函數(shù)到期付息的有價證券的年收益率格式：YIELD(settlement,maturity,rate,pr,redemption,frequency,basis)YIELDDISC(settlement,maturity,pr,redemption,basis)YIELDMAT(settlement,maturity,issue,rate,pr,basis)9.4債券分析函數(shù)7、TBILLEQ/TBILLYIELD/TBILLPRICE功能COUPDAYBS，計算當(dāng)前付息期內(nèi)截止到成交日的天數(shù)。COUPDAYSNC，計算從成交日到下一付息日之間的天數(shù)。COUPDAYS，計算成交日所在的付息期的天數(shù)。

COUPNUM，計算成交日和到期日之間的利息應(yīng)付次數(shù)，向上取整到最近的整數(shù)。COUPPCD，計算成交日之前的上一付息日的日期。格式COUPDAYBS(settlement,maturity,frequency,basis)COUPDAYSNC(settlement,maturity,frequency,basis)COUPDAYS(settlement,maturity,frequency,basis)COUPNUM(settlement,maturity,frequency,basis)COUPPCD(settlement,maturity,frequency,basis)9.4債券分析函數(shù)8、DURATION函數(shù)功能DURATION函數(shù)計算面值100元的定期付息有價證券的修正期限。期限定義為一系列現(xiàn)金流現(xiàn)值的加權(quán)平均值，用于計量債券價格對于收益率變化的敏感程度。格式：DURATION(settlement,maturity,coupon,yld,frequency,basis)9.5Excel財務(wù)函數(shù)應(yīng)用案例1：內(nèi)含報酬率計算某方案投資及營業(yè)現(xiàn)金流量如表10-5所示，求內(nèi)含報酬率。設(shè)所求內(nèi)含報酬率為i，令年012345投資-15000現(xiàn)金流量38003560332030807840則方程的解為所求。9.5Excel財務(wù)函數(shù)應(yīng)用案例2固定資產(chǎn)折舊計算——直線法某項(xiàng)固定資產(chǎn)原值為30000元，預(yù)計凈殘值為1800元，使用年限為4年。選定Excel的一張工作表，輸入SLN函數(shù)及其他內(nèi)容，如表所示ABCD1直線法折舊計算表23年份期初賬面余額折舊額期末賬面余額4130000=SLN(30000,1800,4)=B4-C452=D4=SLN(30000,1800,4)=B5-C563=D5=SLN(30000,1800,4)=B6-C674=D6=B7-D718009.5Excel財務(wù)函數(shù)應(yīng)用ABCD1余額遞減法折舊計算表23年份期初賬面余額折舊額期末賬面余額414000=DB(4000,400,8,1)=B4-C452=D4=DB(4000,400,8,2)=B5-C563=D5=DB(4000,400,8,3)=B6-C674=D6=DB(4000,400,8,4