IRR - ntoni na nendlela babale Excel?

Iprogram ye-Excel wayeyalelwe wadalwa ukuze lula ukusonjululwa kwiinkalo ezininzi, kubandakanywa ishishini. Ukusebenzisa iimpawu zayo, ungenza ngokukhawuleza ukwenza izibalo ezintsonkothileyo, kuquka ukuqikelela lokubuya ezi projekthi. Umzekelo, Excel ivumela ukuba ukubala lula ukuba IRR le projekthi. Indlela yokwenza oko practice, eli nqaku liza kukuxelela.

Yintoni IRR

Le abbreviation ukuchaza isilinganiso sangaphakathi lokubuya (IRR) iprojekthi zotyalo-mali ethile. Esi salathisi lidla isetyenziselwa injongo ngokuthelekisa izindululo malunga nenzuzo kunye nokukhula nethemba. Xa zisephezulu, IRR - na izinga lenzala kwakhona ixabiso elikhoyo zonke nokuphuma kwemali efunekayo ukulungiselela ukuqaliswa kweprojekthi investment (NPV okanye NPV). I-IRR iphezulu kwezithembiso ngakumbi iprojekthi zotyalo-mali.

indlela yokwazi

Ukufumanisa ukuba IRR kweprojekthi, ungenza isigqibo ukuze isebenze okanye ke asikhabe. Umzekelo, ukuba aza kuvula ishishini elitsha kwaye kulindeleke ukuba imali mboleko yayo ngenxa ezithathwe ebhankini, ngoko ke ubalo IRR ukumisela umda nokunyamezeleka eliphezulu izinga lenzala. Ukuba inkampani usebenzisa ngaphezu kwesinye umthombo lotyalo-mali, uthelekiso amaxabiso IRR kunye iindleko zabo ziya kwenza kube lula ukwenza isigqibo sokuba iqalise le projekthi. Ixabiso komthombo mali olunye ibalwa ekuthiwa ifomula bemfuno arithmetic ukuthini. Yayibizwa ngokuba "indleko yekhapitali" okanye "Le ixabiso kwikomkhulu eliphezulu" (zibonakaliswe SS).

Ukusebenzisa le nani, siye:

ukuba:

• IRR> CC, ngoko iprojekthi Ingaqhaliswa;
• IRR = CC, ngoko iprojekthi akayi azise nayiphi na ingeniso okanye ilahleko;
• IRR

Indlela ukubala IRR ngesandla

Kudala phambi kokuvela iikhompyutha GNI ibalwa ekusombululeni inxaki ngu kunokuba nzima (bona. Ezantsi).

Oku kuquka ezi zithethe zixabisekileyo zilandelayo:

• CFt - flow cash ngexesha t kunelixesha lokuphumla;
• IC - utyalo ngexesha lesigaba lokwazisa le projekthi;
• N - lilonke inani ekhefu.

Ngaphandle software eyodwa ukubala IRR le projekthi kunokwenzeka, usebenzisa indlela kelelo ezilandelelanayo okanye uniko. Ngokuba lo isithintelo pre kufuneka kukhethwe rate ukuze ufumane amaxabiso Ubuncinane NPV lomnqongo kwaye enze isilinganiselo.

Ewiliko ngu approximations ezilandelelanayo

Okokuqala, kufuneka uye kwimisebenzi zolwimi. Kule meko, i-IRR ziya kuqondwa uveze r ixabiso apho NPV, ukuba umsebenzi we r, iba zero.

Ngamanye amazwi, IRR = r kangangokuba xa endaweni kwi ibinzana NPV (f (r)) iya zeroed.

Ngoku ukusombulula umsebenzi yaqulunqa indlela approximations ezilandelelanayo.

Ngu uniko ngokuqhelekileyo kuqondwa ngesiphumo sesicelo okuphindwayo umsebenzi zezibalo. Ixabiso umsebenzi ebalwe inyathelo elidlulileyo, ixesha elizayo iba yingxoxo efanayo.

Le IRR Isalathisi oluqhutywa zigaba 2:

• izibalo IRR lamanani egqithiseleyo nezivuno oluqhelekileyo R1 R2 yokuba R1
• ekubaleni kwisalathiso amaxabiso r kufutshane ixabiso IRR afunyenwe ngenxa ukubala odlulileyo.

