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

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áson alapuló számítás meglehetősen egyszerű, a nagyvállalatoknál vagy a befektetési bankoknál 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.

Annak érdekében, hogy ne hagyatkozzon másokra a nehezen megkeresett pénz befektetésekor, az alábbiakban segítek megtanulni, mi az IRR az Excelben, annak számos módosított képlete, és hogyan számíthatja ki az IRR-t Excelben különféle egyszerű módszerekkel.

Mi a belső megtérülési ráta (IRR)?

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 az elkö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 előre jelez egy üzleti projekt vagy pénzügyi befektetés jövedelmezőségét.

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

Mik az IRR-függvények a Microsoft Excelben?

A Microsoftnak köszönhetően nem kell több képletet létrehoznia, vagy bonyolult számításokon kell keresztülmennie egy befektetési terv IRR-jének kiszámításához. 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:

1. Az egyszerű belső megtérülési ráta IRR-je

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

2. XIRR a hozamok egyenlőtlen időzítése esetén

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

3. MIRR a projektalapok újrabefektetésére

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ének használata

Az IRR kiszámítása Excelben: Az IRR szintaxis 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 IRR képlet használata Excelben

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

Ismerje meg, hogyan számíthatja ki az IRR-t Excelben az IRR szintaxis használatával

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

• Nyomja meg az Enter billentyűt , hogy elérje a potenciális befektetés megtérülését.

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.

Nézze meg, hogyan számíthatja ki az IRR-t az Excelben az IRR-féle tippérték segítségével

• Ebben a forgatókönyvben a Guess segítségével találja meg a pontos eredményt, mivel az IRR két eredményt ad.

Az IRR kiszámítása az Excelben negatív Guess-érték használata esetén

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

Az IRR kiszámítása az Excelben: A XIRR szintaxis használata

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 Visszaküldés dátumához a Cashflows oszlop jobb oldalán .
• 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.

Fedezze fel, hogyan számíthatja ki az IRR-t Excelben a XIRR szintaxis használatával

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

=XIRR(C2:C8;D2:D8)

Az IRR kiszámítása az Excelben: A MIRR szintaxis használata

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.

A MIRR függvény használata Excelben

• Ezek a tőkeköltség és az újrabefektetési ráta .

Ismerje meg, hogyan számíthatja ki az IRR-t Excelben a MIRR szintaxis használatával

• 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 kiszámítása az Excelben: A Goal Seek Excel használata

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.

NPV kiszámítása Excelben

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

=NPV(C9,C3:C8)+C2

• Most kattintson az Adatok fülre, majd az Excel szalagmenüjének Előrejelzés parancscsoportjában válassza a Mi van, ha elemzés elemet .
• A felugró helyi menüből válassza a Célkeresés lehetőséget .
• Itt a Set cellának az NPV értéknek kell lennie. Ezután a To értéknek 0-nak kell lennie.

Végezze el a Célkeresést az IRR kiszámításához Excelben

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

Cél Eredmények keresése az IRR-számításhoz Excelben

• Most kattintson az OK gombra az IRR kiszámításához a célkeresésen keresztül.
• 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ámítani. 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.

Tippek az IRR pontos kiszámításához Excelben

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 időközöknek 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 megegyezzenek.
• Ü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átumát január, február, március stb.
• 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.

Mik az IRR korlátai az Excelben?

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őkések é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-kat tartalmaz, akkor előfordulhat, hogy egynél több IRR-t kap ugyanarra a projektre.

Következtetés

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 bármilyen más módszert kihagytam, ne felejtsd el megemlíteni az alábbi megjegyzés rovatban.

Következő lépésként  az Excel IF-THEN képletének használata .