Az IRR kiszámítása Excelben: 4 legjobb módszer

Ha megalapozott döntéseket szeretne hozni a szisztematikus befektetési tervek (SIP) felé, vagy kockázati tőkebefektetőként szeretne üzleti projektekbe fektetni, meg kell tanulnia az IRR kiszámítását Excelben.

A pénzügyi vagy üzleti projektekbe történő befektetések megtérülésének (ROI) kiszámításának számos módja van. Míg a fix kamatozású számítás meglehetősen egyszerű, a nagyvállalatok vagy a befektetési banki szolgáltatások esetében nem mindig ez az eszköz választása a ROI kiszámításához.

Tegyük fel, hogy egy összeget befektet egy befektetési tervbe a következő 20 évre. A terv szerint negyedévente kap hozamot. Namost, hogy okos-e befektetni egy ilyen tervbe vagy sem, azt a SIP IRR-értékéből lehet eldönteni. Minél magasabb az IRR, annál jövedelmezőbb a befektetési terv. Ugyanez igaz az összes többi tőkeigényes vállalkozási befektetésre, banki megtakarítási számlákra, befektetési alapokra, 401(k) stb.

Hogy ne másokra hagyatkozzon, amikor nehezen megkeresett pénzt fektet be, segítek megtanulni, mi az IRR az Excelben, annak számos módosított képlete, valamint az alábbiakban az IRR kiszámítása az Excelben különféle egyszerű módszerekkel.

Az IRR vagy a belső megtérülési ráta egy általános mérőszám, amelyet a pénzügyi tervezők használnak egy potenciális üzleti projekt vagy más típusú befektetés jövőbeli jövedelmezőségének kiszámítására vagy előrejelzésére. Pénzügyi értelemben közismerten a gazdasági megtérülési ráta vagy a diszkontált cash flow megtérülési ráta.

Az IRR-ráta vagy a befektetés diszkontrátája mindenféle pénzáramlás, például a ki- és beáramlás összes nettó jelenértékét nullával egyenlővé teszi. A pénzügyi szakemberek ezt belső megtérülési rátának nevezik, mivel ez a számítás nem vesz figyelembe más olyan tényezőket, amelyek pozitívan vagy negatívan befolyásolhatják az üzletet a következő években.

Például az IRR nem veszi figyelembe a tőkeköltséget, az időbeli inflációs rátát, a pénzügyi kockázatokat stb., amikor egy üzleti projekt vagy pénzügyi befektetés jövedelmezőségét előre jelzi.

Dióhéjban, valahányszor úgy dönt, hogy hosszabb időre befektet bármilyen kereskedelmi projektbe, befektetési alapba vagy nyugdíj-befektetési tervbe, tájékozódjon az adott befektetési lehetőségek IRR-jéről. Ezután válassza ki azt, amelyik a maximális IRR-t adja az összes lehetőség közül.

Olvassa el még:A legjobb Excel költségvetési sablonok

A Microsoftnak köszönhetően nem kell több képletet készítenie vagy bonyolult számításokon átmennie ahhoz, hogy megkapja a befektetési terv IRR-jét. Az Excel három dedikált belső megtérülési ráta számítási funkcióval rendelkezik. Tudjon meg többet ezekről a funkciókról itt:

Az Excel IRR szintaxisa kiszámítja egy üzleti projektbe, befektetési alapba vagy nyugdíjtervezési alapba történő befektetés kamatlábait. A befektetésnek tartalmaznia kell legalább egy pozitív és egy negatív értéket. Ezenkívül az alapba történő pénzáramlásnak rendszeres időközönként kell történnie. Így néz ki a képlet:

=IRR(értékek vagy cellatartományok, [találat argumentum])

• Az értékek az IRR függvény kötelező bemenete. Ez lehet egy számsorozat, egy számtömb, vagy egy referencia cellatartomány, amely pénzáramlásokból áll.
• A Guess argumentum nem kötelező. Ha nem ad meg Guess értéket, akkor azt az Excel 0,1, 10% vagy 10 bázispontos belső megtérülési rátának tekinti, amelyet a befektetési terv megkövetel. Ha megad egy Guess argumentumot, az Excel legfeljebb 20 iteratív munkameneten keresztül futtatja a számításokat, hogy megtalálja a találgatási értékhez közeli IRR-értéket.

