怪老子ETF線上課程

學會IRR、投資難題輕鬆解

日期:2011-10-03

最近受到美國信用評等被調降,以及歐債危機的影響,很多人的荷包最近短少了許多。一些投資者受不了這種波動,紛紛想轉進較保守的標的,但是對著琳瑯滿目的金融商品卻無從下手,究其原因就是不了解這些商品的報酬率是多少,當然就無從選擇了。其實只要知道如何使用簡單的Excel IRR函數,就可以輕易的計算出投資報酬率,讓讀者可以輕鬆的挑對商品。

首先看一個儲蓄險的例子:Michelle看中了一個6年期的儲蓄險:

第1~6年初繳保費157,014元,到了第6年底領回100萬元。

Michelle正納悶著這樣的保單年利率是多少呢?於是拿起計算機算了起來,六年總共繳了942,084保費,也就是賺了57,916,相當於6.15% (=57916/942084),平均每年報酬率是1.02%,看起來比銀行定存都差。

然而這樣的算法是錯誤的,因為沒有把時間的因素考慮進去。雖然第一年到第六年均投入金額都是157,014,但是時間點都不同,所以到了期末時每一筆金額所獲得的本利和都不一樣,當中涉及複利問題,當然無法用簡單算術來計算。

還好微軟Excel的財務函數IRR,提供了一個既簡單又實用的工具,讓我們免除繁雜的數學,只要將所發生的現金流量輸入進去,立即就會算出投資報酬率是多少。IRR的應用非常廣泛,舉凡儲蓄險、股票投資、定期定額基金、標會以及貸款…等。任何跟金錢相關的投資,只要列得出現金流量,都可以用IRR函數計算。

IRR函數

學會描述現金流量

IRR (Internal Rate of Return)稱為內部報酬率,是一個可以利用現金流量反推年化報酬率的工具。而且使用非常簡單,任何投資項目只要列得出現金流量,IRR函數就可以計算出來,堪稱是Excel最常用的財務函數之一。現金流量有正負之分,現金流出以負值來表示;現金流入則以正值來表示。

IRR參數

使用方式是在Excel任何一個儲存格,只要輸入下列IRR公式就可以了:

=IRR(現金流量, 猜測值)

第一個參數是『現金流量』,以數值或儲存格的範圍來表示均可。下圖B9的公式“=IRR(B2:B8)”,其中B2:B8就是儲存格範圍,意思是:B2到B8的每一個儲存格,存放著每一期所發生的現金流量。

第二個參數是『猜測值』,這個參數通常是可以省略的,但是有些時候若答案離一般常用的值太遠時,IRR函數就出現錯誤#NUM!。根據經驗當IRR出現錯誤答案時,若是一個月為一期,只要輸入猜測值”1%”,問題通常就可以解決了。

儲蓄險的應用

上述儲蓄險範例中,現金流量很容易就可以描述出來。每年初都繳了保費157,014元,保費繳納屬現金流出,所以1至6年初都是 -157,014。到了第六年底時,領回滿期金100萬元,屬於現金流入、當然是+1,000,000。

只要如下圖所示,將這六年的現金流量,列在B2~B8儲存格。然後在儲存格B9輸入公式 =IRR(B2:B8),所得到的1.71%就是這儲蓄險的年利率。這結果和Michelle用簡單算術的結果1.02%,當中差距可是不小,不能不謹慎。

六年期儲蓄險

就是這麼簡單!只要知道現金流量,IRR就有辦法算出報酬率是多少。其實現金流量就好比人類血液,醫師靠量脈搏得知身體狀況好壞。投資靠IRR來量測現金流量,判斷獲利多寡,這當中的道理都是相通的。

車貸利率自己算

156期Smart雜誌(p198)所介紹的零利率汽車貸款專案。這是一個相當複雜的貸款,而且網路上也找不到其他的試算工具,真正年利率是多少,消費者往往只能聽信業務員說詞,毫無驗算之能力。

一輛汽車總價71萬9,945元,頭期款6萬9,000元,剩餘650,945元以零利率、分60期攤還,每月只需繳款3,999,但是每年春節那個月則必須繳款5萬元,到了最後一個月繳清貸款餘額18萬1,000元。所以繳了55期的3,999元、5期的5萬元以及最後一期的18萬1,000元。

這貸款號稱零利率,但是消費者可不要被蒙蔽了,天底下沒有零利率這回事。如果真是零利率,那麼為何總價71萬9,945元的車子,用現金只要62萬9,000元就買得到,當中90,945元的價差,不是利息又是甚麼!

對車商而言這輛車的賣價只有一個,就是現金價62萬9,000元,若要貸款買車就必須額外付出利息。表面上零利率的安排,實質等於自己跟銀行貸款56萬元,然而往後每月卻繳了55筆3,999、5筆5萬元以及尾款18萬1,000,總共繳款65萬945元。貸款金額為56萬的原因是:已經有自備款6萬9,000,只差56萬就可以用現金價62萬9,000元買到這輛車了。看清楚:借了56萬元,卻繳了65萬945元,中間差額就是利息。

車貸圖

從現金流量角度來看,第一期是銀行撥款,所以是現金流入56萬元,其他都是每月繳款,屬於現金流出。問題是這樣每期繳款金額都不等的貸款,想要知道貸款利率,並無簡單的公式可應用。但是有了IRR函數卻是易如反掌,只要如下圖所示,將這60期現金流量依序在B4:B64的儲存格列出,然後在B1儲存格輸入下列IRR公式,就可以輕鬆計算出年利率是4.8%。

=IRR(B4:B64, 1%)*12

注:每一期為一個月,所以計算出來的利率是月利率,年利率必須乘上12。而且因為是月利率,所以多加第二參數1%。

驗證

若要驗證算出來的結果是否正確,最讓人信服的方法就是直接做出貸款攤還表,只要下載下列Excel,就可以看到清清楚楚的看到每一期的本金及利息到底是多少,以及每一期的貸款餘額狀況。

Excel檔下載

關於本文提到之Excel檔案,可於此下載:

6年儲蓄險試算Excel下載

車貸利率試算Excel下載

本文刊登於Smart智富月刊158期

Smart158期封面




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