超實用的XIRR函數!用Excel公式,1秒計算出不定期不定額的投資報酬率!(附IRR與CAGR比較)

超實用的XIRR函數

不知道你有沒有遇過這樣的困擾:

投資股票時,不是採定期定額投入資金,而是不定期+不定額,該怎麼計算報酬率?

老實說,我就曾被這問題困擾很多年。

因為券商軟體看到的數字無法反映真實報酬率,加上我採用股息自動再投資,所以很難計算報酬率。

每當有人問我美股的投資績效時,我還真不知該怎麼回答。

但是…如果有投資卻不知報酬率,代表沒有真正掌握住金錢的狀況

幸好最近有位金融前輩提醒我excel可以套用公式,我才上網找到這個快速又方便的計算方法,所以也分享給有同樣困擾的你!

延伸閱讀定期定額存股好不好?單筆投入或分批投入可行嗎?6個定期定額迷思,你犯了哪一個?

什麼是XIRR?與IRR及CAGR有何不同?

計算報酬率時,有以下三種方式:

  • XIRR (延伸內部報酬率):適用於計算各種投資方式的報酬率,包括不定期不定額
  • IRR (內部報酬率):適用於計算定期現金流的報酬率
  • CAGR (年均複合成長率):適用於計算單筆投資的報酬率

XIRR的中文是延伸內部報酬率 (Extended Internal Rate of Return),可計算各種投資方式的報酬率,包括以下類型:

  • 單筆投資
  • 定期定額
  • 定期不定額
  • 不定期不定額

換句話說,它是CAGR與IRR的進化版,算是萬用的報酬率計算法。

它們之間的差異如下:

XIRRIRRCAGR
單筆投資OOO
定期定額投資OOX
定期不定額投資OOX
不定期不定額投資OXX
適用投資類型所有投資定期有現金流的投資(如儲蓄險、收租)單筆投入的投資
XIRR IRR CAGR差異

運用Excel內的XIRR函數,計算不定期不定額的報酬率

那麼…要如何運用Excel內的XIRR公式,計算出報酬率呢?

以下表格為Excel示意圖…

首先,請在excel表格中的「左側」欄位 (也就是A欄),輸入你所投入的資金,並且記得以下原則:

投入的金額為負數,最後一列的目前帳上金額為正數

「右側」欄位 (也就是B欄) 需要填寫投入資金的時間,務必記得填寫「西元年月日」。

 A欄B欄
1投入資金投入時間
2-1,9952018/5/7
3-8,7692018/10/3
4-28,4662019/2/13
5-11,0002020/2/25
6-11,0002020/4/9
7-10,3002020/8/10
8-14,0002021/1/19
9100,3642021/4/17
XIRR=11.3%
Excel欄位示範

以上方的欄位數字為例,計算2018/5/7 ~ 2021/4/17的年化投資報酬率時,需填寫以下函數及範圍:

=XIRR(A2:A9,B2:B9)

透過公式可以計算出這段期間的年化報酬率為11.3%,也就是說這投資案每年以平均11.3%的報酬率「複利成長」。

如果你需要XIRR函數的Excel計算表,歡迎點選下方的LINE官方帳號,輸入密碼:XIRR,即可免費索取。

如果你不滿意目前的投資績效,想要長期獲得穩健的投資報酬率,也歡迎點選下方的【美股ETF懶人投資術】課程 (LINE官方帳號可索取優惠券),幫助你透過指數化投資,贏過市場9成以上的主動投資者。

加入喬王LINE官方帳號
美股ETF懶人投資術 被動投資課程

年化報酬率的正確比較方法

這裡要簡單說明一下「年化報酬率」的意思,以及為何要用它來做比較。

不同期間的報酬率無法放在一起比較
透過「年化報酬率」才能立判高下

當我們在投資時,有時候期間不一樣,所以很難放在一起直接比較。

譬如:

  • A投資案:投資期間一年,報酬率8%
  • B投資案:投資期間一季,報酬率2%
  • C投資案:投資期間兩年,報酬率16%

如果我們只單純看「報酬率」,C投資案的16%是最高的。

但因為C投資案的期間最長,所以無法直接用報酬率來相互比較。

如果統一都改用「 一年」為單位,那麼「平均一年的投資報酬率」大約都是8%左右,所以我們可以說A、B、C三個投資案有相同的報酬率。

PS: 實際計算精確的報酬率時,需要考量複利和幾何平均的概念,公式會比較複雜,這裡就不討論那麼細。

順帶一提,運用XIRR函數計算的好處,除了可以快速幫你處理不同時間、不同金額,而且計算出來的就是「年化報酬率 」,可以省下你自行換算的麻煩唷!

投資績效的比較基準

「挖!喬王!你的美股投資年化報酬率11.3%,很高耶!」

很多人看到我的投資報酬率後,可能會誤以為績效很好,這也是一般投資新手常犯的謬誤。

投資績效的好壞不能只憑感覺
要以「市場績效」當比較基準

當我們投資股票時,一定要和「整體市場」的報酬率一起比較,你才知道你的績效是比整體市場來得好?還是來得差?

台股市場,可以用「加權股價指數」當比較基準,美股市場可以用「S&P 500」指數當比較基準。

所以,喬王以同樣期間的S&P 500指數為例,一樣透過XIRR函數試算,可以得知年化報酬率為16.4%,事實上比我的11.3%還要高出許多。

投入資金投入時間
266.922018/5/7
417.262021/4/17
XIRR=16.4%
資料來源:investing.com