Ha figyelembe kell vennie a pénzáramlás ütemezését, akkor az XIRR függvényt kell használnia. A szintaxis a következő:

=XIRR(értékek vagy cellatartományok, dátumok, [találat argumentum])

A MIRR nagyon megbízható IRR értékeket ad az egyszerű IRR függvényhez képest. Az IRR nem veszi figyelembe a befektetett tőke költségét. Ezért az IRR végső értéke nem pontos. Ehelyett használhatja a MIRR függvényt egy tőketényező költségének az IRR-számításba való bevonásához. Ezért a szintaxis az alábbiak szerint alakul:

=MIRR(értékek vagy cellatartományok, finanszírozási_kamatláb, újrabefektetési_kamatláb)

• Az értékek a cash flow kimutatás, amely legalább egy pozitív és egy negatív cash flow-t tartalmaz.
• A pénzügyi_kamatláb az a kamat, amelyet a hitelezőnek fizet az üzleti projekthez kölcsönzött pénz ellenében.
• Az újrabefektetési_kamatláb az a kamatláb, amelyet az Ön vállalkozása vagy befektetési alapja kap az újrabefektetés utáni cash flow-k után.

Olvassa el még:Az Excel kivonási képlet használata

Mielőtt elkezdené az IRR kiszámítását több beruházási terv vagy projekt esetében, először Excelben kell rendszereznie az adatokat. Íme, hogyan történik:

• Nyisson meg egy Excel munkalapot, és hozzon létre három oszlopfejlécet: Időszak, Leírás, és Pénzforgalom.
• Töltse ki a kiszámítani kívánt adatokat a megfelelő oszlopfejlécek alá.

• Az első befektetésnek negatív értékűnek kell lennie, mivel egy befektetési alapnak vagy üzleti vállalkozónak fizet a pénz felhasználásáért.
• Ha egy hónap vagy egy év után rendszeres bevételhez jut, akkor ezeket a bevételeket pozitív értékként kell hozzáadni.

• Most használja a következő képletet abban a cellában, ahol azt szeretné, hogy az Excel kiszámítsa az IRR értéket:

=IRR(C2:C8)

• Találat Belép hogy egy potenciális befektetés megtérülése megtérüljön.

Ez az IRR-számítás az alapértelmezett 10%-os Guess argumentum alapján történik. Tegyük fel, hogy 12%-os megtérülést keres. Így kell megadnia a feltételezett értéket:

=IRR(C2:C8,30%)

• A fenti táblázatban látható, hogy az utolsó cash flow negatív. Így a cash flow-nak pozitívról negatívra vagy negatívról pozitívra kell változnia.

• Ebben a forgatókönyvben használja a Találd ki hogy megtaláljuk a pontos eredményt, mivel az IRR két eredményt ad.

• A Találd ki érvnek a várt befektetési megtérülésnek kell lennie.

Ha a kezdeti befektetés után különböző dátumokon kap megtérülést, az IRR szintaxis használata hibás eredményeket ad. Ebben a forgatókönyvben az XIRR szintaxist kell használnia. A következő lépéseket próbálhatja ki a saját Excel munkalapján:

• Hozzon létre egy oszlopfejlécet a A visszaküldés dátuma a jobb oldalra Pénzforgalom oszlop.
• Most írja be a befektetés dátumát a tetejére, majd adja meg a kamat vagy a megtérülés várható dátumát.

• Ha elkészült, használja a következőket XIRR szintaxis bármely cellában, hogy megkapja a befektetés belső megtérülési rátáját szabálytalan megtérülési ütemezés mellett:

=XIRR(C2:C8;D2:D8)

Az IRR érték reálisabbá tételéhez használhatja a MIRR képletet. Itt két kötelező tényezőt vehet figyelembe: a tőkeköltséget és az újrabefektetési rátát. Az alábbiakban megtalálja a MIRR Excelben való használatának lépéseit:

• Az aktuális munkalapon adjon hozzá két adatsort a képen látható módon.

• Ezek a Tőkeköltség és Újrabefektetési ráta.

• A megfelelő cellákba írja be az értékeket is.
• Most használja a következő képletet, hogy reális belső megtérülési rátát kapjon:

=MIRR(C2:C8;G2;G3)

