Jak vypočítat IRR v Excelu: 4 nejlepší metody v roce 2023

Pokud chcete činit informovaná rozhodnutí směrem k systematickým investičním plánům (SIP) nebo investovat do obchodních projektů jako venture kapitalista, musíte se naučit, jak vypočítat IRR v Excelu.

Existuje mnoho způsobů, jak vypočítat návratnost investic (ROI) pro investice do finančních nebo obchodních projektů. Zatímco výpočet na základě pevné sazby je poměrně snadný, není vždy volbou nástroje pro výpočet ROI ve velkých podnicích nebo investičním bankovnictví.

Řekněme, že budete investovat určitou částku peněz do investičního plánu na příštích 20 let. Plán říká, že dostanete výnos každé čtvrtletí. Z hodnoty IRR SIP lze nyní rozhodnout, zda je rozumné investovat do takového plánu nebo ne. Čím vyšší IRR, tím lukrativnější je investiční plán. Totéž platí pro všechny ostatní kapitálově náročné investice do podniků, bankovních spořicích účtů, podílových fondů, 401(k) atd.

Abyste se při investování těžce vydělaných peněz nespoléhali na ostatní, pomohu vám níže se naučit, co je IRR v Excelu, jeho několik upravených vzorců a jak vypočítat IRR v Excelu různými jednoduchými způsoby.

Co je vnitřní míra návratnosti (IRR)?

IRR neboli vnitřní míra návratnosti je běžná metrika používaná finančními plánovači k výpočtu nebo předpovídání budoucí ziskovosti potenciálního obchodního projektu nebo jiných typů investic. Ve finančním vyjádření je populárně známá jako ekonomická míra návratnosti nebo míra návratnosti diskontovaných peněžních toků.

Míra IRR nebo diskontní sazba investic činí veškerou čistou současnou hodnotu všech druhů peněžních toků, jako jsou odlivy a přítoky, nulové. Finanční odborníci tomu říkají vnitřní míra návratnosti, protože tento výpočet nezohledňuje další faktory, které by mohly pozitivně nebo negativně ovlivnit podnikání v nadcházejících letech.

Například IRR nebere v úvahu náklady na kapitál, míru inflace v čase, finanční rizika atd., když předpovídá ziskovost obchodního projektu nebo finanční investice.

Stručně řečeno, kdykoli se rozhodnete investovat na delší dobu do jakéhokoli komerčního projektu, podílového fondu nebo penzijního investičního plánu, zjistěte IRR těchto investičních možností. Poté vyberte ze všech možností tu, která poskytuje maximální IRR.

Čtěte také: Nejlepší šablony rozpočtu Excel

Jaké jsou funkce IRR v aplikaci Microsoft Excel?

Díky Microsoftu nemusíte vytvářet více vzorců nebo procházet složitými výpočty, abyste získali IRR investičního plánu. Excel má tři vyhrazené funkce výpočtu vnitřní míry návratnosti. O těchto funkcích se dozvíte zde:

1. IRR pro jednoduchou vnitřní míru návratnosti

Syntaxe IRR v Excelu vypočítává úrokovou sazbu investice do obchodního projektu nebo podílového fondu nebo fondu pro plánování odchodu do důchodu. Investice musí obsahovat alespoň jednu kladnou a zápornou hodnotu. Peněžní toky do fondu by také měly probíhat v pravidelných intervalech. Vzorec vypadá takto:

=IRR(hodnoty nebo rozsahy buněk, [argument odhadu])

• Hodnoty jsou povinným vstupem pro funkci IRR. Může to být řada čísel, pole čísel nebo rozsah referenčních buněk sestávající z peněžních toků.
• Guess argument je volitelný. Pokud nezadáte hodnotu Guess, Excel ji považuje za vnitřní míru návratnosti 0,1, 10 % nebo 10 základních bodů požadovanou investičním plánem. Pokud zadáte argument Guess, Excel spustí výpočty až pro 20 iterativních relací, aby našel hodnotu IRR blízkou hodnotě Guess.

2. XIRR pro nerovnoměrné načasování vrácení

Pokud potřebujete zohlednit rozvrh peněžních toků, musíte použít funkci XIRR. Syntaxe je následující:

=XIRR(hodnoty nebo rozsahy buněk, data, [argument odhadu])

3. MIRR pro reinvestice prostředků projektu

