怪老子ETF線上課程

評估投資績效 先看年化報酬率

日期:2017-10-03

投資要有斬獲不能單靠複利,還得要有高報酬率才能發揮其威力。所以投資的首要之務就是評估報酬率,可是銀行或券商的對帳單通常只會列出累積報酬率,對於年化報酬率全無著墨,這樣就無法比較不同投資的績效。所幸Excel提供了許多計算年化報酬率函數,只是每一個函數該如何正確使用常常讓人困惑,我將Excel常用的函數RRI(2013以後的版本才提供)、RATE、IRR、XIRR做個總整理,方便讀者使用。

其實報酬率是由現金流量決定的,不同的投資呈現出來的現金流量也不一樣,只要把現金流量搞定,剩下的只是把現金流量輸入至函數,就會傳回每一期的報酬率。然而每個函數都有其限制,必須充分了解才行,否則用錯函數當然就得到錯誤的答案。

RRI函數

先從最簡單的RRI函數談起,一項投資不論有多少期數,如果只有期初投入及期末淨值兩筆現金流量,期中都沒有任何現金流入或流出,使用RRI函數是最容易且不會犯錯。

函數輸入語法:RRI(期數, 期初金額, 期末金額)

圖一:RRI現金流量圖

期初與期末金額一律用正值且允許小數點,函數回傳以複利計算之期報酬率,當期數輸入1就等於累積報酬率。
例一:投入10萬元於A基金,5年半後基金淨值16萬7,200元整,求累積及年化報酬率。

累積報酬率67.2%【=RRI(1, 100000, 167200)】,年化報酬率9.8%【=RRI(5.5, 100000, 167200)】。

例二:6年期儲蓄險,一次繳保費50萬1,696元,6年後領回56萬5,058元,求累積及年化報酬率。

累積報酬率12.6%【=RRI(1, 501696, 565058)】,年化報酬率2.0%【=RRI(6, 501696, 565058)】。即便6年可以獲得12.6%累積報酬率,只相當銀行定存2.0%年利率。

RATE函數 適用每期現金流量都一樣

