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

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

什麼是IRR？

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

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

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

IRR揭示了什麼？

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

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

IRR限制

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

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

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

Excel中的IRR計(jì)算

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

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

在哪裡：

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

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

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

作為我們的第一個(gè)猜測，讓我們嘗試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。因此，這項(xiàng)投資的IRN為10％：

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

如何用公式在Excel中計(jì)算IRR

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

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

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

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

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

=IRR(B2:B7)

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

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

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

如果有不平等時(shí)機(jī)的現(xiàn)金流量，使用IRR功能可能會(huì)冒險(xiǎn)，因?yàn)樗僭O(shè)所有付款都發(fā)生在一個(gè)時(shí)期結(jié)束時(shí)，並且所有時(shí)間段是相等的。在這種情況下，XIRR將是一個(gè)更明智的選擇。

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

=XIRR(B2:B7,C2:C7)

筆記：

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

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

MIRR公式可以製定改良的IRR

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

=MIRR(B2:B7,E1,E2)

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

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

IRR，XIRR和MIRR-哪個(gè)更好？

我相信沒有人可以對這個(gè)問題給出通用的答案，因?yàn)樵诮鹑趯W(xué)者中，所有三種方法的理論基礎(chǔ)，優(yōu)勢和缺點(diǎn)仍然存在爭議。也許最好的方法是進(jìn)行所有三個(gè)計(jì)算並比較結(jié)果：

通常，認(rèn)為這是：

• XIRR提供的計(jì)算精度比IRR更好，因?yàn)樗紤]了現(xiàn)金流的確切日期。
• IRR通常對項(xiàng)目的盈利能力進(jìn)行過度樂觀的評估，而Mirr給出了更現(xiàn)實(shí)的情況。

IRR計(jì)算器 - Excel模板

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

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

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

   假設(shè)您的工作表命名為Sheet1 ，則第一個(gè)現(xiàn)金流（初始投資）是在單元A2中，並且第一個(gè)現(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)圍的動(dòng)態(tài)範(fàn)圍中找到詳細(xì)的步驟。

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)金流，並且您的動(dòng)態(tài)內(nèi)部收益公式將相應(yīng)地重新計(jì)算：

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

=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)中進(jìn)行IRR

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

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

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

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

   

2. 在“數(shù)據(jù)”選項(xiàng)卡上，在“預(yù)測組”中，單擊如果分析>目標(biāo)尋求……
3. 在目標(biāo)搜索對話框中，定義要測試的單元格和值：
   • 將單元格- 引用NPV單元格（B10）。
   • 值為- 類型0，這是設(shè)定單元格的所需值。
   • 通過更改細(xì)胞- 對IRR細(xì)胞的引用（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)的，它不會(huì)像公式一樣動(dòng)態(tài)地重新計(jì)算。每次更改原始數(shù)據(jù)之後，您將必須重複上述步驟才能獲得新的IRR。

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

練習(xí)工作簿下載

Excel IRR計(jì)算器 - 示例（.xlsx文件）

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