Как да изчислите IRR в Excel: 4 най-добри метода през 2023 г

Ако искате да вземате информирани решения към систематични инвестиционни планове (SIP) или да инвестирате в бизнес проекти като рисков капиталист, трябва да научите как да изчислявате IRR в Excel.

Има много начини за изчисляване на възвръщаемостта на инвестициите (ROI) за инвестиции, направени във финансови или бизнес проекти. Въпреки че изчисляването на базата на фиксиран лихвен процент е доста лесно, това не винаги е изборът на инструмент за изчисляване на възвръщаемостта на инвестициите в големи предприятия или инвестиционно банкиране.

Да приемем, че ще инвестирате сума пари в инвестиционен план за следващите 20 години. Планът казва, че ще получавате възвръщаемост на всяко тримесечие. Сега, дали е разумно да се инвестира в такъв план или не, може да се реши от стойността на IRR на SIP. Колкото по-висок е IRR, толкова по-доходоносен е инвестиционният план. Същото важи и за всички други капиталоемки инвестиции в предприятия, банкови спестовни сметки, взаимни фондове, 401 (k) и т.н.

За да не разчитате на други, когато става въпрос за инвестиране на трудно спечелени пари, аз ви помагам да научите какво е IRR в Excel, неговите няколко модифицирани формули и как да изчислявате IRR в Excel по различни лесни начини по-долу.

Какво представлява вътрешната норма на възвръщаемост (IRR)?

IRR или вътрешна норма на възвръщаемост е общ показател, използван от финансовите плановици за изчисляване или прогнозиране на бъдещата рентабилност на потенциален бизнес проект или други видове инвестиции. Във финансово отношение това е популярно известно като икономическа норма на възвръщаемост или норма на възвръщаемост на дисконтирания паричен поток.

IRR процентът или дисконтовият процент на инвестицията прави цялата нетна настояща стойност на всички видове парични потоци като изходящи и входящи потоци равни на нула. Финансовите специалисти наричат ​​това вътрешна норма на възвръщаемост, тъй като това изчисление не отчита други фактори, които биха могли да повлияят положително или отрицателно на бизнеса през следващите години.

Например IRR не взема предвид цената на капитала, темпа на инфлация във времето, финансовите рискове и т.н., когато прогнозира рентабилността на бизнес проект или финансова инвестиция.

Накратко, когато решите да инвестирате за продължителен период от време в който и да е търговски проект, взаимен фонд или пенсионен инвестиционен план, разберете IRR на тези инвестиционни опции. След това изберете тази, която дава максимална IRR сред всички опции.

Прочетете също: Най-добрите бюджетни шаблони на Excel

Какви са IRR функциите в Microsoft Excel?

Благодарение на Microsoft не е необходимо да създавате множество формули или да преминавате през сложни изчисления, за да получите IRR на инвестиционен план. Excel има три специални вътрешни функции за изчисляване на нормата на възвръщаемост. Научете за тези функции тук:

1. IRR за проста вътрешна норма на възвръщаемост

Синтаксисът на IRR в Excel изчислява лихвения процент на инвестиция в бизнес проект или взаимен фонд, или фонд за пенсионно планиране. Инвестицията трябва да включва поне една положителна и отрицателна стойност. Също така, паричният поток във фонда трябва да се извършва на редовни интервали. Ето как изглежда формулата:

=IRR(стойности или диапазони от клетки, [аргумент за предположение])

• Стойностите са задължителен вход за функцията IRR. Това може да бъде поредица от числа, масив от числа или диапазон от референтни клетки, състоящи се от парични потоци.
• Аргументът Guess не е задължителен. Ако не предоставите предполагаема стойност, тогава Excel я счита за 0,1, 10% или 10 базисни точки вътрешна норма на възвръщаемост, изисквана от инвестиционния план. Ако предоставите аргумент Guess, Excel ще изпълни изчисленията за до 20 итеративни сесии, за да намери стойност на IRR, близка до стойността Guess.

2. XIRR за неравен момент на връщане

Ако трябва да вземете предвид графика на паричните потоци, тогава трябва да използвате функцията XIRR. Синтаксисът е както следва:

=XIRR(стойности или диапазони от клетки, дати, [предположение аргумент])

3. МИРР за реинвестиране на средства по проекта

