在前幾天我們“CFA與Excel結(jié)合的技巧”這篇文章中,我們介紹了如何的使用excel計(jì)算某項(xiàng)投資的現(xiàn)值,重點(diǎn)講解了NPV與PV兩個(gè)函數(shù)。在實(shí)務(wù)中,我們使用以上方法來(lái)計(jì)算某投資項(xiàng)目的凈現(xiàn)值,以此來(lái)判斷該項(xiàng)目是否值得投資,這種方法在投資項(xiàng)目籌劃中經(jīng)常用到。除開(kāi)計(jì)算凈現(xiàn)值外,我們也要設(shè)定項(xiàng)目?jī)衄F(xiàn)值為零的情況下計(jì)算項(xiàng)目的內(nèi)部回報(bào)率是多少,并和該項(xiàng)目的資金成本(如項(xiàng)目的融資成本,或者公司的加權(quán)平均資金本成本等)做比較,以此判斷項(xiàng)目?jī)?nèi)部回報(bào)率是否大于資金的成本,從而能為公司帶來(lái)正的價(jià)值增值,下面我們?nèi)谲SCFA老師將介紹下如何使用excel來(lái)計(jì)算項(xiàng)目的內(nèi)部回報(bào)率。

cfa

下面是CFA三級(jí)學(xué)長(zhǎng)總結(jié)的CFA學(xué)習(xí)資料大禮包,在CFA考試前一套好的CFA學(xué)習(xí)資料,會(huì)對(duì)你有很大的幫助。

01

內(nèi)部回報(bào)率

內(nèi)部回報(bào)率(IRR)即使是項(xiàng)目的凈現(xiàn)值為零的復(fù)合收益率,設(shè)其為,則:

金融CFA

在excel中我們可以使用IRR函數(shù)來(lái)計(jì)算投資項(xiàng)目的內(nèi)部收益率,其語(yǔ)法如下:

IRR(values, [guess])

其中:

values: 必需,表示投資項(xiàng)目的現(xiàn)金流,必須包含有一個(gè)正值或一個(gè)負(fù)值。

guess:可選,默認(rèn)為10%,表示對(duì)內(nèi)部收益率的估計(jì)值。

Excel使用迭代法計(jì)算內(nèi)部收益率,從初始值開(kāi)始不斷修正計(jì)算結(jié)果,直至其精度小于 0.00001%。如果迭代20 次仍未找到結(jié)果,則返回錯(cuò)誤值 #NUM!

下面我們看一個(gè)例子:

例1:計(jì)算內(nèi)部回報(bào)率

現(xiàn)有一投資項(xiàng)目,其初始投資為800元,*至第五年末分別帶來(lái)的正現(xiàn)金流為200,250,300,350與400元,求該項(xiàng)目的內(nèi)部回報(bào)率。

解:使用IRR函數(shù)計(jì)算該項(xiàng)目的內(nèi)部回報(bào)率(如下圖)??梢钥吹?,在一般的投資項(xiàng)目中,期初投資為現(xiàn)金流出即為負(fù)的現(xiàn)金流,而后期投資收益則為現(xiàn)金流入即為正的現(xiàn)金流,將這些現(xiàn)金流代入IRR函數(shù),便可以計(jì)算出項(xiàng)目的內(nèi)部收益率。我們可以使用NPV函數(shù)來(lái)對(duì)以上的計(jì)算結(jié)果進(jìn)行驗(yàn)證,可以發(fā)現(xiàn)在NPV函數(shù)中代入之前計(jì)算得到的內(nèi)部回報(bào)率,得到項(xiàng)目的凈現(xiàn)值為零,說(shuō)明項(xiàng)目的內(nèi)部回報(bào)率確為22.16%。

CFA計(jì)算內(nèi)部回報(bào)率

回憶上一節(jié)的內(nèi)容,NPV函數(shù)與PV函數(shù)都可以用來(lái)計(jì)算項(xiàng)目的凈現(xiàn)值,區(qū)別在于,后者只適用于每期現(xiàn)金流都相同的項(xiàng)目。與之類似,在計(jì)算項(xiàng)目的內(nèi)部回報(bào)率時(shí),也有兩個(gè)函數(shù),分別是IRR函數(shù)與RATE函數(shù),后者只能計(jì)算每期現(xiàn)金流相同的項(xiàng)目的內(nèi)部回報(bào)率。RATE函數(shù)的語(yǔ)法如下:

RATE(nper, pmt, pv, [fv], [type], [guess])