Ekusombululeni ingxaki R1 R2 mazikhethwe ngendlela yokuba NPV = f (r) yatshintsha ixabiso layo ukusuka thabatha no dibanisa okanye ngenye indlela ngaphakathi kuluhlu (R1, R2).

Ngaloo ndlela siya kuba Ngokwefomyula yokubala i ibinzana figure IRR eboniswe ngezantsi.

Oku kulandela ukuba ukufumana amaxabiso ezifunekayo IRR precomputed amaxabiso ezahlukeneyo NPV% th rate.

Phakathi izalathi si zokunikwa NPV, PI kunye SS kukho ubudlelwane ilandelayo:

• NPV ukuba ixabiso HIV, ngoko ke IRR> SS kunye PI> 1;
• ukuba NPV = 0, ngoko ke IRR = akorobhe kunye PI = 1;
• ukuba ixabiso NPV lelikhabayo, i-IRR

indlela lomzobo

Ke kaloku, ukuba uyazi ukuba yintoni na nendlela ukubala IRR ngesandla, kubalulekile ukuba aqhelane enye indlela yokusombulula le ngxaki, obengowabaya kakhulu ethandwa phambi kwakukho iikhompyutha. Sithetha malunga nenguqulelo graphic kwenkcazelo IRR. YeTshathi kufuneka ukufumana ixabiso NPV, umjikelezi ifomula yayo zokubala amaxabiso ezahlukeneyo rate isaphulelo.

Indlela ukubala IRR in Excel

Njengoko ubona, ngesandla ufumane GNI - kunzima. Oku kufuna ulwazi oluthile lwemathematika kunye nexesha. kulula kakhulu ukuba bafunde ukubala indlela IRR ku Excel (jonga umzekelo. ngezantsi).

Ukulungiselela le njongo, xa isicelo spreadsheet Microsoft umsebenzi eyakhelwe-ngaphakathi ekhethekileyo ukubala lwangaphakathi izinga isaphulelo - IRR, enika ixabiso IRR efunekayo ngokwalo ipesenti.

syntax

IRR (yintoni na yaye ukubala njani oko kuyimfuneko ukwazi iingcali nje kuphela kodwa kubaboleki zesiqhelo) e Excel okhethiweyo njengo IRR (Values; Guess).

Makhe sihlolisise ngokweenkcukacha lwesivakalisi:

• Phantsi amaxabiso uluhlu wayeqonda okanye kubhekiswa iiseli eziqulathe inani apho ukubala VSD, kokuqwalasela zonke iimfuno ezichazwe kule parameter.
• Le ngqikelelo ixabiso eyaziwa ukuba ukusondela IRR result.

Ngo-Microsoft Excel ngenxa ekubaleni IRR usebenzisa indlela apha ngasentla bophindo. Yena uqala ezixabisa "vekeleke", yaye wenza ubalo elibujikele ukuvelisa isiphumo kunye nokuchaneka 0,00001%. Ukuba eyakhelwe-ngaphakathi HDR ayisebenzi ngenxa emva kokuba amalinge-20, ngoko ke spreadsheet ekhutshwe impazamo ixabiso, wazibiza ngokuba "# NUM!".

Njengoko practice ibonisa, kwiimeko ezininzi akukho mfuneko ukukhankanya ixabiso ubukhulu le "vekeleke xona". Ukuba lishiyiwe, kumqhubekekisi iqwalasela ukuba 0.1 (10%).

Ukuba umsebenzi IRR eyakhelwe-ngaphakathi ubuyisela impazamo "# NUM!" Okanye xa ngaba isiphumo singesiso njengoko kulindeleke, unako ukwenza izibalo kwakhona, kodwa ixabiso eyahlukileyo i "vekeleke 'ingxabano.

Izigqibo "Eksele": Indlela 1

Zama ukubala IRR (ntoni na nendlela yokubala eli xabiso ngesandla sele ukwazi) lisetyana eyakhelwe-ngaphakathi HDR. Masithi kufuneka data iminyaka 9 kwangaphambili, apho bangena spreadsheet Excel.

	A	B	C	D	E
