Jak obliczyć IRR w Excelu: 4 najlepsze metody w 2023 r

Jeśli chcesz podejmować świadome decyzje w kierunku systematycznych planów inwestycyjnych (SIP) lub inwestować w projekty biznesowe jako inwestor venture capital, musisz nauczyć się obliczać IRR w Excelu.

Istnieje wiele sposobów obliczania zwrotu z inwestycji (ROI) w przypadku inwestycji w projekty finansowe lub biznesowe. Chociaż obliczenie oparte na stałej stopie procentowej jest dość łatwe, nie zawsze jest to wybór narzędzia do obliczania ROI w dużych firmach lub bankowości inwestycyjnej.

Załóżmy, że zainwestujesz pewną sumę pieniędzy w plan inwestycyjny na następne 20 lat. Plan mówi, że będziesz otrzymywać zwrot co kwartał. Teraz, czy mądrze jest inwestować w taki plan, czy nie, można zdecydować na podstawie wartości IRR SIP. Im wyższa IRR tym bardziej lukratywny plan inwestycyjny. To samo dotyczy wszystkich innych kapitałochłonnych inwestycji w biznes, bankowych rachunków oszczędnościowych, funduszy wspólnego inwestowania, 401(k) itp.

Abyś nie polegał na innych, jeśli chodzi o inwestowanie ciężko zarobionych pieniędzy, poniżej pomogę Ci dowiedzieć się, czym jest IRR w Excelu, jego kilka zmodyfikowanych formuł i jak obliczyć IRR w Excelu na różne łatwe sposoby.

Co to jest wewnętrzna stopa zwrotu (IRR)?

Wewnętrzna stopa zwrotu, czyli wewnętrzna stopa zwrotu, jest powszechnie stosowaną miarą stosowaną przez planistów finansowych do obliczania lub przewidywania przyszłej rentowności potencjalnego projektu biznesowego lub innego rodzaju inwestycji. W kategoriach finansowych jest to powszechnie znane jako ekonomiczna stopa zwrotu lub zdyskontowana stopa zwrotu z przepływów pieniężnych.

Stopa IRR lub stopa dyskontowa inwestycji powoduje, że cała wartość bieżąca netto wszelkiego rodzaju przepływów pieniężnych, takich jak wypływy i wpływy, jest równa zeru. Specjaliści od finansów nazywają tę wewnętrzną stopę zwrotu, ponieważ kalkulacja ta nie uwzględnia innych czynników, które mogą pozytywnie lub negatywnie wpłynąć na działalność w nadchodzących latach.

Na przykład IRR nie uwzględnia kosztu kapitału, stopy inflacji w czasie, ryzyka finansowego itp. podczas przewidywania rentowności projektu biznesowego lub inwestycji finansowej.

Krótko mówiąc, za każdym razem, gdy zdecydujesz się zainwestować przez dłuższy czas w jakikolwiek projekt komercyjny, fundusz inwestycyjny lub plan emerytalny, dowiedz się, jaka jest IRR tych opcji inwestycyjnych. Następnie wybierz tę, która daje maksymalną IRR spośród wszystkich opcji.

Jakie są funkcje IRR w programie Microsoft Excel?

Dzięki Microsoft nie musisz tworzyć wielu formuł ani przechodzić przez skomplikowane obliczenia, aby uzyskać IRR planu inwestycyjnego. Excel posiada trzy dedykowane wewnętrzne funkcje obliczania stopy zwrotu. Dowiedz się więcej o tych funkcjach tutaj:

1. IRR dla prostej wewnętrznej stopy zwrotu

Składnia IRR w programie Excel oblicza stopę procentową inwestycji w projekt biznesowy, fundusz wspólnego inwestowania lub fundusz planowania emerytalnego. Inwestycja musi zawierać co najmniej jedną wartość dodatnią i ujemną. Ponadto przepływ środków pieniężnych do funduszu powinien następować w regularnych odstępach czasu. Oto jak wygląda formuła:

=IRR(wartości lub zakresy komórek, [odgadnij argument])

• Wartości to wymagane dane wejściowe dla funkcji IRR. Może to być seria liczb, tablica liczb lub zakres komórek odniesienia składający się z przepływów pieniężnych.
• Argument zgadnij jest opcjonalny. Jeśli nie podasz wartości Guess, program Excel uzna ją za wewnętrzną stopę zwrotu 0,1, 10% lub 10 punktów bazowych wymaganą przez plan inwestycyjny. Jeśli podasz argument Zgadnij, program Excel przeprowadzi obliczenia dla maksymalnie 20 sesji iteracyjnych, aby znaleźć wartość IRR zbliżoną do wartości Zgadnij.

2. XIRR dla nierównych terminów zwrotów

Jeśli musisz uwzględnić harmonogram przepływów pieniężnych, musisz użyć funkcji XIRR. Składnia jest następująca:

=XIRR(wartości lub zakresy komórek, daty, [odgadnij argument])

3. MIRR dla Reinwestycji Funduszy Projektu