除了期初pv、期末fv之外,若期中還會出現現金流量,就不能使用RRI函數了,這時候就換RATE函數出場,只是期中的現金流量pmt必須每期都一樣才行,且現金流量也有流向之分,流入為正值流出為負值。典型的現金流量如圖二所示,每一筆流向都可以任意變更,只是不可以全部都同向,否則會回傳錯誤(#NUM!)。

函數輸入語法:RATE(期數, 每期金額, 期初金額, [期末金額], [type], [猜測值])

注:Excel的語法中括弧代表可省略參數,且type參數指定pmt出現於每一期的期初(1)或期末(0),猜測值是使用者預估較可能出現的報酬率,通常可以省略。

圖二:

例三:5年前於每年底均投入10萬元於A基金,5年後基金的淨值成長至66萬3,000元,求年化報酬率。

圖三:例三的現金流量圖

例三的現金流量如圖三所示,一年為一期,每一期的現金流量都一樣,期初因為沒有投入,所以期初金額pv=0,每期金額pmt = -100000,期末金額fv=663000,期數nper=5,得到年化報酬率14.2%【=RATE(5, -100000,0,663000)】。

銀行對帳單都顯示累積報酬率,總共投入50萬元,拿回66萬3,000元,所以累積報酬率為32.6% 【=RRI(1,50,66.3)】。一般會直接將累積報酬除上年數就得到年平均報酬率6.5%。這樣算是錯誤的,因為每年投入的10萬元時間點都不一樣,不可以直接相加。

只要RRI函數能計算的都可用RATE函數計算,因為RATE函數除了期初、期末之外又多了期中金額。例如例二的儲蓄險,使用RATE函數也會得到相同答案2.0%【=RATE(6, 0,-501696,565058)】,只是要注意RATE的現金流量有正負流向之分,而RRI全部均使用正值。

IRR函數 適用每期現金流量都不同的計算

RATE函數雖然允許期中出現現金流量,但每一筆都必須一樣才行。若期中的現金流量每一期都不一樣,就必須使用IRR函數。IRR使用連續儲存格輸入現金流量,每一個儲存格代表一期的淨現金流量,第1個儲存格為期初,接著是第1期末、第2期末…第n期末,所以期數就是儲存格的數量減1。

函數輸入語法:IRR(現金流量, [猜測值])

例四:2017/1/3投資台幣10萬元於富達歐元債券基金A類股月配息(ISIN: LU0168050333),至2017/9/1止求年化報酬率。

圖四列出了每月單位配息(歐元)及匯率,儲存格F5至F13是該項投資的台幣現金流量。儲存格B2的公式【=IRR(F5:F13)】就計算每一期的報酬率,因為一期為一個月所以傳回的數值是月報酬率。將月報酬率乘上12只是年名目報酬率,月複利後的年報酬率,必須用EFFECT函數將年名目報酬率換算成實質報酬率。EFFECT函數語法如下:

EFFECT(名目報酬率, 複利次數)

所以儲存格B2中的公式【=EFFECT(B1*12, 12)】就是將月報酬率換算成年報酬率,所以這檔債券型基金這期間的表現相當於每年9.59%。

圖四:富達歐元債券基金之現金流量

只要RRI及RATE函數適用的計算,IRR也可以得到相同的答案。例如例三使用IRR也會得到一樣的答案14.2%【=IRR({0, -100000, -100000, -100000, -100000, 563000})】,上述公式的參數是以陣列方式表示,大括弧表示陣列,中間以逗點分隔每一期現金流量,且每一個儲存格只能有一筆現金流量,多筆現金流量得先行加總,所以最後一期的淨現金流量為663000-100000=563000。

XIRR 適用非固定期間的計算

RRI、RATE、IRR都以期數作為計算基礎,所以輸入的參數會出現期數,不論一期是多久,因為這些函數回傳的數值一律是期報酬率,如果一期為一個月就回傳月報酬率,若一期為一年就回傳年報酬率。若每一期不是一年才必須用EFFECT函數轉換成年實質報酬率。不論一期是多久,若要使用上述函數,現金流量都必須發生在固定期間。

可是常有投資案的現金流量並非固定期間出現,最典型的應用就是股票投資,投資期間領到的配息時間不是確定的,賣出股票的時間也不在固定時間點,這類應用就必須使用XIRR函數。XIRR跟IRR不一樣,XIRR沒有期數的概念,要求輸入的每一筆現金流量都必須註明發生的日期。

函數輸入語法:XIRR(現金流量, 日期, [猜測值])

例五:2014/9/1以每股128.5元收盤價買入台積電(2330)股票一張,持有至2017/9/12以每股219.0元賣出,之後於2015/6/29元每股配息4.4999元、2016/6/27每股配息6元、2017/6/26每股配息7元,這樣年化報酬率是多少?

投資明細如圖五所示,現金流量欄位列出了現金流量,日期欄位也列出了現金流量發生的日期,儲存格B2輸入使用XIRR函數公式【=XIRR(台積電明細表[現金流量],台積電明細表[日期])】,輕易即可算出年化報酬率為23.1%,跟華倫巴菲特的波克夏公司投資績效相當喔。

要特別注意,XIRR雖然也是使用儲存格輸入現金流量,但不是用儲存格的數量換算期數,因為XIRR沒有期數概念,所以回傳數值也不是期報酬率,而是直接算出年報酬率。因為XIRR適用任意期間、任意筆數的現金流量,所以只要符合RRI、RATE、IRR函數應用範圍,也可以使用XIRR計算。

圖五:投資台積電明細

函數彙總

將EXCEL的報酬率函數整理成下列表格,讀者只要根據實際的應用,便可以輕易挑選出適用的函數。

  RRI RATE IRR XIRR
現金流量筆數 兩筆 多筆 多筆 無限制
期中現金流量 不允許 必須一樣 允許不一樣 無限制
現金流量流向 無流向分別 有流向分別 有流向分別 有流向分別
期間 固定 固定 固定 非固定
傳回數值 期報酬率 期報酬率 期報酬率 年化報酬率

Excel檔案下載

點選下列Excel圖示可以下載範例四及五的試算表

本文刊登於Smart智富月刊230期62頁(2017 10月)




■ 本網站內容儘可能精確完整,但不保證無誤。若做為投資依據,風險請自行斟酌 ,本網站不負賠償之責任。
■ 網站所有資料均為版權所有,非經書面允許請勿轉載或使用。