Az IRR és módosított képletei 20 iteratív számítást végeznek az eredmény megjelenítése előtt. Ha ennél nagyobb pontosságra van szüksége, használhatja az Excel Goal Seek funkcióját az IRR kiszámításához. Ezzel a módszerrel az Excel akár 32 000 iteratív számítást is végrehajt, mielőtt visszatérési értéket generál. Íme a Goal Seek használatára és az IRR kiszámítására vonatkozó utasítások Excelben:

• Állítsa be az IRR-t 11%-ra vagy bármi mást kézzel.

• Számítsa ki az IRR alatti NPV-t a következő képlettel:

=NPV(C9,C3:C8)+C2

• Most kattintson a Adat fület, majd válassza ki a Mi lenne, ha elemzés ban,-ben Előrejelzés parancscsoportot az Excel szalagmenüjében.
• Válassza ki Goal Seek a felugró helyi menüből.
• Itt, a Cella beállítása az NPV értéknek kell lennie. Aztán a Értékel 0-nak kell lennie.

• Készlet A cella megváltoztatásával az IRR értékre.
• Csak be kell írnia a cellahivatkozásokat a képen látható módon.

• Most kattintson rendben az IRR kiszámításához a Goal Seek segítségével.
• A jelenlegi adatkészlet szerint az NPV 0,00 lesz, ha az IRR 12%.

A Goal Seek módszerben valójában nem kell az IRR-t a cash flow-ból és a befektetés összegéből számolnia. Ehelyett be kell állítania a várható IRR-t az IRR-cellában, és ennek segítségével kiszámítani az NPV-t a pénzforgalom és a befektetés összege alapján. Ezután használja az Excel iteratív számítási képességét annak előrejelzésére, hogy az NPV mikor lesz 0,00, amelynél az IRR-érték.

Ne feledje ezeket a tippeket és tényeket, amikor az IRR-t Excelben számítja ki, hogy megbízható IRR-százalékot kapjon:

• Az Értékek rész argumentumának tartalmaznia kell egy bevételt (pozitív cash flow) és a ráfordítást (negatív cash flow), hogy a funkció pontosan működjön.
• Az IRR függvény csak a Values ​​argumentumban szereplő számokat dolgozza fel. Figyelmen kívül hagy minden más elemet, például logikai értékeket, üres cellákat és szövegeket.
• Rendszeres pénzforgalmi intervallumoknak kell lenniük, például heti, havi, negyedéves, éves stb. Nem kötelező, hogy a pénzáramlások dollárértékben egyenlőek legyenek.
• Ügyeljen arra, hogy a pénzforgalmi táblázatot időrendi sorrendben készítse el. Például, ha havonta 100 USD-t fizet, akkor rendezze a pénzforgalmi értékeket és azok dátumait január, február, március stb.
• Az egyszerű IRR-számításoknál nem kötelező Guess argumentumot megadni.
• Ha Guess értéket használ, az IRR képlet a Guess argumentumhoz legközelebb eső értéket jeleníti meg. Ha a #NUM! Hiba, módosítsa a Guess argumentum értékét.

Mielőtt az Excelben kiszámított IRR értéket valós helyzetekben alkalmazná, ismernie kell az Excel belső megtérülési ráta képletének korlátait is:

• Az IRR a ROI százalékos kifejezése. Nem veszi figyelembe az abszolút értéket. Így magasabb százalékos megtérülést mutathat egy alacsony dollárértékű projekt esetében. A kockázati tőkebefektetők és az egyéni befektetők azonban magasabb dollárértékű projekteket keresnek, bár a megtérülési százalék alacsonyabb, mint a kis projekteknél.
• Az IRR-képlet úgy ad vissza értéket, hogy figyelembe veszi, hogy a projekt a tőkéjét vagy a pénzáramlását a számított IRR-rel megegyező mértékben fekteti be újra. Ez nem mindig lehetséges, mert a megtérülési ráták nagyon ingadoznak a tőkepiacon.
• Ha a beruházási terv vagy projekt váltakozó pozitív és negatív cash flow-t tartalmaz, akkor előfordulhat, hogy egynél több IRR-t kap ugyanarra a projektre.

Most már tudja, hogyan kell kiszámítani az IRR-t az Excelben különféle dedikált IRR szintaxisok segítségével. Azt is megtanulta, hogyan használja az Excel Goal Seek alkalmazást az IRR kiszámításához az Excelben. Ha kihagytam egy másik módszert, ne felejtsd el megemlíteni az alábbi megjegyzés rovatban.

Következö, hogyan kell használni az Excel IF-THEN formulát.