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

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í. Nyní, zda je rozumné investovat do takového plánu nebo ne, lze rozhodnout z hodnoty IRR SIP. Čí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í, pomůžu vám zjistit, 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 níže.

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. Z finančního hlediska 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.

Přečtěte si také:Nejlepší šablony rozpočtu 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:

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 bude považovat 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.

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])

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.

Přečtěte si také:Jak používat vzorec pro odečítání v Excelu

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ů: Doba, Popis, a Tok peněz.
• Vyplňte data, která chcete vypočítat, do příslušných záhlaví sloupců.

• První investice by měla být záporná, 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 roce, pak by se tyto příjmy měly přičíst jako kladné hodnoty.

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

=IRR(C2:C8)

• Udeřil Vstupte získat návratnost investic z 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í.

• V tomto scénáři použijte a Tipni si najít přesný výsledek, protože IRR poskytne dva výsledky.

• The Tipni si argumentem by měla být návratnost investic, kterou očekáváte.

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 pravou stranu Tok peněz sloupec.
• Nyní zadejte datum investice do horní části a poté zadejte očekávané datum úroku nebo návratnosti kreditů.

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

=XIRR(C2:C8;D2:D8)

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.

• Tohle jsou Náklady na kapitál a Míra reinvestice.

• 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)

IRR a jeho upravené vzorce provedou před zobrazením výsledku 20 iterativní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 aplikaci Excel:

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

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

=NPV(C9,C3:C8)+C2

• Nyní klikněte na Data a poté vyberte Co kdyby analýza v Předpověď skupina příkazů v nabídce pásu karet Excel.
• Vybrat Hledání cíle z kontextové nabídky, která se objeví.
• Tady, Nastavit buňku by měla být hodnota NPV. Poté, Hodnotit by měla být 0.

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

• Nyní klikněte 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 peněžního toku 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.

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 je 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.

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.

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.

Další, jak používat vzorec Excel IF-THEN.