日期:2015-05-24
一項投資案的投資報酬率,可以用Excel的IRR函數輕易的計算出來。只要將投資案的現金流量描述給IRR,就會回傳該投資案的期化報酬率。這個函數雖然好用,但是有幾個眉角要特別注意,否則就很容易算錯。所謂垃圾進、垃圾出,現金流量若是描述不對,IRR當然就會計算出錯誤的結果。
IRR函數的參數使用儲存格來描述投資案的現金流量,每一儲存格代表一期的淨現金流量,若同一期出現多筆現金流量,就必須先行加總。
例一:每一個月底定期定額投入1萬元基金,6個月後該基金淨值6萬3,200元,該投資案的年化報酬率為多少?
例一的現金流量圖如圖一所示,每月投入的一萬元屬於現金流出,所以用負數來表示。第6個月的期末淨值6萬3,200元,雖然基金尚未贖回,但是計算時假設該筆基金以當時淨值贖回,所以是現金流入以正值代表。
例一總共有6期,所以只能用6個儲存格來描述現金流量。除了第6期外,每一期都僅有一筆現金流量,所以1至5期直接輸入負1萬元即可。至於第6期因為有兩筆現金流量,一筆是投入的負1萬元,另外一筆是淨值正6萬3,200元,所以第六期的淨現金流量就是正5萬3200【=63200-10000】。
圖一:例一的現金流量圖
圖二就是用IRR計算的公式, B5~B10這6個儲存格描述6期的淨現金流量,只要將儲存格範圍當作IRR的參數輸入即可,所以B1的公式為【=IRR(B5:B10)】,就得到投資報酬率每月2.08%,若要換算成年化報酬率可以用EFFECT函數,B1乘上12是名目報酬率,第2個參數是複利次數。B2年化報酬率【=EFFECT(B1*12, 12)】,結果等於27.95%。
圖二:例一的試算公式
一個儲存格只能代表一期的淨現金流量,可是一期之中又有期初及期末之分,而兩者剛好相差一期,所以一期當中若同時有期初及期末兩種現金流量,不可以直接用算術相加,必須拆開成兩期,將期初的現金流量併入上一期的期末,或者是將期末的現金流量併入下一期的期初。
每一儲存格只能在期初或期末之中選擇一個,而且所有的儲存格必須一致。若是選用期初,那麼每個儲存格都必須選用期初的淨現金流量。若選用期末,當然都必須使用期末的淨現金流量,用個例子來說明就更清楚。
例二:期初單筆投入10萬元,而且每一個月底定期定額投入1萬元基金,到了第6個月淨值為17萬8,342元,這樣年化報酬率為多少?
圖三是例二的現金流量,這當圖跟例一非常類似,除了期初多了一筆10萬元的投資,以及期末淨值變更外,其他沒有多大改變。現金流量仍然是6期,但是第1期期初多了一筆10萬元。第1期總共有兩筆現金流量,期初的負10萬元以及期末的負1萬元,所以無法直接相加。
因為大部分現金流量都屬於期末,所以儲存格都以期末為準,所以第1期初的負10萬元,只好併入前一期末,所以第1期前面多加了一期,就是為0期的淨現金流量為負10萬。
圖三:例二的現金流量圖
圖四就是例二的試算公式,現金流量以C5至C11儲存格來描述,所以月報酬率B1的公式為【=IRR(C5:C11)】,年報酬率公式B2為年實質報酬率,月報酬率乘上12就是名目利率,再用EFFECT函數求實質利率【=EFFECT(B1*12,12)】,答案為31.68%。
如果儲存格以期末為準,就是0至6期。若是以期初為準,就是1至7期。其實,對IRR函數而言根本不管期初或期末,只用儲存格判斷相對位置,這樣就足夠了。只是對使用者而言,心中必須有期初及期末之分,否則現金流量容易描述錯誤。
圖四:例二的試算公式
再來看一個常見的錯誤,就是壽險的報酬率計算,為了敘述方便,壽險建議書中常把期初及期末混和一起,當計算報酬率時不可以直接使用這些數據,必須將期初及期末區分出來,才會得到正確結果。
表一節錄自一個終身壽險的建議書,繳費是在34歲的年初,可是解約金的計算時間點是年尾。例如第1列的34歲,當年初繳費101萬5,852元,若是當年底解約,只剩95萬1,456元。所以用IRR計算時,若每一期都以年底為準,34歲的年初就是33歲的年底,所以現金流量的描述必須將繳款的金額往上挪一個儲存格才正確。圖五是該壽險前五年的現金流量圖,而圖六則是該壽險前五年的投資報酬率計算公式。
表一:壽險建議書摘要
圖五:例三現金流量圖
圖六:例三試算公式