2014年9月2日 星期二

導入現金流概念,正確的投資報酬率算法與應用 (含豐富的圖文範例解說) (利用Excel XIRR公式) (2014-09-08 更新)

(歡迎轉載分享,但請註明出處,謝謝)

本文沒有複雜的數學公式,比較多範例與應用,煩請讀者耐心看完,必有收穫。


我今天要講的也不是什麼新奇的東西,已經行之有年,但其實很多人不知道或者是知道了卻也忽略之。我覺得這個概念很重要,非常重要,而且very easy to use。


以我自身周遭的經驗為例,問了十個朋友:『你怎麼計算投資報酬率?』,有十個會回答我,阿不就是這樣算。


以下應該是大家耳熟能詳的報酬率公式:







但事實上,這種計算方式存在著兩個問題:


  1. 沒有加計時間成本
    無法得知該投資計畫賺錢是否有效率?假設投資計畫A(耗時兩年)與投資計畫B(耗時一年)期初皆投入相同的金額,投資期間結束後,兩者的投資報酬率皆為5%,花了同樣的資金獲得一樣的報酬率,但計畫B只花了一年的時間,很明顯地,投資計
    畫B較A來的有效率。

    再舉例,假設你花了1萬元各買了一隻金雞母,A金雞每個月可以幫你生5顆金蛋,B金雞每個月卻可以生10顆金蛋,今天想要煮雞來吃,你會先考慮殺哪隻雞?毫不猶豫的,應該會先殺A金雞。

  2. 沒有導入現金流概念
    當你的投資計畫是定期定額不定期不定額,你就更需要導入現金流。

    什麼是現金流?一個很簡單的圖例如下:
    現金收進口袋,或是從口袋掏出錢來,簡單講就是口袋裡現金流動的紀錄。

    舉個例子,下圖是假設某定期定額單筆投入投資的現金流量表:
    左邊的定期定額投資計畫,從2014/1/1~2014/6/1,每個月1號定期扣款購買基金5,000元,到了2014/12/31將該基金全部贖回賣掉,得到資產淨值31,000。

    右邊的單筆投入則是在2014/1/1投入30,000元,到了2014/12/31一樣將該基金贖回賣掉,得到資產淨值31,000元。

    一樣投入了30,000元的資金,一個是分期分批投入,一個是期初單筆投入,到了期末一樣領回31,000元,兩種投資方式在銀行的基金報表,通常都會這樣計算:

    (期末淨值31000-投入成本30000)/投入成本30000=3.33%

    但事實上,定期定額投資計畫的年化報酬率是稍高的4.23%,原因在於期初單筆投入必須在一開始就扣除資金無法動用,而分批投入則是前六個月每月只給一小部分,為了達到期末一樣的利潤,必須提高報酬率,因此得到較高的年化報酬率。這是加入時間成本及現金流後的精確計算方式,將在稍後提到。

將報酬率加入時間成本現金流的概念以後,最大的好處在於讓不同的投資計畫,不管是定存、房地產買賣租賃、基金、股票、保險等等,很容易透過這種方式相互比較,辨別優劣,幫助我們來做投資決策。


這種投資報酬率,叫做內部報酬率(Internal Rate of Return,簡稱IRR),為了避免投資朋友看到公式就頭昏,我這邊不詳述內部報酬率的計算方式,許多厲害的財經部落格都寫的很好,想知道"為什麼"的朋友可以參考下面連結,做更進一步的了解。


先學會用,有興趣可以再學如何推導,但若是連用都不用,那就很可惜了。


根據我自身的經驗,要計算IRR最簡單的方式就是透過Excel的XIRR公式,XIRR的好處是可以只要填上日期以及現金流量表,算出來的值即為該投資之年化投資報酬率
(如果沒有Excel的朋友,最簡單的方式是註冊Google帳號,進入網路硬碟建立試算表,就有提供計算XIRR的功能。這邊提供了本文所有的範例,想了解的朋友點擊連結進入Google文件後,直接檢視或是選左上角檔案->另存副本,下載至自己的網路硬碟自行延伸編輯。)


接下來,我提供幾個實例應用,給各位做參考。





如果你對XIRR尚有質疑,最簡單的驗證方式,就是拿出你手邊的定存單,試著計算看看其XIRR算出之年化報酬率是否等於銀行公告定存的年利率。

下圖是某銀行三個月定存利率0.95%的現金流量試算,XIRR算出來的值就是0.95%。




這是某檔高收益債基金的投資報表:


上面紅框標示的數字為含累計配息報酬率,其實就是將利潤(含配息)及投入成本,用我們最一開始提到的公式下去做計算,得到11.4%這個粗糙的數字(銀行通常不會幫你算IRR),而經過XIRR計算以後,年化報酬率大約是7.63%(尚未扣掉銀行信託保管費,因此實際上報酬率會再低一些),這個投資將近三年的期間,有很高的參考價值,有興趣看整個現金流量表的,可以點擊連結到試算表(含所有的範例)




另外,最多人喜歡買的保險,XIRR一樣可以派得上用場,舉一個實例,郵局某六年期儲蓄險,每年存入16270元,六年後可領回10萬元,透過XIRR計算如下圖:

說好聽是有保險的功能,但實際上理賠的金額很少,另一方面,就投資角度來說,0.69%的年化報酬率也遠輸給銀行三年期定存1.5%,有看過平民保險王的朋友就知道,保險是為了保障而不是獲利,買高理賠金額的基本意外險搭配投資在更好的部位,才是比較好的選擇。真正意外來的時候,才有高額的保障。


有空的時候,不妨把自己手上的保險單拿出來,透過XIRR放大鏡下去檢視,或許你有不一樣的想法。





