Jak obliczyć IRR w Excelu: 4 najlepsze metody

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, pomogę Ci dowiedzieć się, czym jest IRR w programie Excel, jego kilka zmodyfikowanych formuł oraz sposób obliczania IRR w programie Excel na różne łatwe sposoby poniżej.

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

Przeczytaj także:Najlepsze szablony budżetu programu 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:

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

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

MIRR daje wysoce wiarygodne wartości IRR w porównaniu z prostą funkcją IRR. IRR nie uwzględnia kosztu kapitału, który inwestujesz. 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.

Przeczytaj także:Jak korzystać z formuły odejmowania programu Excel

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 środków pieniężnych.
• Wypełnij dane, które chcesz obliczyć pod odpowiednimi nagłówkami kolumn.

• Pierwsza inwestycja powinna mieć wartość ujemną, ponieważ płacisz funduszowi inwestycyjnemu lub przedsiębiorcy biznesowemu 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.

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

=IRR(C2:C8)

• Uderzyć Wchodzić 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.

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

• The Zgadywać argumentem powinien być oczekiwany zwrot z inwestycji.

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 Data zwrotu na prawą stronę Przepływy środków pieniężnych kolumna.
• Teraz wprowadź datę inwestycji na górze, a następnie wprowadź przewidywaną datę odsetek lub zwrotu kredytów.

• Po zakończeniu użyj następujących Składnia XIRR w dowolnej komórce, aby uzyskać wewnętrzną stopę zwrotu dla inwestycji o nieregularnym harmonogramie zwrotu:

=XIRRR(C2:C8;D2:D8)

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.

• To są Koszt kapitału I Stopa reinwestycji.

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

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.

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

=NPV(C9;C3:C8)+C2

• Teraz kliknij Dane zakładkę, a następnie wybierz Co jeśli analiza w Prognoza grupa poleceń w menu wstążki programu Excel.
• Wybierać Poszukiwanie celu z menu kontekstowego, które się pojawi.
• Tutaj Ustaw komórkę powinna być wartością NPV. A później Cenić powinno być 0.

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

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

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.

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 bardzo się zmieniają.
• 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.

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ępny w górę, jak korzystać z formuły Excela JEŻELI-TO.