Jak obliczyć IRR w Excelu (łatwa formuła)

Podczas pracy z budżetowaniem kapitałowym, IRR (wewnętrzna stopa zwrotu) służy do zrozumienia ogólnej stopy zwrotu, jaką projekt wygeneruje na podstawie przyszłych serii przepływów pieniężnych.

W tym samouczku pokażę Ci, jak to zrobić oblicz IRR w Excelu, czym różni się od innej popularnej miary NPV i różnych scenariuszy, w których można używać wbudowanych formuł IRR w programie Excel.

Wyjaśnienie wewnętrznej stopy zwrotu (IRR)

IRR to stopa dyskontowa stosowana do mierzyć zwrot z inwestycji na podstawie okresowych dochodów.

IRR jest pokazywana w procentach i może być wykorzystana do podjęcia decyzji, czy projekt (inwestycja) jest opłacalny dla firmy, czy nie.

Pozwólcie, że wyjaśnię IRR na prostym przykładzie.

Załóżmy, że planujesz kupić firmę za 50 000 USD, która będzie generować 10 000 USD rocznie przez następne 10 lat. Możesz użyć tych danych, aby obliczyć IRR tego projektu, czyli stopę zwrotu z inwestycji w wysokości 50 000 USD.

W powyższym przykładzie IRR wynosi 15% (zobaczymy, jak to obliczyć w dalszej części samouczka). Oznacza to, że jest to równoznaczne z zainwestowaniem pieniędzy ze stopą 15% lub zwrotem przez 10 lat.

Gdy masz już wartość IRR, możesz jej użyć do podejmowania decyzji. Więc jeśli masz inny projekt, w którym IRR wynosi więcej niż 15%, powinieneś zamiast tego zainwestować w ten projekt.

Lub, jeśli planujesz wziąć pożyczkę lub pozyskać kapitał i kupić ten projekt za 50 000 $, upewnij się, że koszt kapitału jest mniejszy niż 15% (w przeciwnym razie płacisz więcej jako koszt kapitału niż zarabiasz z projekt).

Funkcja IRR w programie Excel - składnia

Excel pozwala obliczyć wewnętrzną stopę zwrotu za pomocą funkcji IRR. Ta funkcja ma następujące parametry:

=IRR(wartości; [zgadnij])

• wartości - tablica komórek zawierających liczby, dla których chcesz obliczyć wewnętrzną stopę zwrotu.
• zgadywać - liczba, którą zgadujesz, jest zbliżona do wyniku IRR (nie jest to obowiązkowe i domyślnie wynosi 0,1 - 10%). Jest to używane, gdy istnieje możliwość uzyskania kilku wyników i w takim przypadku funkcja zwraca wynik najbliższy wartości argumentu zgadywania.

Oto kilka ważnych warunków wstępnych korzystania z funkcji:

• Funkcja IRR uwzględni tylko liczby z określonego zakresu komórek. Wszelkie wartości logiczne lub ciągi tekstowe w tablicy lub argumencie odniesienia zostaną zignorowane
• Kwoty w parametrze wartości muszą być sformatowane jako liczby
• Parametr „zgadnij” musi być wartością procentową w formacie dziesiętnym (jeśli jest podany)
• Komórka, w której wyświetlany jest wynik funkcji, musi być sformatowana jako odsetek
• Kwoty występują w regularne odstępy czasu (miesiące, kwartały, lata)
• Jedna kwota musi być negatywny przepływy pieniężne (reprezentujące inwestycję początkową), a inne kwoty powinny być pozytywny przepływy pieniężne, reprezentujące okresowe dochody
• Wszystkie kwoty powinny być podane w porządek chronologiczny ponieważ funkcja oblicza wynik na podstawie kolejności kwot

W przypadku, gdy chcesz obliczyć wartość IRR, przy której przepływy pieniężne pojawiają się w różnych odstępach czasu, powinieneś użyć Funkcja XIRR w Excelu, który umożliwia również określenie dat dla każdego przepływu środków pieniężnych. Przykład tego omówiono w dalszej części samouczka

Przyjrzyjmy się teraz przykładowi, aby lepiej zrozumieć, jak korzystać z funkcji IRR w programie Excel.

Obliczanie wewnętrznej stopy zwrotu dla różnych przepływów pieniężnych

Załóżmy, że masz zestaw danych, jak pokazano poniżej, w którym mamy początkową inwestycję w wysokości 30 000 USD, a następnie zmienne przepływy pieniężne/dochody z niej przez następne sześć lat.