nper: 必需,表示現(xiàn)金流的總期數(shù)。

pmt:必需,表示每期的現(xiàn)金流,如果省略 pmt,則必須包括fv的參數(shù)。

pv:必需,表示未來(lái)現(xiàn)金流的現(xiàn)值總和。

fv:可選,默認(rèn)為0,表示現(xiàn)金流*一期的付款金額。如果省略fv,則必須包括pmt的參數(shù)。

type:可選,默認(rèn)為0,為0表示現(xiàn)金流發(fā)生在期末,為1表示現(xiàn)金流發(fā)生在期初。

guess:可選,默認(rèn)為10%,表示初始猜測(cè)的內(nèi)部回報(bào)率。

我們看一下RATE函數(shù)的具體應(yīng)用:

例2:計(jì)算年金內(nèi)部回報(bào)率

假設(shè)某一筆年金期初支付1000元,未來(lái)三十年每年末返還100元,求該年金的內(nèi)部回報(bào)率是多少?

解:顯然對(duì)于該年金可以使用RATE函數(shù)計(jì)算其內(nèi)部回報(bào)率,其中nper為30,pmt為100,pv為-1000,則可以計(jì)算得到項(xiàng)目的內(nèi)部回報(bào)率為9.31%。使用PV函數(shù)驗(yàn)證,代入計(jì)算得到的內(nèi)部回報(bào)率,可以發(fā)現(xiàn)現(xiàn)值為1000,恰為該項(xiàng)目的期初投資。

CFA計(jì)算年金內(nèi)部回報(bào)率

02

等額還款計(jì)劃

另一個(gè)常見(jiàn)的問(wèn)題是等額還款計(jì)劃問(wèn)題,比如在住房或汽車貸款中,期初從銀行獲得一筆貸款,并約定在未來(lái)每月以一個(gè)固定金額償還貸款。在這類問(wèn)題中,我們知道貸款的總金額、貸款利率以及償還期數(shù),需要計(jì)算每期的償還金額。在excel中,我們可以使用PMT函數(shù)求解此類問(wèn)題。PMT函數(shù)的語(yǔ)法如下:

PMT(rate, nper, pv, [fv], [type])

其中:

rate: 必需,表示貸款利率

nper:必需,表示貸款的總期數(shù)

pv:必需,表示總的貸款額

fv:可選,默認(rèn)為0,表示*一次付款后希望得到的現(xiàn)金余額

type:可選,默認(rèn)為0,為0表示現(xiàn)金流發(fā)生在期末,為1表示現(xiàn)金流發(fā)生在期初

我們看一個(gè)PMT函數(shù)的具體應(yīng)用:

例3:使用PMT函數(shù)計(jì)算每年還款額

一筆銀行貸款的貸款總額為10萬(wàn)元,貸款利率為8%,分五年還清,求每年的等額還款。

解:將各項(xiàng)數(shù)據(jù)代入PMT函數(shù),則rate應(yīng)為8%,nper為5,pv為100000,fv與type都為默認(rèn)值,則可得到每期還款應(yīng)為25045.65元。

等額還款計(jì)劃

需要注意的是,貸款本金前應(yīng)加上負(fù)號(hào),這樣才能*每期還款額為正。

在實(shí)際工作中,除了給出每期的還款額,我們一般還會(huì)列出一份每期還款情況的明細(xì)表或者說(shuō)貸款表,貸款表中列出了每期還款中利息部分與本金部分各為多少。一般而言,對(duì)于等額還款計(jì)劃,每期還款中利息部分的比重逐漸下降,而本金部分的比重逐漸上升,只到*還清全部本金。使用貸款表,我們可以驗(yàn)證計(jì)算出的每期還款額是否正確,如果計(jì)算正確,則還款*一期時(shí),所有貸款金額應(yīng)被償還完畢。下面,我們看看如何用excel制作貸款表。

例4:制作貸款表

使用例3中的數(shù)據(jù),制作等額還款計(jì)劃的貸款表,驗(yàn)證例3中計(jì)算得到的每期還款額是否正確。

解:在等額還款計(jì)劃的每期還款中,利息部分應(yīng)為年初剩余未還本金乘以貸款利率,而本金部分為每期還款額減去利息部分。據(jù)此,我們可以制作貸款表如下:

等額還款計(jì)劃與貸款表

可以看到,第五期還款后,剩余的未還本金成為零,說(shuō)明例3計(jì)算出的每期還款額是正確的。