Excel中的IRR計算（內部收益率）

該教程顯示瞭如何通過公式和目標尋求功能計算Excel中項目的IRR。您還將學習如何創建內部返回模板以自動執行所有IRR計算。

當您知道擬議投資的內部回報率時，您可能會認為自己需要評估它 - IRR越大，IRR越好。實際上，這並不是那麼簡單。 Microsoft Excel提供了三種不同的功能來找到內部收益率，真正了解您實際上使用IRR計算的內容將非常有幫助。

• 什麼是內部收益率（IRR）？
• Excel中的手動IRR計算
• 如何用Excel公式計算IRR
  • IRR公式計算內部收益率
  • xirr公式以找到不平等時機現金流的IRR公式
  • MIRR配方進行修改IRR
• 內部收益計算器 /模板的速率
• 如何與目標尋求IRR

什麼是IRR？

內部收益率（IRR）是一個常用的度量標準，可以估計潛在投資的盈利能力。有時，它也稱為折扣現金流量的回報率或經濟回報率。

從技術上講，IRR是使所有現金流量（流入和流出）的淨現值的折現率從等於零的一定投資。

“內部”一詞表示僅考慮內部因素；諸如通貨膨脹，資本成本和各種財務風險之類的外部因素被排除在計算之外。

IRR揭示了什麼？

在資本預算中，IRR被廣泛用於評估預期投資的盈利能力並對多個項目進行排名。一般原則與此一樣簡單：內部收益率越高，項目越有吸引力。

當估計一個項目時，財務分析師通常將IRR與公司的加權平均資本或障礙率進行比較，這是公司可以接受的投資的最低收益率。在假設的情況下，當IRR是做出決定的唯一標準時，如果IRR大於障礙率，則將項目視為一項良好的投資。如果IRR低於資本成本，則應拒絕該項目。實際上，還有許多其他因素會影響決定，例如淨現值（NPV），投資回收期，絕對退貨價值等。

IRR限制

儘管IRR是評估資本項目的一種非常流行的方法，但它確實存在許多固有的缺陷，可能會導致次優的決策。 IRR的主要問題是：

• 相對度量。 IRR認為百分比而不是絕對價值，結果，它可以偏愛一個具有很高回報率的項目，但美元價值很小。實際上，公司可能更喜歡一個大型項目，而IRR較低的項目比具有較高IRR的小型項目更喜歡。在這方面，NPV是一個更好的指標，因為它考慮了通過進行項目獲得或損失的實際金額。
• 相同的再投資率。 IRR假設項目產生的所有現金流都以等於IRR本身的速率進行再投資，這是一個非常不現實的情況。 MIRR可以解決此問題，該問題允許指定不同的財務和再投資率。
• 多個結果。對於交替現金流量和負現金流量的項目，可以找到多個IRR。該問題還可以在MIRR中解決，後者旨在僅產生一個速率。

儘管存在這些缺陷，但IRR仍然是對資本預算的重要衡量標準，至少，在做出投資決定之前，您應該對其進行懷疑。

Excel中的IRR計算

由於內部收益率是給定現金流量等於零的淨現值的折現率，因此IRR計算基於傳統的NPV公式：

如果您對總和表示法不太熟悉，則IRR公式的擴展形式可能更容易理解：

在哪裡：

• CF _0-初始投資（由負數表示）
• cf ₁ ，cf ₂ …cf _n-現金流量
• 我 - 週期編號
• n-總數總計
• IRR-內部回報率

公式的性質使得沒有分析方法來計算IRR。我們必須使用“猜測和檢查”方法來找到它。為了更好地理解內部收益率的概念，讓我們在一個非常簡單的示例上執行IRR計算。

示例：您現在投資1000美元，在未來2年內拿回500美元和660美元。什麼折現率使淨現值零？

作為我們的第一個猜測，讓我們嘗試8％的速率：

• 現在：PV = - $ 1,000
• 1年：PV = $ 500 /（1 0.08） ¹ = $ 462.96
• 2年：PV = $ 660 /（1 0.08） ² = $ 565.84