1	Ixesha (unyaka) T	iindleko zokuqala	ingeniso yemali	cash flow	cash flow
2	0	200 000 bagcobe.	- p.	200000 p.	200000 p.
3	1	- p.	50000 p.	30000 on p.	20000 on p.
4	2	- p.	60000 p.	33000 p.	27000 p.
5	3	- p.	45000 p.	28000 p.	17000 p.
6	4	- p.	50000 p.	15000 on p.	35000 p.
7	5	- p.	53000 p.	20000 on p.	33000 p.
8	6	- p.	47000 p.	18000 on p.	29000 p.
9	7	- p.	62000 p.	25000 p.	37000 p.
10	8	- p.	70000 p.	30 000 p.	40000 p.
11	9	- p.	64000 p.	33000 p.	31000 p.
12				IRR	6%

Iseli ne Idilesi E12 laqalisa ifomula "= VSD. (E3: E2)" Ngenxa yayo iprosesa yesicelo itafile ovelisa ixabiso le-6%.

Izigqibo "Eksele": Version 2

Ngokutsho idatha enikwe bekunjalo kumzekelo wokuqala, ukubala IRR kuthetha "ukukhangela izisombululo" superstructure.

It ikuvumela ukuba ukukhangela ixabiso eliphezulu IRR NPV = 0. Ukuze wenze oku, kufuneka ukubala NPV (okanye NPV). Kuyinto elingana umdibaniso nokuphuma kwemali ngesaphulelo ukusuka komnye unyaka ukuya komnye.

	A	B	C	D	E	F
1	Ixesha (unyaka) T	iindleko zokuqala	ingeniso yemali	cash flow	cash flow	Discount Cash Flow
2	0	200000 p.	- p.	200000 p.	200000 p.
3	1	- p.	50000 p.	20 000 p.	20000 on p.	20000 on p.
4	2	- p.	60000 p.	20000 on p.	27000 p.	27000 p.
5	3	- p.	45000 p.	20000 on p.	17000 p.	17000 p.
6	4	- p.	50000 p.	20000 on p.	35000 p.	35000 p.
7	5	- p.	53000 p.	20000 on p.	33000 p.	33 000 p.
8	6	- p.	47000 p.	20000 on p.	29000 p.	29000 p.
9	7	- p.	62000 p.	20000 on p.	37000 p.	37000 p.
10	8	- p.	70000 p.	20000 on p.	40000 p.	40000 p.
11	9	- p.	64000 p.	20000 on p.	31000 p.	31000 p.
12					NPV	69000 p.
					IRR

cash flow ngesaphulelo ibalwa yi kwifomula «= E5 / (1 + $ F $ 11) ^ A5».

Emva koko, lo Ifomula NPV "= SUM (F5: F13) -B7».

Okulandelayo, kufuneka ufumane ngokusekelwe ifoun ngokusebenzisa wokongeza i-ons "Solver" lixabiso ireyithi isaphulelo IRR apho NPV yale projekthi ilingana zero. Ufuna ukuvula "Data" candelo kwi menu engundoqo ukuze kufikelelwe kule njongo kwaye ufumane umsebenzi "Khangela izisombululo."

Kwi window elivelayo, gcwalisa umgca "iseli Seta ekujoliswe", ngokucacisa idilesi NPV ubalo ifomula, t. E. + $ F $ 16. ngoko:

• kukhethwe inani le "0" iiseli;
• i window "utshintsho iseli" ukwenza i parameter + $ F $ 17, t. e. ixabiso isilinganiso sangaphakathi ukubuya.

Ngenxa noun spreadsheet uzalisa iseli ezingenanto kunye nedilesi ixabiso F17 ye izinga isaphulelo. Njengoko kubonakala etafileni, umphumo ilingana-6%, nto leyo ingqamana ekubaleni parameter efanayo bafumana usebenzisa ifomula Excel eyakhelwe-ngaphakathi.

MIRR

Kwezinye iimeko kuyimfuneko ukubala ukuguqula isilinganiso sangaphakathi ukubuya. It ibonisa IRR ubuncinane iprojekthi xa utyale. Ifomula yokubala MIRR ngolu hlobo lulandelayo.

apho:

• MIRR - isilinganiso sangaphakathi imbuyekezo yotyalo-mali;
• COFt - ukuphuma mali ngexesha le projekthi ixesha t;
• CIFt - yokufika ezezimali;
• r - izinga isaphulelo, nto leyo ilingana ne-avareji yeendleko elilinganiswe ngokomyinge eyinkunzi imali yi-WACC;
• d -% rate Star uyityala;
• n - inani lamathuba ixesha.