Dla tych danych musimy obliczyć IRR, co można zrobić za pomocą poniższego wzoru:

=IRR(D2:D8)

Wynikiem funkcji jest 8.22%, czyli IRR przepływów pieniężnych po sześciu latach.

Notatka: Jeśli funkcja zwraca a #NUM! błąd, należy w formule wypełnić parametr „zgadnij”. Dzieje się tak, gdy formuła uważa, że ​​wiele wartości może być poprawnych i musi mieć wartość przewidywania, aby zwrócić IRR najbliższą podanemu przez nas przypuszczeniu. W większości przypadków nie będziesz jednak musiał tego używać

Dowiedz się, kiedy inwestycja przynosi dodatnią wewnętrzną stopę zwrotu

Możesz również obliczyć IRR za każdy okres w przepływie pieniężnym i zobacz, kiedy dokładnie inwestycja zaczyna przynosić dodatnią wewnętrzną stopę zwrotu.

Załóżmy, że mamy poniższy zestaw danych, w którym mam wszystkie przepływy pieniężne wymienione w kolumnie C.

Chodzi o to, aby dowiedzieć się, w którym roku IRR tej inwestycji staje się dodatni (wskazując, kiedy projekt wychodzi na zero i staje się opłacalny).

Aby to zrobić, zamiast obliczać IRR dla całego projektu, poznamy IRR dla każdego roku.

Można to zrobić, używając poniższej formuły w komórce D3, a następnie kopiując ją dla wszystkich komórek w kolumnie.

=IRR($C$2:C3)

Jak widać, IRR po roku 1 (wartości D2:D3) wynosi -80%, po roku 2 (D2:D4) -52% itd.

Ten przegląd pokazuje nam, że inwestycja w wysokości 30 000 USD przy danym przepływie pieniężnym ma dodatnią wewnętrzną stopę zwrotu po piątym roku.

Może to być przydatne, gdy musisz wybrać jeden z dwóch projektów o podobnym IRR. Bardziej opłacalne byłoby wybranie projektu, w którym IRR szybciej osiąga dodatnią wartość, ponieważ oznacza to mniejsze ryzyko odzyskania kapitału początkowego.

Zauważ, że w powyższym wzorze odwołanie do zakresu jest mieszane, to znaczy pierwsze odwołanie do komórki ($ C $ 2) jest zablokowane przez znaki dolarów przed numerem wiersza i literą kolumny, a drugie odwołanie (C3) nie jest zablokowany.

Dzięki temu podczas kopiowania formuły zawsze uwzględnia ona całą kolumnę do wiersza, w którym formuła jest stosowana.

Używanie funkcji IRR do porównywania wielu projektów

Funkcja IRR w Excelu może być również wykorzystana do porównania inwestycji i zwrotów z kilku projektów oraz sprawdzenia, który projekt jest najbardziej opłacalny.

Załóżmy, że masz zestaw danych, jak pokazano poniżej, w którym masz trzy projekty z początkową inwestycją (która jest pokazana jako ujemna, ponieważ jest to odpływ), a następnie z serią dodatnich przepływów pieniężnych.