添加這些，我們的NPV等於$ 28.81：

哦，甚至沒有接近0。也許更好的猜測，例如10％，可以改變事物嗎？

• 現在：PV = - $ 1,000
• 1年：PV = $ 500 /（1 0.1） ¹ = $ 454.55
• 2年：PV = $ 660 /（1 0.1） ² = $ 545.45
• NPV：-1000 $ 454.55 $ 545.45 = $ 0.00

就是這樣！以10％的折現率，NPV正好為0。因此，這項投資的IRN為10％：

這就是您手動計算內部收益率的方式。 Microsoft Excel，其他軟件程序和各種在線IRR計算器也依賴此試驗和錯誤方法。但是與人類不同，計算機可以很快進行多次迭代。

如何用公式在Excel中計算IRR

Microsoft Excel提供了3個功能來查找內部收益率：

• IRR-最常用的功能，用於計算定期發生的一系列現金流量的內部收益率。
• XIRR - 找到一系列以不規則時間間隔發生的現金流量的IRR。由於它考慮了付款的確切日期，因此此功能提供了更好的計算準確性。
• MIRR -返回修改後的內部收益率，這是IRR的變體，它考慮了借貸成本和在重新投資正現金流量時獲得的複合利息。

在下面，您將找到所有這些功能的示例。為了一致性，我們將在所有公式中使用相同的數據集。

IRR公式計算內部收益率

假設您正在考慮使用B2：B7中的現金流量進行5年的投資。要確定IRR，請使用此簡單的公式：

=IRR(B2:B7)

筆記。為了使IRR公式正常工作，請確保您的現金流量至少具有一個負（流出）和一個正值（流入），並且所有值都按時間順序列出。

有關更多信息，請參閱Excel IRR功能。

XIRR公式以尋找不規則現金流的IRR

如果有不平等時機的現金流量，使用IRR功能可能會冒險，因為它假設所有付款都發生在一個時期結束時，並且所有時間段是相等的。在這種情況下，XIRR將是一個更明智的選擇。

隨著B2：B7中的現金流量及其在C2：C7中的日期，該公式將如下：

=XIRR(B2:B7,C2:C7)

筆記：

• 儘管XIRR功能不一定按時間順序排列，但第一個現金流（初始投資）的日期應首先在陣列中。
• 必須以有效的excel日期提供日期；提供文本格式的日期會使Excel有誤解它們的風險。
• Excel XIRR函數使用不同的公式來得出結果。 XIRR公式根據365天的年份折扣後續付款，結果，Xirr始終返回年度內部收益率。

有關更多詳細信息，請參閱Excel XIRR功能。

MIRR公式可以製定改良的IRR

為了處理更現實的情況，當項目資金以更接近公司資本成本的速度進行再投資時，您可以通過使用MIRR公式來計算修改後的內部回報率：

=MIRR(B2:B7,E1,E2)

其中b2：b7是現金流量，E1是融資率（借款的成本），而E2是再投資利率（收益再投資時獲得的利息）。

筆記。由於Excel MIRR函數計算利潤中的複合興趣，因此其結果可能與IRR和XIRR函數的結果大不相同。

IRR，XIRR和MIRR-哪個更好？

我相信沒有人可以對這個問題給出通用的答案，因為在金融學者中，所有三種方法的理論基礎，優勢和缺點仍然存在爭議。也許最好的方法是進行所有三個計算並比較結果：

通常，認為這是：

• XIRR提供的計算精度比IRR更好，因為它考慮了現金流的確切日期。
• IRR通常對項目的盈利能力進行過度樂觀的評估，而Mirr給出了更現實的情況。

IRR計算器 - Excel模板

如果您需要定期在Excel中進行IRR計算，則設置內部返回模板可以使您的生活更加輕鬆。

我們的計算器將包括所有三個公式（IRR，XIRR和MIRR），因此您不必擔心哪個結果更有效，但可以考慮所有結果。