MIRR дава много надеждни IRR стойности в сравнение с простата IRR функция. IRR не отчита цената на капитала, който инвестирате. Следователно крайната стойност на IRR не е точна. Вместо това можете да използвате функцията MIRR, за да включите цената на капиталов фактор в изчисляването на IRR. Следователно синтаксисът става както е показано по-долу:

=MIRR(стойности или диапазони от клетки, финансов_процент, реинвестиран_процент)

• Стойностите са отчетът за паричните потоци, състоящ се от поне един от положителни и отрицателни парични потоци.
• Finance_rate е лихвата, която плащате на заемодателя срещу парите, взети назаем за бизнес проект.
• Reinvest_rate е лихвеният процент, който вашият бизнес или инвестиционен фонд получава върху паричните потоци при реинвестиране.

Прочетете също: Как да използвате формулата за изваждане на Excel

Как да изчислим IRR в Excel: Използвайки синтаксиса на IRR

Преди да започнете да изчислявате IRR за няколко инвестиционни плана или проекта, трябва да организирате данните в Excel. Ето как се прави:

• Отворете работен лист на Excel и създайте три заглавки на колони: Период , Описание и Парични потоци .
• Попълнете данните, които искате да бъдат изчислени, под съответните заглавки на колони.

Използване на формулата за IRR в Excel

• Първата инвестиция трябва да е с отрицателна стойност, тъй като плащате на взаимен фонд или бизнес предприемач, за да използва парите.
• Ако ще получавате редовен доход след един месец или една година, тогава тези доходи трябва да се добавят като положителни стойности.

Научете как да изчислявате IRR в Excel, като използвате синтаксиса на IRR

• Сега използвайте следната формула в клетка, където искате Excel да изчисли IRR стойността:

=IRR(C2:C8)

• Натиснете Enter, за да получите ROI за потенциална инвестиция.

Това изчисление на IRR се основава на аргумент по подразбиране Guess от 10%. Да приемем, че търсите 12% възвръщаемост. Ето как трябва да въведете предполагаемата стойност:

=IRR(C2:C8;30%)

• В горната таблица виждате, че последният паричен поток е отрицателен. По този начин паричният поток трябва да се промени от положителен на отрицателен или отрицателен на положителен.

Гледайте как да изчислите IRR в Excel с помощта на предполагаемата стойност на IRR

• В този сценарий използвайте предположение , за да намерите точния резултат, тъй като IRR ще доведе до два резултата.

Как да изчислим IRR в Excel, когато използваме отрицателна стойност на Guess

• Аргументът Guess трябва да бъде възвръщаемостта на инвестицията, която очаквате.

Как да изчислим IRR в Excel: Използвайки синтаксиса XIRR

Когато получавате възвръщаемост на различни дати след първоначалната инвестиция, използването на IRR синтаксис ще даде грешни резултати. В този сценарий трябва да използвате синтаксиса XIRR. Ето стъпките, които можете да опитате на собствения си работен лист в Excel:

• Създайте заглавка на колона за Дата на връщане от дясната страна на колоната Парични потоци .
• Сега въведете датата на инвестицията в горната част и след това въведете очакваната дата на лихва или връщане на кредити.

Открийте как да изчислите IRR в Excel с помощта на синтаксиса XIRR

• След като сте готови, използвайте следния синтаксис XIRR във всяка клетка, за да получите вътрешната норма на възвръщаемост за инвестиция с нередовен график за възвръщаемост на плащанията:

=XIRR(C2:C8;D2:D8)

Как да изчислим IRR в Excel: Използвайки синтаксиса MIRR

За да направите стойността на IRR по-реалистична, можете да използвате формулата MIRR. Тук можете да включите два задължителни фактора: цена на капитала и норма на реинвестиране. Намерете по-долу стъпките за използване на MIRR в Excel:

• В текущия работен лист добавете два реда информация, както е показано на изображението.

Използване на функцията MIRR в Excel

• Това са цената на капитала и процентът на реинвестиране .

Научете как да изчислявате IRR в Excel с помощта на MIRR синтаксиса

• Въведете и стойностите в съответните им клетки.
• Сега използвайте следната формула, за да получите реалистична вътрешна норма на възвръщаемост:

=MIRR(C2:C8;G2;G3)

Как да изчислим IRR в Excel: Използване на Goal Seek Excel

