IRR - што гэта такое і як разлічыць у Excel?

Праграма Excel першапачаткова была створана для палягчэння разлікаў у многіх сферах, уключаючы бізнес. Выкарыстоўваючы яе магчымасці, можна хутка вырабляць складаныя вылічэнні, у тым ліку для прагназавання даходнасці тых ці іншых праектаў. Напрыклад, Excel дазваляе досыць лёгка разлічыць IRR праекта. Як гэта зрабіць на практыцы, раскажа гэты артыкул.

Што такое IRR

Такі абрэвіятурай пазначаюць ўнутраную норму прыбытковасці (ВНД) канкрэтнага інвестыцыйнага праекта. Гэты паказчык часта ўжываецца з мэтай супастаўлення прапаноў па перспектыве прыбытковасці і росту бізнэсу. У лікавым выразе IRR - гэта працэнтная стаўка, пры якой абнуляецца прыведзеная кошт усіх грашовых патокаў, неабходных для рэалізацыі інвестыцыйнага праекта (пазначаецца NPV або ЧПС). Чым ВНД вышэй, тым больш перспектыўным з'яўляецца інвестыцыйны праект.

як ацэньваць

Высветліўшы ВНД праекта, можна прыняць рашэнне аб яго запуску або адмовіцца ад яго. Напрыклад, калі збіраюцца адкрыць новы бізнес і мяркуецца прафінансаваць яго за кошт пазыкі, ўзятай з банка, то разлік IRR дазваляе вызначыць верхнюю дапушчальную мяжу працэнтнай стаўкі. Калі ж кампанія выкарыстоўвае больш адной крыніцы інвесціравання, то параўнанне значэння IRR з іх коштам дасць магчымасць прыняць абгрунтаванае рашэнне аб мэтазгоднасці запуску праекта. Кошт больш аднаго крыніц фінансавання разлічваюць па, так званай формуле ўзважанай арыфметычнай сярэдняй. Яна атрымала назву «Кошт капіталу» або «Кошт авансаваць капіталу» (пазначаецца СС).

Выкарыстоўваючы гэты паказчык, маем:

калі:

• IRR> СС, то праект можна запускаць;
• IRR = СС, то праект не прынясе ні прыбытку, ні страт;
• IRR <СС, то праект загадзя стратны і ад яго варта адмовіцца.

Як разлічыць IRR ўручную

Задоўга да з'яўлення кампутараў ВНД вылічалі, вырашаючы досыць складанае раўнанне (гл. Унізе).

У яе ўваходзяць наступныя велічыні:

• CFt - грашовы паток за прамежак часу t;
• IC - фінансавыя ўкладанні на этапе запуску праекта;
• N - сумарны лік інтэрвалаў.

Без адмысловых праграм разлічыць IRR праекта можна, выкарыстоўваючы метад паслядоўнага набліжэння або ітэрацый. Для гэтага папярэдне неабходна падбіраць бар'ерныя стаўкі такім чынам, каб знайсці мінімальныя значэння ЧПС па модулю, і ажыццяўляюць апраксімацыю.

Рашэнне метадам паслядоўных набліжэнняў

Перш за ўсё, прыйдзецца перайсці на мову функцый. У такім кантэксце пад IRR будзе разумецца такое значэнне даходнасці r, пры якой NPV, быўшы функцыяй ад r, становіцца роўная нулю.

Іншымі словамі, IRR = r такому, што пры падстаноўцы ў выраз NPV (f (r)) яно абнуляецца.

Цяпер вырашым сфармуляваную задачу метадам паслядоўных набліжэнняў.

Пад ітэрацыі прынята разумець вынік паўторнага прымянення той ці іншай матэматычнай аперацыі. Пры гэтым значэнне функцыі, вылічанае на папярэднім кроку, падчас наступнага становіцца яе жа аргументам.

Разлік паказчыка IRR ажыццяўляецца ў 2 этапы:

• вылічэнне IRR пры крайніх значэннях нармальнай даходнасці r1 і r2 такіх, што r1
• разлік гэтага паказчыка пры значэннях r, блізкіх да значэння IRR, атрыманы ў вынiку ажыццяўлення папярэдніх вылічэнняў.