最後的範例,要講最常見的股票投資。在計算報酬率上若投資朋友還是採用期初減期末這種粗糙的算法,希望我能把你搖醒,不要再蒙蔽自己的眼睛了,試著採用XIRR算法檢視看看吧!


為了方便大家,我這邊提供了完整的試算表範例(點開連結直接檢視或是選左上角檔案->另存副本,下載至自己的網路硬碟自行延伸編輯


(點選圖片可看大圖)


這表格的使用,需要有一點點Excel的基礎,而有些欄位包含一些公式,往後在新增的時候,利用Copy and Paste(複製貼上)可以將公式保留,只需填寫有背景顏色的部份即可。

個人認為短線進出的投資朋友尤其需要,在頻繁進出的交易過程中,勞力費心拿到的年化報酬率,是否能夠打敗大盤?


這邊提供一個數據,根據發行量加權股價報酬指數歷史數據,從民國93/9/1至民國103/9/1的過去這十年,算出來的年化報酬率大約在8.87%左右。


若是長期下來無法打敗台股指數年化報酬率8.87%這個數據,是不是該換個心態,考慮買低費用的指數型ETF,採用Buy and Hold的策略。投資其實很簡單,只是很多人把它複雜化了,若去考慮未來5年10年甚至20年的長期投資路程,真的有辦法玩這種年年殺進殺出投資遊戲嗎?





常見問題Q&A


Q: 若是購買基金、股票、房地產,尚未賣掉結清,在XIRR上該如何填入現金流量表?

A: 在現金流量表最後一個欄位,左側日期鍵入當天日期(利用"=TODAY()"公式,可以免除每天都要改日期的麻煩),右側則算出該投資計畫中所有資產在該日得到的淨值總和,也就是說假設在該日將所有資產賣掉結清,會得到多少。

Q: XIRR在計算時有需要將日期欄位照時間順序排序嗎?

A: 根據我測試,就算時間打亂不按照順序,算出來的值也是一樣的喔!!所以有沒有照時間順序排序是沒有關係的。

有問題的朋友,歡迎來信或是留言詢問,我將會整理大家比較常見的問題,隨時Update在本文,以造福更多的投資朋友們。



延伸閱讀:






喜歡本文的朋友,歡迎到Facebook粉絲專頁HC愛筆記財經部落格按讚,隨時得知最新文章的消息。

23 則留言:

  1. 試算表中利用importhtml去yahoo stock撈今日收盤價的功能好像失效了,請檢查一下,謝謝!

    回覆刪除
    回覆
    1. 謝謝告知^^ 已經修復。
      想抓YAHOO的股價請改用=importXML(CONCATENATE("http://finance.yahoo.com/webservice/v1/symbols/","0056",".tw/quote"),"//field[@name='price']")

      刪除
    2. 請問今日價錢又跳掉了嗎@@ 這幾天都NA....

      刪除
    3. 跳掉+1,這工具很好用啊~~

      刪除
  2. 請問儲蓄險部分 若是買六年期 但故意放到第十年才領回 這樣EXCEL公式表的IRR 該怎計算才對呢? 謝謝!

    回覆刪除
  3. 雲端硬碟的EXCEL下載了都會有缺,怎辦呢

    回覆刪除
    回覆
    1. 建議請直接建立副本(點選檔案->另建副本),在Google雲端硬碟直接做編輯即可,下載後通常都會有格式不符的情況。

      刪除
  4. 如果要記錄定期不定額ETF,是否要一支一支分開算,有辦法弄成像股票那樣嗎?

    回覆刪除
    回覆
    1. 一樣輸入日期跟現金流量,利用XIRR就會幫你算出來的。

      刪除
  5. 冒昧請教HC大大
    台股指數(或S&P500等指數)的年化報酬率
    要怎麼計算呢?
    謝謝

    回覆刪除
    回覆
    1. 參考一下怪老子的這篇文章吧!http://www.masterhsiao.com.tw/EDN/Edn2012-007/Edn2012-007.php

      刪除
  6. 我下載副本後,用EXCEL開啟,卻跳視窗要修復,點是修復後:
    已移除的記錄: /xl/worksheets/sheet.xml 部分的 公式
    已移除的記錄: /xl/worksheets/sheet.xml 部分的 共用公式
    公式就錯誤了,請問是我下載錯誤還是有其他原因呢?

    回覆刪除
    回覆
    1. 其實我還是比較建議使用 Goolge 試算表來做編輯,我不太習慣用 Excel 。
      https://www.google.com/sheets/about/

      刪除
  7. 民間的互助會, 我曾嘗試拿來算IRR. 但算出來的數值都很怪. 或許標會不適合用IRR來算報酬率

    回覆刪除
    回覆
    1. 互助會應該是因為沒有結束點(只要這遊戲持續進行),所以你打進去算正常來說會很奇怪,若你有一個已經結束的互助會,打進去試看看。

      刪除
  8. 能舉個例子說明配股該如何輸入嗎? 謝謝您:)

    回覆刪除
    回覆
    1. 配股其實很簡單耶!只需要在你的股票庫存數量增加股數,那麼最後一個欄位的目前淨值就會跟著增加,就這樣。

      刪除
    2. 您好 那配股時,現金流要填多少? 謝謝

      刪除
    3. 配股不算現金流,就只是增加目前資產淨值而已。

      刪除
  9. 配股 現金流是填0嗎? 非常謝謝您!!

    回覆刪除
    回覆
    1. 配股不算現金流,就只是增加目前資產淨值而已。

      刪除
  10. 請問:TD自動退稅(債券)的部分,應該就算現金流入(+)對吧?

    回覆刪除
    回覆
    1. 那當然,收入你口袋裡的錢,當然是現金流入。

      刪除