MIRR poskytuje vysoce spolehlivé hodnoty IRR ve srovnání s jednoduchou funkcí IRR. IRR nezohledňuje náklady na kapitál, který investujete. Konečná hodnota IRR tedy není přesná. Místo toho můžete použít funkci MIRR k zahrnutí nákladů na kapitálový faktor do výpočtu IRR. Syntaxe tedy vypadá následovně:

=MIRR(hodnoty nebo rozsahy buněk, finanční_sazba, reinvestice)

• Hodnoty je výkaz peněžních toků skládající se z alespoň jednoho kladného a záporného peněžního toku.
• Finance_rate je úrok, který platíte věřiteli oproti penězům vypůjčeným na obchodní projekt.
• Sazba reinvestice je úroková sazba, kterou váš podnik nebo investiční fond obdrží z peněžních toků při reinvestici.

Čtěte také: Jak používat vzorec pro odečítání v Excelu

Jak vypočítat IRR v Excelu: Použití syntaxe IRR

Než budete moci začít počítat IRR pro několik investičních plánů nebo projektů, musíte data uspořádat v Excelu. Zde je návod, jak se to dělá:

• Otevřete list aplikace Excel a vytvořte tři záhlaví sloupců: Období , Popis a Peněžní toky .
• Vyplňte data, která chcete vypočítat, do příslušných záhlaví sloupců.

Použití vzorce IRR v Excelu

• První investice by měla být záporná hodnota, protože platíte podílovému fondu nebo obchodnímu podnikateli za využití peněz.
• Pokud budete mít pravidelný příjem po jednom měsíci nebo po roce, měly by být tyto příjmy přičteny jako kladné hodnoty.

Naučte se vypočítat IRR v Excelu pomocí syntaxe IRR

• Nyní použijte následující vzorec v buňce, kde chcete, aby Excel vypočítal hodnotu IRR:

=IRR(C2:C8)

• Stisknutím klávesy Enter získáte návratnost investic potenciální investice.

Tento výpočet IRR je založen na výchozím argumentu Guess 10 %. Řekněme, že hledáte 12% návratnost. Zde je návod, jak potřebujete zadat hodnotu odhadu:

=IRR(C2:C8,30%)

• Ve výše uvedené tabulce vidíte, že poslední peněžní tok je záporný. Peněžní tok se tedy musí změnit z pozitivního na negativní nebo negativní na pozitivní.

Podívejte se, jak vypočítat IRR v Excelu pomocí odhadované hodnoty IRR

• V tomto scénáři použijte odhad k nalezení přesného výsledku, protože IRR vytvoří dva výsledky.

Jak vypočítat IRR v Excelu při použití záporné hodnoty Guess

• Argumentem Guess by měla být návratnost investic, kterou očekáváte.

Jak vypočítat IRR v Excelu: Pomocí syntaxe XIRR

Když získáte výnosy v různých datech po počáteční investici, použití syntaxe IRR poskytne chybné výsledky. V tomto scénáři musíte použít syntaxi XIRR. Zde jsou kroky, které můžete vyzkoušet na svém vlastním excelovém listu:

• Vytvořte záhlaví sloupce pro Datum vrácení na pravé straně sloupce Peněžní toky .
• Nyní zadejte datum investice do horní části a poté zadejte očekávané datum úroku nebo návratnosti kreditů.

Zjistěte, jak vypočítat IRR v Excelu pomocí syntaxe XIRR

• Po dokončení použijte v libovolné buňce následující syntaxi XIRR , abyste získali vnitřní míru návratnosti investic s nepravidelným plánem návratnosti:

=XIRR(C2:C8;D2:D8)

Jak vypočítat IRR v Excelu: Pomocí syntaxe MIRR

Aby byla hodnota IRR realističtější, můžete použít vzorec MIRR. Zde můžete zahrnout dva povinné faktory: náklady na kapitál a míru reinvestice. Níže naleznete kroky pro použití MIRR v Excelu:

• Do aktuálního listu přidejte dva řádky informací, jak je znázorněno na obrázku.

Použití funkce MIRR v Excelu

• Jedná se o náklady kapitálu a míru reinvestice .

Naučte se vypočítat IRR v Excelu pomocí syntaxe MIRR

• Do příslušných buněk zadejte také hodnoty.
• Nyní použijte následující vzorec, abyste získali realistickou vnitřní míru návratnosti:

=MIRR(C2:C8;G2;G3)

Jak vypočítat IRR v Excelu: Pomocí aplikace Goal Seek Excel