Пры вырашэнні задачы r1 і r2 выбіраюцца такім чынам, каб NPV = f (r) усярэдзіне інтэрвалу (r1, r2) мяняла сваё значэнне з мінусу на плюс ці наадварот.

Такім чынам, маем формулу разліку паказчыка IRR ў выглядзе выразы, прадстаўленага ніжэй.

З яе вынікае, што для атрымання значэння IRR патрабуецца папярэдне вылічыць ЧПС пры розных значэннях% -ай стаўкі.

Паміж паказчыкамі NPV, PI і СС маецца наступная ўзаемасувязь:

• калі значэнне NPV станоўчае, то IRR> СС і PI> 1;
• калі NPV = 0, тады IRR = СС і PI = 1;
• калі значэнне NPV адмоўнае, то IRR <СС і PI <1.

графічны метад

Зараз, калі вы ведаеце, што гэта такое IRR і як разлічыць яго ўручную, варта пазнаёміцца і з яшчэ адным метадам вырашэння гэтай задачы, які быў адным з найбольш запатрабаваных да таго, як з'явіліся кампутары. Гаворка ідзе аб графічным варыянце вызначэння IRR. Для пабудовы графікаў патрабуецца знайсці значэнне NPV, падстаўляючы ў формулу яе разліку розныя значэнні стаўкі дыскантавання.

Як разлічыць IRR ў Excel

Як бачым, ўручную знаходзіць ВНД - досыць складана. Для гэтага патрабуюцца пэўныя матэматычныя веды і час. Нашмат прасцей даведацца, як разлічыць IRR ў Excel (прыклад гл. Ніжэй).

Для гэтай мэты ў вядомым таблічным працэсары Microsoft ёсць адмысловая ўбудаваная функцыя для разліку ўнутранай стаўкі дысконту - ВСД, якая і дае шуканае значэнне IRR ў працэнтных выражэнні.

сінтаксіс

IRR (што гэта такое і як разлічыць неабходна ведаць не толькі спецыялістам, але і шараговым пазычальнікам) у Excel пазначаецца, як ВСД (Значэнні; Здагадка).

Разгледзім падрабязней яго сінтаксіс:

• Пад значэннямі разумеецца масіў або спасылка на вочкі, якія ўтрымліваюць колькасці, для якіх неабходна падлічыць ВСД, улічваючы ўсе патрабаванні, названыя для гэтага паказчыка.
• Здагадка ўяўляе сабой велічыню, пра якую вядома, што яна блізкая да выніку IRR.

У Microsoft Excel для разліку ВСД выкарыстоўвае вышэйапісаны метад ітэрацый. Ён запускаецца са значэння «Здагадка», і выконвае цыклічныя вылічэнні, да атрымання выніку з дакладнасцю 0,00001%. Калі убудаваная функцыя ВСД не выдае вынік пасля здзяйснення 20 спробаў, тады таблічны працэсар выдаецца значэнне памылку, пазначаную, як «# ЛІК!».

Як паказвае практыка, у большасці выпадкаў адсутнічае неабходнасці задаваць значэнне для велічыні «Здагадка». Калі яно апушчана, то працэсар лічыць яго роўным 0,1 (10%).

Калі убудаваная функцыя ВСД вяртае памылку «# ЛІК!» Або калі атрыманы вынік не адпавядае чаканням, то можна зрабіць вылічэнні зноў, але ўжо з іншым значэннем для аргументу «Здагадка».

Рашэнні ў "Эксэля": варыянт 1

Паспрабуем вылічыць IRR (што гэта такое і як разлічыць гэтую велічыню ўручную вам ужо вядома) з дапамогай убудаванай функцыі ВСД. Выкажам здагадку, у нас ёсць дадзеныя на 9 гадоў наперад, якія занесены ў табліцу Excel.

	A	B	C	D	E