MIRR daje wysoce wiarygodne wartości IRR w porównaniu z prostą funkcją IRR. IRR nie uwzględnia kosztu zainwestowanego kapitału. Dlatego ostateczna wartość IRR nie jest dokładna. Zamiast tego można użyć funkcji MIRR, aby uwzględnić koszt czynnika kapitału w obliczeniach IRR. W związku z tym składnia wygląda następująco:

=MIRR(wartości lub zakresy komórek, stopa_finansowa, stopa_reinwestycji)

• Wartości to rachunek przepływów pieniężnych składający się z co najmniej jednego dodatniego i ujemnego przepływu pieniężnego.
• Finance_rate to odsetki, które płacisz pożyczkodawcy od pieniędzy pożyczonych na projekt biznesowy.
• Reinvest_rate to stopa procentowa, jaką Twoja firma lub fundusz inwestycyjny otrzymuje od przepływów pieniężnych po reinwestycji.

Jak obliczyć IRR w programie Excel: Korzystanie ze składni IRR

Zanim zaczniesz obliczać wewnętrzną stopę zwrotu dla kilku planów inwestycyjnych lub projektów, musisz uporządkować dane w programie Excel. Oto jak to się robi:

• Otwórz arkusz programu Excel i utwórz trzy nagłówki kolumn: Okres , Opis i Przepływy pieniężne .
• Wypełnij dane, które chcesz obliczyć pod odpowiednimi nagłówkami kolumn.

Korzystanie z formuły IRR w Excelu

• Pierwsza inwestycja powinna mieć wartość ujemną, ponieważ płacisz funduszowi inwestycyjnemu lub przedsiębiorcy za wykorzystanie pieniędzy.
• Jeśli będziesz otrzymywać regularne dochody po miesiącu lub roku, to dochody te należy dodać jako wartości dodatnie.

Dowiedz się, jak obliczyć IRR w programie Excel przy użyciu składni IRR

• Teraz użyj następującej formuły w komórce, w której program Excel ma obliczyć wartość IRR:

=IRR(C2:C8)

• Naciśnij Enter, aby uzyskać zwrot z inwestycji w potencjalną inwestycję.

To obliczenie IRR jest oparte na domyślnym argumencie Guess wynoszącym 10%. Powiedzmy, że szukasz 12% zwrotu. Oto jak należy wprowadzić wartość domyślną:

=IRR(C2:C8,30%)

• W powyższej tabeli widać, że ostatni przepływ pieniężny jest ujemny. Zatem przepływ pieniężny musi zmienić się z dodatniego na ujemny lub z ujemnego na dodatni.

Obejrzyj Jak obliczyć wewnętrzną stopę zwrotu w programie Excel na podstawie odgadniętej wartości IRR

• W tym scenariuszu użyj metody Guess , aby znaleźć dokładny wynik, ponieważ IRR da dwa wyniki.

Jak obliczyć IRR w programie Excel przy użyciu ujemnej wartości Guess

• Argumentem Guess powinien być oczekiwany zwrot z inwestycji.

Jak obliczyć IRR w programie Excel: przy użyciu składni XIRR

Kiedy otrzymujesz zwroty w różnych terminach po początkowej inwestycji, użycie składni IRR da błędne wyniki. W tym scenariuszu należy użyć składni XIRR. Oto kroki, które możesz wypróbować na własnym arkuszu programu Excel:

• Utwórz nagłówek kolumny dla Daty zwrotów po prawej stronie kolumny Przepływy pieniężne .
• Teraz wprowadź datę inwestycji na górze, a następnie wprowadź przewidywaną datę odsetek lub zwrotu kredytów.

Dowiedz się, jak obliczyć IRR w programie Excel przy użyciu składni XIRR

• Po zakończeniu użyj następującej składni XIRR w dowolnej komórce, aby uzyskać wewnętrzną stopę zwrotu z inwestycji z harmonogramem nieregularnych zwrotów:

=XIRRR(C2:C8;D2:D8)

Jak obliczyć IRR w programie Excel: przy użyciu składni MIRR

Aby wartość IRR była bardziej realistyczna, możesz użyć formuły MIRR. Tutaj możesz uwzględnić dwa obowiązkowe czynniki: koszt kapitału i stopę reinwestycji. Znajdź poniżej kroki, aby użyć MIRR w Excelu:

• W bieżącym arkuszu dodaj dwa wiersze informacji, jak pokazano na obrazku.

Korzystanie z funkcji MIRR w programie Excel

• Są to Koszt kapitału i Stopa reinwestycji .

Dowiedz się, jak obliczyć IRR w programie Excel przy użyciu składni MIRR

• W odpowiednich komórkach również wprowadź wartości.
• Teraz użyj następującego wzoru, aby uzyskać realistyczną wewnętrzną stopę zwrotu:

=MIRR(C2:C8;G2;G3)

Jak obliczyć IRR w programie Excel: Korzystanie z funkcji wyszukiwania celu w programie Excel