Aby uzyskać najlepszy projekt (który ma najwyższą IRR, będziemy musieli obliczyć IRR dla każdego projektu za pomocą prostej formuły IRR:

=IRR(C2:C8)

Powyższy wzór da IRR dla projektu 1. Podobnie, możesz również obliczyć IRR dla pozostałych dwóch projektów.

Jak widzisz:

• Projekt 1 ma wewnętrzną stopę zwrotu w wysokości 5.60%
• Projekt 2 ma wewnętrzną stopę zwrotu w wysokości 1.75%
• Projekt 3 ma IRR w wysokości 14.71%.

Jeśli przyjmiemy, że koszt kapitału wynosi 4,50%, możemy stwierdzić, że inwestycja 2 jest nie do przyjęcia (bo doprowadzi do straty), natomiast inwestycja 3 jest najbardziej opłacalna, z najwyższą wewnętrzną stopą zwrotu.

Jeśli więc musisz podjąć decyzję o zainwestowaniu tylko w jeden projekt, powinieneś wybrać Projekt 3, a jeśli możesz zainwestować w więcej niż jeden projekt, możesz zainwestować w Projekt 1 i 3.

Definicja: Jeśli zastanawiasz się, jaki jest koszt kapitału, to pieniądze, które będziesz musiał zapłacić, aby uzyskać dostęp do pieniędzy. Na przykład, jeśli bierzesz 100 000 USD pożyczki na 4,5% rocznie, koszt kapitału wynosi 4,5%. Podobnie, jeśli wyemitujesz akcje uprzywilejowane obiecujące 5% zwrotu, aby otrzymać 100K, koszt kapitału wyniesie 5%. W rzeczywistych scenariuszach firma zwykle pozyskuje pieniądze z różnych źródeł, a jej koszt kapitału jest średnią ważoną wszystkich tych źródeł kapitału.

Obliczanie IRR dla nieregularnych przepływów pieniężnych

Jednym z ograniczeń funkcji IRR w programie Excel jest to, że przepływy pieniężne muszą być okresowe z takimi samymi odstępami między nimi.

Ale w prawdziwym życiu mogą się zdarzyć sytuacje, w których Twoje projekty zwracają się w nieregularnych odstępach czasu.

Na przykład poniżej znajduje się zestaw danych, w którym przepływy pieniężne odbywają się w nieregularnych odstępach czasu (patrz daty w kolumnie A).

W tym przykładzie nie możemy użyć zwykłej funkcji IRR, ale istnieje inna funkcja, która może to zrobić - the Funkcja XIRR.

Funkcja XIRR pobiera przepływy pieniężne oraz daty, co pozwala jej uwzględnić nieregularne przepływy pieniężne i podać poprawną IRR.

W tym przykładzie IRR można obliczyć za pomocą poniższego wzoru:

=XIRR(B2:B8;A2:A8)

W powyższym wzorze przepływy pieniężne są podawane jako pierwszy argument, a daty jako drugi argument.

Jeśli ta formuła zwraca #NUM! błąd, powinieneś wprowadzić trzeci argument z przybliżoną IRR, której oczekujesz. Nie martw się, nie musi to być dokładne ani nawet bardzo bliskie, tylko przybliżenie IRR, które Twoim zdaniem dałoby. Dzięki temu formuła lepiej iteruje i daje wynik.

IRR vs NPV – co jest lepsze?

Jeśli chodzi o ocenę projektów, stosuje się zarówno NPV, jak i IRR, ale NPV jest bardziej niezawodną metodą.

NPV to metoda wartości bieżącej netto, w której oceniasz wszystkie przyszłe przepływy pieniężne i obliczasz, jaka będzie wartość bieżąca netto wszystkich tych przepływów pieniężnych.

Jeśli ta wartość okaże się wyższa niż początkowy odpływ, projekt jest opłacalny, w przeciwnym razie projekt nie jest opłacalny.

Z drugiej strony, kiedy obliczasz IRR dla projektu, mówi ci, jaka byłaby stopa zwrotu wszystkich przyszłych przepływów pieniężnych, aby uzyskać kwotę odpowiadającą obecnemu odpływowi. Na przykład, jeśli dzisiaj wydajesz 100 000 USD na projekt, który ma IRR lub 10%, to mówi Ci, że jeśli zdyskontujesz wszystkie przyszłe przepływy pieniężne przy stopie dyskontowej 10%, otrzymasz 100 000 USD.

Podczas gdy obie metody są wykorzystywane podczas oceny projektów, bardziej wiarygodna jest metoda NPV. Istnieje możliwość uzyskania sprzecznych wyników podczas oceny projektu przy użyciu metody NPV i IRR.

W takim przypadku najlepiej kierować się rekomendacją uzyskaną metodą NPV.

Ogólnie metoda IRR ma pewne wady, które sprawiają, że metoda NPV jest bardziej niezawodna:

• Metoda wyższa lub zakłada, że ​​wszystkie przyszłe przepływy pieniężne wygenerowane z projektu zostaną ponownie zainwestowane przy tej samej stopie zwrotu (tj. IRR projektu). w większości przypadków jest to nieuzasadnione założenie, ponieważ większość przepływów pieniężnych byłaby reinwestowana w inne projekty, które mogą mieć inne IR lub niepewność, takie jak obligacje, które miałyby znacznie niższą stopę zwrotu.
• W przypadku wielu wypływów i wpływów w projekcie, dla tego projektu będzie wiele IRR. To znowu bardzo utrudnia porównanie.

Pomimo swoich wad, IRR jest dobrym sposobem oceny projektu i może być używana w połączeniu z metodą NPV, gdy decydujesz, który projekt wybrać.

W tym samouczku pokazałem, jak korzystać z Funkcja IRR w Excelu. Omówiłem również, jak obliczyć IRR w przypadku nieregularnych przepływów pieniężnych za pomocą funkcji XIRR.

Mam nadzieję, że ten samouczek okazał się przydatny!