1	Перыяд (год) Т	першапачатковыя выдаткі	грашовы даход	грашовы выдатак	грашовы паток
2	0	200 000 р.	- р.	200000 р.	200000 р.
3	1	- р.	50000 р.	30000 р.	20000 р.
4	2	- р.	60000 р.	33000 р.	27000 р.
5	3	- р.	45000 р.	28000 р.	17000 р.
6	4	- р.	50000 р.	15000 р.	35000 р.
7	5	- р.	53000 р.	20000 р.	33000 р.
8	6	- р.	47000 р.	18000 р.	29000 р.
9	7	- р.	62000 р.	25000 р.	37000 р.
10	8	- р.	70000 р.	30 000 р.	40000 р.
11	9	- р.	64000 р.	33000 р.	31000 р.
12				IRR	6%

У вочка з адрасам Е12 ўведзена формула «= ВСД (Е3: Е2)». У выніку яе прымянення таблічны працэсар выдаў значэнне 6%.

Рашэнні ў "Эксэля": варыянт 2

Па дадзеных, прыведзеных у папярэднім прыкладзе, вылічыць IRR дапамогай надбудовы «Пошук рашэнняў».

Яна дазваляе ажыццявіць пошук аптымальнага значэння ВСД для NPV = 0. Для гэтага неабходна разлічыць ЧПС (або NPV). Ён роўны суме дыскантаваных грашовых патокаў па гадах.

	A	B	C	D	E	F
1	Перыяд (год) Т	першапачатковыя выдаткі	грашовы даход	грашовы выдатак	грашовы паток	Дысконтныя грашовы паток
2	0	200000 р.	- р.	200000 р.	200000 р.
3	1	- р.	50000 р.	За 20 000 р.	20000 р.	20000 р.
4	2	- р.	60000 р.	20000 р.	27000 р.	27000 р.
5	3	- р.	45000 р.	20000 р.	17000 р.	17000 р.
6	4	- р.	50000 р.	20000 р.	35000 р.	35000 р.
7	5	- р.	53000 р.	20000 р.	33000 р.	33 000 р.
8	6	- р.	47000 р.	20000 р.	29000 р.	29000 р.
9	7	- р.	62000 р.	20000 р.	37000 р.	37000 р.
10	8	- р.	70000 р.	20000 р.	40000 р.	40000 р.
11	9	- р.	64000 р.	20000 р.	31000 р.	31000 р.
12					NPV	69000 р.
					IRR

Дыскантаваных грашовы паток вылічаецца па формуле «= E5 / (1 + $ F $ 11) ^ A5».

Тады для NPV атрымліваецца формула «= сум (F5: F13) -B7».

Далей неабходна адшукаць на аснове аптымізацыі з дапамогай надбудовы «Пошук рашэнняў» такое значэнне стаўкі дыскантавання IRR, пры якім NPV праекта стане роўным нулю. Для дасягнення гэтай мэты патрабуецца адкрыць у галоўным меню раздзел «Дадзеныя» і знайсці там функцыю «Пошук рашэнняў».

У якое з'явілася акне запаўняюць запісы з "Усталяваць мэтавую вочка», паказаўшы адрас формулы разліку NPV, т. Е. + $ F $ 16. затым:

• выбіраюць значэнне для дадзенай ячэйкі "0";
• у акно «Змены вочкі» ўносяць параметр + $ F $ 17, т. е. значэнне ўнутранай нормы прыбытковасці.

У выніку аптымізацыі таблічны працэсар запоўніць пустое вочка з адрасам F17 значэннем стаўкі дыскантавання. Як відаць з табліцы, вынік роўны 6%, што цалкам супадае з разлікам таго ж параметру, атрыманых з выкарыстаннем убудаванай формулы ў Excel.

MIRR

У некаторых выпадках патрабуецца разлічыць мадыфікаваную ўнутраную норму прыбытковасці. Ён адлюстроўвае мінімальны ВНД праекта ў выпадку ажыццяўлення рэінвестыцыі. Формула разліку MIRR выглядае наступным чынам.

дзе:

• MIRR - унутраная норма даходнасці інвестпраекта;
• COFt - адток з праекта грашовых сродкаў ва часавыя перыяды t;
• CIFt - прыток фінансаў;
• r - стаўка дыскантавання, якая роўная сярэднеўзважанай кошту укладваць капітал WACC;
• d -% -ая стаўка рэінвеставання;
• n - колькасць часовых перыядаў.