IRR и неговите модифицирани формули извършват 20 итеративни изчисления, преди да покажат резултат. Ако имате нужда от по-голяма точност от тази, можете да използвате функцията Excel Goal Seek, за да изчислите IRR. При този метод Excel извършва до 32 000 итеративни изчисления, преди да генерира върната стойност. Ето инструкциите за използване на Goal Seek и изчисляване на IRR в Excel:

• Задайте IRR на 11% или нещо друго ръчно.

Изчисляване на NPV в Excel

• Изчислете NPV под IRR, като използвате тази формула:

=NPV(C9,C3:C8)+C2

• Сега щракнете върху раздела Данни и след това изберете Анализ какво-ако в групата команди Прогноза в менюто на лентата на Excel.
• Изберете Търсене на цел от контекстното меню, което се появява.
• Тук клетката Set трябва да бъде стойността на NPV. След това стойността To трябва да бъде 0.

Изпълнете търсене на цел, за да изчислите IRR в Excel

• Задайте чрез промяна на клетката на стойността на IRR.
• Просто трябва да въведете препратките към клетките, както е показано на изображението.

Резултати от Goal Seek за изчисляване на IRR в Excel

• Сега щракнете върху OK , за да изчислите IRR чрез Goal Seek.
• Според текущия набор от данни, NPV ще бъде 0,00, ако IRR е 12%.

При метода Goal Seek всъщност не е необходимо да изчислявате IRR от паричния поток и сумата на инвестицията. Вместо това задавате очаквана IRR в клетката IRR и я използвате, за да изчислите NPV, като използвате паричен поток и сума на инвестицията. След това използвате способността за итеративно изчисление на Excel, за да предвидите кога NPV стане 0,00 при коя стойност на IRR.

Съвети за правилно изчисляване на IRR в Excel

Запомнете тези съвети и факти, когато започнете да изчислявате IRR в Excel, за да получите надеждни проценти на IRR:

• Аргументът за частта Стойности трябва да съдържа доход (положителен паричен поток) и разход (отрицателен паричен поток), за да работи функцията точно.
• Функцията IRR ще обработва само числа в аргумента Стойности. Той ще игнорира всички други елементи като логически стойности, празни клетки и текстове.
• Трябва да има редовни интервали на паричните потоци като седмични, месечни, тримесечни, годишни и т.н. Не е задължително паричните потоци да са еднакви в доларова стойност.
• Уверете се, че сте подготвили таблицата на паричните потоци в хронологичен ред. Например, ако плащате $100 всеки месец, тогава подредете стойностите на паричните потоци и техните дати от януари, февруари, март и т.н.
• При прости изчисления на IRR не е задължително да въведете аргумент Guess.
• Когато използвате стойност на Guess, формулата за IRR ще покаже стойност, най-близка до аргумента Guess. Ако показва #NUM! Грешка, променете стойността на аргумента Guess.

Какви са ограниченията на IRR в Excel?

Преди да можете да приложите стойността на IRR, изчислена в Excel в ситуации от реалния свят, вие също трябва да знаете присъщите ограничения на формулата за вътрешна норма на възвръщаемост в Excel:

• IRR е израз на ROI в проценти. Не отчита абсолютната стойност. По този начин може да покаже по-висок процент на възвръщаемост за проект с ниска стойност в долари. Въпреки това, рисковите капиталисти и индивидуалните инвеститори търсят проекти с по-висока стойност в долари, въпреки че процентът на възвръщаемост на инвестициите е по-малък от малките проекти.
• Формулата за IRR връща стойност, като се има предвид, че проектът ще реинвестира своя капитал или паричен поток със същата скорост като изчислената IRR. Това не винаги е възможно, тъй като нивата на възвръщаемост варират много на капиталовия пазар.
• Ако инвестиционният план или проект включва редуване на положителни и отрицателни парични потоци, тогава може да получите повече от една IRR за един и същ проект.

Заключение

Сега знаете как да изчислявате IRR в Excel, като използвате различни специални синтаксиси на IRR. Освен това научихте как да използвате Excel Goal Seek за изчисляване на IRR в Excel. Ако съм пропуснал някой друг метод, не забравяйте да го споменете в полето за коментари по-долу.

След това  как да използвате формулата на Excel IF-THEN .