延伸閱讀指數化投資是什麼?被動投資 vs 主動投資,兩者差異在哪?有何優缺點?

比報酬率更重要的是…風險報酬率

「喬王,原來你這麼弱,竟然還輸給整體市場績效!」

看完我剛才的說明之後,肯定又有人會這麼跟我說吧?

拜託可以不要一下誇我、一下貶我嗎…= =

新手只在意「報酬率」
老手更在乎「風險報酬率」

老實說,當你全部把資金都all in在追蹤S&P 500股票指數的金融工具時,這段期間確實有可能會獲得16%的高報酬率。

但我認為新手通常只在意報酬率,很容易忘了背後的風險與波動。

這也是為什麼前陣子台灣的海運類股大漲,或者比特幣大漲時,一群散戶投入所有積蓄,甚至還大開槓桿借大錢,把資金都all in投入到股市,結果有人賠了一台車子、一棟房子,甚至還有人因此輕生。

當你是真正的投資老手時,你會更在乎自己「冒了多少風險」去「獲得多少的報酬」

補充一下:

如果套用投資學的CAPM理論,就是指在「風險性資產」中,加入了「無風險資產」的投資組合。

在投資的世界中,不存在最佳的投資組合,只存在風險和報酬之間的平衡

有些人的風險接受度比較高,有些人比較低,資本配置線的任意一點都是可以投資的組合,但投資人必須在風險和報酬中做出選擇 。

如果對這理論有興趣,可再自行研究CAPM理論。

pic pimg tw wannabuyhouse 1621524364 2595827298 g l jpg

所以,就算知道長期而言整體股市的報酬是很豐厚的,我還是會做好股債的資產配置,把一部分資金放在債券或和股市相關性低的標的上。

因為我知道:透過犧牲一部份的報酬,降低我的整體資產波動度,才能讓自己安心睡覺,又能專注在賺更多錢的本業上。

你想要投資海外ETF,卻不知如何開始嗎?被動投資已經多次被證明是一個簡單又長期有效的方法。

這門課教你用低風險、低成本的美股ETF,打造你的長期獲利系統!趕快一起加入學習的行列吧!

美股ETF懶人投資術被動投資線上課程

績效不需和別人比較,傾聽內心最重要!

老實說,我常遇到有些自認投資很厲害的人,一聽到我寫投資理財的文章,或在學校/機構分享投資理財知識,就彷彿「鬥牛」看到「紅旗」一樣,迫不及待地想要問我的投資績效如何。

遇到這樣的人,我多半一笑置之。

因為他們通常都是挑選幾個看似成長性很高的股票,並且根據不同的時機點積極地買進和賣出。

不過我認為,花了那麼多時間選股,又承受這麼大的波動,如果績效沒高出整體市場5%以上,而且沒有持續獲利10年以上,我覺得其實是白忙一場!

更何況很多人賺錢都沒跟大盤比,只要賺錢就吹噓自己多厲害,殊不知站在風口上,連豬都會飛!

PS: 我很少把話說得這麼不好聽,但我真的看過太多自認聰明的人常做這些事,吸引很多新手盲目追隨,反而害慘了很多人,所以我才這麼氣…

正因為如此,與其到處問別人的績效如何,不如問問自己:「想要犧牲多少的報酬,去獲得更安穩的投資?

因為這個問題,沒有人可以幫你決定!

總結

遇到不定期不定額的投資,不用煩惱,透過excel的XIRR函數公式,就能輕鬆算出年化報酬率。

當你遇到不同時間長度的投資案時,可以透過「年化報酬率」來幫助你一起比較。

投資績效的好壞不能只憑「感覺」,還要以「市場績效」來當比較的基準

比報酬率更重要的是:風險報酬率。

新手只在意「報酬率」,老手更在乎「風險報酬率」

所以,你必須知道你究竟是「冒了多少風險」去「獲得多少的報酬」。

當你想清楚「需要犧牲多少的報酬,去獲得更安穩的投資」,你才能建立出一套適合自己的交易系統,而且不會隨時受到市場漲跌或外人煽動而慌了陣腳囉!

延伸閱讀

pic pimg tw wannabuyhouse 1570774886 3235162052 n jpg

(Visited 10,256 times, 10 visits today)

喬王

喬王,台大經濟系/台科大科管所畢業,原本是銀行儲備幹部及總行PM,為了追求理想生活,大膽放棄了年薪百萬的工作,成為一名自由工作者。 目前同時經營部落格、Podcast、房地產及線上課程,專門幫助小資上班族達成人生三件事:穩健投資 x 買房安居 x 斜槓享受人生。 目前在Hahow好學校開設6門線上課程(含輔導其他講師),也曾受邀到富邦銀行、台北大學、臺北市立大學、台北市立教育大學、台灣科技大學、龍華科技大學、台灣西門子、三商美邦、天地人文創等機構演講,累積學員至少3000位以上。

This Post Has 2 Comments

  1. david

    請問若用xirr 的方法 中間有把錢匯出帳戶 該怎麼標記呢? 謝謝

    1. 喬王

      目前還沒看過有人提過相關的計算方式,所以我無法肯定。
      但我的做法一樣會記錄匯出日期,並且填上匯出金額,只不過會把”負值”改為”正值”。
      因為中間有匯出,所以帳上剩下的錢會扣除匯出的金額,得到的才是合理的報酬率。

發佈留言

You are currently viewing 超實用的XIRR函數!用Excel公式,1秒計算出不定期不定額的投資報酬率!(附IRR與CAGR比較)