Вылічэнне MIRR ў таблічным працэсары

Пазнаёміўшыся са ўласцівасцямі IRR (што гэта такое і як разлічыць яе значэнне графічна вам ужо вядома), можна лёгка навучыцца вылічаць мадыфікаваную ўнутраную норму прыбытковасці ў Excel.

Для гэтага ў таблічным працэсары прадугледжаная адмысловая ўбудаваная функцыя МВСД. Возьмем усё той жа, ужо разгляданы прыклад. Як разлічыць IRR па ім, ужо разглядалася. Для MIRR табліца выглядае наступным чынам.

	A	B	C	D	E
1	Памер крэдыту ў працэнтах		10%
2	узровень рэінвеставання		12%
3	Перыяд (год) Т	першапачатковыя выдаткі	грашовы даход	грашовы выдатак	грашовы паток
4	0	200000 р.	- р.	200000 р.	200000 р.
5	1	- р.	50000 р.	30000 р.	20000 р.
6	2	- р.	60000 р.	33000 р.	27000 р.
7	3	- р.	45000 р.	28000 р.	17000 р.
8	4	- р.	50000 р.	15000 р.	35000 р.
9	5	- р.	53000 р.	20000 р.	33000 р.
10	6	- р.	47000 р.	18000 р.	29000 р.
11	7	- р.	62000 р.	25000 р.	37000 р.
12	8	- р.	70000 р.	30000 р.	40000 р.
13	9	- р.	64000 р.	33000 р.	31000 р.
14				MIRR	9%

У вочка Е14 ўводзіцца формула для MIRR «= МВСД (E3: E13; C1; C2)».

Перавагі і недахопы выкарыстання паказчыка ўнутранай нормы прыбытковасці

Метад ацэньвання перспектыўнасці праектаў, з дапамогай вылічэнні IRR і параўнання з велічынёй кошту капіталу не з'яўляецца дасканалым. Аднак у яго ёсць пэўныя перавагі. Да іх адносяцца:

• Магчымасць параўнання розных інвестпраектаў па ступені іх прывабнасці і эфектыўнасці выкарыстання ўкладзеных капіталаў. Напрыклад, можна ажыццявіць параўнанне з прыбытковасцю ў выпадку безрызыкоўнымі актываў.
• Магчымасць параўнаць розныя інвестыцыйныя праекты, якія маюць розны гарызонт інвеставання.

У той жа час відавочныя недахопы гэтага паказчыка. Да іх адносяцца:

• няздольнасць паказчыка ўнутранай нормы даходнасці адлюстраваць памер рэінвеставання ў праект;
• складанасць прагназавання грашовых плацяжоў, так як на іх велічыню ўплывае мноства фактараў рызыкі, аб'ектыўная ацэнка якіх уяўляе вялікую складанасць;
• няздольнасць адлюстраваць абсалютны памер даходу (выручаных грашовых сродкаў) ад велічыні інвестыцыі.

Звярніце ўвагу! Апошні недахоп быў дазволены шляхам вядзення паказчыка MIRR, пра які было падрабязна расказана вышэй.

Як уменне разлічваць IRR можа спатрэбіцца пазычальнікам

Па патрабаванням расійскага ЦБ, усе банкі, якія дзейнічаюць на тэрыторыі РФ, абавязаны ўказваць эфектыўную працэнтную стаўку (ЭПС). Яе можа самастойна разлічыць любы пазычальнік. Для гэтага яму прыйдзецца скарыстацца таблічны працэсар, напрыклад, Microsoft Excel і выбраць убудаваную функцыю ВСД. Для гэтага вынік у той жа вочку варта памножыць на перыяд плацяжоў Т (калі яны штомесячныя, то Т = 12, калі дзённыя, то Т = 365) без акруглення.

Цяпер, калі вы ведаеце, што такое ўнутраная норма даходнасці, таму, калі вам скажуць: «Для кожнага з ніжэйпералічаных праектаў разлічыце IRR», у вас не ўзнікне якіх-небудзь цяжкасцяў.