該教程顯示瞭如何通過公式和目標(biāo)尋求功能計算Excel中項目的IRR。您還將學(xué)習(xí)如何創(chuàng)建內(nèi)部返回模板以自動執(zhí)行所有IRR計算。

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

什麼是IRR？

內(nèi)部收益率（IRR）是一個常用的度量標(biāo)準(zhǔn)，可以估計潛在投資的盈利能力。有時，它也稱為折扣現(xiàn)金流量的回報率或經(jīng)濟回報率。

從技術(shù)上講，IRR是使所有現(xiàn)金流量（流入和流出）的淨(jìng)現(xiàn)值的折現(xiàn)率從等於零的一定投資。

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

IRR揭示了什麼？

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

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

IRR限制

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

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

儘管存在這些缺陷，但IRR仍然是對資本預(yù)算的重要衡量標(biāo)準(zhǔn)，至少，在做出投資決定之前，您應(yīng)該對其進行懷疑。

Excel中的IRR計算

由於內(nèi)部收益率是給定現(xiàn)金流量等於零的淨(jìng)現(xiàn)值的折現(xiàn)率，因此IRR計算基於傳統(tǒng)的NPV公式：

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

在哪裡：

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

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

示例：您現(xiàn)在投資1000美元，在未來2年內(nèi)拿回500美元和660美元。什麼折現(xiàn)率使淨(jìng)現(xiàn)值零？

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

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

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

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

• 現(xiàn)在：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％的折現(xiàn)率，NPV正好為0。因此，這項投資的IRN為10％：

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

如何用公式在Excel中計算IRR

Microsoft Excel提供了3個功能來查找內(nèi)部收益率：

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

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

IRR公式計算內(nèi)部收益率

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

=IRR(B2:B7)

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

有關(guān)更多信息，請參閱Excel IRR功能。

XIRR公式以尋找不規(guī)則現(xiàn)金流的IRR

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

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

=XIRR(B2:B7,C2:C7)

筆記：

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

有關(guān)更多詳細信息，請參閱Excel XIRR功能。

MIRR公式可以製定改良的IRR

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

=MIRR(B2:B7,E1,E2)

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

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

IRR，XIRR和MIRR-哪個更好？

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

通常，認為這是：

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

IRR計算器 - Excel模板

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

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

1. 將現(xiàn)金流量和日期輸入兩列（在我們的情況下為A和B）。
2. 在2個單獨的單元中輸入金融率和再投資率?？蛇x地，分別命名這些命名為finance_rate和renvest_rate 。
3. 創(chuàng)建兩個動態(tài)定義的範(fàn)圍，命名為cash_flows和日期。

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

   cash_flows：

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

   日期：

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

   可以在如何創(chuàng)建Excel中的命名範(fàn)圍的動態(tài)範(fàn)圍中找到詳細的步驟。

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

   =IRR(Cash_flows)

   =XIRR(Cash_flows, Dates)

   =MIRR(Cash_flows, Finance_rate, Reinvest_rate)

完畢！現(xiàn)在，您可以在A列中輸入任何數(shù)量的現(xiàn)金流，並且您的動態(tài)內(nèi)部收益公式將相應(yīng)地重新計算：

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

=IFERROR(IRR(Cash_flows), "")

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

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

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

如何在目標(biāo)尋求目標(biāo)中進行IRR

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

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

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

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

   

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

   完成後，單擊確定。

   

4. 目標(biāo)搜索狀態(tài)對話框?qū)⒊霈F(xiàn)，並讓您知道是否找到了解決方案。如果成功，IRR單元格中的值將被新的NPV零替換。

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

   

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

筆記。通過目標(biāo)尋求發(fā)現(xiàn)的IRR值是靜態(tài)的，它不會像公式一樣動態(tài)地重新計算。每次更改原始數(shù)據(jù)之後，您將必須重複上述步驟才能獲得新的IRR。

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

練習(xí)工作簿下載

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

以上是Excel中的IRR計算（內(nèi)部收益率）的詳細內(nèi)容。更多資訊請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！