Calculation MIRR in iprosesa tabular

Uqhelene ne iipropati IRR (yintoni na yaye ukubala njani ixabiso layo ngokwegrafu sele ukwazi), unako lula ukufunda ukubala indlela ukuguqula isilinganiso sangaphakathi abuyele Excel.

Kuba esi sicelo spreadsheet, okhethekileyo eyakhelwe-ngaphakathi umsebenzi MIRR. Thabatha bonke efanayo, sele ingqalelo umzekelo. Indlela ukubala IRR phezu kwayo, ukuba sele ingqalelo. MIRR le theyibhile ngolu hlobo lulandelayo.

	A	B	C	D	E
1	imali mboleko njengepesenti		10%
2	izinga utyale		12%
3	Ixesha (unyaka) T	iindleko zokuqala	ingeniso yemali	cash flow	cash flow
4	0	200000 p.	- p.	200000 p.	200000 p.
5	1	- p.	50000 p.	30000 on p.	20000 on p.
6	2	- p.	60000 p.	33000 p.	27000 p.
7	3	- p.	45000 p.	28000 p.	17000 p.
8	4	- p.	50000 p.	15000 on p.	35000 p.
9	5	- p.	53000 p.	20000 on p.	33000 p.
10	6	- p.	47000 p.	18000 on p.	29000 p.
11	7	- p.	62000 p.	25000 p.	37000 p.
12	8	- p.	70000 p.	30000 on p.	40000 p.
13	9	- p.	64000 p.	33000 p.	31000 p.
14				MIRR	9%

Iseli yaziswa E14 formula ye MIRR «= MIRR (E3: E13; C1; C2)».

Izinto eziluncedo nezingeloncedo zokusebenzisa kwisalathisi isilinganiso sangaphakathi lokubuya

Indlela ulinganisele amathemba kweeprojekthi ngokuthi yokubala IRR kwaye kuthelekiswa nexabiso indleko yekhapitali ayikho egqibeleleyo. Nakuba kunjalo, ukuba ngaba banamalungelo athile. Ezi ziquka:

• Ukukwazi ukuthelekisa botyalo-mali abohlukeneyo ngokwemiqathango ezinomtsalane zisebenzise eyiyo eyinkunzi imali. Umzekelo, unako ukwenza uthelekiso kunye isivuno kwimeko asethi zomngcipheko-free.
• Ukukwazi ukuthelekisa iiprojekthi zotyalo ezahlukeneyo kunye esifaneleyo yotyalo-mali eyahlukeneyo.

Ngelo xesha ubuthathaka ezicacileyo zesi salathisi. Ezi ziquka:

• ukusilela isalathisi izinga lwangaphakathi lokubuya ukubonisa isixa utyale kule projekthi;
• kwakunzima okubonisa iintlawulo eziziinkozo, njengoko ixabiso kwabo bachatshazelwa ubungozi ezininzi, uvavanyo njongo leyo nzima kakhulu;
• Ukungaphumeleli ukubonisa isixa ngokupheleleyo ingeniso (imali ezinkozo) malunga nexabiso lotyalo-mali.

Ziqapheleni! ethile Last wasonjululwa ngokugcina isalathisi MIRR, leyo ichazwe ngokwenkcukacha ngasentla.

Njengoko ukukwazi ukubala IRR kunokuba luncedo kubaboleki

Ngokweemfuno ze-Russian Central Bank, zonke iibhanki elisebenza kummandla Russian Federation, iya kubonisa olusebenzayo izinga lenzala (ESR). Kusenokuba ngokuzimeleyo ukubala na umboleki. Ukuze wenze oku ke kuya kufuneka ukusebenzisa spreadsheet, umzekelo, Microsoft Excel, uze ukhethe eyakhelwe-ngaphakathi HDR. Ngenxa yoko iziphumo kwiseli enye ukuze yande yi intlawulo ixesha T (ukuba ngenyanga, ngoko T = 12 ukuba ezijiko-jiko, ngoko T = 365) ngaphandle ngokusondeza.

Ngoku, ukuba uyazi ukuba yintoni na isilinganiso sangaphakathi lokubuya, ngoko xa uthi, "Kuba nganye kwezi projekthi zilandelayo, abale IRR», aninabo nayiphi na ubunzima.