IRR a jeho upravené vzorce provedou před zobrazením výsledku 20 iteračních výpočtů. Pokud potřebujete větší přesnost, můžete k výpočtu IRR použít funkci Excel Goal Seek. V této metodě Excel provede až 32 000 iterativních výpočtů, než vygeneruje návratovou hodnotu. Zde jsou pokyny k použití funkce Hledání cílů a výpočtu IRR v Excelu:

• Nastavte IRR na 11 % nebo cokoliv jiného ručně.

Výpočet NPV v Excelu

• Vypočítejte NPV pod IRR pomocí tohoto vzorce:

=NPV(C9,C3:C8)+C2

• Nyní klikněte na kartu Data a poté vyberte What-If Analysis ve skupině příkazů Prognóza na pásu karet aplikace Excel.
• Z kontextové nabídky, která se zobrazí, vyberte možnost Hledání cíle .
• Zde by měla být v buňce Set hodnota NPV. Hodnota To by pak měla být 0.

Proveďte Goal Seek pro výpočet IRR v Excelu

• Nastavit Změnou buňky na hodnotu IRR.
• Stačí zadat odkazy na buňky, jak je znázorněno na obrázku.

Cíl Hledání výsledků pro výpočet IRR v Excelu

• Nyní klikněte na OK pro výpočet IRR pomocí hledání cíle.
• Podle aktuálního souboru dat bude NPV 0,00, pokud je IRR 12 %.

V metodě Goal Seek ve skutečnosti nemusíte vypočítat IRR z cash flow a výše investice. Místo toho nastavíte očekávanou IRR v buňce IRR a použijete ji k výpočtu NPV pomocí peněžního toku a částky investice. Potom použijete schopnost iterativního výpočtu aplikace Excel k předpovědi, kdy NPV dosáhne 0,00, při které hodnotě IRR.

Tipy pro správný výpočet IRR v Excelu

Pamatujte na tyto tipy a fakta, když budete počítat IRR v Excelu, abyste získali spolehlivá procenta IRR:

• Argument pro část Hodnoty musí obsahovat příjem (kladný peněžní tok) a výdaj (negativní peněžní tok), aby funkce fungovala přesně.
• Funkce IRR zpracuje pouze čísla v argumentu Hodnoty. Bude ignorovat jakékoli další prvky, jako jsou logické hodnoty, prázdné buňky a texty.
• Musí existovat pravidelné intervaly peněžních toků jako týdenní, měsíční, čtvrtletní, roční atd. Není povinné, aby se peněžní toky rovnaly hodnotě v dolarech.
• Ujistěte se, že jste připravili tabulku peněžních toků v chronologickém pořadí. Pokud například platíte 100 USD každý měsíc, uspořádejte hodnoty peněžních toků a jejich data od ledna, února, března atd.
• V jednoduchých výpočtech IRR není povinné zadávat argument Guess.
• Při použití hodnoty Guess bude vzorec IRR ukazovat hodnotu nejbližší argumentu Guess. Pokud se zobrazí #NUM! Chyba, změňte hodnotu argumentu Guess.

Jaká jsou omezení IRR v Excelu?

Než budete moci použít hodnotu IRR vypočítanou v Excelu v reálných situacích, měli byste také znát přirozená omezení vzorce pro vnitřní míru návratnosti v Excelu:

• IRR je vyjádření ROI v procentech. Nezohledňuje absolutní hodnotu. Může tedy vykazovat vyšší procento návratnosti u projektu s nízkou dolarovou hodnotou. Investoři rizikového kapitálu a individuální investoři však hledají projekty s vyšší dolarovou hodnotou, ačkoli procento návratnosti investic je nižší než u malých projektů.
• Vzorec IRR vrací hodnotu za předpokladu, že projekt bude reinvestovat svůj kapitál nebo peněžní tok stejnou rychlostí, jako je vypočítaná IRR. To není vždy možné, protože návratnost na kapitálovém trhu hodně kolísá.
• Pokud investiční plán nebo projekt zahrnuje střídání kladných a záporných peněžních toků, můžete pro stejný projekt získat více než jedno IRR.

Závěr

Nyní víte, jak vypočítat IRR v Excelu pomocí různých vyhrazených syntaxí IRR. Také jste se naučili, jak používat Excel Goal Seek k výpočtu IRR v Excelu. Pokud jsem vynechal nějakou jinou metodu, nezapomeňte to zmínit v poli komentáře níže.

Dále,  jak používat vzorec Excel IF-THEN .