1. 將現金流量和日期輸入兩列（在我們的情況下為A和B）。
2. 在2個單獨的單元中輸入金融率和再投資率。可選地，分別命名這些命名為finance_rate和renvest_rate 。
3. 創建兩個動態定義的範圍，命名為cash_flows和日期。

   假設您的工作表命名為Sheet1 ，則第一個現金流（初始投資）是在單元A2中，並且第一個現金流的日期在單元格B2中，根據這些公式將命名範圍製成：

   cash_flows：

   =OFFSET(Sheet1!$A$2,0,0,COUNT(Sheet1!$A:$A),1)

   日期：

   =OFFSET(Sheet1!$B$2,0,0,COUNT(Sheet1!$B:$B),1)

   可以在如何創建Excel中的命名範圍的動態範圍中找到詳細的步驟。

4. 使用您剛剛創建的名稱作為以下公式的參數。請注意，公式可以在A和B以外的任何列中輸入，分別保留用於現金流量和日期。

   =IRR(Cash_flows)

   =XIRR(Cash_flows, Dates)

   =MIRR(Cash_flows, Finance_rate, Reinvest_rate)

完畢！現在，您可以在A列中輸入任何數量的現金流，並且您的動態內部收益公式將相應地重新計算：

作為對可能忘記填寫所有必需輸入單元格的粗心用戶的預防措施，您可以將公式包裝在IFERROR功能中以防止錯誤：

=IFERROR(IRR(Cash_flows), "")

=IFERROR(XIRR(Cash_flows, Dates), "")

=IFERROR(MIRR(Cash_flows, Finance_rate, Reinvest_rate), "")

請記住，如果finance_rate和/或renevest_rate單元格為空白，則excel mirr函數假定它們等於零。

如何在目標尋求目標中進行IRR

Excel IRR功能僅執行20次迭代，以達到速率，XIRR執行100次迭代。如果在許多迭代之後找不到在0.00001％以內的結果準確，則#num！返回錯誤。

如果您正在為IRR計算尋找更準確性，則可以通過使用目標Seek功能來強迫Excel進行超過32,000次迭代，這是何種分析的一部分。

這個想法是要尋求目標，以找到使NPV等於0的百分比。這就是：：

1. 以這種方式設置源數據：
   • 在列中輸入現金流（在此示例中B2：B7）。
   • 將預期的IRR放入某些單元格（B9）。您輸入的價值實際上並不重要，您只需要將某些東西“饋送”到NPV公式，因此只需將所有的百分比考慮在內，例如10％。
   • 在另一個單元格（B10）中輸入以下NPV公式： =NPV(B9,B3:B7) B2

   

2. 在“數據”選項卡上，在“預測組”中，單擊如果分析>目標尋求……
3. 在目標搜索對話框中，定義要測試的單元格和值：
   • 將單元格- 引用NPV單元格（B10）。
   • 值為- 類型0，這是設定單元格的所需值。
   • 通過更改細胞- 對IRR細胞的引用（B9）。

   完成後，單擊確定。

   

4. 目標搜索狀態對話框將出現，並讓您知道是否找到了解決方案。如果成功，IRR單元格中的值將被新的NPV零替換。

   單擊確定以接受新值或取消以恢復原始值。

   

以類似的方式，您可以使用目標尋求功能查找XIRR。唯一的區別是您需要使用XNPV公式而不是NPV。

筆記。通過目標尋求發現的IRR值是靜態的，它不會像公式一樣動態地重新計算。每次更改原始數據之後，您將必須重複上述步驟才能獲得新的IRR。

這就是在Excel中進行IRR計算的方法。要仔細查看本教程中討論的公式，歡迎您下面下載我們的示例工作簿。我感謝您閱讀，並希望下週在我們的博客上見到您！

練習工作簿下載

Excel IRR計算器 - 示例（.xlsx文件）

以上是Excel中的IRR計算（內部收益率）的詳細內容。更多資訊請關注PHP中文網其他相關文章！