IRR i jego zmodyfikowane formuły wykonują 20 obliczeń iteracyjnych przed wyświetleniem wyniku. Jeśli potrzebujesz większej dokładności, możesz użyć funkcji Excel Goal Seek do obliczenia IRR. W tej metodzie program Excel wykonuje do 32 000 obliczeń iteracyjnych przed wygenerowaniem wartości zwracanej. Oto instrukcje korzystania z Goal Seek i obliczania IRR w Excelu:

• Ustaw IRR na 11% lub cokolwiek innego ręcznie.

Obliczanie NPV w Excelu

• Oblicz NPV poniżej IRR za pomocą tego wzoru:

=NPV(C9;C3:C8)+C2

• Teraz kliknij kartę Dane , a następnie wybierz Analiza co-jeśli w grupie poleceń Prognoza w menu wstążki programu Excel.
• Wybierz Szukanie celu z menu kontekstowego, które się pojawi.
• Tutaj komórka Set powinna być wartością NPV. Wtedy wartość Do powinna wynosić 0.

Wykonaj Goal Seek, aby obliczyć IRR w Excelu

• Ustaw Zmieniając komórkę na wartość IRR.
• Wystarczy wprowadzić odwołania do komórek, jak pokazano na obrazku.

Cel Szukaj wyników do obliczania IRR w programie Excel

• Teraz kliknij OK , aby obliczyć IRR za pomocą funkcji Goal Seek.
• Zgodnie z bieżącym zbiorem danych, wartość NPV wyniesie 0,00, jeśli IRR wynosi 12%.

W metodzie Goal Seek tak naprawdę nie musisz obliczać IRR na podstawie przepływów pieniężnych i kwoty inwestycji. Zamiast tego ustawiasz oczekiwaną IRR w komórce IRR i używasz jej do obliczania NPV przy użyciu przepływów pieniężnych i kwoty inwestycji. Następnie korzystasz z funkcji obliczeń iteracyjnych programu Excel, aby przewidzieć, kiedy NPV osiągnie wartość 0,00, przy której wartości IRR.

Wskazówki dotyczące prawidłowego obliczania IRR w programie Excel

Pamiętaj o tych wskazówkach i faktach podczas obliczania IRR w programie Excel, aby uzyskać wiarygodne wartości procentowe IRR:

• Argument dla części Wartości musi zawierać dochód (dodatni przepływ pieniężny) i wydatek (ujemny przepływ pieniężny), aby funkcja działała dokładnie.
• Funkcja IRR przetwarza tylko liczby w argumencie Wartości. Zignoruje wszelkie inne elementy, takie jak wartości logiczne, puste komórki i teksty.
• Muszą istnieć regularne odstępy między przepływami pieniężnymi, takie jak tygodniowe, miesięczne, kwartalne, roczne itp. Nie jest obowiązkowe, aby przepływy pieniężne były równe wartości dolara.
• Upewnij się, że przygotowałeś tabelę przepływów pieniężnych w porządku chronologicznym. Na przykład, jeśli płacisz 100 USD miesięcznie, ułóż wartości przepływów pieniężnych i ich daty ze stycznia, lutego, marca itd.
• W prostych obliczeniach IRR wprowadzanie argumentu Guess nie jest obowiązkowe.
• Podczas korzystania z wartości Guess formuła IRR pokaże wartość najbliższą argumentowi Guess. Jeśli pokazuje #NUM! Błąd, zmień wartość argumentu Guess.

Jakie są ograniczenia IRR w Excelu?

Zanim będziesz mógł zastosować wartość IRR obliczoną w programie Excel w rzeczywistych sytuacjach, powinieneś również poznać nieodłączne ograniczenia formuły wewnętrznej stopy zwrotu w programie Excel:

• IRR jest wyrażeniem ROI w procentach. Nie bierze pod uwagę wartości bezwzględnej. W związku z tym może wykazywać wyższy procent zwrotu dla projektu o niskiej wartości dolara. Jednak inwestorzy venture capital i inwestorzy indywidualni szukają projektów o wyższej wartości dolara, chociaż procent zwrotu z inwestycji jest mniejszy niż w przypadku małych projektów.
• Formuła IRR zwraca wartość, biorąc pod uwagę, że projekt ponownie zainwestuje swój kapitał lub przepływy pieniężne po tej samej stopie, co obliczona IRR. Nie zawsze jest to możliwe, ponieważ stopy zwrotu na rynku kapitałowym podlegają dużym wahaniom.
• Jeśli plan inwestycyjny lub projekt obejmuje naprzemienne dodatnie i ujemne przepływy pieniężne, możesz uzyskać więcej niż jedną IRR dla tego samego projektu.

Wniosek

Teraz wiesz, jak obliczyć IRR w Excelu przy użyciu różnych dedykowanych składni IRR. Nauczyłeś się również, jak używać programu Excel Goal Seek do obliczania IRR w programie Excel. Jeśli przegapiłem jakąkolwiek inną metodę, nie zapomnij wspomnieć o tym w polu komentarza poniżej.

Następnie,  jak korzystać z formuły programu Excel JEŻELI-TO .